关联查询代码参考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
