Mybatis学习之关联查询与懒加载

一对一关联

一对一，使用 associate 标签

嵌套查询

EmployeeMapper.xml

<?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="cn.htj.dao.EmployeeMapper">
	<resultMap id="BaseResultMap" type="cn.htj.domain.Employee">
		<id property="id" column="id"/>
		<result property="name" column="name"/>
	</resultMap>
	<resultMap id="EmpDeptMap" type="cn.htj.domain.Employee" extends="BaseResultMap">
		<association property="dept" column="dept_id"
			select="cn.htj.dao.DepartmentMapper.findById"/>
	</resultMap>
	<select id="findById" parameterType="Integer" resultMap="EmpDeptMap">
		select * from employee where id = #{id}
	</select>
</mapper>

DepartmentMapper.xml

<?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="cn.htj.dao.DepartmentMapper">
	<select id="findById" parameterType="int" resultType="cn.htj.domain.Department">
		select * from department where id = #{id}
	</select>
</mapper>

联合查询

<!-- 联合查询 -->
<resultMap id="EmpDeptMap2" type="cn.htj.domain.Employee" extends="BaseResultMap">
	<association property="dept" javaType="cn.htj.domain.Department">
		<id property="id" column="id"/>
		<result property="name" column="name"/>
	</association>
</resultMap>
<select id="findById2" parameterType="Integer" resultMap="EmpDeptMap2">
	select e.*, d.* from employee e, department d where e.dept_id = d.id and e.id = #{id}
</select>

---

POJO类

public class Employee {
	private Integer id;
	private String name;
	private Department dept;
	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public Department getDept() {
		return dept;
	}
	public void setDept(Department dept) {
		this.dept = dept;
	}
}

-----
public class Department {
	private Integer id;
	private String name;
	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	@Override
	public String toString() {
		return "Department [id=" + id + ", name=" + name + "]";
	}
}

Mapper接口

public interface EmployeeMapper {
	public Employee findById(Integer id);
	public Employee findById2(Integer id);
}
--------
public interface DepartmentMapper {
	public Department findById(Integer id);
}

Mapper XML文件

1. EmployeeMapper.xml

<?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="cn.htj.dao.EmployeeMapper">
	<resultMap id="BaseResultMap" type="cn.htj.domain.Employee">
		<id property="id" column="id"/>
		<result property="name" column="name"/>
	</resultMap>
	<!-- 嵌套查询 -->
	<resultMap id="EmpDeptMap" type="cn.htj.domain.Employee" extends="BaseResultMap">
		<association property="dept" column="dept_id"
			select="cn.htj.dao.DepartmentMapper.findById"/>
	</resultMap>
	<select id="findById" parameterType="Integer" resultMap="EmpDeptMap">
		select * from employee where id = #{id}
	</select>
	
	<!-- 联合查询 -->
	<resultMap id="EmpDeptMap2" type="cn.htj.domain.Employee" extends="BaseResultMap">
		<association property="dept" javaType="cn.htj.domain.Department">
			<id property="id" column="id"/>
			<result property="name" column="name"/>
		</association>
	</resultMap>
	<select id="findById2" parameterType="Integer" resultMap="EmpDeptMap2">
		select e.*, d.* from employee e, department d where e.dept_id = d.id and e.id = #{id}
	</select>
</mapper>

2. DepartmentMapper.xml

<?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="cn.htj.dao.DepartmentMapper">
	<select id="findById" parameterType="int" resultType="cn.htj.domain.Department">
		select * from department where id = #{id}
	</select>
</mapper>

---

一对多关联

一对多，使用 collection 标签

嵌套查询

例如，在 UserMapper.xml 中

<!-- 嵌套查询 -->
<resultMap type="cn.htj.domain.User" id="userAndOrders1">
	<id property="id" column="id"/>
	<result property="username" column="username"/>
	<result property="birthday" column="birthday"/>
	<result property="sex" column="sex"/>
	<result property="address" column="address"/>
	<collection property="orders" ofType="cn.htj.domain.Orders" column="id"
		select="cn.htj.dao.OrdersMapper.selectOrdersById"/>
</resultMap>
<select id="selectUserOrdersById" parameterType="Integer" resultMap="userAndOrders1">
	select * from user where id=#{id}
</select>

在OrdersMapper.xml 中

	<select id="selectOrdersById" resultType="cn.htj.domain.Orders">
		select * from orders where user_id=#{user_id}
	</select>

联合查询（连接查询）

首先，保证User类里有 List<Orders> orders 属性。

1. 使用resultMap

<!-- 联合查询 1 -->
<resultMap type="cn.htj.domain.User" id="userAndOrders2">
	<id property="id" column="id"/>
	<result property="username" column="username"/>
	<result property="birthday" column="birthday"/>
	<result property="sex" column="sex"/>
	<result property="address" column="address"/>
	<collection property="orders" ofType="cn.htj.domain.Orders">
		<id property="id" column="id"/>
		<result property="number" column="number"/>
	</collection>
</resultMap>
<select id="selectUserOrdersById2" parameterType="Integer" resultMap="userAndOrders2">
	select u.*,o.id,o.number from user u, orders o where u.id=o.user_id and u.id=#{id}
</select>

2. 使用POJO类，resultType

<!-- 联合查询 2 (使用POJO存储结果) -->
<select id="selectUserOrdersById3" parameterType="Integer" resultType="cn.htj.domain.UserOrders">
	select u.id,u.username,u.birthday,o.id oid,o.number from user u, orders o where u.id=o.user_id and u.id=#{id}
</select>

pojo类：

public class UserOrders {
	private Integer id;
	private String username;
	private Date birthday;
	private String sex;
	private String address;
	private Integer oid;
	private String number;
	//getter setter ...
}

UserMapper.java

public interface UserMapper {
	public User selectUserOrdersById(Integer id);
	public List<User> selectUserOrdersById2(Integer uid);
	public List<UserOrders> selectUserOrdersById3(Integer uid);
}

懒加载

懒加载又称为延迟加载

• 直接加载：执行完对主加载对象的 select 语句，马上执行对关联对象的 select 查询。
• 侵入式延迟： 执行对主加载对象的查询时，不会执行对关联对象的查询。但当要访问主加载对象的详情属性时，就会马上执行关联对象的select查询。
• 深度延迟： 执行对主加载对象的查询时，不会执行对关联对象的查询。访问主加载对象的详情时也不会执行关联对象的select查询。只有当真正访问关联对象的详情时，才会执行对关联对象的 select 查询。

需要注意的是，延迟加载要求，对关联对象的查询与主加载对象的查询必须分别进行的select语句，不能是使用多表连接所进行的select查询。

所以，嵌套查询才能懒加载。

在Mybatis的配置文件中

	<!--全局参数设置-->
	<settings>
	    <!--延迟加载总开关-->
	    <setting name="lazyLoadingEnabled" value="true"/>
	    <!--侵入式延迟加载开关-->
	    <!--3.4.1版本之前默认是true，之后默认是false-->
	    <setting name="aggressiveLazyLoading" value="true"/>
	</settings>

测试

public class TestUserDao {
	// spring上下文
	private ApplicationContext ctx;
	@Before
	public void setUp() throws Exception {
		// 读取spring的上下文，然后封装到ctx
		ctx = new ClassPathXmlApplicationContext("beans.xml");
	}
	@Test
	public void test01() throws Exception {
		UserMapper mapper = (UserMapper) ctx.getBean("userMapper");
		User u1 = mapper.selectUserOrdersById(1);//《1》
		System.out.println(u1.getId());//《2》
		System.out.println(u1);//《3》 （User的toString使用到了orders）
	}
}

1. 当lazyLoadingEnabled为false，表示不开启懒加载。
   在执行《1》后，就立即会发送多个sql，查询关联的表。
   select * from user where id=?
select * from orders where user_id=?

2. 当 lazyLoadingEnabled 为 true，并且 aggressiveLazyLoading 为 false 时，是深度延迟的情况，只有在使用到orders才会查数据库。
   在执行《1》后，会发送 select * from user where id=? ，《2》句不会发sql，《3》句会发送 select * from orders where user_id=?

3. 当 lazyLoadingEnabled 为 true，并且 aggressiveLazyLoading 为 true 时，是侵入式延迟的情况，一使用user的属性（即使不是orders）就会查询数据库。
   在执行《1》后，会发送 select * from user where id=? ，《2》句就会发 select * from orders where user_id=?，《3》句当然就不会发送了