sql.txt
#设计一个老师的表格
create table teacher(
tid int(10),
tname varchar(20),
tsex varchar(4),
tage int(3)
) character set utf8;
#添加主键约束
alter table teacher add constraint pk_teacher primary key(tid);
#设计一个学生的表格
create table student(
sid int(10),
sname varchar(20),
ssex varchar(4),
sage int(3)
) character set utf8;
#添加主键约束
alter table student add constraint pk_student primary key(sid);
#设计一张中间表 老师-学生的关系
create table tea_stu(
tid int(10),
sid int(10)
) character set utf8;
#分别设置两个列的外键约束
alter table tea_stu add constraint fk_teacher foreign key(tid) references teacher(tid);
alter table tea_stu add constraint fk_student foreign key(sid) references student(sid);
#设置联合主键
alter table tea_stu add constraint pk_tea_stu primary key(tid,sid);
#添加测试数据
insert into teacher values(1,'zzt','男',18);
insert into teacher values(2,'panda','男',58);
insert into teacher values(3,'艾薇','女',16);
insert into student values(1,'赵一','男',18);
insert into student values(2,'钱一','女',17);
insert into student values(3,'孙一','女',19);
insert into student values(4,'李一','男',18);
insert into student values(5,'周一','男',17);
insert into student values(6,'吴一','女',19);
insert into student values(7,'郑一','女',18);
insert into student values(8,'王一','男',16);
insert into tea_stu values(1,1);
insert into tea_stu values(1,2);
insert into tea_stu values(1,3);
insert into tea_stu values(1,4);
insert into tea_stu values(1,5);
insert into tea_stu values(1,6);
insert into tea_stu values(1,7);
insert into tea_stu values(1,8);
insert into tea_stu values(2,1);
insert into tea_stu values(2,2);
insert into tea_stu values(2,4);
insert into tea_stu values(2,7);
insert into tea_stu values(3,1);
insert into tea_stu values(3,2);
insert into tea_stu values(3,5);
insert into tea_stu values(3,8);
select * from teacher;
select * from student;
select t.*,s.* from teacher t,tea_stu ts,student s where t.tid = ts.tid and ts.sid = s.sid;
Student（一个学生可以有多个授课老师）
package domain;
import java.util.List;
public class Student {
//自有属性
private Integer sid;
private String sname;
private String ssex;
private Integer sage;
//关联属性
private List<Teacher> teacherList;
public Student(){}
public Student(Integer sid, String sname, String ssex, Integer sage, List<Teacher> teacherList) {
this.sid = sid;
this.sname = sname;
this.ssex = ssex;
this.sage = sage;
this.teacherList = teacherList;
}
@Override
public String toString() {
return "Student{" +
"sid=" + sid +
", sname='" + sname + '\'' +
", ssex='" + ssex + '\'' +
", sage=" + sage +
", teacherList=" + teacherList +
'}';
}
public Integer getSid() {
return sid;
}
public void setSid(Integer sid) {
this.sid = sid;
}
public String getSname() {
return sname;
}
public void setSname(String sname) {
this.sname = sname;
}
public String getSsex() {
return ssex;
}
public void setSsex(String ssex) {
this.ssex = ssex;
}
public Integer getSage() {
return sage;
}
public void setSage(Integer sage) {
this.sage = sage;
}
public List<Teacher> getTeacherList() {
return teacherList;
}
public void setTeacherList(List<Teacher> teacherList) {
this.teacherList = teacherList;
}
}
Teacher（一个老师可以教多个学生）
public class Teacher {
//自有属性
private Integer tid;
private String tname;
private String tsex;
private Integer tage;
//关联属性
private List<Student> studentList;
public Teacher(){}
public Teacher(Integer tid, String tname, String tsex, Integer tage, List<Student> studentList) {
this.tid = tid;
this.tname = tname;
this.tsex = tsex;
this.tage = tage;
this.studentList = studentList;
}
@Override
public String toString() {
return "Teacher{" +
"tid=" + tid +
", tname='" + tname + '\'' +
", tsex='" + tsex + '\'' +
", tage=" + tage +
", studentList=" + studentList +
'}';
}
public Integer getTid() {
return tid;
}
public void setTid(Integer tid) {
this.tid = tid;
}
public String getTname() {
return tname;
}
public void setTname(String tname) {
this.tname = tname;
}
public String getTsex() {
return tsex;
}
public void setTsex(String tsex) {
this.tsex = tsex;
}
public Integer getTage() {
return tage;
}
public void setTage(Integer tage) {
this.tage = tage;
}
public List<Student> getStudentList() {
return studentList;
}
public void setStudentList(List<Student> studentList) {
this.studentList = studentList;
}
}
StudentDao
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.mapping.FetchType;
public interface StudentDao {
@Results(
id="selectStudent",
value={
@Result(property = "sid",column = "sid",id=true),
@Result(property = "sname",column = "sname"),
@Result(property = "ssex",column = "ssex"),
@Result(property = "sage",column = "sage"),
@Result(property = "teacherList",javaType = List.class,column = "sid",many = @Many(select="selectTeacher",fetchType = FetchType.LAZY))
}
)
@Select("select * from student where sid = #{sid}")
//一个带着关联查询的selectOne方法
public Student selectOne(Integer sid);
//需要一个辅助方法
@Select("select t.* from tea_stu ts inner join teacher t on ts.tid = t.tid where ts.sid = #{sid}")
public Teacher selectTeacher(Integer sid);
//一个方法 联合查询 所有学生+每个学生对应的所有老师
@Select("select * from student")
@ResultMap("selectStudent") //其中selectStudent是@Results的id
public List<Student> selectAll();
}
TeacherDao
public interface TeacherDao {
//根据老师tid 老师信息+学生信息
@Results(
id="selectTeacher",
value={
@Result(property = "tid",column = "tid",id=true),
@Result(property = "tname",column = "tname"),
@Result(property = "tsex",column = "tsex"),
@Result(property = "tage",column = "tage"),
@Result(property = "studentList",javaType = List.class,column = "tid",many=@Many(select="selectStudent",fetchType = FetchType.LAZY))
}
)
@Select("select * from teacher where tid = #{tid}")
public Teacher selectOne(Integer tid);
//辅助方法
@Select("select s.* from tea_stu ts inner join student s on ts.sid = s.sid where ts.tid = #{tid}")
public Student selectStudent(Integer tid);
@Select("select * from teacher")
@ResultMap("selectTeacher")
public List<Teacher> selectAll();
}
configuration.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/testmybatis?useSSL=false"/>
<property name="username" value="root"/>
<property name="password" value="123456"/>
</dataSource>
</environment>
</environments>
<mappers>
<--扫描dao.TeacherDao下的注解-->
<mapper class="dao.TeacherDao"></mapper>
</mappers>
</configuration>
StudentService
public class StudentService {
private StudentDao dao = MyUtil.getMapper(StudentDao.class,true);
//学生业务类
//需求 给定一个学生的sid编号 查询学生的信息+这个学生选课的老师信息
public Student selectOne(Integer sid){
return dao.selectOne(sid);
}
//所有学生的信息+每个学生对应的老师信息
public List<Student> selectAll(){
return dao.selectAll();
}
}
TeacherService
public class TeacherService {
private TeacherDao dao = MyUtil.getMapper(TeacherDao.class,true);
//老师业务类
//需求 给定一个老师的tid编号 查询老师的信息+这个老师教所有学生的信息
public Teacher selectOne(Integer tid){
return dao.selectOne(tid);
}
public List<Teacher> selectAll(){
return dao.selectAll();
}
}
网友评论