MyBatis——嵌套查询/延迟加载
一、嵌套查询:
在映射文件中resultMap标签内配置<association>/<collection>标签;
1)association:
1、嵌套结果映射:多表查询;
(1)实体:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
public class User { private Long id; private String name; private Integer age; private UserDetail userDetail; public Long getId() { return id; } public void setId(Long id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public Integer getAge() { return age; } public void setAge(Integer age) { this.age = age; } public UserDetail getUserDetail() { return userDetail; } public void setUserDetail(UserDetail userDetail) { this.userDetail = userDetail; } @Override public String toString() { return "User{" + "id=" + id + ", name='" + name + '\'' + ", age=" + age + ", userDetail=" + userDetail + '}'; } }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
public class UserDetail { private Long uId; private Long balance; public Long getuId() { return uId; } public void setuId(Long uId) { this.uId = uId; } public Long getBalance() { return balance; } public void setBalance(Long balance) { this.balance = balance; } @Override public String toString() { return "UserDetail{" + "uId=" + uId + ", balance=" + balance + '}'; } }
(2)映射表:
property:bean需要对应查询的字段;
resultMap:副表映射结果集;
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.qf.mapper.UserMapper"> <resultMap id="BaseResultMap" type="com.qf.entity.User"> <id column="id" jdbcType="BIGINT" property="id"/> <result column="name" jdbcType="VARCHAR" property="name"/> <result column="age" jdbcType="INTEGER" property="age"/> <association property="userDetail" resultMap="UserDetailBaseResultMap"></association> </resultMap> <resultMap id="UserDetailBaseResultMap" type="com.qf.entity.UserDetail"> <result column="u_id" jdbcType="BIGINT" property="uId"/> <result column="balance" jdbcType="DECIMAL" property="balance"/> </resultMap> <select id="selectDetailById" resultMap="BaseResultMap"> select id,name,age,u_id,balance from user u,user_detail ud where u.id=ud.u_id; </select> </mapper>
2、嵌套查询映射:子查询;
(1)实体:和上面一样;
(2)UserDetail映射文件:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.qf.mapper.UserDetailMapper"> <resultMap id="BaseResultMap" type="com.qf.entity.UserDetail"> <result column="u_id" jdbcType="BIGINT" property="uId"/> <result column="balance" jdbcType="DECIMAL" property="balance"/> </resultMap> <sql id="Base_Column_List"> u_id, balance </sql> <select id="selectById" parameterType="java.lang.Long" resultMap="BaseResultMap"> select <include refid="Base_Column_List"/> from user_detail where u_id = #{uId,jdbcType=BIGINT} </select> </mapper>
(4)User映射文件:
property:bean需要对应查询的字段;
select:查询的子语句;
column:两表匹配的字段;
PS:子表需要有对应的sql语句;
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.qf.mapper.UserMapper"> <resultMap id="BaseResultMap" type="com.qf.entity.User"> <id column="id" jdbcType="BIGINT" property="id"/> <result column="name" jdbcType="VARCHAR" property="name"/> <result column="age" jdbcType="INTEGER" property="age"/> <association property="userDetail" select="com.qf.mapper.UserDetailMapper.selectById" column="id"></association> </resultMap> <select id="selectAll" resultMap="BaseResultMap"> select * from user user; </select> </mapper>
(5)测试结果:
//子查询共查询了四次;
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
DEBUG [main] - ====> Preparing: select u_id, balance from user_detail where u_id = ? DEBUG [main] - ====> Parameters: 1(Long) DEBUG [main] - <==== Total: 1 DEBUG [main] - ====> Preparing: select u_id, balance from user_detail where u_id = ? DEBUG [main] - ====> Parameters: 2(Long) DEBUG [main] - <==== Total: 1 DEBUG [main] - ====> Preparing: select u_id, balance from user_detail where u_id = ? DEBUG [main] - ====> Parameters: 3(Long) DEBUG [main] - <==== Total: 1 DEBUG [main] - ====> Preparing: select u_id, balance from user_detail where u_id = ? DEBUG [main] - ====> Parameters: 4(Long) DEBUG [main] - <==== Total: 1 DEBUG [main] - <== Total: 4 User{id=1, name='ww', age=100, userDetail=UserDetail{uId=1, balance=30}} User{id=2, name='ls', age=15, userDetail=UserDetail{uId=2, balance=40}} User{id=3, name='ws', age=18, userDetail=UserDetail{uId=3, balance=50}} User{id=4, name='ws', age=19, userDetail=UserDetail{uId=4, balance=60}}
2)collection:嵌套集合;
<collection property="userDetail" resultMap="UserDetailBaseResultMap" javaType="java.util.ArrayList" ofType="com.qf.entity.UserDetail"></collection> property:bean需要对应查询的字段; resultMap:副表结果集; javaType:副表返回值类型; ofType:副表entity;
(2)实体类:注意集合属性类型对应;
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
public class User { private Long id; private String name; private Integer age; private List<UserDetail> userDetail; public Long getId() { return id; } public void setId(Long id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public Integer getAge() { return age; } public void setAge(Integer age) { this.age = age; } public List<UserDetail> getUserDetail() { return userDetail; } public void setUserDetail(List<UserDetail> userDetail) { this.userDetail = userDetail; } @Override public String toString() { return "User{" + "id=" + id + ", name='" + name + '\'' + ", age=" + age + ", userDetail=" + userDetail + '}'; } }
(2)User映射文件:
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.qf.mapper.UserMapper"> <resultMap id="BaseResultMap" type="com.qf.entity.User"> <id column="id" jdbcType="BIGINT" property="id"/> <result column="name" jdbcType="VARCHAR" property="name"/> <result column="age" jdbcType="INTEGER" property="age"/> <collection property="userDetail" resultMap="UserDetailBaseResultMap" javaType="java.util.ArrayList" ofType="com.qf.entity.UserDetail"></collection> </resultMap> <resultMap id="UserDetailBaseResultMap" type="com.qf.entity.UserDetail"> <result column="u_id" jdbcType="BIGINT" property="uId"/> <result column="balance" jdbcType="DECIMAL" property="balance"/> </resultMap> <select id="selectAll" resultMap="BaseResultMap"> select id,name,age,u_id,balance from user u,user_detail ud where u.id=ud.u_id; </select> </mapper>
二、延迟加载:
1)开启:mybatis.xml中配置:
<settings> <!-- 启用延迟加载特性,不配置默认关闭该特性--> <setting name="lazyLoadingEnabled" value="true"></setting> <!-- 延迟加载: false;及时加载:true; --> <setting name="aggressiveLazyLoading" value="true"/> </settings>
2)使用上述第一个子查询的例子:
1、实际打印的结果:执行了四次子查询,因为有4条数据;
2、不使用子查询中的字段,在测试类中只获取主查询字段中的Id:
//从日志中可以看到,子查询没有执行;
三、log4j.properties:
//用来打印日志,查看执行了拿些语句;
# Global logging configuration
log4j.rootLogger=DEBUG, stdout
# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n