MyBatis基于注解开发(联合查询多对多)

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();
    }
}