MYBATIS06_表结构关系一对一、一对多、多对多、延迟加载

①. 使用association一对一

• ①. 需求:一对一查询的需求:查询一个订单,与此同时查询出该订单所属的用户
  

//1.实体类
public class User {
    private int id;
    private String username;
    private String password;
    private Date birthday;
    }
public class Orders {
    private int id;
    private String orderTime;
    private double total;
    /*当前订单属于哪一个用户*/
    private User 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.xiaozhi.mapper.OrderMapperday03">

    <!--一对一-->
    <resultMap id="orderMap" type="com.xiaozhi.domain.Orders">
        <!--手动指定字段与实体属性的映射关系
        column:数据表的字段名称
        property:实体的属性名称
        -->
        <id column="oid" property="id"></id>
        <result column="ordertime" property="orderTime"/>
        <result column="total" property="total"/>
        <!--实现一对一
        property:当前实体(Orders)中的属性名称(private User user)
        javaType:当前实体(Orders)中属性的类型(User)
        -->
        <association property="user" javaType="com.xiaozhi.domain.User">
            <id column="id" property="id"/>
            <result column="username" property="username"/>
            <result column="password" property="password"/>
            <result column="birthday" property="birthday"/>
        </association>
    </resultMap>
    
    <select id="findAll" resultMap="orderMap">
        select o.id oid,o.ordertime,o.total,o.uid,u.*
        from orders o inner join user u
        on o.uid=u.id
    </select>

</mapper>

②. 使用collection一对多

• 一对多查询的需求:查询一个用户,与此同时查询出该用户具有的订单

public class User {
    private int id;
    private String username;
    private String password;
    private Date birthday;
    //代表当前用户具备哪些订单
    private List<Orders> orderList;
    }

    <!--一对多-->
    <resultMap id="userMap" type="com.xiaozhi.domain.User">
        <id column="uid" property="id"/>
        <result column="username" property="username"/>
        <result column="password" property="password"/>
        <result column="birthday" property="birthday"/>
        <!--一对多:配置集合信息
         property:当前实体(User)中的属性名称(private List<Orders> orderList)
         javaType:当前实体(User)中属性的类型(Orders)
        -->
        <collection property="orderList" ofType="com.xiaozhi.domain.Orders">
            <id column="oid" property="id"></id>
            <result column="ordertime" property="orderTime"/>
            <result column="total" property="total"/>
        </collection>
    </resultMap>

   <select id="findAll" resultMap="userMap">
        select u.*,o.id oid,o.ordertime,o.total,o.uid
        from user u inner join orders o
        on u.id=o.uid
   </select>

③. 使用collection多对对

• 多对多查询的需求:查询用户同时查询出该用户的所有角色

public class User {
    private int id;
    private String username;
    private String password;
    private Date birthday;
    //代表当前用户具备哪些订单
    private List<Order> orderList;
    //代表当前用户具备哪些角色
    private List<Role> roleList;
}

public class Role {

    private int id;
    private String rolename;

}

<!--多对多-->
    <resultMap id="userRoleMap" type="com.xiaozhi.domain.User">
        <id column="userId"  property="id"/>
        <result column="username" property="username" />
        <result column="password" property="password" />
        <result column="birthday" property="birthday" />

        <collection property="roleList" ofType="com.xiaozhi.domain.Role">
            <id column="roleId" property="id"/>
            <result column="roleName" property="roleName" />
            <result column="roleDesc" property="roleDesc" />
        </collection>
    </resultMap>

    <!--多对多-->
    <select id="findUserRoleAll" resultMap="userRoleMap">
    select * from
    user u ,sys_user_role sur ,sys_role sr
    where u.id=sur.userid and sur.roleid=sr.id
    </select>

④. 延迟加载使用

• ①. 延迟查询是一对一和一对多查询的延续。

• ②. 在默认的一对一和一对多中,一条SQL就能够查询到所有数据,但是,有的数据有时候一时半会用不上,例如查询员工,捎带获取员工的部门数据,但是部门数据使用的频率低,这种时候可以使用延迟查询,首先获取到所有的员工数据,然后在需要的时候再去获取部门数据。当需要使用数据的时候才去加载既是延迟加载

• ③. 如何开启延迟加载
  

    <!--mybatis的设置选项,可以改变-->
    <settings>
        <setting name="lazyLoadingEnabled" value="true"/>
        <!--由于我们当前的版本是3.4.5,默认已经是false-->
        <!--<setting name="aggressiveLazyLoading " value="false"/>-->
    </settings>

• ④. 1对1延迟加载

	<resultMap id="baseMap1" type="emp"> 
		<id column="id" property="id"/> 
		<result column="name" property="name"/> 
		<result column="age" property="age"/>
		<association property="dept" javaType="Dept" column="deptid" select="queryDeptById">
			<id column="deptid" property="deptid"/> 
			<result column="dname" property="dname"/> 
		</association> 
	</resultMap> 
	<select id="queryDeptById" parameterType="int" resultType="dept"> 
		SELECT * FROM t_dept where deptid = #{deptid} 
	</select> 
	<select id="queryEmp" resultMap="baseMap1"> 
		SELECT *FROM t_emp t1 
	</select>

• ⑤. 1对多的延迟加载

<resultMap id="baseMap2" type="dept"> 
	<id column="deptid" property="deptid"/> 
	<result column="dname" property="dname"/> 
	<collection property="emps" ofType="emp" column="deptid" select="queryEmpByDid"> 
		<id column="id" property="id"/> 
		<result column="name" property="name"/> 
		<result column="age" property="age"/> 
	</collection>
</resultMap>

<select id="queryDept" resultMap="baseMap2"> S
	ELECT* FROM t_dept t1 
</select> 
<select id="queryEmpByDid" parameterType="int" resultType="emp" > 
	SELECT * FROM t_emp where deptid = #{deptid} 
</select>