Mybatis一对多关联映射实例

Mybatis一对多关联映射实例

需求：查询购买了商品的用户的信息和用户的订单信息以及订单的明细。

这句话有点绕，稍微捋一捋，假设天明在京东商城有过一次购物经历

这一次他买了三个旺仔小馒头和4瓶旺仔牛奶

现在的需求就是需要

（1）、查询出天明的信息

（2）、天明下的那个订单的信息

（3）以及订单里面购物清单的详细信息。

定义实体类

用户实体

package com.hl.myabtis.first.beas;

import java.util.Date;

public class User {

    private int id;
    private String username;
    private char sex;
    private Date birthday;
    private String address;
    public String getUsername() {
        return username;
    }
    public void setUsername(String username) {
        this.username = username;
    }
    public char getSex() {
        return sex;
    }
    public void setSex(char 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;
    }
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    @Override
    public String toString() {
        return "User [id=" + id + ", username=" + username + ", sex=" + sex
                + ", birthday=" + birthday + ", address=" + address + "]";
    }

}

订单实体

package com.hl.myabtis.first.beas;

import java.util.Date;
import java.util.List;

public class Orders {

    private Integer id;

    private Integer user_id;

    private String number;

    private Date createtime;

    private String note;

    //用户信息
    private User user;

    //订单明细
    private List<Orderdetail> orderdetails;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public Integer getUserId() {
        return user_id;
    }

    public void setUserId(Integer userId) {
        this.user_id = userId;
    }

    public String getNumber() {
        return number;
    }

    public void setNumber(String number) {
        this.number = number;
    }

    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;
    }
    public User getUser() {
        return user;
    }

    public void setUser(User user) {
        this.user = user;
    }

    public List<Orderdetail> getOrderdetailList() {
        return orderdetails;
    }

    public void setOrderdetailList(List<Orderdetail> orderdetails) {
        this.orderdetails = orderdetails;
    }

    @Override
    public String toString() {
        return "Orders [id=" + id + ", user_id=" + user_id + ", number="
                + number + ", createtime=" + createtime + ", note=" + note
                + ", user=" + user + ", orderdetails=" + orderdetails
                + "]";
    }

}

订单明细实体

package com.hl.myabtis.first.beas;
/**
 *  订单明细
 * @author 浪丶荡
 *
 */
public class Orderdetail {

    private Integer id;

    private Integer orders_id;

    private Integer items_id;

    private Integer items_number;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public Integer getOrders_id() {
        return orders_id;
    }

    public void setOrders_id(Integer orders_id) {
        this.orders_id = orders_id;
    }

    public Integer getItems_id() {
        return items_id;
    }

    public void setItems_id(Integer items_id) {
        this.items_id = items_id;
    }

    public Integer getItems_number() {
        return items_number;
    }

    public void setItems_number(Integer items_number) {
        this.items_number = items_number;
    }

    @Override
    public String toString() {
        return "Orderdetail [id=" + id + ", orders_id=" + orders_id
                + ", items_id=" + items_id + ", items_number=" + items_number
                + "]";
    }

}

mapper映射文件

<!DOCTYPE mapper
    PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
    "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.hl.mybatis.first.mapper.OrdersCustomMapper">

    <!-- 用户信息resultMap -->
    <resultMap type="user" id="userResultMap">
        <!-- 关联查询用户的唯一标识 
            column:指定唯一标识用户信息的列
            javaType:映射到User的对应属性类型
        -->
        <id column="user_id" javaType="java.lang.Integer"/>
        <result column="username" property="username"/>
        <result column="sex" property="sex"/>
        <result column="address" property="address"/>
    </resultMap>
    <!-- 配置Order映射信息
            id：订餐结果集唯一标识
            property：对应订单类属性
    -->
    <resultMap type="Orders" id="ordersResultMap">
        <id column="orderId" property="id"/>
        <result column="user_id" property="user_id"/>
        <result column="number" property="number"/>
        <result column="createtime" property="createtime"/>
        <result column="note" property="note"/>
    </resultMap>

    <!-- 订单明细 resultMap-->
    <resultMap type="com.hl.myabtis.first.beas.Orderdetail" id="ordersDetailResultMap">
        <!-- 订单明细的唯一标识 -->
        <id column="orderdetail_id" property="id"/>
        <result column="itemsId" property="items_id"/>
        <result column="itemsNum" property="items_number"/>
        <result column="ordersID" property="orders_id"/>
    </resultMap>

    <!-- 定义查询订单关联查询用户ResultMap 
        com.hl.myabtis.first.beas.Orders将整个查询的信息映射到Order上
    -->
    <resultMap type="com.hl.myabtis.first.beas.Orders" id="ordersUserResultMap">
        <!-- 配置Order映射信息
            id：订餐结果集唯一标识
            property：对应订单类属性
         -->
        <id column="orderId" property="id"/>
        <result column="user_id" property="user_id"/>
        <result column="number" property="number"/>
        <result column="createtime" property="createtime"/>
        <result column="note" property="note"/>

        <association property="user" resultMap="userResultMap" />
    </resultMap>

    <!-- 查询订单及订单明细的resultMap -->
    <resultMap type="com.hl.myabtis.first.beas.Orders" id="ordersAndOrderdetailResultMap" extends="ordersUserResultMap">
        <!-- 订单以及用户信息通过继承获得 -->
        <!-- 引用订单明细 resultMap-->
        <collection property="orderdetails" resultMap="ordersDetailResultMap" />
    </resultMap>

    <!-- 查询订单关联 查询用户信息以及订单明细  ResultMap映射-->
    <select id="findOrdersAndOrderdetailResultMap" resultMap="ordersAndOrderdetailResultMap">
        SELECT
            o.id as orderId,
            o.note,
            o.createtime,
            o.number,
            o.user_id,
            u.address,
            u.sex,
            u.username,
            od.id AS orderdetail_id,
            od.items_id AS itemsId,
            od.items_num AS itemsNum,
            od.orders_id AS ordersID
        FROM
            USER u,
            orders o,
            orderdetail od
        WHERE
            o.user_id = u.id and o.id = od.orders_id
    </select>   
</mapper>

mapper接口

public interface OrdersCustomMapper {
    //查询订单关联 查询用户信息以及订单明细  ResultMap映射
    public List<Orders> findOrdersAndOrderdetailResultMap() throws Exception;

}

测试用例

package com.hl.mybatis.first.test;

import java.io.InputStream;
import java.util.List;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;

import com.hl.myabtis.first.beas.Orders;
import com.hl.myabtis.first.beas.OrdersCustom;
import com.hl.mybatis.first.mapper.OrdersCustomMapper;

public class OrdersMapperTest {

    private SqlSessionFactory sqlSessionFactory;

    @Before
    public void testBefore() throws Exception{

        String resource = "SqlMapConfig.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource );
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    }
    @Test
    public void testFindOrdersAndOrderdetailResultMap() throws Exception{
        SqlSession sqlSession = sqlSessionFactory.openSession();
        OrdersCustomMapper ordersMapper = sqlSession.getMapper(OrdersCustomMapper.class);
        List<Orders> list = ordersMapper.findOrdersAndOrderdetailResultMap();
        for (Orders order : list) {
            System.out.println(order);
        }
        sqlSession.close();
    }
}

附上源码：http://download.csdn.net/detail/leisure_life/9852850