Java web开发（14）Mybatis入门_c使用ResultMap实现复杂映射,延迟加载

1背景介绍

POJO类。

getter和setter省略。User，Orders，Orderdetail，Items

下面红色是后来为需求添加，既能表明他们之间相互关系，也为下面的高级映射做准备。

一个用户对应多个订单，所以用户类中添加order的列表。

private List<Orders> ordersList;

一个订单只属于一个用户，所以添加User 对象

private User user;

一个订单有多个订单明细，所以添加orderdetail的列表

private List<Orderdetail> orderdetails;

一个订单明细有一中物品，需要其对象

private Items items;

//User
    private int id;
    private String username;// 用户姓名
    private String sex;// 性别
    private Date birthday;// 生日
    private String address;// 地址
　　private List<Orders> ordersList;
//Orders
    private Integer id;

    private Integer userId;

    private String number;

    private Date createtime;

    private String note;
　　private User user;
　　private List<Orderdetail> orderdetails;


//Orderdetail
    private Integer id;

    private Integer ordersId;

    private Integer itemsId;

    private Integer itemsNum;
    private Items items;
    //Items
private Integer id;

    private String name;

    private Float price;

    private String pic;

    private Date createtime;

    private String detail;

对应的表

user

order

orderdetail

items

建立OrderMapperCustom.java和OrderMapperCustom.xml文件。

2一对多映射

查询语句：

SELECT orders.*,user.`username`,user.`sex`,user.`address`,
orderdetail.`id` orderdetail_id,
orderdetail.`orders_id`,
orderdetail.`items_id`,orderdetail.`items_num`
FROM orders,USER,orderdetail
 WHERE orders.`user_id`=user.`id`
AND orderdetail.orders_id=orders.`id` 

public interface OrderMapperCustom {
    //查询订单(关联用户)及订单明细
        public List<Orders>  findOrdersAndOrderDetailResultMap()throws Exception;;
}

OrderMapperCutom.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="com.legion.mapper.OrderMapperCustom">
    <resultMap type="com.legion.hello.Orders"
        id="OrdersAndOrderDetailResultMap">
        <!-- 订单信息 -->
        <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" />
        <!--Order 类有一个USer属性，查询的关于用户的信息使用association配置到这里 -->
        <association property="user"
            javaType="com.legion.hello.User">
            <id column="user_id" property="id" />
            <result column="username" property="username" />
            <result column="sex" property="sex" />
            <result column="address" property="address" />
        </association>
        <!-- Order 类中有关于Orderdetail的列表，所以这里使用collection -->
        <collection property="orderdetails"
            ofType="com.legion.hello.Orderdetail">
            <id column="orderdetail_id" property="id" />
            <result column="items_id" property="itemsId" />
            <result column="items_num" property="itemsNum" />
            <result column="orders_id" property="ordersId" />
        </collection>
    </resultMap>
    <select id="findOrdersAndOrderDetailResultMap"
        resultMap="OrdersAndOrderDetailResultMap">
        select
        orders.*,user.username,user.sex,user.address,
        orderdetail.id orderdetail_id,
        orderdetail.items_id,
        orderdetail.items_num,
        orderdetail.orders_id
        from orders,user,orderdetail
        where orders.user_id=user.id and orderdetail.orders_id=orders.id
    </select>
</mapper>

测试代码

    @Test
    public void testFindOrdersAndOrderDetailResultMap() throws Exception {

        SqlSession sqlSession = sqlSessionFactory.openSession();
        // 创建代理对象
        OrderMapperCustom ordersMapperCustom = sqlSession
                .getMapper(OrderMapperCustom.class);

        // 调用maper的方法
        List<Orders> list = ordersMapperCustom
                .findOrdersAndOrderDetailResultMap();
        System.out.println("end of test");
        System.out.println(list);

        sqlSession.close();
    }

3多对多查询

 查询语句

SELECT orders.*,user.`username`,user.`sex`,user.`address`,
orderdetail.`id` orderdetail_id,
orderdetail.`orders_id`,
orderdetail.`items_id`,orderdetail.`items_num`,
items.`name` item_name,
items.`detail` item_detail,
items.`price` item_price
FROM orders,USER,orderdetail,items
 WHERE orders.`user_id`=user.`id`
AND orderdetail.orders_id=orders.`id` AND orderdetail.`items_id`=items.`id`

在OrderMapperCustom.java中添加

        //查询用户购买商品信息
        public List<User>  findUserAndItemsResultMap()throws Exception;

在OrderMapperCustom.xml文件添加

<select id="findUserAndItemsResultMap"
        resultMap="UserAndItemsResultMap">
        SELECT
        orders.*,
        USER.username,
        USER.sex,
        USER.address,
        orderdetail.id orderdetail_id,
        orderdetail.items_id,
        orderdetail.items_num,
        orderdetail.orders_id,
        items.name items_name,
        items.detail items_detail,
        items.price items_price
        FROM
        orders,
        USER,
        orderdetail,
        items
        WHERE orders.user_id = user.id AND orderdetail.orders_id=orders.id AND
        orderdetail.items_id = items.id
    </select>
    <resultMap type="com.legion.hello.User"
        id="UserAndItemsResultMap">
        <id column="user_id" property="id" />
        <result column="username" property="username" />
        <result column="sex" property="sex" />
        <result column="address" property="address" />

        <!-- 订单信息 一个用户对应多个订单，使用collection映射 -->
        <collection property="ordersList"
            ofType="com.legion.hello.Orders">
            <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" />

            <!-- 订单明细 一个订单包括 多个明细 -->
            <collection property="orderdetails"
                ofType="com.legion.hello.Orderdetail">
                <id column="orderdetail_id" property="id" />
                <result column="items_id" property="itemsId" />
                <result column="items_num" property="itemsNum" />
                <result column="orders_id" property="ordersId" />

                <!-- 商品信息 一个订单明细对应一个商品 -->
                <association property="items"
                    javaType="com.legion.hello.Items">
                    <id column="items_id" property="id" />
                    <result column="items_name" property="name" />
                    <result column="items_detail" property="detail" />
                    <result column="items_price" property="price" />
                </association>
            </collection>
        </collection>
    </resultMap>

测试代码

@Test
    public void testFindUserAndItemsResultMap() throws Exception {

        SqlSession sqlSession = sqlSessionFactory.openSession();
        // 创建代理对象
        OrderMapperCustom ordersMapperCustom = sqlSession
                .getMapper(OrderMapperCustom.class);

        // 调用maper的方法
        List<User> list = ordersMapperCustom.findUserAndItemsResultMap();

        System.out.println(list);

        sqlSession.close();
    }

 3延迟加载

先看一个简单查询

SELECT *FROM orders;

分别添加接口和配置

public List<Orders> findOrdersUserLazyLoading()throws Exception;

    <select id="findOrdersUserLazyLoading"
        resultMap="OrdersUserLazyLoadingResultMap">
        SELECT * FROM orders
    </select>
    <!-- 延迟加载的resultMap -->
    <resultMap type="com.legion.hello.Orders"
        id="OrdersUserLazyLoadingResultMap">
        <!--对订单信息进行映射配置 -->
        <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" />
    </resultMap>

测试代码

    @Test
    public void testFindOrdersUserLazyLoading() throws Exception {
        SqlSession sqlSession = sqlSessionFactory.openSession();// 创建代理对象
        OrderMapperCustom ordersMapperCustom = sqlSession
                .getMapper(OrderMapperCustom.class);
        // 查询订单信息（单表）
        List<Orders> list = ordersMapperCustom.findOrdersUserLazyLoading();
        
    }

这个例子中延迟加载的概念：

本例子只会查询订单，返回一个Orders列表。但是每一个Orders对象中的属性User为null。

此时在配置中添加select查询，根据user_id查询User对象。关键：这个查询在调用findOrdersUserLazyLoading的时候

并不会作用，而是在调用Orders的getUser方法的时候才会作用。

SqlMapConfig.xml文件添加

    <settings>
        <!-- 打开延迟加载 的开关 -->
        <setting name="lazyLoadingEnabled" value="true" />
        <!-- 将积极加载改为消极加载即按需要加载 -->
        <setting name="aggressiveLazyLoading" value="false" />
    </settings>

新的映射配置

    <select id="findOrdersUserLazyLoading"
        resultMap="OrdersUserLazyLoadingResultMap">
        SELECT * FROM orders
    </select>
    <!-- 延迟加载的resultMap -->
    <resultMap type="com.legion.hello.Orders"
        id="OrdersUserLazyLoadingResultMap">
        <!--对订单信息进行映射配置 -->
        <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" />
        <association property="user" javaType="com.legion.hello.User"
        select="com.legion.mapper.UserMapper.findUserById" column="user_id">
        </association>
    </resultMap>

测试代码

    @Test
    public void testFindOrdersUserLazyLoading() throws Exception {
        SqlSession sqlSession = sqlSessionFactory.openSession();// 创建代理对象
        OrderMapperCustom ordersMapperCustom = sqlSession
                .getMapper(OrderMapperCustom.class);
        // 查询订单信息（单表）
        List<Orders> list = ordersMapperCustom.findOrdersUserLazyLoading();
        for(Orders o:list) {
            System.out.println(o.getUser());
        }
    }