Mybatis 学习笔记(六)——延迟加载
一、延迟加载介绍
延迟加载的目的是为了加快查询速度,提升数据库性能。对于一个复杂的查询sql,在业务许可的情况下,我们可以用两种方式来提升查询速度(Mybatis环境),让数据库的性能蹭蹭的往上提升。第一种是将这个复查查询分成两个 statement 先执行其中一个,然后根据需求在 Service 中调用执行另一个 statement ;第二种是通过延迟加载的方式(按需加载)。下面重点介绍通过延迟加载的方式实现数据库性能的提升。
二、延迟加载的实现
这里以查询订单的用户信息为例。
(一)数据库结构
CREATE TABLE `orders` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL COMMENT '下单用户id',
`number` varchar(32) NOT NULL COMMENT '订单号',
`createtime` datetime NOT NULL COMMENT '创建订单时间',
`note` varchar(100) DEFAULT NULL COMMENT '备注',
PRIMARY KEY (`id`),
KEY `FK_orders_1` (`user_id`),
CONSTRAINT `FK_orders_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
CREATE TABLE `user` (
`ID` int(10) NOT NULL AUTO_INCREMENT,
`USERNAME` varchar(255) DEFAULT NULL,
`SEX` varchar(255) DEFAULT NULL,
`birthday` date DEFAULT NULL,
`address` varchar(255) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4;
(二)编写POJO类
/mybatis01/src/com/po/Orders.java
package com.po;
import java.util.Date;
import java.util.List;
/**
* 订单类
* @author 欧阳
*
*/
public class Orders {
private Integer id; //id
private Integer userId; //用户id
private String number; //数量
private Date createtime; //创建时间
private String note; //备注
private User user; //用户信息
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public Integer getUserId() {
return userId;
}
public void setUserId(Integer userId) {
this.userId = userId;
}
public String getNumber() {
return number;
}
public void setNumber(String number) {
this.number = number == null ? null : number.trim();
}
public Date getCreatetime() {
return createtime;
}
public void setCreatetime(Date createtime) {
this.createtime = createtime;
}
public String getNote() {
return note;
}
public void setNote(String note) {
this.note = note == null ? null : note.trim();
}
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
}
/mybatis01/src/com/po/User.java
package com.po;
import java.util.Date;
import java.util.List;
/**
* 用户类
* @author 欧阳
*
*/
public class User {
private int id; //id
private String username; //用户名
private String sex; //性别
private Date birthday; //生日
private String address; //地址
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
}
(三)编写Mapper文件
/mybatis01/src/config/mapper/OrdersCustomMapper.xml
<!--
延迟加载 :延迟加载的目的是为了提高查询速度,提升数据库性能
需求:查询订单的用户信息
只有resultMap中的association和collection才有延迟加载功能
-->
<resultMap type="com.po.Orders" id="OrdersUserLazyLoading">
<!-- 配置映射的订单信息 -->
<id column="id" property="id"/>
<result column="user_id" property="userId"/>
<result column="number" property="number"/>
<result column="createtime" property="createtime"/>
<result column="note" property="note"/>
<!--
select:指定延迟加载需要执行的statement的id(是根据userId查询用户信息的statement)
column:Orders订单信息中关联User用户信息查询的列,是user_id
fetchType="lazy":启用延时加载,可在SqlMapConfig.xml中setting中配置全局的
-->
<association property="user" select="findUserById"
column="user_id" fetchType="lazy">
</association>
</resultMap>
<!-- 查询订单信息 -->
<select id="findOrdersUserLazyLoading" resultMap="OrdersUserLazyLoading">
select * from orders
</select>
<!-- 根据用户id查询用户信息 -->
<select id="findUserById" parameterType="int" resultType="user">
select * from user where id = #{id}
</select>
在上述代码中的 association 的属性 fetchType="lazy"
设置后才启动延时加载,也可不在此设置,而在配置文件中设置全局参数 lazyLoadingEnabled
和 aggressiveLazyLoading
,,但你会发现只设置 lazyLoadingEnabled 也好使,但是我要说的是,如果这样设置可能会带来一想不到的问题,所以两个都要设置。
<!-- 全局配置参数 -->
<settings>
<!-- 全局性设置懒加载 -->
<setting name="lazyLoadingEnabled" value="true"/>
<!--
当设置为‘true’的时候,懒加载的对象可能被任何懒属性全部加载。
否则,每个属性都按需加载。
-->
<setting name="aggressiveLazyLoading" value="false"/>
</settings>
(四)编写Mapper接口
/mybatis01/src/com/mapper/OrdersCustomMapper.java
/**
* 延迟加载,查询订单信息,关联查询创建订单的用户信息
*/
public List<Orders> findOrdersUserLazyLoading() throws Exception;
(五)JUnit 测试
/mybatis01/test/com/mapper/OrdersCustomMapperTest.java
@Test
public void testFindOrdersUserLazyLoading() {
SqlSession sqlsession = sqlSessionFactory.openSession();
OrdersCustomMapper mapper = sqlsession
.getMapper(OrdersCustomMapper.class);
try {
List<Orders> orders = mapper.findOrdersUserLazyLoading();
//对orders进行遍历
for(Orders entry : orders) {
//在调用entry.getUser() 时 去查询用户信息,这里是按需加载(延迟加载)
System.out.println(entry.getUser().getUsername());
}
} catch (Exception e) {
e.printStackTrace();
}
}
(六)测试结果验证
当没有注释System.out.println(entry.getUser().getUsername());
时,日志输出如下:
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - Opening JDBC Connection
DEBUG [main] - Created connection 2100440237.
DEBUG [main] - Setting autocommit to false on JDBC Connection [[email protected]]
DEBUG [main] - ==> Preparing: select * from orders
DEBUG [main] - ==> Parameters:
DEBUG [main] - <== Total: 3
DEBUG [main] - ==> Preparing: select * from user where id = ?
DEBUG [main] - ==> Parameters: 1(Integer)
DEBUG [main] - <== Total: 1
张三
张三
DEBUG [main] - ==> Preparing: select * from user where id = ?
DEBUG [main] - ==> Parameters: 10(Integer)
DEBUG [main] - <== Total: 1
测试
而注释System.out.println(entry.getUser().getUsername());
后,日志输出如下:
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - Opening JDBC Connection
DEBUG [main] - Created connection 2100440237.
DEBUG [main] - Setting autocommit to false on JDBC Connection [[email protected]]
DEBUG [main] - ==> Preparing: select * from orders
DEBUG [main] - ==> Parameters:
DEBUG [main] - <== Total: 3
根据这两次的日志输出情况可以看出,查询订单的用户信息是延迟加载的(按需加载)。