MyBatis(六) sql片段定義、級聯查詢、巢狀查詢

z1340954953發表於2018-07-09

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>

Sql Server系列：巢狀查詢
2014-12-01
SQLServer巢狀
對比SQL中簡單巢狀查詢與非巢狀查詢CF
2022-03-21
SQL巢狀
巢狀子查詢
2021-09-09
巢狀
巢狀關聯會查詢兩次
2022-09-02
巢狀
Hierarchical Queries 級聯查詢(樹狀結構查詢)
2013-09-26
資料庫 - 連線查詢、巢狀查詢、集合查詢
2015-05-05
資料庫巢狀
SQL語句巢狀查詢問題
2017-06-15
SQL巢狀
兩表關聯查詢：sql、mybatis
2024-04-12
SQLMyBatis
資料庫查詢優化：巢狀查詢
2017-01-24
資料庫優化巢狀
MyBatis關聯查詢
2016-08-28
MyBatis
MySQL——優化巢狀查詢和分頁查詢
2018-11-01
MySql優化巢狀
[冷楓推薦]：資料庫操作，內外聯查詢，分組查詢，巢狀查詢，交叉查詢，多表查詢，語句小結。
2006-05-10
資料庫巢狀
EleasticSearch6.0 巢狀查詢
2020-04-29
AST巢狀
MyBatis初級實戰之六：一對多關聯查詢
2021-01-22
MyBatis
Mysql 巢狀查詢100例子
2022-06-27
MySql巢狀
關聯查詢時使用樹狀查詢要小心
2007-10-02
MyBatis基礎：MyBatis關聯查詢（4）
2017-09-13
MyBatis
es中如何使用巢狀物件查詢
2021-02-08
巢狀物件
MongoDB之資料查詢（巢狀集合）
2017-08-29
MongoDB巢狀
Access/VBA/Excel-13-巢狀查詢
2017-12-16
Excel巢狀
Mybatis查詢
2023-05-19
MyBatis
SQL查詢的：子查詢和多表查詢
2020-11-18
SQL
mybatis查詢列表
2017-06-01
MyBatis
SQL 三表聯合查詢
2019-09-26
SQL
01-sql-聯合查詢
2020-10-18
SQL
解決 mybatis一對多分頁問題聯級查詢
2020-09-25
MyBatis
Transformation之Subquery Un-nesting（子查詢的非巢狀）SU【六】
2015-02-27
ORM巢狀
mybatis多表聯合查詢的寫法
2024-04-19
MyBatis
Spring Data Jpa 的簡單查詢多表查詢HQL，SQL ，動態查詢， QueryDsl ，自定義查詢筆記
2018-08-15
SpringSQL筆記
【SQL查詢】集合查詢之INTERSECT
2016-11-13
SQL
sql遞迴查詢子級
2015-10-13
SQL遞迴
MyBatis從入門到精通(十)：使用association標籤實現巢狀查詢
2019-07-12
MyBatis巢狀
MyBatis從入門到精通(十二)：使用collection標籤實現巢狀查詢
2019-07-17
MyBatis巢狀
Mysql中的巢狀子查詢問題QSBS
2022-03-01
MySql巢狀
oracle 雜湊查詢與巢狀查詢跟表的先後關係測試
2013-06-17
Oracle巢狀
MySQL聯結查詢和子查詢
2018-02-24
MySql
MyBatis初級實戰之五：一對一關聯查詢
2021-01-21
MyBatis
SQL三表左關聯查詢
2016-07-26
SQL

MyBatis(六) sql片段定義、級聯查詢、巢狀查詢

相關文章