需求:查询用户信息,关联查询该用户购买的商品信息
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 , items
where
orders.user_id = user.id
and orders.id = orderdetail.orders_id
and 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;
}
@Override
public 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 , items
where
orders.user_id = user.id
and orders.id = orderdetail.orders_id
and 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.sex
from 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_num
from orders , user , orderdetail
where
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 , items
where
orders.user_id = user.id
and orders.id = orderdetail.orders_id
and items.id = orderdetail.items_id
</select>
</mapper>
3OrderMapper接口
public List<User> findOrderAndItemRstMap();
4测试
@Test
public void testFindOrderAndItemRstMap() {
SqlSession sqlSession = sqlSessionFactory.openSession();
OrdersMapper ordersMapper = sqlSession.getMapper(OrdersMapper.class);
List<User> list = ordersMapper.findOrderAndItemRstMap();
sqlSession.close();
}