一、 一对多查询
1.1 需求
查询所有订单信息及订单下的订单明细信息。
1.2 sql语句
这里我们需要查询的表有订单表和订单详情表
主查询表:订单表
关联查询表:订单明细
SELECT
orders.*,
user.username,
user.sex ,
orderdetail.id orderdetail_id,
orderdetail.items_num,
orderdetail.items_id
FROM
orders,
USER,
orderdetail
WHERE orders.user_id = user.id AND orders.id = orderdetail.orders_id
1.3 resultMap进行一对多映射思路
resultMap 提供collection完成关联信息映射到集合对象中。
在orders类中创建集合属性:
package com.sihai.mybatis.po;
import java.io.Serializable;
import java.util.Date;
import java.util.List;
public class Orders implements Serializable {
private Integer id;
private Integer userId;
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 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();
}
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
public List<Orderdetail> getOrderdetails() {
return orderdetails;
}
public void setOrderdetails(List<Orderdetail> orderdetails) {
this.orderdetails = orderdetails;
}
}
//订单明细
private List<Orderdetail> orderdetails;
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();
}
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
public List<Orderdetail> getOrderdetails() {
return orderdetails;
}
public void setOrderdetails(List<Orderdetail> orderdetails) {
this.orderdetails = orderdetails;
}
}
1.4 mapper.xml
我们在mapper.xml文件中创建select语句,其中需要用到resultMap,在下面我们将会介绍。
<!-- 一对多查询使用reusltMap完成
查询订单关联查询订单明细
-->
<select id="findOrderAndOrderDetails" resultMap="orderAndOrderDetails" >
SELECT
orders.*,
user.username,
user.sex ,
orderdetail.id orderdetail_id,
orderdetail.items_num,
orderdetail.items_id
FROM
orders,
USER,
orderdetail
WHERE orders.user_id = user.id AND orders.id = orderdetail.orders_id
</select>
1.5 resultMap定义
这里我们建立查询订单和订单详情的resultMap,其中我们先继承order和user的resultMap,这样我们就只需要写关联关系就可以了。
<!-- 一对多,查询订单及订单明细 -->
<resultMap type="orders" id="orderAndOrderDetails" extends="ordersUserResultMap">
<!-- 映射订单信息,和用户信息,这里使用继承ordersUserResultMap -->
<!-- 映射订单明细信息
property:要将关联信息映射到orders的哪个属性中
ofType:集合中pojo的类型
-->
<collection property="orderdetails" ofType="com.sihai.mybatis.po.Orderdetail">
<!-- id:关联信息订单明细的唯 一标识
property:Orderdetail的属性名
-->
<id column="orderdetail_id" property="id"/>
<result column="items_num" property="itemsNum"/>
<result column="items_id" property="itemsId"/>
</collection>
</resultMap>
<!-- 一对一查询resultMap -->
<resultMap type="orders" id="ordersUserResultMap">
<!-- 完成了订单信息的映射配置 -->
<!-- id:订单关联用户查询的唯 一 标识 -->
<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"/>
<!-- 下边完成关联信息的映射
association:用于对关联信息映射到单个pojo
property:要将关联信息映射到orders的哪个属性中
javaType:关联信息映射到orders的属性的类型,是user的类型
-->
<association property="user" javaType="user">
<!-- id:关联信息的唯 一标识 -->
<!-- property: 要映射到user的哪个属性中-->
<id column="user_id" property="id"/>
<!-- result就是普通列的映射 -->
<result column="username" property="username"/>
<result column="sex" property="sex"/>
</association>
</resultMap>
1.6 mapper.java
记得用mapper代理的方式生成dao的时候,需要符合mybatis的dao的编码规范。
// 一对多查询,使用resultMap
public List<Orders> findOrderAndOrderDetails() throws Exception;
二、 一对多查询(复杂)
2.1 需求
查询所有用户信息,关联查询订单及订单明细信息及商品信息,订单明细信息中关联查询商品信息
2.2 sql
主查询表:用户信息
关联查询:订单、订单明细,商品信息
SELECT
orders.*,
user.username,
user.sex ,
orderdetail.id orderdetail_id,
orderdetail.items_num,
orderdetail.items_id,
items.name items_name,
items.detail items_detail
FROM
orders,
USER,
orderdetail,
items
WHERE orders.user_id = user.id AND orders.id = orderdetail.orders_id AND items.id = orderdetail.items_id
2.3 pojo定义
在user.java中创建映射的属性:集合 List<Orders> orderlist
在Orders中创建映射的属性:集合List<Orderdetail> orderdetails
在Orderdetail中创建商品属性:pojo Items items
user.java
package com.sihai.mybatis.po;
import java.io.Serializable;
import java.util.Date;
import java.util.List;
/**
* @author sihai
*/
public class User implements Serializable {
private int id;
private String username;// 用户姓名
private String sex;// 性别
private Date birthday;// 生日
private String address;// 地址
//多个订单
private List<Orders> 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;
}
public List<Orders> getOrderlist() {
return orderlist;
}
public void setOrderlist(List<Orders> orderlist) {
this.orderlist = orderlist;
}
@Override
public String toString() {
return "User [id=" + id + ", username=" + username + ", sex=" + sex
+ ", birthday=" + birthday + ", address=" + address + "]";
}
}
orders.java
package com.sihai.mybatis.po;
import java.io.Serializable;
import java.util.Date;
import java.util.List;
public class Orders implements Serializable {
private Integer id;
private Integer userId;
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 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();
}
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
public List<Orderdetail> getOrderdetails() {
return orderdetails;
}
public void setOrderdetails(List<Orderdetail> orderdetails) {
this.orderdetails = orderdetails;
}
}
orderDetails.java
package com.sihai.mybatis.po;
import java.io.Serializable;
public class Orderdetail implements Serializable {
private Integer id;
private Integer ordersId;
private Integer itemsId;
private Integer itemsNum;
//商品信息
private 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;
}
public Items getItems() {
return items;
}
public void setItems(Items items) {
this.items = items;
}
@Override
public String toString() {
return "Orderdetail [id=" + id + ", ordersId=" + ordersId
+ ", itemsId=" + itemsId + ", itemsNum=" + itemsNum + "]";
}
}
2.4 mapper.xml
创建查询所有用户信息,关联查询订单及订单明细信息及商品信息,订单明细信息中关联查询商品信息的mapper
<!-- 一对多查询使用reusltMap完成
查询用户及订单和订单明细,关联商品,的信息
-->
<select id="findUserOrderDetail" resultMap="userOrderDetailResultMap" >
SELECT
orders.*,
user.username,
user.sex ,
orderdetail.id orderdetail_id,
orderdetail.items_num,
orderdetail.items_id,
items.name items_name,
items.detail items_detail
FROM
orders,
USER,
orderdetail,
items
WHERE orders.user_id = user.id AND orders.id = orderdetail.orders_id AND items.id = orderdetail.items_id
</select>
2.5 resultMap
这里我们需要继承上面的resultMap,然后需要注意这里的关联关系比较的复杂,所以在编写的时候要把关系理清楚来。
<!-- 一对多查询,查询用户及订单明细和商品信息 -->
<resultMap type="user" id="userOrderDetailResultMap">
<!-- 用户信息映射 -->
<id column="user_id" property="id"/>
<result column="username" property="username"/>
<result column="sex" property="sex"/>
<!-- 订单信息 -->
<collection property="orderlist" ofType="com.sihai.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"/>
<!-- 订单明细映射 -->
<collection property="orderdetails" ofType="com.sihai.mybatis.po.Orderdetail">
<!-- id:关联信息订单明细的唯 一标识
property:Orderdetail的属性名
-->
<id column="orderdetail_id" property="id"/>
<result column="items_num" property="itemsNum"/>
<result column="items_id" property="itemsId"/>
<!-- 商品信息 -->
<association property="items" javaType="com.sihai.mybatis.po.Items">
<id column="item_id" property="id"/>
<result column="items_name" property="name"/>
<result column="items_detail" property="detail"/>
</association>
</collection>
</collection>
</resultMap>
2.6 mapper.java
// 一对多查询,使用resultMap
public List<User> findUserOrderDetail() throws Exception;
三、总结
在进行一对多查询的时候,我认为我们只要把一下几点做好了,其实就没有什么问题了。
1、理清楚各个实体之间的关系,这样可以比较准确的写出sql语句。
2、理清了关联关系之后,当然就是sql的编写了,在mybatis中sql语句编写的能力就显现出来了,不是和hibernate一样,弱化了sql的能力。
3、mapper类的编写,这里需要注意的就是resultMap的编写,这里也是需要注意其中的关联关系,只要这个清楚,基本上就没什么问题了。
哈哈,今天就写到这里吧,写的好累啊,继续加油,坚持。。。