Mybatis的多对一和一对多嵌套查询

115 阅读 0 评论 76 点赞

我是靠谱客的博主开朗美女，这篇文章主要介绍Mybatis的多对一和一对多嵌套查询，现在分享给大家，希望可以做个参考。

嵌套查询

- 建表
- 多对一
- 一对多

建表

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
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注解（需要lombok插件）可以便捷开发，但不容易维护。
@Data 帮我们完成 get，set，toString，无参构造等等方法

复制代码

1
2
3
4
5
6
7
@Data
public class Students {
    private int id ;
    private String name;
    private Teacher teacher;
}

一：老师类

复制代码

1
2
3
4
5
6
@Data
public class Teacher {
    private int id;
    private String name;
}

接口类 StudentsMapper
因为是多对一，多个学生对应一个老师，所以返回类型用List集合

复制代码

1
2
3
4
5
public interface StudentsMapper {
    //得到所有的Students 多对一处理
    List<Students> getStudents();
}

映射器 StudentsMapper.xml
返回结果集，是Students类型的，Students第三个属性是Teacher对象，所以复杂查询嵌套应该使用

复制代码

1
2
<association property="teacher" javaType="teacher">

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--嵌套结果集查询-->
<mapper namespace="com.kai.dao.StudentsMapper">
<!-- 多对一结果集-->
    <resultMap id="getStuTea" type="students">
        <result column="sid" property="id"/>
        <result column="sname" property="name"/>
        <!--复杂嵌套查询 association POJO的属性是对象 collection POJO的属性是集合-->
        <association property="teacher" javaType="teacher">
            <result column="tname" property="name"/>
            <result column="tid" property="id"/>
        </association>

    </resultMap>

    <select id="getStudents" resultMap="getStuTea">
        SELECT s.id sid,s.name sname,t.name tname,t.id tid
        FROM student s,teacher t
        WHERE s.tid = t.id;
    </select>
</mapper>

测试代码：

复制代码

1
2
3
4
5
6
7
8
9
10
11
    //多对一
    @Test
    public void test2(){
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        StudentsMapper mapper = sqlSession.getMapper(StudentsMapper.class);
        List<Students> students = mapper.getStudents();
        for (Students s : students) {
            System.out.println(s);
        }
    }

一对多

复制代码

1
2
3
4
5
6
7
@Data
public class Students2 {
    private int id ;
    private String name;
    private int tid;
}

复制代码

1
2
3
4
5
6
7
@Data
public class Teacher2 {
    private int id;
    private String name;
    private List<Students2> student2;
}

因为一对多，一个老师对应多个学生，返回类型就是一个老师

复制代码

1
2
3
4
5
public interface TeacherMapper {
    Teacher2 getTeaAndStu(@Param("uid") int id);

}

返回结果集，是Teacher2类型的，Teacher2第三个属性是Students集合，所以复杂查询嵌套应该使用

复制代码

1
2
 <collection property="student2"  ofType="Students2">

JavaType和ofType都是用来指定对象类型的
JavaType是用来指定pojo中属性的类型
ofType指定的是映射到list集合属性中pojo的类型

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.kai.dao.TeacherMapper">

<!-- 一对多查询 -->
    <resultMap id="TeacherAndStudents" type="teacher2">
        <result column="tid" property="id"/>
        <result column="tname" property="name"/>
<!--           JavaType和ofType都是用来指定对象类型的
               JavaType是用来指定pojo中属性的类型
               ofType指定的是映射到list集合属性中pojo的类型
-->
        <collection property="student2"  ofType="Students2">
            <result column="sid" property="id"/>
            <result column="sname" property="name"/>
        </collection>

    </resultMap>
    <select id="getTeaAndStu" resultMap="TeacherAndStudents" parameterType="int">
        SELECT s.id sid,s.name sname,t.id tid ,t.name tname
    FROM student s ,teacher t
    WHERE s.tid = t.id AND t.id=#{uid}
    </select>

</mapper>

复制代码

1
2
3
4
5
6
7
8
9
    //一对多
    @Test
    public void test3(){
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
        Teacher2 teaAndStu = mapper.getTeaAndStu(1);
        System.out.println(teaAndStu);
    }