多对多映射

需求：查询用户信息，关联查询该用户购买的商品信息

sql:主信息user， 从信息 orders orderdetail item

user---1:m --> orders--1:m-->orderdetail --1:1--> item

select orders.id, orders.user_id, orders.number, user.username, user.sex , orderdetail.id  detailId, orderdetail.items_id , orderdetail.items_num, items.name , items.price from orders , user  , orderdetail , itemswhere orders.user_id = user.id and orders.id =  orderdetail.orders_idand items.id = orderdetail.items_id

1修改po类

在User内添加 成员属性 private List<Orders> orderList ;

package cn.itcast.po;import java.util.Date;import java.util.List;public class User {private int id;private String username;private String sex;private Date birthday;private String address;private List<Orders> orderList ;public List<Orders> getOrderList() {return orderList;}public void setOrderList(List<Orders> orderList) {this.orderList = orderList;}public int getId() {return id;}public void setId(int id) {this.id = id;}public String getUsername() {return username;}public void setUsername(String username) {this.username = username;}public String getSex() {return sex;}public void setSex(String 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;}@Overridepublic String toString() {return "\nUser [id=" + id + ", username=" + username + ", sex=" + sex+ ", birthday=" + birthday + ", address=" + address + "]";}}

在Order内添加  private List<Orderdetail> detaiList;

package cn.itcast.po;import java.util.Date;import java.util.List;public class Orders {    private Integer id;    private Integer userId;    private String number;    private Date createtime;    private String note;    private List<Orderdetail> detaiList;        public List<Orderdetail> getDetaiList() {return detaiList;}public void setDetaiList(List<Orderdetail> detaiList) {this.detaiList = detaiList;}public Integer getId() {        return id;    }    public void setId(Integer id) {        this.id = id;    }    public Integer getUserId() {        return userId;    }    public void setUserId(Integer userId) {        this.userId = userId;    }    public String getNumber() {        return number;    }    public void setNumber(String number) {        this.number = number == null ? null : number.trim();    }    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 == null ? null : note.trim();    }}

在Orderdetail内添加  private Items items;

package cn.itcast.po;public class Orderdetail {    private Integer id;    private Integer ordersId;    private Integer itemsId;    private Integer itemsNum;    private Items items;        public Items getItems() {return items;}public void setItems(Items items) {this.items = items;}public Integer getId() {        return id;    }    public void setId(Integer id) {        this.id = id;    }    public Integer getOrdersId() {        return ordersId;    }    public void setOrdersId(Integer ordersId) {        this.ordersId = ordersId;    }    public Integer getItemsId() {        return itemsId;    }    public void setItemsId(Integer itemsId) {        this.itemsId = itemsId;    }    public Integer getItemsNum() {        return itemsNum;    }    public void setItemsNum(Integer itemsNum) {        this.itemsNum = itemsNum;    }}

package cn.itcast.po;import java.util.Date;public class Items {    private Integer id;    private String name;    private Float price;    private String pic;    private Date createtime;    private String detail;    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 == null ? null : name.trim();    }    public Float getPrice() {        return price;    }    public void setPrice(Float price) {        this.price = price;    }    public String getPic() {        return pic;    }    public void setPic(String pic) {        this.pic = pic == null ? null : pic.trim();    }    public Date getCreatetime() {        return createtime;    }    public void setCreatetime(Date createtime) {        this.createtime = createtime;    }    public String getDetail() {        return detail;    }    public void setDetail(String detail) {        this.detail = detail == null ? null : detail.trim();    }}

2映射文件

<!-- 多对多映射 --><resultMap type="cn.itcast.po.User" id="OrderAndItemRstMap"><!-- 用户信息 一对多 --><id column="user_id" property="id"/><result column="username" property="username"/><result column="sex" property="sex"/><!-- 订单信息  一对多 --><collection property="orderList" ofType="cn.itcast.po.Orders"><id column="id" property="id"/><result column="user_id" property="userId"/><result column="number" property="number"/><!-- 订单明细信息 一对一--><collection property="detaiList" ofType="cn.itcast.po.Orderdetail"><id column="detailId" property="id"/><result column="items_id" property="itemsId"/><result column="items_num" property="itemsNum"/><!-- 商品信息 --><association property="items" javaType="cn.itcast.po.Items"><id column="items_id" property="id"/><result column="name" property="name"/><result column="price" property="price"/></association></collection></collection></resultMap><select id="findOrderAndItemRstMap" resultMap="OrderAndItemRstMap">select orders.id, orders.user_id, orders.number, user.username, user.sex , orderdetail.id  detailId, orderdetail.items_id , orderdetail.items_num, items.name , items.price from orders , user  , orderdetail , itemswhere orders.user_id = user.id and orders.id =  orderdetail.orders_idand items.id = orderdetail.items_id</select></mapper>

<?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.itcast.mapper.OrdersMapper"><!-- 一对一映射之 resultType --><select id="findOrderAndUser" resultType="cn.itcast.po.OrderExt">select orders.id, orders.user_id, orders.number, user.username, user.sexfrom orders , user where orders.user_id = user.id</select><!-- 一对一之 resultMap --><resultMap type="cn.itcast.po.OrderExt" id="OrderAndUserRstMap"><!-- 订单信息 --><id column="id" property="id"/><result column="user_id" property="userId"/><result column="number" property="number"/><!-- 用户信息一对一  property:关联信息查询结果将要映射的扩展类中的对象属性名称--><association property="user" javaType="cn.itcast.po.User"><id column="user_id" property="id"/><result column="username" property="username"/><result column="sex" property="sex"/></association></resultMap><select id="findOrderAndUserRstMap" resultMap="OrderAndUserRstMap">select     orders.id, orders.user_id, orders.number, user.username, user.sex    from orders , user     where orders.user_id = user.id</select><!-- 一对多之 resultMap --><resultMap type="cn.itcast.po.OrderExt" id="OrderAndDetailRstMap" extends="OrderAndUserRstMap"><!-- 订单明细信息 --><collection property="detaiList" ofType="cn.itcast.po.Orderdetail"><id column="detailId" property="id"/><result column="items_id" property="itemsId"/><result column="items_num" property="itemsNum"/></collection></resultMap><select id="findOrderAndDetailRstMap" resultMap="OrderAndDetailRstMap">select orders.id,orders.user_id,orders.number, user.username,user.sex ,orderdetail.id  detailId,orderdetail.items_id ,orderdetail.items_numfrom orders , user  , orderdetailwhere orders.user_id = user.id and orders.id =  orderdetail.orders_id</select><!-- 多对多映射 --><resultMap type="cn.itcast.po.User" id="OrderAndItemRstMap"><!-- 用户信息 一对多 --><id column="user_id" property="id"/><result column="username" property="username"/><result column="sex" property="sex"/><!-- 订单信息  一对多 --><collection property="orderList" ofType="cn.itcast.po.Orders"><id column="id" property="id"/><result column="user_id" property="userId"/><result column="number" property="number"/><!-- 订单明细信息 一对一--><collection property="detaiList" ofType="cn.itcast.po.Orderdetail"><id column="detailId" property="id"/><result column="items_id" property="itemsId"/><result column="items_num" property="itemsNum"/><!-- 商品信息 --><association property="items" javaType="cn.itcast.po.Items"><id column="items_id" property="id"/><result column="name" property="name"/><result column="price" property="price"/></association></collection></collection></resultMap><select id="findOrderAndItemRstMap" resultMap="OrderAndItemRstMap">select orders.id, orders.user_id, orders.number, user.username, user.sex , orderdetail.id  detailId, orderdetail.items_id , orderdetail.items_num, items.name , items.price from orders , user  , orderdetail , itemswhere orders.user_id = user.id and orders.id =  orderdetail.orders_idand items.id = orderdetail.items_id</select></mapper>

3OrderMapper接口

public List<User> findOrderAndItemRstMap();

4测试

@Testpublic void testFindOrderAndItemRstMap() {SqlSession sqlSession = sqlSessionFactory.openSession();OrdersMapper ordersMapper = sqlSession.getMapper(OrdersMapper.class);List<User> list = ordersMapper.findOrderAndItemRstMap();sqlSession.close();}