MyBatis(六) sql片段定义、级联查询、嵌套查询

于 2018-07-09 15:32:48 发布
阅读量2k 收藏 1
点赞数
分类专栏： MyBatis 文章标签： MyBatis级联查询和嵌套查询 discriminator元素使用 collection元素 association元素
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/Ditto_zhou/article/details/80926438
版权
本文介绍了如何在MyBatis中定义SQL片段以提高代码复用，详细解析了resultMap中的各种元素，包括constructor、id和result。接着，文章探讨了级联查询，通过association和collection元素处理一对一和一对多的关系，并展示了discriminator元素在根据条件选择结果集中的应用。最后，讨论了级联查询可能导致的N+1问题和MyBatis的延迟加载机制，以及如何通过fetchType配置实现局部加载。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

SQL片段的定义

将公用的SQL片段提取出来减少工作量

 <sql id="studentColumns">
  	stu_id,stu_Age,stu_Sex,stu_Name
  </sql>
  <select id="queryStudentInfo" resultType="student" parameterType="map">
    select  <include refid="studentColumns"></include>
    from student_info where stu_id = #{id}
  </select>

也可以在sql片段中定义形参

<sql id="studentColumns">
  	${prefix}.stu_id,${prefix}.stu_Age,${prefix}.stu_Sex,${prefix}.stu_Name 
  </sql>
 <select id="queryStudentInfo" resultMap="studentMap" parameterType="int">
    select  
    <include refid="studentColumns">
    	<property name="prefix" value="m"/>
    </include>
    from student_info m where m.stu_id = #{id}
  </select>
因为我们注入的参数并不是SQL语句的参数而是SQL语句本身，前面有说过#{}用来注入参数，${}注入sql语句本身

resultMap结果集定义详解

* resultMap里面的元素

<resultMap type="" id="">
  	<constructor></constructor>
  	<id/>
  	<result/>
  	<association property=""></association>
  	<collection property=""></collection>
  	<discriminator javaType=""></discriminator>
  </resultMap>

type: 结果集的type，一般都是定义为javaBean，定义为map，不方便

constructor:在javabean没有创建无参的构造器时候，指定的调用的构造器方法

id和result：指定映射关系

<!-- 
  	column: 映射的列名
  	property: 映射的JavaBean的属性
  	jdbctype: 数据库里面的类型
  	javaType: java里面的类型
  	typeHandler: 类型转换中，指定的处理器，可以不指定
  	 -->
  	<result column="stu_age" property="stuAge" jdbcType="varchar" javaType="string"
  	typeHandler="类型处理器"
  	/>

级联查询的处理

association: 表示一对一关系

collection: 表示一对多关系

discriminator: 根据条件选择结果集

一对一级联查询

* 学生和学生卡是一对一关系


需求是查询学生的时候将学生卡信息也一并查询出来

* student对象中定义了StudentCard对象

public class Student {
	private Integer stuId;
	private Integer stuAge;
	private String stuSex;
	private String stuName;
	private StudentCard card;
	//get/set .....
public class StudentCard {
	private Integer stuId;
	private Integer cardNum;
	private Date registerTime;
	//get/set....

定义studentMapper.xml

<resultMap type="student" id="studentAndCourse">
  	<id column="stu_id" property="stuId"/>
  	<result column="stu_age" property="stuAge" />
  	<result column="stu_sex" property="stuSex" />
  	<result column="stu_name" property="stuName"/>
  	<association property="card" column="stu_id"  
  	select="cn.bing.mapper.StudentCardMapper.queryStudentCardInfo">
  	</association>
  </resultMap>
  <select id="queryStudentInfo" resultMap="studentAndCourse" parameterType="int">
    select  *   from student_info m where m.stu_id = #{id}
  </select>

在association元素中

card是关联对象的字段名称，select是调用的另一个CardMapper里的查询方法，必须写全路径+方法名称

column是方法形参获取的值对应在Student的列名(此时是student中Bean的主键列名)

* StudentCardMapper.xml

<mapper namespace="cn.bing.mapper.StudentCardMapper">
	<resultMap type="studentCard" id="cardInfoMapper">
		<id column="stu_id" property="stuId"/>
		<result column="card_num" property="cardNum"/>
		<result column="register_time" property="registerTime" 
		jdbcType="TIMESTAMP" javaType="java.util.Date"
		typeHandler="org.apache.ibatis.type.DateTypeHandler"
		/>
	</resultMap>
	<select id="queryStudentCardInfo" resultMap="cardInfoMapper" parameterType="int">
		select * from student_card where stu_id = #{id}
	</select>
</mapper>

测试：将mapper.xml 加到mybatis-config.xml配置中，直接调用queryStudentInfo方法，从日志中看出，发起了对学生卡的查询

DEBUG 2018-07-05 16:47:15,744 org.apache.ibatis.logging.jdbc.BaseJdbcLogger:==>  Preparing: select * from student_info m where m.stu_id = ? 
DEBUG 2018-07-05 16:47:15,782 org.apache.ibatis.logging.jdbc.BaseJdbcLogger:==> Parameters: 14(Integer)
DEBUG 2018-07-05 16:47:15,800 org.apache.ibatis.logging.jdbc.BaseJdbcLogger:====>  Preparing: select * from student_card where stu_id = ? 
DEBUG 2018-07-05 16:47:15,800 org.apache.ibatis.logging.jdbc.BaseJdbcLogger:====> Parameters: 14(Integer)
一对多级联查询

学生信息和学生课程是一对多关系


* pojo定义

在student类的一方，增加List<Course> list 属性

public class Student {
	private Integer stuId;
	private Integer stuAge;
	private String stuSex;
	private String stuName;
	private StudentCard card;
	private List<Course> list = new ArrayList<Course>();
	//get/set .....
public class Course {
	private Integer courseId;
	private String courseName;
	//get /set ...

* studentMapper.xml

<resultMap type="student" id="studentAndCourse">
  	<id column="stu_id" property="stuId"/>
  	<result column="stu_age" property="stuAge" />
  	<result column="stu_sex" property="stuSex" />
  	<result column="stu_name" property="stuName"/>
  	<association property="card" column="stu_id"  
  	select="cn.bing.mapper.StudentCardMapper.queryStudentCardInfo">
  	</association>
  	<!-- 对课程表的一对多关系级联查询 -->
  	<collection property="list" column="stu_id"
  	select="cn.bing.mapper.CourseMapper.queryCourseInfos"
  	></collection>
  </resultMap>
 <select id="queryStudentInfo" resultMap="studentAndCourse" parameterType="int">
    select  *   from student_info m where m.stu_id = #{id}
  </select>

* courseMapper.xml

<mapper namespace="cn.bing.mapper.CourseMapper">
 	<select id="queryCourseInfos" resultType="course" parameterType="int" >
 		select course_id as courseId,course_name as courseName 
 		from course_info where stu_id = #{id}
 	</select>
 </mapper>

测试: 将xml加到mybatis-config.xml配置中 调用queryStudentInfo,打印日志

DEBUG 2018-07-09 09:36:14,278 org.apache.ibatis.transaction.jdbc.JdbcTransaction:Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@176b067]
DEBUG 2018-07-09 09:36:14,281 org.apache.ibatis.logging.jdbc.BaseJdbcLogger:==>  Preparing: select * from student_info m where m.stu_id = ? 
DEBUG 2018-07-09 09:36:14,316 org.apache.ibatis.logging.jdbc.BaseJdbcLogger:==> Parameters: 5(Integer)
DEBUG 2018-07-09 09:36:14,342 org.apache.ibatis.logging.jdbc.BaseJdbcLogger:====>  Preparing: select stu_id,card_num,register_time from student_card where stu_id = ? 
DEBUG 2018-07-09 09:36:14,343 org.apache.ibatis.logging.jdbc.BaseJdbcLogger:====> Parameters: 5(Integer)
DEBUG 2018-07-09 09:36:14,352 org.apache.ibatis.logging.jdbc.BaseJdbcLogger:<====      Total: 1
DEBUG 2018-07-09 09:36:14,353 org.apache.ibatis.logging.jdbc.BaseJdbcLogger:====>  Preparing: select course_id as courseId,course_name as courseName from course_info where stu_id = ? 
DEBUG 2018-07-09 09:36:14,353 org.apache.ibatis.logging.jdbc.BaseJdbcLogger:====> Parameters: 5(Integer)
DEBUG 2018-07-09 09:36:14,355 org.apache.ibatis.logging.jdbc.BaseJdbcLogger:<====      Total: 2
DEBUG 2018-07-09 09:36:14,356 org.apache.ibatis.logging.jdbc.BaseJdbcLogger:<==      Total: 1
discriminator鉴别器级联

鉴别器级联是在特定的条件下去使用不同的结果映射，比如说需要根据学生的性别去区分学生的生理状况。

<!-- 鉴别器级联，根据性别，关联不同的健康结果集 -->
  	<discriminator javaType="string" jdbcType="TINYINT" column="stu_sex" >
  		<case value="1" resultMap="malehealthMapper"></case>
  		<case value="0" resultMap="femalhealthMapper">
  		</case>
  	</discriminator>

* 建立表结构

CREATE TABLE `female_health` (
	`stu_id` INT(11) NOT NULL AUTO_INCREMENT,
	`stu_heigth` DECIMAL(10,2) NULL DEFAULT '0.00',
	`stu_weigth` DECIMAL(10,2) NULL DEFAULT '0.00',
	`stu_strength` DECIMAL(10,2) NULL DEFAULT '0.00',
	PRIMARY KEY (`stu_id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
ROW_FORMAT=COMPACT
AUTO_INCREMENT=7
;

CREATE TABLE `male_health` (
	`stu_id` INT(11) NOT NULL AUTO_INCREMENT,
	`stu_heigth` DECIMAL(10,2) NULL DEFAULT '0.00',
	`stu_weigth` DECIMAL(10,2) NULL DEFAULT '0.00',
	`stu_strength` DECIMAL(10,2) NULL DEFAULT '0.00',
	PRIMARY KEY (`stu_id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=6
;

* 创建男生的健康实体,映射类，映射文件

public class MaleHealth {
	private Integer stuId;
	private BigDecimal stuHeigth;
	private BigDecimal stuWeigth;
	private BigDecimal stuStrength;
	//get/set ...
public interface MaleHealthMapper {
	public List<MaleHealth> getMaleHealthInfos(@Param("id")Integer id);
}
<mapper namespace="cn.bing.mapper.MaleHealthMapper">
 	<select id="getMaleHealthInfos" 
 	resultType="cn.bing.pojo.MaleHealth"
 	parameterType="int" >
 		select stu_id as stuId,
 		stu_heigth as stuHeigth,
 		stu_weigth as stuWeigth,
 		stu_strength  as stuStrength
 		from male_health where stu_id = #{id}
 	</select>
 </mapper>

* 创建女生的健康实体，映射类，映射文件

public class FemaleHealth {
	private Integer stuId;
	private BigDecimal stuHeigth;
	private BigDecimal stuWeigth;
	private BigDecimal stuStrength;
	//set get 。。。
public interface FeMaleHealthMapper {
	public List<MaleHealth> getFeMaleHealthInfos(@Param("id")Integer id);
}
<mapper namespace="cn.bing.mapper.FeMaleHealthMapper">
 	<select id="getFeMaleHealthInfos" resultType="femaleHealth" parameterType="int" >
 		select stu_id as stuId,
 		stu_heigth as stuHeigth,
 		stu_weigth as stuWeigth,
 		stu_strength as stuStrength  
 		from female_health where stu_id = #{id}
 	</select>
 </mapper>

* 创建男生健康封装类和女生健康封装类，接受映射结果

public class StudentMaleHealthBean extends Student{
	private List<MaleHealth> maleList = new ArrayList<MaleHealth>();
	//get set ...
public class StudentFeMaleHealthBean extends Student{
	private List<FemaleHealth> femaleList = new ArrayList<FemaleHealth>();
	//get set 。。。

* 修改studentMapper.xml

 <resultMap type="student" id="studentAndCourse">
  	<id column="stu_id" property="stuId"/>
  	<result column="stu_age" property="stuAge" />
  	<result column="stu_sex" property="stuSex" />
  	<result column="stu_name" property="stuName"/>
  	<association property="card" column="stu_id"  
  	select="cn.bing.mapper.StudentCardMapper.queryStudentCardInfo">
  	</association>
	<!--对课程表的一对多关系级联查询 -->
  	<collection property="list" column="stu_id"
  	select="cn.bing.mapper.CourseMapper.queryCourseInfos"
  	></collection>
  	<!-- 鉴别器级联，根据性别，关联不同的健康结果集 -->
  	<discriminator javaType="string" jdbcType="TINYINT" column="stu_sex" >
  		<case value="1" resultMap="malehealthMapper"></case>
  		<case value="0" resultMap="femalhealthMapper">
  		</case>
  	</discriminator>
  </resultMap>
  
  <resultMap type="cn.bing.mapper.StudentMaleHealthBean" id="malehealthMapper" extends="studentAndCourse">
  	<collection property="maleList" column="stu_id" 
  	select="cn.bing.mapper.MaleHealthMapper.getMaleHealthInfos"
  	>
  	</collection>
  </resultMap>
  <resultMap type="cn.bing.mapper.StudentFeMaleHealthBean" id="femalhealthMapper" extends="studentAndCourse">
  	<collection property="femaleList" column="stu_id" 
  	 select="cn.bing.mapper.FeMaleHealthMapper.getFeMaleHealthInfos"
  	></collection>
  </resultMap>
  <select id="queryStudentInfo" resultMap="studentAndCourse" parameterType="int">
    select  *   from student_info m where m.stu_id = #{id}
  </select>
注意： * 男生/女生健康的映射必须继承StudentAndCourse的resultMapper。

对于级联查询的性能分析

     级联查询会查询出一些不必须的信息，而且增加上结果集映射的复杂度。每增加一个级联关系，会导致sql执行查询不必须的信息，导致不必要的资源浪费，这就是N+1问题。必须使用级联的情况，可以使用延迟加载。

延迟加载

MyBatis的配置中有两个全局的参数

lazyLoadingEnabled: 是否开启延迟加载功能，默认是false

aggressiveLazyLoading:对于任何延迟属性的加载是否使的带有延迟属性的对象完整加载，就是按调用加载。

<settings>
	<!-- 这个比较好理解，就是开启延迟加载 -->
	<setting name="lazyLoadingEnabled" value="true"/>
	<!-- 比如学生信息查询，只获取学生的学生卡信息，不会去将学生的课程信息、健康信息加载出来 -->
	<setting name="aggressiveLazyLoading" value="false"/>
</settings>
Student student = mapper.queryStudentInfo(5);
StudentCard card = student.getCard();
System.out.println(card.getCardNum());
DEBUG 2018-07-09 11:20:20,969 org.apache.ibatis.logging.jdbc.BaseJdbcLogger:==>  Preparing: select * from student_info m where m.stu_id = ? 
DEBUG 2018-07-09 11:20:21,006 org.apache.ibatis.logging.jdbc.BaseJdbcLogger:==> Parameters: 5(Integer)
DEBUG 2018-07-09 11:20:21,082 org.apache.ibatis.logging.jdbc.BaseJdbcLogger:<==      Total: 1
DEBUG 2018-07-09 11:20:21,083 org.apache.ibatis.logging.jdbc.BaseJdbcLogger:==>  Preparing: select stu_id,card_num,register_time from student_card where stu_id = ? 
DEBUG 2018-07-09 11:20:21,084 org.apache.ibatis.logging.jdbc.BaseJdbcLogger:==> Parameters: 5(Integer)
DEBUG 2018-07-09 11:20:21,090 org.apache.ibatis.logging.jdbc.BaseJdbcLogger:<==      Total: 1

注意，如果只设置lazyLoadingEnabled参数为true，因为MyBatis是按照层级加载的，学生卡和课程信息在一个层级，查询

学生卡信息的时候会将课程信息查询出来，此时aggressiveLazyLoading参数设置为false，按照按需求加载解决。

设置局部加载

存在一些信息是需要级联查询的一并查询出来，一些信息是不需要的，此时可以在association，collection 元素加载

 fetchType配置。

fetchType="eager" //立即加载
fetchType="lazy"  //延迟加载
另一个查询方式

将查询的sql的字段，映射到指定的字段上

public List<Student> queryJoinInfo();
<select id="queryJoinInfo" resultMap="studentMap">
	select 
	m1.*,
	m2.card_num,
	m3.course_name,
	if(m1.stu_sex='1',m4.stu_heigth,m5.stu_heigth) stu_heigth,
	if(m1.stu_sex='1',m4.stu_weigth,m5.stu_weigth) stu_weigth,
	if(m1.stu_sex='1',m4.stu_strength,m5.stu_strength) stu_strength 
	from student_info m1
	left join student_card m2 on m1.stu_id = m2.stu_id
	left join course_info m3 on m1.stu_id = m3.stu_id
	left join male_health m4 on m1.stu_id = m4.stu_id
	left join female_health m5 on m1.stu_id = m5.stu_id
  </select>

结果映射定义

<resultMap type="cn.bing.pojo.Student" id="studentMap">
  	<id column="stu_id" property="stuId"/>
  	<result column="stu_age" property="stuAge" />
  	<result column="stu_sex" property="stuSex" />
  	<result column="stu_name" property="stuName"/>
  	<association property="card" 
  	javaType="cn.bing.pojo.StudentCard"
  	column="stu_id"
  	>
  		<id column="stu_id" property="stuId"/>
		<result column="card_num" property="cardNum"/>
		<result column="register_time" property="registerTime" 
		jdbcType="TIMESTAMP" javaType="java.util.Date"
		typeHandler="org.apache.ibatis.type.DateTypeHandler"
		/>
  	</association>
  	<collection property="list" ofType="cn.bing.pojo.Course" foreignColumn="stu_id">
  		<id column="course_id" property="courseId"/>
  		<result column="course_name" property="courseName"/>
  	</collection>
  	<!-- 鉴别器级联，根据性别，关联不同的健康结果集 -->
  	<discriminator javaType="string" jdbcType="TINYINT" column="stu_sex" >
  		<case value="1" resultMap="malehealthBeanMapper"></case>
  		<case value="0" resultMap="femalhealthBeanMapper"></case>
  	</discriminator>
  </resultMap>
  <resultMap type="cn.bing.mapper.StudentMaleHealthBean" 
  id="malehealthBeanMapper" 
  extends="studentMap" 
  >
  <collection property="maleList" ofType="cn.bing.pojo.MaleHealth" >
  	<id column="stu_id" property="stuId"/>
  	<result column="stu_heigth" property="stuHeigth"/>
  	<result column="stu_weigth" property="stuWeigth"/>
  	<result column="stu_strength" property="stuStrength"/>
  </collection>
  </resultMap>
  <resultMap type="cn.bing.mapper.StudentFeMaleHealthBean" 
  id="femalhealthBeanMapper" extends="studentMap">
  <collection property="femaleList" ofType="cn.bing.pojo.FemaleHealth">
  	<id column="stu_id" property="stuId"/>
  	<result column="stu_heigth" property="stuHeigth"/>
  	<result column="stu_weigth" property="stuWeigth"/>
  	<result column="stu_strength" property="stuStrength"/>
  </collection>
  </resultMap>







评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值