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