mybatis进阶2——关联查询

作者: 王侦 | 来源:发表于2018-09-25 10:41 被阅读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.多对多

参考

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

java大搜罗

Mybatis