一对一关联
一对一,使用 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文件
- 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>
- 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
属性。
- 使用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>
- 使用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)
}
}
-
当lazyLoadingEnabled为false,表示不开启懒加载。
在执行《1》后,就立即会发送多个sql,查询关联的表。
select * from user where id=?
select * from orders where user_id=?
-
当 lazyLoadingEnabled 为 true,并且 aggressiveLazyLoading 为 false 时,是深度延迟的情况,只有在使用到orders才会查数据库。
在执行《1》后,会发送select * from user where id=?
,《2》句不会发sql,《3》句会发送select * from orders where user_id=?
-
当 lazyLoadingEnabled 为 true,并且 aggressiveLazyLoading 为 true 时,是侵入式延迟的情况,一使用user的属性(即使不是orders)就会查询数据库。
在执行《1》后,会发送select * from user where id=?
,《2》句就会发select * from orders where user_id=?
,《3》句当然就不会发送了