承接上一篇:Mybatis 學習筆記(四)——關聯(映射)關系(一對一,一對多)
三、多對多映射關系
1. 需求:查詢使用者及商品資訊
2. POJO類
/mybatis01/src/com/po/User.java
package com.po;
import java.util.Date;
import java.util.List;
/**
* 使用者類
* @author 歐陽
*
*/
public class User {
private int id; //id
private String username; //使用者名
private String sex; //性别
private Date birthday; //生日
private String address; //位址
private List<Orders> 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;
}
public List<Orders> getOrderslist() {
return orderslist;
}
public void setOrderslist(List<Orders> orderslist) {
this.orderslist = orderslist;
}
}
/mybatis01/src/com/po/Orders.java
package com.po;
import java.util.Date;
import java.util.List;
/**
* 訂單類
* @author 歐陽
*
*/
public class Orders {
private Integer id; //id
private Integer userId; //使用者id
private String number; //數量
private Date createtime; //建立時間
private String note; //備注
private User user; //使用者資訊
private List<Orderdetail> orderdetail; //訂單明細
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> getOrderdetail() {
return orderdetail;
}
public void setOrderdetail(List<Orderdetail> orderdetail) {
this.orderdetail = orderdetail;
}
}
/mybatis01/src/com/po/Orderdetail.java
package com.po;
/**
* 訂單詳情類
* @author 歐陽
*
*/
public class Orderdetail {
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;
}
}
/mybatis01/src/com/po/Items.java
package com.po;
import java.util.Date;
/**
* 商品類
* @author 歐陽
*
*/
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();
}
@Override
public String toString() {
return "Items [id=" + id + ", name=" + name + ", price=" + price
+ ", pic=" + pic + ", createtime=" + createtime + ", detail="
+ detail + "]";
}
}
3. 編寫Mapper.xml
将下面的代碼加入 OrdersCustomMapper.xml 中
<!--
查詢使用者及使用者購買商品資訊(多對多)
-->
<resultMap type="com.po.User" id="UserAndItemsResultMap">
<!-- 使用者資訊 -->
<id column="u_id" property="id"/>
<result column="username" property="username"/>
<result column="sex" property="sex"/>
<result column="address" property="address"/>
<!-- 配置映射的訂單資訊 -->
<collection property="orderslist" ofType="com.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="orderdetail" ofType="com.po.Orderdetail">
<id column="od_id" property="id"/>
<result column="orders_id" property="ordersId"/>
<result column="items_id" property="itemsId"/>
<result column="items_num" property="itemsNum"/>
<!-- 配置映射的商品資訊 -->
<association property="items" javaType="com.po.Items">
<id column="i_id" property="id"/>
<result column="name" property="name"/>
<result column="detail" property="detail"/>
<result column="price" property="price"/>
</association>
</collection>
</collection>
</resultMap>
<select id="findUserAndItemsResultMap" resultMap="UserAndItemsResultMap">
SELECT
o.*,
u.id u_id,
u.username,
u.sex,
u.address,
od.id od_id,
od.items_id,
od.items_num,
od.orders_id,
i.id i_id,
i.name,
i.detail,
i.price
FROM
orders o,
USER u,
orderdetail od,
items i
WHERE
o.user_id = u.id
AND od.orders_id = o.id
AND od.items_id = i.id
</select>
4. 編寫Mapper接口
将下面代碼添加到 OrdersCustomMapper.java 中
/*
* 查詢使用者及商品資訊
*/
public List<User> findUserAndItemsResultMap() throws Exception;
5. Mapper接口測試
将下面代碼加入 OrdersCustomMapperTest.java 中
@Test
public void testFindUserAndItemsResultMap() {
SqlSession sqlsession = sqlSessionFactory.openSession();
OrdersCustomMapper mapper = sqlsession
.getMapper(OrdersCustomMapper.class);
try {
List<User> users = mapper.findUserAndItemsResultMap();
System.out.println(users.size());
} catch (Exception e) {
e.printStackTrace();
}
}
四、總結
個人覺得這個映射關系配置非常簡單,隻要掌握了規律就可以編寫出。在POJO編寫、Mapper.xml編寫、Mapper接口編寫這三步中,最難的就是POJO編寫。
在知道需求之後,首先第一步要确定查詢的哪張表是主表,哪些是從表,在分析出這個之後,在主表對應的POJO的基礎之上增加從表對應的POJO為屬性(一對一則為屬性,一對多為集合屬性),然後再分析該POJO(屬性)相應的表下是否有從表,接着在該POJO的基礎上再增加從表對應的POJO為屬性(一對一則為屬性,一對多為集合屬性),以此類推直到沒有從表了。
在編寫Mapper.xml檔案時,一般屬性用 result ;一對一用 association ;一對多用 collection;而多對多是用 result 、association 、collection三個之間互相嵌套表示,按照我編寫POJO的思路根據POJO的屬性和主從的先後關系一步一步的編寫 Mapper.xml 檔案就不會出錯。
而在編寫Mapper接口時隻需要安裝開發規範編寫即可。