3.多对多查询
1.需求:查询用户及用户购买商品信息
2.sql语句
查询主表是:用户表
关联表:由于用户商品没有直接关联,通过订单明细进行关联;orders, orderdetail,items
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
eg
在用户表user中添加订单表order属性 订单属性里有订单明细orderdetail属性 订单明细表里添加items属性 实现关联
1.user类
public class User {
private int id;
private String username;
private String sex;
private Date birthday;
private String address;
private List<Orders> ordersList;
public List<Orders> getOrdersList() {
return ordersList;
}
public void setOrdersList(List<Orders> ordersList) {
this.ordersList = ordersList;
}
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 "User [id=" + id + ", username=" + username + ", sex=" + sex + ", birthday=" + birthday + ", address="
+ address + ", ordersList=" + ordersList + "]";
}
}
2.orders类
public class Orders {
private Integer id;
private Integer userId;
private String number;
private Date createtime;
private String note;
// 用户的信息
private User user;
//订单明细
private List<Orderdetail> orderdetails;
public List<Orderdetail> getOrderdetails() {
return orderdetails;
}
public void setOrderdetails(List<Orderdetail> orderdetails) {
this.orderdetails = orderdetails;
}
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
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 Integer getUserId() {
return userId;
}
public void setUserId(Integer userId) {
this.userId = userId;
}
@Override
public String toString() {
return "Orders [id=" + id + ", userId=" + userId + ", number=" + number + ", createtime=" + createtime
+ ", note=" + note + ", user=" + user + ", orderdetails=" + orderdetails + "]";
}
}
3.orderdetail类
public class Orderdetail {
private Integer id;
private Integer itemsId;
private Integer itemsNum;
private Integer ordersId;
//订单明细和商品信息
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 getItemsId() {
return itemsId;
}
public void setItemsId(Integer itemsId) {
this.itemsId = itemsId;
}
public Integer getItemsNum() {
return itemsNum;
}
public void setItemsNum(Integer itemsNum) {
this.itemsNum = itemsNum;
}
public Integer getOrdersId() {
return ordersId;
}
public void setOrdersId(Integer ordersId) {
this.ordersId = ordersId;
}
@Override
public String toString() {
return "Orderdetail [id=" + id + ", itemsId=" + itemsId + ", itemsNum=" + itemsNum + ", ordersId=" + ordersId
+ ", items=" + items + "]";
}
}
4.items类
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;
}
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;
}
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;
}
@Override
public String toString() {
return "Items [id=" + id + ", name=" + name + ", price=" + price + ", pic=" + pic + ", createtime=" + createtime
+ ", detail=" + detail + "]";
}
}
5.mapper映射
<mapper namespace="cn.mybatis.mapper.OrdersMapperCustom">
<resultMap type="cn.mybatis.po.User" id="UserAndItemsResultMap">
<id column="user_id" property="id"/>
<result column="username" property="username"/>
<result column="sex" property="sex"/>
<result column="adress" property="adress"/>
<!-- 订单信息一对多 -->
<collection property="ordersList" ofType="cn.mybatis.po.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"/>
<!-- 订单明细 1对多 -->
<collection property="orderdetails" ofType="cn.mybatis.po.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"/>
<!-- 商品信息 1对1 -->
<association property="items" javaType="cn.mybatis.po.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>
<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>
</mapper>
6.mapper接口
public List<User> findUserAndItemsResultMap() throws Exception;
7.测试类
@Test
public void testfindUserAndItemsResultMap() throws Exception{
//获取Session
SqlSession session = sqlSessionFactory.openSession();
//获取Mapper代理对象
OrdersMapperCustom ordersMapperCustom = session.getMapper(OrdersMapperCustom.class);
//调用代理方法
List<User> list = ordersMapperCustom.findUserAndItemsResultMap();
System.out.println(list);
session.close();
}
小结
1.多对多查询总结
将查询用户购买的商品信息明细清单(用户名,用户地址,购买商品名称,购买商品时间,购买商品数量)
针对上边的需求就使用resultType将查询到的记录映射到一个扩展的pojo中,很简单实现明细清单的功能
一对多是多对多的特例,如下需求:
查询用户购买商品的商品信息,用户和商品的关系是多对多的关系
需求1:
查询字段;用户账号,用户名称,用户性别,商品名称,商品价格(常见)
企业开发中常见明细列表,用户购买商品明细列表
使用resultType将上边查询列映射到pojo中
需求2:
查询字段,用户账号,用户名称,购买商品数量,商品明细(鼠标移上显示明细(延迟加载))
使用resultMap将用户购买的商品明细列表映射到user对象中
总结
使用resultMap是针对那些对查询结果映射有特殊要求的功能,比如特殊要求映射成list中包括多个list
2.resultMap总结
1.resultType
作用:将查询结果按照sql列名pojo属性名一致性映射到pojo中
场合:常见一些明细记录的展示,比如用户购买商品明细,将关联查询信息全部展示在页面时,
此时可直接使用resultType将每一条记录映射到pojo中,在前端页面遍历list(list中是pojo)即可
2.resultMap
作用:使用association和collection完成一对一和一对多高级映射(对结果有特殊的映射要求)
association:
作用:将关联查询信息映射到一个pojo对象中
场合:为了方便查询关联信息可以使用association将关联订单信息映射为用户对象的pojo属性中,
比如,查询订单及关联用户信息
使用resultMap无法将查询结果映射到pojo对象的pojo属性中,
根据对结果集查询遍历的需要选择使用resultMap和resultType
collection:
作用:将关联查询信息映射到一个list集合中
场合:为了方便查询遍历关联信息可以使用collection将关联信息映射到list集合中,
比如,查询用户权限范围模块及模块下的菜单,可以使用collection将模块映射到模块list中,
将菜单列表映射到模块对象的菜单list属性中,这样做的目的也是方便对查询结果集进行遍历查询
如果使用resultType无法将查询结果映射到list集合中