mybatis进阶2——关联查询

2018-09-25 本文已影响257人王侦

关联查询代码参考mybatis-demo
测试代码AssociationQueryTest.java

0.关联查询的应用场景

适用于传统软件，对于高并发、数据量巨大的互联网应用，要慎用！

1.关联查询概览

在关系型数据库中，关联元素是专门处理关联关系的。如：一辆汽车需要一个引擎，这是一对一的关系。一辆汽车需要4个或更多的轮子，这是一对多的关系。

关联元素
association 一对一关系
collection 一对多关系
discriminator 鉴别器映射
关联方式
嵌套结果：使用嵌套结果映射来处理重复的联合结果的子集（一个SQL语句）
嵌套查询：通过执行另外一个SQL映射语句来返回预期的复杂类型（多个SQL语句）
嵌套结果示例

SELECT a.id, 
       a.user_name,
       a.real_name,
       a.sex,
       a.mobile,
       b.comp_name,
       b.years,
       b.title
FROM t_user a,
     t_job_history b
WHERE a.id = b.`user_id`

嵌套查询实例

SELECT a.id, 
       a.user_name,
       a.real_name,
       a.sex,
       a.mobile
FROM t_user a;
     
SELECT b.comp_name,
       b.years,
       b.title
FROM  t_job_history b
WHERE b.`user_id`=3;

2.一对一关联查询

2.1 嵌套结果

association标签嵌套结果方式常用属性：

property：对应实体类中的属性名，必填项
javaType：属性对应的Java类型
resultMap：可以直接使用现有的resultMap，而不需要在这里配置映射关系
columnPrefix：查询列的前缀，配置前缀后，在子标签配置result的column时可以省略前缀

Tips：

resultMap可以通过使用extends实现继承关系，简化很多配置作用
关联的表查询的类添加前缀是编程的好习惯
通过添加完整的命名空间，可以引用其他xml文件的resultMap

    <select id="selectUserPosition1" resultMap="userAndPosition1">
        select
            a.id, 
            user_name,
            real_name,
            sex,
            mobile,
            email,
            a.note,
            b.id  post_id,
            b.post_name,
            b.note post_note
        from t_user a,
            t_position b
        where a.position_id = b.id
    </select>

    <resultMap id="userAndPosition1" extends="BaseResultMap" type="TUser">
        <association property="position" javaType="TPosition" columnPrefix="post_">
            <id column="id" property="id"/>
            <result column="name" property="postName"/>
            <result column="note" property="note"/>
        </association>
    </resultMap>

测试代码：

    @Test
    // 1对1两种关联方式
    public void testOneToOne() {
        // 2.获取sqlSession
        SqlSession sqlSession = sqlSessionFactory.openSession();
        // 3.获取对应mapper
        TUserMapper mapper = sqlSession.getMapper(TUserMapper.class);
        // 4.执行查询语句并返回结果
        // ----------------------
        List<TUser> list1 = mapper.selectUserPosition1();
        for (TUser tUser : list1) {
            System.out.println(tUser);
        }
    }

测试准备：
1）generatorConfig.xml自动生成相应的代码
2）修改TUser等代码
3）添加mapper映射

    <!-- 映射文件，mapper的配置文件 -->
    <mappers>
        <!--直接映射到相应的mapper文件 -->
        <mapper resource="sqlmapper/TUserMapper.xml" />
        <mapper resource="sqlmapper/TJobHistoryMapper.xml" />
        <mapper resource="sqlmapper/TPositionMapper.xml" />
        <mapper resource="sqlmapper/THealthReportFemaleMapper.xml" />
        <mapper resource="sqlmapper/THealthReportMaleMapper.xml" />
         <mapper class="com.enjoylearning.mybatis.mapper.TJobHistoryAnnoMapper"/>
    </mappers>

测试结果：

2018-09-25 08:18:52.291 [main] DEBUG c.e.mybatis.mapper.TUserMapper.selectUserPosition1 - ==>  Preparing: select a.id, user_name, real_name, sex, mobile, email, a.note, b.id post_id, b.post_name, b.note post_note from t_user a, t_position b where a.position_id = b.id 
2018-09-25 08:18:52.377 [main] DEBUG c.e.mybatis.mapper.TUserMapper.selectUserPosition1 - ==> Parameters: 
2018-09-25 08:18:52.445 [main] DEBUG c.e.mybatis.mapper.TUserMapper.selectUserPosition1 - <==      Total: 5
TUser [id=1, userName=lison, realName=李小宇, sex=1, mobile=186995587422, email=lison@qq.com, note=lison的备注, positionId=1]
TUser [id=3, userName=cindy, realName=王美丽, sex=2, mobile=18695988747, email=xxoo@163.com, note=cindy's note, positionId=1]
TUser [id=126, userName=mark, realName=毛毛, sex=1, mobile=18695988747, email=xxoo@163.com, note=mark's note, positionId=1]
TUser [id=131, userName=mark, realName=毛毛, sex=1, mobile=18695988747, email=xxoo@163.com, note=mark's note, positionId=1]
TUser [id=2, userName=james, realName=陈大雷, sex=1, mobile=18677885200, email=james@qq.com, note=james的备注, positionId=2]

2.2 嵌套查询

association标签嵌套查询方式常用属性：

select：另一个映射查询的id，MyBatis会额外执行这个查询获取嵌套对象的结果
column：列名（或别名），将主查询中列的结果作为嵌套查询的参数
fetchType：数据加载方式，可选值为lazy和eager，分别为延迟加载和积极加载，这个配置会覆盖全局的lazyLoadingEnabled配置

    <select id="selectUserPosition2" resultMap="userAndPosition2">
        select
        a.id,
        a.user_name,
        a.real_name,
        a.sex,
        a.mobile,
        a.position_id
        from t_user a
    </select>

    <resultMap id="userAndPosition2" extends="BaseResultMap" type="TUser">
        <association property="position" fetchType="lazy"  column="position_id" select="com.enjoylearning.mybatis.mapper.TPositionMapper.selectByPrimaryKey" />
    </resultMap>

其中：TPositionMapper.xml中的selectByPrimaryKey如下：

  <sql id="Base_Column_List">
    <!--
      WARNING - @mbg.generated
      This element is automatically generated by MyBatis Generator, do not modify.
    -->
    id, post_name, note
  </sql>
  <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
    <!--
      WARNING - @mbg.generated
      This element is automatically generated by MyBatis Generator, do not modify.
    -->
    select 
    <include refid="Base_Column_List" />
    from t_position
    where id = #{id,jdbcType=INTEGER}
  </select>

测试代码：

    @Test
    // 1对1两种关联方式
    public void testOneToOne() {
        // 2.获取sqlSession
        SqlSession sqlSession = sqlSessionFactory.openSession();
        // 3.获取对应mapper
        TUserMapper mapper = sqlSession.getMapper(TUserMapper.class);
        // 4.执行查询语句并返回结果
        // ----------------------
//      List<TUser> list1 = mapper.selectUserPosition1();
//      for (TUser tUser : list1) {
//          System.out.println(tUser);
//      }

        List<TUser> list2 = mapper.selectUserPosition2();
        for (TUser tUser : list2) {
            System.out.println(tUser.getPosition().getPostName());
        }
    }

测试结果：

2018-09-25 09:07:30.292 [main] DEBUG c.e.mybatis.mapper.TUserMapper.selectUserPosition2 - ==>  Preparing: select a.id, a.user_name, a.real_name, a.sex, a.mobile, a.position_id from t_user a 
2018-09-25 09:07:30.374 [main] DEBUG c.e.mybatis.mapper.TUserMapper.selectUserPosition2 - ==> Parameters: 
2018-09-25 09:07:30.428 [main] DEBUG c.e.m.mapper.TPositionMapper.selectByPrimaryKey - ====>  Preparing: select id, post_name, note from t_position where id = ? 
2018-09-25 09:07:30.429 [main] DEBUG c.e.m.mapper.TPositionMapper.selectByPrimaryKey - ====> Parameters: 1(Integer)
2018-09-25 09:07:30.438 [main] DEBUG c.e.m.mapper.TPositionMapper.selectByPrimaryKey - <====      Total: 1
2018-09-25 09:07:30.439 [main] DEBUG c.e.m.mapper.TPositionMapper.selectByPrimaryKey - ====>  Preparing: select id, post_name, note from t_position where id = ? 
2018-09-25 09:07:30.440 [main] DEBUG c.e.m.mapper.TPositionMapper.selectByPrimaryKey - ====> Parameters: 2(Integer)
2018-09-25 09:07:30.442 [main] DEBUG c.e.m.mapper.TPositionMapper.selectByPrimaryKey - <====      Total: 1
2018-09-25 09:07:30.449 [main] DEBUG c.e.mybatis.mapper.TUserMapper.selectUserPosition2 - <==      Total: 4
总经理
零时工
总经理
总经理

非懒加载的情况：

注意：这里只进行了三次查询，为什么不是N+1=5?
这里跟缓存有关，总经理值查了一次。

setting里面的属性
Tips：N+1查询问题

问题描述：执行了一个单独SQL语句来获取结果列表（就是+1）；对返回的每条记录，执行一个查询语句来为每条记录加载细节（就是N）。这个问题会导致成百上千的SQL语句被执行。
解决方法：使用fetchType=lazy并且全局setting进行改善

<setting name="aggressiveLazyLoading" value="false"/>

2018-09-25 09:26:46.470 [main] DEBUG c.e.mybatis.mapper.TUserMapper.selectUserPosition2 - ==>  Preparing: select a.id, a.user_name, a.real_name, a.sex, a.mobile, a.position_id from t_user a 
2018-09-25 09:26:46.499 [main] DEBUG c.e.mybatis.mapper.TUserMapper.selectUserPosition2 - ==> Parameters: 
2018-09-25 09:26:46.557 [main] DEBUG c.e.mybatis.mapper.TUserMapper.selectUserPosition2 - <==      Total: 4
2018-09-25 09:26:46.558 [main] DEBUG c.e.m.mapper.TPositionMapper.selectByPrimaryKey - ==>  Preparing: select id, post_name, note from t_position where id = ? 
2018-09-25 09:26:46.577 [main] DEBUG c.e.m.mapper.TPositionMapper.selectByPrimaryKey - ==> Parameters: 1(Integer)
2018-09-25 09:26:46.578 [main] DEBUG c.e.m.mapper.TPositionMapper.selectByPrimaryKey - <==      Total: 1
总经理
2018-09-25 09:26:46.578 [main] DEBUG c.e.m.mapper.TPositionMapper.selectByPrimaryKey - ==>  Preparing: select id, post_name, note from t_position where id = ? 
2018-09-25 09:26:46.579 [main] DEBUG c.e.m.mapper.TPositionMapper.selectByPrimaryKey - ==> Parameters: 2(Integer)
2018-09-25 09:26:46.582 [main] DEBUG c.e.m.mapper.TPositionMapper.selectByPrimaryKey - <==      Total: 1
零时工
总经理
总经理

懒加载情况：

3.一对多关联查询

collection支持的属性以及属性的作用和association完全相同
mybatis会根据id标签，进行字段的合并，合理配置好ID标签可以提高处理的效率

Tips：
如果要配置一个相当复杂的映射，一定要从基础映射开始配置，每增加一些配置就进行对应的测试，在循序渐进的过程中更容易发现和解决问题。

3.1 嵌套结果

    <select id="selectUserJobs1" resultMap="userAndJobs1">
        select
        a.id,
        a.user_name,
        a.real_name,
        a.sex,
        a.mobile,
        b.comp_name,
        b.years,
        b.title
        from t_user a,
        t_job_history b
        where a.id = b.user_id
    </select>

    <resultMap id="userAndJobs1" extends="BaseResultMap" type="TUser">
        <collection property="jobs"
                    ofType="com.enjoylearning.mybatis.entity.TJobHistory" >
            <result column="comp_name" property="compName" jdbcType="VARCHAR" />
            <result column="years" property="years" jdbcType="INTEGER" />
            <result column="title" property="title" jdbcType="VARCHAR" />
        </collection>
    </resultMap>

测试程序：

    @Test
    // 1对多两种关联方式
    public void testOneToMany() {
        // 2.获取sqlSession
        SqlSession sqlSession = sqlSessionFactory.openSession();
        // 3.获取对应mapper
        TUserMapper mapper = sqlSession.getMapper(TUserMapper.class);
        // 4.执行查询语句并返回结果
        // ----------------------
        List<TUser> selectUserJobs1 = mapper.selectUserJobs1();
//        List<TUser> selectUserJobs2 = mapper.selectUserJobs2();
        for (TUser tUser : selectUserJobs1) {
            System.out.println(tUser);
        }
//        for (TUser tUser : selectUserJobs2) {
//            System.out.println(tUser.getJobs().size());
//        }
    }

测试结果：

2018-09-25 09:50:43.860 [main] DEBUG c.e.mybatis.mapper.TUserMapper.selectUserJobs1 - ==>  Preparing: select a.id, a.user_name, a.real_name, a.sex, a.mobile, b.comp_name, b.years, b.title from t_user a, t_job_history b where a.id = b.user_id 
2018-09-25 09:50:43.921 [main] DEBUG c.e.mybatis.mapper.TUserMapper.selectUserJobs1 - ==> Parameters: 
2018-09-25 09:50:43.942 [main] DEBUG c.e.mybatis.mapper.TUserMapper.selectUserJobs1 - <==      Total: 6
TUser [id=1, userName=lison, realName=李小宇, sex=1, mobile=186995587422, email=null, note=null, positionId=]
TUser [id=2, userName=james, realName=陈大雷, sex=1, mobile=18677885200, email=null, note=null, positionId=]
TUser [id=3, userName=cindy, realName=王美丽, sex=2, mobile=18695988747, email=null, note=null, positionId=]

3.2 嵌套查询

    <select id="selectUserJobs2" resultMap="userAndJobs2">
        select
        a.id,
        a.user_name,
        a.real_name,
        a.sex,
        a.mobile
        from t_user a
    </select>

    <resultMap id="userAndJobs2" extends="BaseResultMap" type="TUser">
        <collection property="jobs" fetchType="lazy" column="id"
                    select="com.enjoylearning.mybatis.mapper.TJobHistoryMapper.selectByUserId" />
    </resultMap>

TJobHistoryMapper.xml中的selectByUserId：

    <select id="selectByUserId" resultMap="BaseResultMap"  parameterType="java.lang.Integer">
        select
        <include refid="Base_Column_List" />
        from t_job_history
        where user_id = #{userId,jdbcType=INTEGER}
    </select>

测试程序：

    @Test
    // 1对多两种关联方式
    public void testOneToMany() {
        // 2.获取sqlSession
        SqlSession sqlSession = sqlSessionFactory.openSession();
        // 3.获取对应mapper
        TUserMapper mapper = sqlSession.getMapper(TUserMapper.class);
        // 4.执行查询语句并返回结果
        // ----------------------
//        List<TUser> selectUserJobs1 = mapper.selectUserJobs1();
        List<TUser> selectUserJobs2 = mapper.selectUserJobs2();
//        for (TUser tUser : selectUserJobs1) {
//            System.out.println(tUser);
//        }
        for (TUser tUser : selectUserJobs2) {
            System.out.println(tUser.getJobs().size());
        }
    }

测试结果：

2018-09-25 09:52:28.543 [main] DEBUG c.e.mybatis.mapper.TUserMapper.selectUserJobs2 - ==>  Preparing: select a.id, a.user_name, a.real_name, a.sex, a.mobile from t_user a 
2018-09-25 09:52:28.623 [main] DEBUG c.e.mybatis.mapper.TUserMapper.selectUserJobs2 - ==> Parameters: 
2018-09-25 09:52:28.701 [main] DEBUG c.e.mybatis.mapper.TUserMapper.selectUserJobs2 - <==      Total: 4
2018-09-25 09:52:28.703 [main] DEBUG c.e.m.mapper.TJobHistoryMapper.selectByUserId - ==>  Preparing: select id, user_id, comp_name, years, title from t_job_history where user_id = ? 
2018-09-25 09:52:28.704 [main] DEBUG c.e.m.mapper.TJobHistoryMapper.selectByUserId - ==> Parameters: 1(Integer)
2018-09-25 09:52:28.705 [main] DEBUG c.e.m.mapper.TJobHistoryMapper.selectByUserId - <==      Total: 1
1
2018-09-25 09:52:28.706 [main] DEBUG c.e.m.mapper.TJobHistoryMapper.selectByUserId - ==>  Preparing: select id, user_id, comp_name, years, title from t_job_history where user_id = ? 
2018-09-25 09:52:28.707 [main] DEBUG c.e.m.mapper.TJobHistoryMapper.selectByUserId - ==> Parameters: 2(Integer)
2018-09-25 09:52:28.709 [main] DEBUG c.e.m.mapper.TJobHistoryMapper.selectByUserId - <==      Total: 2
2
2018-09-25 09:52:28.709 [main] DEBUG c.e.m.mapper.TJobHistoryMapper.selectByUserId - ==>  Preparing: select id, user_id, comp_name, years, title from t_job_history where user_id = ? 
2018-09-25 09:52:28.709 [main] DEBUG c.e.m.mapper.TJobHistoryMapper.selectByUserId - ==> Parameters: 3(Integer)
2018-09-25 09:52:28.710 [main] DEBUG c.e.m.mapper.TJobHistoryMapper.selectByUserId - <==      Total: 3
3
2018-09-25 09:52:28.711 [main] DEBUG c.e.m.mapper.TJobHistoryMapper.selectByUserId - ==>  Preparing: select id, user_id, comp_name, years, title from t_job_history where user_id = ? 
2018-09-25 09:52:28.711 [main] DEBUG c.e.m.mapper.TJobHistoryMapper.selectByUserId - ==> Parameters: 126(Integer)
2018-09-25 09:52:28.712 [main] DEBUG c.e.m.mapper.TJobHistoryMapper.selectByUserId - <==      Total: 0
0

4.discriminator鉴别器映射

在特定的情况下使用不同的POJO进行关联，鉴别器元素就是被设计来处理这个情况的。鉴别器非常容易理解，因为其表现很像Java中的switch语句。

dicriminator标签常用的两个属性如下：

column：用于设置要进行鉴别比较值的列
javaType：用于指定列的类型，保证使用相同的Java类型来比较值

discriminator标签可以有1个或多个case标签，case标签包含以下三个属性：

value：为discriminator指定column用来匹配的值
resultMap：当column值和value值匹配时，可以配置使用resultMap指定的映射，resultMap优先级高于resultType
resultType：当column值和value值匹配时，用于配置使用resultType指定的映射

    <resultMap id="userAndHealthReportMale" extends="userAndHealthReport" type="TUser">
        <collection property="healthReports" column="id"
                    select= "com.enjoylearning.mybatis.mapper.THealthReportMaleMapper.selectByUserId"></collection>
    </resultMap>

    <resultMap id="userAndHealthReportFemale" extends="userAndHealthReport" type="TUser">
        <collection property="healthReports" column="id"
                    select= "com.enjoylearning.mybatis.mapper.THealthReportFemaleMapper.selectByUserId"></collection>
    </resultMap>

    <resultMap id="userAndHealthReport" extends="BaseResultMap" type="TUser">
        <discriminator column="sex"  javaType="int">
            <case value="1" resultMap="userAndHealthReportMale"/>
            <case value="2" resultMap="userAndHealthReportFemale"/>
        </discriminator>
    </resultMap>


    <select id="selectUserHealthReport" resultMap="userAndHealthReport">
        select
        <include refid="Base_Column_List" />
        from t_user a
    </select>

体检报告：
TUser中

   private List<HealthReport> healthReports;

测试代码：

    @Test
    public void testDiscriminator(){
        // 2.获取sqlSession
        SqlSession sqlSession = sqlSessionFactory.openSession();
        // 3.获取对应mapper
        TUserMapper mapper = sqlSession.getMapper(TUserMapper.class);
//      // 4.执行查询语句并返回结果
//      // ----------------------
        List<TUser> list = mapper.selectUserHealthReport();
        for (TUser tUser : list) {
            System.out.println(tUser);
        }
    }

添加com.enjoylearning.mybatis.mapper.THealthReportMaleMapper.selectByUserId，同理还有Female的。

  <select id="selectByUserId" parameterType="java.lang.Integer" resultMap="BaseResultMap">
    <!--
      WARNING - @mbg.generated
      This element is automatically generated by MyBatis Generator, do not modify.
    -->
    select
    <include refid="Base_Column_List" />
    from t_health_report_male
    where user_id = #{userID,jdbcType=INTEGER}
  </select>

测试结果：

2018-09-25 10:33:43.344 [main] DEBUG org.apache.ibatis.transaction.jdbc.JdbcTransaction - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@289710d9]
2018-09-25 10:33:43.350 [main] DEBUG c.e.m.mapper.TUserMapper.selectUserHealthReport - ==>  Preparing: select id, user_name, real_name, sex, mobile, email, note, position_id from t_user a 
2018-09-25 10:33:43.391 [main] DEBUG c.e.m.mapper.TUserMapper.selectUserHealthReport - ==> Parameters: 
2018-09-25 10:33:43.410 [main] DEBUG c.e.m.m.THealthReportMaleMapper.selectByUserId - ====>  Preparing: select id, check_project, detail, user_id from t_health_report_male where user_id = ? 
2018-09-25 10:33:43.411 [main] DEBUG c.e.m.m.THealthReportMaleMapper.selectByUserId - ====> Parameters: 1(Integer)
2018-09-25 10:33:43.412 [main] DEBUG c.e.m.m.THealthReportMaleMapper.selectByUserId - <====      Total: 3
2018-09-25 10:33:43.414 [main] DEBUG c.e.m.m.THealthReportMaleMapper.selectByUserId - ====>  Preparing: select id, check_project, detail, user_id from t_health_report_male where user_id = ? 
2018-09-25 10:33:43.414 [main] DEBUG c.e.m.m.THealthReportMaleMapper.selectByUserId - ====> Parameters: 2(Integer)
2018-09-25 10:33:43.415 [main] DEBUG c.e.m.m.THealthReportMaleMapper.selectByUserId - <====      Total: 0
2018-09-25 10:33:43.416 [main] DEBUG c.e.m.m.THealthReportFemaleMapper.selectByUserId - ====>  Preparing: select id, item, score, user_id from t_health_report_female where user_id = ? 
2018-09-25 10:33:43.417 [main] DEBUG c.e.m.m.THealthReportFemaleMapper.selectByUserId - ====> Parameters: 3(Integer)
2018-09-25 10:33:43.418 [main] DEBUG c.e.m.m.THealthReportFemaleMapper.selectByUserId - <====      Total: 3
2018-09-25 10:33:43.419 [main] DEBUG c.e.m.m.THealthReportMaleMapper.selectByUserId - ====>  Preparing: select id, check_project, detail, user_id from t_health_report_male where user_id = ? 
2018-09-25 10:33:43.419 [main] DEBUG c.e.m.m.THealthReportMaleMapper.selectByUserId - ====> Parameters: 126(Integer)
2018-09-25 10:33:43.420 [main] DEBUG c.e.m.m.THealthReportMaleMapper.selectByUserId - <====      Total: 0
2018-09-25 10:33:43.420 [main] DEBUG c.e.m.mapper.TUserMapper.selectUserHealthReport - <==      Total: 4
TUser [id=1, userName=lison, realName=李小宇, sex=1, mobile=186995587422, email=lison@qq.com, note=lison的备注, positionId=]
TUser [id=2, userName=james, realName=陈大雷, sex=1, mobile=18677885200, email=james@qq.com, note=james的备注, positionId=]
TUser [id=3, userName=cindy, realName=王美丽, sex=2, mobile=18695988747, email=xxoo@163.com, note=cindy's note, positionId=]
TUser [id=126, userName=mark, realName=毛毛, sex=1, mobile=18695988747, email=xxoo@163.com, note=mark's note, positionId=]

5.多对多

先决条件1.多对多需要一种中间表建立连接关系
先决条件2.多对多关系是由两个一对多关系组成的，一对多也可用两种方式实现

参考

1）享学课堂Lison老师笔记
2）阿里巴巴java开发手册 1.4.0

mybatis进阶2——关联查询

0.关联查询的应用场景

1.关联查询概览

2.一对一关联查询

2.1 嵌套结果

2.2 嵌套查询

3.一对多关联查询

3.1 嵌套结果

3.2 嵌套查询

4.discriminator鉴别器映射

5.多对多

参考

猜你喜欢

热点阅读