mybatis復雜查詢

環境搭建

mysql建表

USE `mybatis`;

CREATE TABLE `teacher`(
    `id` INT(10) NOT NULL,
    `name` VARCHAR(30) DEFAULT NULL,
    PRIMARY KEY(`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO teacher(`id`,`name`) VALUES(1,'郭老師');


CREATE TABLE `student` (
    `id` INT(10) NOT NULL,
    `name` VARCHAR(30) DEFAULT NULL,
    `tid` INT(10) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `fktid` (`tid`),
    CONSTRAINT `fktid` FOREIGN KEY(`tid`) REFERENCES teacher(`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO student(`id`,`name`,`tid`) VALUES(1,'小明','1');
INSERT INTO student(`id`,`name`,`tid`) VALUES(2,'小紅','1');
INSERT INTO student(`id`,`name`,`tid`) VALUES(3,'小張','1');
INSERT INTO student(`id`,`name`,`tid`) VALUES(4,'小李','1');
INSERT INTO student(`id`,`name`,`tid`) VALUES(5,'小王','1');

?

學生物體

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student {
    private int id;
    private String name;
    private Teacher teacher;
}

老師物體

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Teacher {
    private int id;
    private String name;
    private List<Student> students;
}

直接查詢

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.g.dao.StudentMapper">

    <select id="getStudents" resultType="Student">
        select * from mybatis.student
    </select>

</mapper>

?

查詢結果

Student(id=1, name=小明, teacher=null)
Student(id=2, name=小紅, teacher=null)
Student(id=3, name=小張, teacher=null)
Student(id=4, name=小李, teacher=null)
Student(id=5, name=小王, teacher=null)

行程完成，退出碼 0

?
?
?
?
?
?

多對一

第一種、按照結果嵌套處理

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.g.dao.StudentMapper">
    
    <select id="getStudents" resultMap="StudentTeacher2">
        select s.id sid,s.name sname,t.name tname
        from mybatis.student s,mybatis.teacher t
        where  s.tid = t.id
    </select>

    <resultMap id="StudentTeacher2" type="Student">
        <result property="id" column="sid"/>
        <result property="name" column="sname"/>
        <association property="teacher" javaType="Teacher">
            <result property="name" column="tname"/>
        </association>
    </resultMap>

</mapper>

輸出結果

Student(id=1, name=小明, teacher=Teacher(id=1, name=郭老師))
Student(id=2, name=小紅, teacher=Teacher(id=2, name=郭老師))
Student(id=3, name=小張, teacher=Teacher(id=3, name=郭老師))
Student(id=4, name=小李, teacher=Teacher(id=4, name=郭老師))
Student(id=5, name=小王, teacher=Teacher(id=5, name=郭老師))

行程完成，退出碼 0

?
?
?

第二種、按照查詢嵌套處理

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.g.dao.StudentMapper">

    <select id="getTeacher" resultType="Teacher">
        select * from mybatis.teacher where id=#{id}
    </select>

    <resultMap id="StudentTeacher" type="Student">
        <result property="id" column="id"/>
        <result property="name" column="name"/>
        <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
    </resultMap>

    <select id="getStudents" resultMap="StudentTeacher">
        select * from mybatis.student
    </select>

</mapper>

查詢結果

Student(id=1, name=小明, teacher=Teacher(id=1, name=郭老師))
Student(id=2, name=小紅, teacher=Teacher(id=1, name=郭老師))
Student(id=3, name=小張, teacher=Teacher(id=1, name=郭老師))
Student(id=4, name=小李, teacher=Teacher(id=1, name=郭老師))
Student(id=5, name=小王, teacher=Teacher(id=1, name=郭老師))

行程完成，退出碼 0

?
?
?
?

一對多

修改學生物體

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Teacher {
    private int id;
    private String name;
    private int tid;
}

按照結果嵌套處理

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.g.dao.TeacherMapper">
    <select id="getTeachers" resultMap="TeacherStudent">
        select t.id tid, t.name tname, s.id sid, s.name sname
        from mybatis.teacher t,mybatis.student s
        where s.tid = t.id
    </select>

    <resultMap id="TeacherStudent" type="Teacher">
        <result property="id" column="tid"/>
        <result property="name" column="tname"/>
        <collection property="students" ofType="Student">
            <result property="id" column="sid"/>
            <result property="name" column="sname"/>
        </collection>
    </resultMap>

</mapper>

查詢結果

Teacher(id=1, name=郭老師, students=[Student(id=1, name=小明, tid=1), Student(id=2, name=小紅, tid=1), Student(id=3, name=小張, tid=1), Student(id=4, name=小李, tid=1), Student(id=5, name=小王, tid=1)])

行程完成，退出碼 0