Mybatis一对多中的SQL（查询、删除、更新、插入）及表的设计

1.1现实模型

一个Teacher有多个Student，一个Student有一个Teacher，通过Teacher来管理Student

1.2实体类

Teacher

public class Teacher{    private int id;    private String name;    private Set<Student> stus=new HashSet<Student>();}

Student

public class Student{    private int id;    private String name;}

1.3表的设计

teacher

t_id t_name 14 刘继兵

student

s_id s_name tea_id 7 邓超元 14 8 余辉 14

tea_id位student表的外键

1.3.1创建表的SQL

create table teacher(t_id int primary key auto_increment,t_name char(20));create table student(s_id int primary key auto_increment,s_name char(20),tea_id int);

1.3.2添加外键

alter table student add constraint t_s_const foreign key(tea_id) references teacher(t_id) on delete cascade on update cascade;

1.4SQL语句

1.4.1插入

插入单挑数据：insert into teacher(t_name) values(?);插入多条数据：insert into student(s_name,tea_id) values ('xio',1),('dad',2);

1.4.2删除

delete from teacher where t_id=?;

1.4.3更新

update teacher set t_name='jhonson' where t_id=?;

1.4.4查询

select teacher.*, student.s_id,student.s_name from teacher left outer join student on student.tea_id=teacher.t_id where teacher.t_id=?;