我家小花儿

这个屌丝很懒，什么也没留下！

热门标签

MyBatis实现多表查询_lambdaquerywrapper连表查询

作者：我家小花儿 | 2024-04-09 09:24:33

踩

lambdaquerywrapper连表查询

前言

以下学习内容所需数据表

一、关联查询

一、手动处理映射关系

在数据查询后映射到实体类中时，如果映射的属性名和数据表的字段名不一致时，就会返回null，解决办法是使用resultMap进行配置
手动处理数据库查询字段和封装实体类属性之间的映射关系
1 主键一般使用id属性
2 当属性名和查询出的数据表字段名相同可以不写映射关系
EmpMapper.xml

<mapper namespace="com.xiaohui.mapper.EmpMapper">
    <resultMap id="empMap" type="emp">
        <!--<id property="empno" column="empno"></id>-->
        <result property="name" column="ename"></result>
        <!--<result property="job" column="job"></result>
        <result property="sal" column="sal"></result>
        <result property="hiredate" column="hiredate"></result>
        <result property="mgr" column="mgr"></result>
        <result property="comm" column="comm"></result>
        <result property="deptno" column="deptno"></result>-->
    </resultMap>
    <!--List<Emp> findAll();-->
    <select id="findAll" resultMap="empMap">
        select * from emp
    </select>
</mapper>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

结果：name那列是修改了实体类中的ename，经过手动处理映射关系后正常显示数据

DEBUG - Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@221a3fa4]
DEBUG - ==>  Preparing: select * from emp 
DEBUG - ==> Parameters: 
DEBUG - <==      Total: 14
Emp(empno=7369, name=SMITH, job=CLERK, mgr=7902, hiredate=Wed Dec 17 00:00:00 CST 1980, sal=800.0, comm=null, deptno=20)
Emp(empno=7499, name=ALLEN, job=SALESMAN, mgr=7698, hiredate=Fri Feb 20 00:00:00 CST 1981, sal=1600.0, comm=300.0, deptno=30)
Emp(empno=7521, name=WARD, job=SALESMAN, mgr=7698, hiredate=Sun Feb 22 00:00:00 CST 1981, sal=1250.0, comm=500.0, deptno=30)
Emp(empno=7566, name=JONES, job=MANAGER, mgr=7839, hiredate=Thu Apr 02 00:00:00 CST 1981, sal=2975.0, comm=null, deptno=20)
Emp(empno=7654, name=MARTIN, job=SALESMAN, mgr=7698, hiredate=Mon Sep 28 00:00:00 CST 1981, sal=1250.0, comm=1400.0, deptno=30)
Emp(empno=7698, name=BLAKE, job=MANAGER, mgr=7839, hiredate=Fri May 01 00:00:00 CST 1981, sal=2850.0, comm=null, deptno=30)
Emp(empno=7782, name=CLARK, job=MANAGER, mgr=7839, hiredate=Tue Jun 09 00:00:00 CST 1981, sal=2450.0, comm=null, deptno=10)
Emp(empno=7788, name=SCOTT, job=ANALYST, mgr=7566, hiredate=Sun Apr 19 00:00:00 CDT 1987, sal=3000.0, comm=null, deptno=20)
Emp(empno=7839, name=KING, job=PRESIDENT, mgr=null, hiredate=Tue Nov 17 00:00:00 CST 1981, sal=5000.0, comm=null, deptno=10)
Emp(empno=7844, name=TURNER, job=SALESMAN, mgr=7698, hiredate=Tue Sep 08 00:00:00 CST 1981, sal=1500.0, comm=0.0, deptno=30)
Emp(empno=7876, name=ADAMS, job=CLERK, mgr=7788, hiredate=Sat May 23 00:00:00 CDT 1987, sal=1100.0, comm=null, deptno=20)
Emp(empno=7900, name=JAMES, job=CLERK, mgr=7698, hiredate=Thu Dec 03 00:00:00 CST 1981, sal=950.0, comm=null, deptno=30)
Emp(empno=7902, name=FORD, job=ANALYST, mgr=7566, hiredate=Thu Dec 03 00:00:00 CST 1981, sal=3000.0, comm=null, deptno=20)
Emp(empno=7934, name=MILLER, job=CLERK, mgr=7782, hiredate=Sat Jan 23 00:00:00 CST 1982, sal=1300.0, comm=null, deptno=10)
DEBUG - Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@221a3fa4]
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

二、实现一对一关联查询

在进行关联查询时，我们需要把两张表或者几张表的数据查询后进行实体类的封装，这时需要在主体实体类中添加关联表的实体类属性

@AllArgsConstructor
@NoArgsConstructor
@Data
public class Emp implements Serializable {
    private Integer empno;
    private String ename;
    private String job;
    private Integer mgr;
    private Date hiredate;
    private Double sal;
    private Double comm;
    private Integer deptno;
    // 组合一个Dept对象作为自己的属性
    private Dept dept;
}

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Dept implements Serializable {
    private Integer deptno;
    private String dname;
    private String loc;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

在EmpMapper.xml映射文件中使用resultMap标签进行属性关系映射，这样做的目的是为了查询出来的数据的字段名能匹配到实体类中的属性。

association 封装一对一信息关系的标签
- property emp（实体类）类的属性名
- javaType 用哪个类的对象给属性赋值（关联的实体类）

    <resultMap id="empJoinDept" type="emp">
        <id property="empno" column="empno"></id>
        <result property="ename" column="ename"></result>
        <result property="job" column="job"></result>
        <result property="sal" column="sal"></result>
        <result property="hiredate" column="hiredate"></result>
        <result property="mgr" column="mgr"></result>
        <result property="comm" column="comm"></result>
        <result property="deptno" column="deptno"></result>
        <association property="dept" javaType="dept">
            <id property="deptno" column="deptno"></id>
            <result property="dname" column="dname"></result>
            <result property="loc" column="loc"></result>
        </association>
    </resultMap>
    
   	<select id="findEmpJoinDeptByEmpno" resultMap="empJoinDept">
        select
            *
        from
            emp e
        left join
            dept  d
        on
            e.deptno =d.deptno
        where
            empno = #{empno}
    </select>
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

测试代码：

    @Test
    public void testfindEmpJoinDeptByEmpno(){
        EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
        Emp empJoinDeptByEmpno = mapper.findEmpJoinDeptByEmpno(7499);
        System.out.println(empJoinDeptByEmpno.toString());
    }
1
2
3
4
5
6

结果：

DEBUG - Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@23e84203]
DEBUG - ==>  Preparing: select * from emp e left join dept d on e.deptno =d.deptno where empno = ? 
DEBUG - ==> Parameters: 7499(Integer)
DEBUG - <==      Total: 1
Emp(empno=7499, ename=ALLEN, job=SALESMAN, mgr=7698, hiredate=Fri Feb 20 00:00:00 CST 1981, sal=1600.0, comm=300.0, deptno=30, dept=Dept(deptno=30, dname=SALES, loc=CHICAGO))
1
2
3
4
5

三、实现一对多关联查询

在进行一对多关联查询时，使用list集合进行封装数据，在DeptMapper.xml映射文件中使用collection标签进行一对多配置

collection
- property 实体类的属性名
- ofType 关联的实体类

 <resultMap id="deptJoinEmps" type="dept">
        <id property="deptno" column="deptno"></id>
        <result property="dname" column="dname"></result>
        <result property="loc" column="loc"></result>
        <!--处理一对多关系的标签-->
        <collection property="empList" ofType="emp">
            <!--设置emp本身的八个属性的映射关系-->
            <id property="empno" column="empno"></id>
            <result property="ename" column="ename"></result>
            <result property="job" column="job"></result>
            <result property="sal" column="sal"></result>
            <result property="hiredate" column="hiredate"></result>
            <result property="mgr" column="mgr"></result>
            <result property="comm" column="comm"></result>
            <result property="deptno" column="deptno"></result>
        </collection>
    </resultMap>

    <select id="findDeptJoinEmpsByDeptno" resultMap="deptJoinEmps">
        select
            *
        from
            dept d
        left join
            emp  e
        on
            d.deptno =e.deptno
        where
            d.deptno = #{deptno}
    </select>
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
30

实体类中进行一对多的属性添加操作

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Dept implements Serializable {
    private Integer deptno;
    private String dname;
    private String loc;

    // 组合一个Emp的List集合作为属性
    private List<Emp> empList;
}
1
2
3
4
5
6
7
8
9
10
11

测试代码：

    @Test
    public void testfindDeptJoinEmpsByDeptno(){
        DeptMapper mapper = sqlSession.getMapper(DeptMapper.class);
        Dept deptJoinEmpsByDeptno = mapper.findDeptJoinEmpsByDeptno(20);
        System.out.println(deptJoinEmpsByDeptno);
        List<Emp> empList = deptJoinEmpsByDeptno.getEmpList();
        empList.forEach(System.out::println);
    }
1
2
3
4
5
6
7
8

结果：

DEBUG - Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@305ffe9e]
DEBUG - ==>  Preparing: select * from dept d left join emp e on d.deptno =e.deptno where d.deptno = ? 
DEBUG - ==> Parameters: 20(Integer)
DEBUG - <==      Total: 5
Dept(deptno=20, dname=RESEARCH, loc=DALLAS, empList=[Emp(empno=7369, ename=SMITH, job=CLERK, mgr=7902, hiredate=Wed Dec 17 00:00:00 CST 1980, sal=800.0, comm=null, deptno=20, dept=null), Emp(empno=7566, ename=JONES, job=MANAGER, mgr=7839, hiredate=Thu Apr 02 00:00:00 CST 1981, sal=2975.0, comm=null, deptno=20, dept=null), Emp(empno=7788, ename=SCOTT, job=ANALYST, mgr=7566, hiredate=Sun Apr 19 00:00:00 CDT 1987, sal=3000.0, comm=null, deptno=20, dept=null), Emp(empno=7876, ename=ADAMS, job=CLERK, mgr=7788, hiredate=Sat May 23 00:00:00 CDT 1987, sal=1100.0, comm=null, deptno=20, dept=null), Emp(empno=7902, ename=FORD, job=ANALYST, mgr=7566, hiredate=Thu Dec 03 00:00:00 CST 1981, sal=3000.0, comm=null, deptno=20, dept=null)])
Emp(empno=7369, ename=SMITH, job=CLERK, mgr=7902, hiredate=Wed Dec 17 00:00:00 CST 1980, sal=800.0, comm=null, deptno=20, dept=null)
Emp(empno=7566, ename=JONES, job=MANAGER, mgr=7839, hiredate=Thu Apr 02 00:00:00 CST 1981, sal=2975.0, comm=null, deptno=20, dept=null)
Emp(empno=7788, ename=SCOTT, job=ANALYST, mgr=7566, hiredate=Sun Apr 19 00:00:00 CDT 1987, sal=3000.0, comm=null, deptno=20, dept=null)
Emp(empno=7876, ename=ADAMS, job=CLERK, mgr=7788, hiredate=Sat May 23 00:00:00 CDT 1987, sal=1100.0, comm=null, deptno=20, dept=null)
Emp(empno=7902, ename=FORD, job=ANALYST, mgr=7566, hiredate=Thu Dec 03 00:00:00 CST 1981, sal=3000.0, comm=null, deptno=20, dept=null)
DEBUG - Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@305ffe9e]
1
2
3
4
5
6
7
8
9
10
11

四、实现多对多关联查询

在查询多对多关系的映射表时，需要提供一张主键映射表，这样做是为了方便两张表进行多对多的一个关系
实体类：

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Project implements Serializable {
    private Integer pid;
    private String pname;
    private Integer money;
    // 组合一个ProjectRecord对象集合作为属性
    private List<ProjectRecord> projectRecords;
}

@Data
@AllArgsConstructor
@NoArgsConstructor
public class ProjectRecord implements Serializable {
    private Integer empno;
    private Integer pid;
    // 组合一个Emp对象作为属性
    private Emp emp;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

ProjectRecord作用于两张不相关的表进行多对多查询的关联表
ProjectMapper.xml映射文件

    <resultMap id="projectJoinEmps" type="project">
        <id property="pid" column="pid"></id>
        <result property="pname" column="pname"></result>
        <result property="money" column="money"></result>
        <!--一对多 集合属性-->
        <collection property="projectRecords" ofType="projectRecord">
            <id property="empno" column="empno"></id>
            <id property="pid" column="pid"></id>
            <!--一对一-->
            <association property="emp" javaType="emp">
                <!--设置emp本身的八个属性的映射关系-->
                <id property="empno" column="empno"></id>
                <result property="ename" column="ename"></result>
                <result property="job" column="job"></result>
                <result property="sal" column="sal"></result>
                <result property="hiredate" column="hiredate"></result>
                <result property="mgr" column="mgr"></result>
                <result property="comm" column="comm"></result>
                <result property="deptno" column="deptno"></result>
            </association>
        </collection>
    </resultMap>

    <!-- Project findProjectJoinEmpsByPid(int pid);-->
    <select id="findProjectJoinEmpsByPid" resultMap="projectJoinEmps">
        select
            *
        from
            project p
        left join
            projectrecord pr
        on
            p.pid = pr.pid
        left join
            emp e
        on
            pr.empno = e.empno
        where
            p.pid = #{pid}
    </select>
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
30
31
32
33
34
35
36
37
38
39
40

测试代码：

    @Test
    public void testfindProjectJoinEmpsByPid(){
        ProjectMapper mapper = sqlSession.getMapper(ProjectMapper.class);
        Project project = mapper.findProjectJoinEmpsByPid(2);
        System.out.println(project.getPid());
        System.out.println(project.getPname());
        System.out.println(project.getMoney());
        List<ProjectRecord> projectRecords = project.getProjectRecords();
        for(ProjectRecord projectRecord : projectRecords){
            Emp emp = projectRecord.getEmp();
            System.out.println(emp);
        }
    }
1
2
3
4
5
6
7
8
9
10
11
12
13

结果：

DEBUG - Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@6f10d5b6]
DEBUG - ==>  Preparing: select * from project p left join projectrecord pr on p.pid = pr.pid left join emp e on pr.empno = e.empno where p.pid = ? 
DEBUG - ==> Parameters: 2(Integer)
DEBUG - <==      Total: 3
2
学生选课系统
100000
Emp(empno=7369, ename=SMITH, job=CLERK, mgr=7902, hiredate=Wed Dec 17 00:00:00 CST 1980, sal=800.0, comm=null, deptno=20, dept=null)
Emp(empno=7499, ename=ALLEN, job=SALESMAN, mgr=7698, hiredate=Fri Feb 20 00:00:00 CST 1981, sal=1600.0, comm=300.0, deptno=30, dept=null)
Emp(empno=7521, ename=WARD, job=SALESMAN, mgr=7698, hiredate=Sun Feb 22 00:00:00 CST 1981, sal=1250.0, comm=500.0, deptno=30, dept=null)
DEBUG - Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@6f10d5b6]
1
2
3
4
5
6
7
8
9
10
11

二、级联查询

级联查询，顾名思义，就是利于数据库表间的外键关联关系进行自动的级联查询操作。使用MyBatis实现级联查询，除了实体类增加关联属性外，还需要在映射文件中进行配置。

一、积极（立即）加载

在DeptMapper.xml映射文件中调用另外一个映射文件已经写好的sql时，需要通过collection标签来进行相关配置来达到操作一对多关联查询的效果。

在collection中使用以下几个属性来进行配置
- select=“com.msb.mapper.EmpMapper.findEmpsByDeptno” 调用的另一个SQL语句
- javaType=“list” 实体类的属性数据类型
- column=“deptno” 给另一个SQL语句传入的参数列
- jdbcType=“INTEGER” 参数对应JDBC的数据类型
- fetchType=“eager” 加载方式 eager 积极加载 lazy延迟加载

<mapper namespace="com.xiaohui.mapper.DeptMapper">
    <!--Dept findDeptByDeptno(int deptno);
    select="com.msb.mapper.EmpMapper.findEmpsByDeptno" 调用的另一个SQL语句
    javaType="list"  实体类的属性数据类型
    column="deptno"  给另一个SQL语句传入的参数列
    jdbcType="INTEGER" 参数对应JDBC的数据类型
    fetchType="eager"  加载方式 eager 积极加载  lazy延迟加载-->
    <resultMap id="deptJoinEmps" type="dept">
        <id property="deptno" column="deptno"></id>
        <result property="dname" column="dname"></result>
        <result property="loc" column="loc"></result>
        <collection property="empList"
            select="com.xiaohui.mapper.EmpMapper.findEmpsByDeptno"
            javaType="list"
            column="deptno"
            jdbcType="INTEGER"
            fetchType="eager"
        >
        </collection>
    </resultMap>

    <select id="findDeptByDeptno" resultMap="deptJoinEmps">
        select * from dept where deptno = #{deptno}
    </select>
</mapper>

<mapper namespace="com.xiaohui.mapper.EmpMapper">
    <select id="findEmpsByDeptno" resultType="emp">
        select * from emp where deptno = #{deptno}
    </select>
</mapper>
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
30
31

测试代码

    @Test
    public void testFindDeptByDeptno(){
        DeptMapper deptMapper = sqlSession.getMapper(DeptMapper.class);
        Dept deptByDeptno = deptMapper.findDeptByDeptno(20);
        System.out.println(deptByDeptno.getDeptno());
        System.out.println(deptByDeptno.getDname());
        System.out.println(deptByDeptno.getLoc());
        List<Emp> empList = deptByDeptno.getEmpList();
        empList.forEach(System.out::println);
    }
1
2
3
4
5
6
7
8
9
10

结果：

DEBUG - Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@305ffe9e]
DEBUG - ==>  Preparing: select * from dept where deptno = ? 
DEBUG - ==> Parameters: 20(Integer)
DEBUG - ====>  Preparing: select * from emp where deptno = ? 
DEBUG - ====> Parameters: 20(Integer)
DEBUG - <====      Total: 5
DEBUG - <==      Total: 1
20
RESEARCH
DALLAS
Emp(empno=7369, ename=SMITH, job=CLERK, mgr=7902, hiredate=Wed Dec 17 00:00:00 CST 1980, sal=800.0, comm=null, deptno=20)
Emp(empno=7566, ename=JONES, job=MANAGER, mgr=7839, hiredate=Thu Apr 02 00:00:00 CST 1981, sal=2975.0, comm=null, deptno=20)
Emp(empno=7788, ename=SCOTT, job=ANALYST, mgr=7566, hiredate=Sun Apr 19 00:00:00 CDT 1987, sal=3000.0, comm=null, deptno=20)
Emp(empno=7876, ename=ADAMS, job=CLERK, mgr=7788, hiredate=Sat May 23 00:00:00 CDT 1987, sal=1100.0, comm=null, deptno=20)
Emp(empno=7902, ename=FORD, job=ANALYST, mgr=7566, hiredate=Thu Dec 03 00:00:00 CST 1981, sal=3000.0, comm=null, deptno=20)
DEBUG - Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@305ffe9e]
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

二、延迟加载

延迟加载，又称按需加载。延迟加载的内容等到真正使用时才去进行加载（查询）。多用在关联对象或集合中。
延迟加载的好处：先从单表查询、需要时再从关联表去关联查询，大大降低数据库在单位时间内的查询工作量,将工作在时间上的分配更加均匀，而且单表要比关联查询多张表速度要快。
延迟加载的设置
第一步：全局开关：在sqlMapConfig.xml中打开延迟加载的开关。配置完成后所有的association和collection元素都生效

<settings>
      <setting name="lazyLoadingEnabled" value="true"/>
      <setting name="aggressiveLazyLoading" value="true"/>
  </settings>
1
2
3
4

lazyLoadingEnabled:是否开启延迟加载。是Mybatis是否启用懒加载的全局开关。当开启时，所有关联对象都会延迟加载。特定关联关系中可通过设置fetchType属性来覆盖该项的开关状态
aggressiveLazyLoading：当开启时，任何方法的调用都会懒加载对象的所有属性。否则，每个属性会按需加载,
测试代码：

    @Test
    public void testFindDeptByDeptno(){
        DeptMapper deptMapper = sqlSession.getMapper(DeptMapper.class);
        Dept deptByDeptno = deptMapper.findDeptByDeptno(20);
//        System.out.println(deptByDeptno.getDeptno());
//        System.out.println(deptByDeptno.getDname());
//        System.out.println(deptByDeptno.getLoc());
//        List<Emp> empList = deptByDeptno.getEmpList();
//        empList.forEach(System.out::println);
    }
1
2
3
4
5
6
7
8
9
10

结果：

DEBUG - Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@305ffe9e]
DEBUG - ==>  Preparing: select * from dept where deptno = ? 
DEBUG - ==> Parameters: 20(Integer)
DEBUG - <==      Total: 1
DEBUG - Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@305ffe9e]
1
2
3
4
5

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/我家小花儿/article/detail/391769