【 关联查询 】—— 多对多
需求
查询用户信息及用户购买的商品信息，要求将关联信息映射到主 pojo 的 pojo 属性中
Sql
Select
user.id,
user.username,
user.address,
orders.id orders_id,
orders.user_id,
orders.number,
orders.createtime,
orders.note,
orderdetail.id detail_id,
orderdetail.items_id,
orderdetail.items_num,
items.name items_name,
items.detail items_detail
FROM
USER,orders,orderdetail,items
WHERE user.`id` = orders.`user_id`
AND orders.`id` = orderdetail.`orders_id`
AND orderdetail.`items_id` = items.`id`
SELECT
u.id,
u.username,
u.address,
o.id order_id,
o.number,
o.createtime,
o.note,
od.id detail_id,
od.items_id,
od.items_num,
it.name,
it.price,
it.detail
FROM
user u,
orders o,
orderdetail od,
items it
WHERE
o.user_id = u.id
AND o.id = od.orders_id
AND od.items_id = it.id;
映射思路
- 将用户信息映射到user中。
- 在user类中添加订单列表属性
List<Orders> orderslist，将用户创建的订单映射到orderslist - 在Orders中添加订单明细列表属性
List<Orderdetail> detailList，将订单的明细映射到detailList - 在Orderdetail中添加Items属性，将订单明细所对应的商品映射到Items
第一步：UserMapper.java
package cn.ys.mapper;
import cn.ys.model.User;
import java.util.List;
public interface UserMapper {
/**
* 查询用户信息及用户购买的商品信息
*/
public List<User> findUserAndOrderInfo();
}
第二步：User/Orders/Orderdetail/Items.java
package cn.ys.model;
import java.io.Serializable;
import java.util.Date;
import java.util.List;
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 List<Orders> getOrderList() {
return orderList;
}
public void setOrderList(List<Orders> orderList) {
this.orderList = orderList;
}
public User() {
}
public User(String username, String sex, Date birthday, String address) {
this.username = username;
this.sex = sex;
this.birthday = birthday;
this.address = address;
}
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 + "]";
}
}
package cn.ys.model;
import java.util.Date;
import java.util.List;
public class Orders {
private Integer id;
private Integer user_id;
private String note;//备注
private String number;
private Date createtime;//写意的创建时间
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;
}
@Override
public String toString() {
return "Orders{" +
"id=" + id +
", user_id=" + user_id +
", note='" + note + '\'' +
", number='" + number + '\'' +
", createtime=" + createtime +
'}';
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public Integer getUser_id() {
return user_id;
}
public void setUser_id(Integer user_id) {
this.user_id = user_id;
}
public String getNote() {
return note;
}
public void setNote(String note) {
this.note = note;
}
public Date getCreatetime() {
return createtime;
}
public void setCreatetime(Date createtime) {
this.createtime = createtime;
}
public String getNumber() {
return number;
}
public void setNumber(String number) {
this.number = number;
}
}
package cn.ys.model;
/**
* 定单详情
*/
public class OrderDetail {
private Integer id;//定单详情ID
private Integer itemsId;//商品ID
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 getItemsId() {
return itemsId;
}
public void setItemsId(Integer itemsId) {
this.itemsId = itemsId;
}
public Integer getItemsNum() {
return itemsNum;
}
public void setItemsNum(Integer itemsNum) {
this.itemsNum = itemsNum;
}
@Override
public String toString() {
return "OrderDetail{" +
"id=" + id +
", itemsId=" + itemsId +
", itemsNum=" + itemsNum +
'}';
}
}
package cn.ys.model;
public class Items {
private Integer id;
private String name;
private String price;
private String detail;
@Override
public String toString() {
return "Items{" +
"id=" + id +
", name='" + name + '\'' +
", price='" + price + '\'' +
", detail='" + 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 String getPrice() {
return price;
}
public void setPrice(String price) {
this.price = price;
}
public String getDetail() {
return detail;
}
public void setDetail(String detail) {
this.detail = detail;
}
}
第三步：UserMapper.xml
<?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.ys.mapper.UserMapper">
<!-- ==============查询用户信息及用户购买的商品信息============-->
<resultMap id="userRslMap" type="user">
<!-- 1.匹配user属性 -->
<id column="id" property="id"></id>
<result column="username" property="username"/>
<result column="password" property="password"/>
<!--2.匹配user的orderList-->
<collection property="orderList" ofType="orders">
<id column="order_id" property="id"></id>
<result column="number" property="number"/>
<result column="createtime" property="createtime"/>
<result column="note" property="note"/>
<!-- 3.匹配Orders里有orderDetails-->
<collection property="orderDetails" ofType="orderDetail">
<id column="detail_id" property="id"></id>
<result column="items_id" property="itemsId"/>
<result column="items_num" property="itemsNum"/>
<!-- 4.配置定单详情的商品信息-->
<association property="items" javaType="items">
<id column="items_id" property="id"/>
<result column="name" property="name"/>
<result column="price" property="price"/>
<result column="detail" property="detail"/>
</association>
</collection>
</collection>
</resultMap>
<select id="findUserAndOrderInfo" resultMap="userRslMap">
SELECT
u.id,
u.username,
u.address,
o.id order_id,
o.number,
o.createtime,
o.note,
od.id detail_id,
od.items_id,
od.items_num,
it.name,
it.price,
it.detail
FROM
user u,
orders o,
orderdetail od,
items it
WHERE
o.user_id = u.id
AND o.id = od.orders_id
AND od.items_id = it.id
</select>
</mapper>
第四步：测试
package cn.ys.test;
import cn.ys.mapper.OrderMapper;
import cn.ys.mapper.UserMapper;
import cn.ys.model.OrderDetail;
import cn.ys.model.Orders;
//import cn.ys.model.OrdersExt;
import cn.ys.model.User;
import cn.ys.vo.UserQueryVO;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
public class Demo01 {
SqlSession session;
@Before
public void before() throws IOException {
System.out.println("before.....获取session");
// a)读取配置文件；
InputStream is = Resources.getResourceAsStream("SqlMapConfig.xml");
//b)通过SqlSessionFactoryBuilder创建SqlSessionFactory会话工厂。
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(is);
session = sessionFactory.openSession();
}
@After
public void after(){
session.close();
}
/**
* 多对多
* @throws IOException
*/
@Test
public void test9() throws IOException {
UserMapper mapper = session.getMapper(UserMapper.class);
List<User> users = mapper.findUserAndOrderInfo();
for (User user : users){
System.out.println("用户信息:" + user);
for (Orders order : user.getOrderList()){
System.out.println("定单信息:" + order);
System.out.println("订单详情:");
for (OrderDetail od : order.getOrderDetails()){
System.out.println(od + ":" + od.getItems());
}
System.out.println("------------------------------");
}
}
}
}
打印效果
C:\Java\jdk1.8.0_181\bin\java.exe -ea -Didea.test.cyclic.buffer.size=1048576 "-javaagent:C:\Program Files\JetBrains\IntelliJ IDEA 2018.2.1\lib\idea_rt.jar=50204:C:\Program Files\JetBrains\IntelliJ IDEA 2018.2.1\bin" -Dfile.encoding=UTF-8 -classpath "C:\Program Files\JetBrains\IntelliJ IDEA 2018.2.1\lib\idea_rt.jar;C:\Program Files\JetBrains\IntelliJ IDEA 2018.2.1\plugins\junit\lib\junit-rt.jar;C:\Program Files\JetBrains\IntelliJ IDEA 2018.2.1\plugins\junit\lib\junit5-rt.jar;C:\Java\jdk1.8.0_181\jre\lib\charsets.jar;C:\Java\jdk1.8.0_181\jre\lib\deploy.jar;C:\Java\jdk1.8.0_181\jre\lib\ext\access-bridge-64.jar;C:\Java\jdk1.8.0_181\jre\lib\ext\cldrdata.jar;C:\Java\jdk1.8.0_181\jre\lib\ext\dnsns.jar;C:\Java\jdk1.8.0_181\jre\lib\ext\jaccess.jar;C:\Java\jdk1.8.0_181\jre\lib\ext\jfxrt.jar;C:\Java\jdk1.8.0_181\jre\lib\ext\localedata.jar;C:\Java\jdk1.8.0_181\jre\lib\ext\nashorn.jar;C:\Java\jdk1.8.0_181\jre\lib\ext\sunec.jar;C:\Java\jdk1.8.0_181\jre\lib\ext\sunjce_provider.jar;C:\Java\jdk1.8.0_181\jre\lib\ext\sunmscapi.jar;C:\Java\jdk1.8.0_181\jre\lib\ext\sunpkcs11.jar;C:\Java\jdk1.8.0_181\jre\lib\ext\zipfs.jar;C:\Java\jdk1.8.0_181\jre\lib\javaws.jar;C:\Java\jdk1.8.0_181\jre\lib\jce.jar;C:\Java\jdk1.8.0_181\jre\lib\jfr.jar;C:\Java\jdk1.8.0_181\jre\lib\jfxswt.jar;C:\Java\jdk1.8.0_181\jre\lib\jsse.jar;C:\Java\jdk1.8.0_181\jre\lib\management-agent.jar;C:\Java\jdk1.8.0_181\jre\lib\plugin.jar;C:\Java\jdk1.8.0_181\jre\lib\resources.jar;C:\Java\jdk1.8.0_181\jre\lib\rt.jar;C:\Users\ys951\Desktop\JavaSETest\JavaEE\out\production\mybatis;C:\Users\ys951\Desktop\JavaSETest\JavaEE\mybatis\lib\asm-3.3.1.jar;C:\Users\ys951\Desktop\JavaSETest\JavaEE\mybatis\lib\cglib-2.2.2.jar;C:\Users\ys951\Desktop\JavaSETest\JavaEE\mybatis\lib\commons-logging-1.1.1.jar;C:\Users\ys951\Desktop\JavaSETest\JavaEE\mybatis\lib\javassist-3.17.1-GA.jar;C:\Users\ys951\Desktop\JavaSETest\JavaEE\mybatis\lib\junit-4.8.2.jar;C:\Users\ys951\Desktop\JavaSETest\JavaEE\mybatis\lib\log4j-1.2.17.jar;C:\Users\ys951\Desktop\JavaSETest\JavaEE\mybatis\lib\log4j-api-2.0-rc1.jar;C:\Users\ys951\Desktop\JavaSETest\JavaEE\mybatis\lib\log4j-core-2.0-rc1.jar;C:\Users\ys951\Desktop\JavaSETest\JavaEE\mybatis\lib\mybatis-3.2.7.jar;C:\Users\ys951\Desktop\JavaSETest\JavaEE\mybatis\lib\mysql-connector-java-5.1.7-bin.jar;C:\Users\ys951\Desktop\JavaSETest\JavaEE\mybatis\lib\slf4j-api-1.7.5.jar;C:\Users\ys951\Desktop\JavaSETest\JavaEE\mybatis\lib\slf4j-log4j12-1.7.5.jar" com.intellij.rt.execution.junit.JUnitStarter -ideVersion5 -junit4 cn.ys.test.Demo01,test9
before.....获取session
DEBUG [main] - Logging initialized using 'class org.apache.ibatis.logging.slf4j.Slf4jImpl' adapter.
DEBUG [main] - Class not found: org.jboss.vfs.VFS
DEBUG [main] - JBoss 6 VFS API is not available in this environment.
DEBUG [main] - Class not found: org.jboss.vfs.VirtualFile
DEBUG [main] - VFS implementation org.apache.ibatis.io.JBoss6VFS is not valid in this environment.
DEBUG [main] - Using VFS adapter org.apache.ibatis.io.DefaultVFS
DEBUG [main] - Find JAR URL: file:/C:/Users/ys951/Desktop/JavaSETest/JavaEE/out/production/mybatis/cn/ys/model
DEBUG [main] - Not a JAR: file:/C:/Users/ys951/Desktop/JavaSETest/JavaEE/out/production/mybatis/cn/ys/model
DEBUG [main] - Reader entry: Items.class
DEBUG [main] - Reader entry: Order.class
DEBUG [main] - Reader entry: OrderDetail.class
DEBUG [main] - Reader entry: Orders.class
DEBUG [main] - Reader entry: User.class
DEBUG [main] - Listing file:/C:/Users/ys951/Desktop/JavaSETest/JavaEE/out/production/mybatis/cn/ys/model
DEBUG [main] - Find JAR URL: file:/C:/Users/ys951/Desktop/JavaSETest/JavaEE/out/production/mybatis/cn/ys/model/Items.class
DEBUG [main] - Not a JAR: file:/C:/Users/ys951/Desktop/JavaSETest/JavaEE/out/production/mybatis/cn/ys/model/Items.class
DEBUG [main] - Reader entry: ���� 4 B
DEBUG [main] - Find JAR URL: file:/C:/Users/ys951/Desktop/JavaSETest/JavaEE/out/production/mybatis/cn/ys/model/Order.class
DEBUG [main] - Not a JAR: file:/C:/Users/ys951/Desktop/JavaSETest/JavaEE/out/production/mybatis/cn/ys/model/Order.class
DEBUG [main] - Reader entry: ���� 4
DEBUG [main] - Find JAR URL: file:/C:/Users/ys951/Desktop/JavaSETest/JavaEE/out/production/mybatis/cn/ys/model/OrderDetail.class
DEBUG [main] - Not a JAR: file:/C:/Users/ys951/Desktop/JavaSETest/JavaEE/out/production/mybatis/cn/ys/model/OrderDetail.class
DEBUG [main] - Reader entry: ���� 4 A
DEBUG [main] - Find JAR URL: file:/C:/Users/ys951/Desktop/JavaSETest/JavaEE/out/production/mybatis/cn/ys/model/Orders.class
DEBUG [main] - Not a JAR: file:/C:/Users/ys951/Desktop/JavaSETest/JavaEE/out/production/mybatis/cn/ys/model/Orders.class
DEBUG [main] - Reader entry: ���� 4 a
DEBUG [main] - Find JAR URL: file:/C:/Users/ys951/Desktop/JavaSETest/JavaEE/out/production/mybatis/cn/ys/model/User.class
DEBUG [main] - Not a JAR: file:/C:/Users/ys951/Desktop/JavaSETest/JavaEE/out/production/mybatis/cn/ys/model/User.class
DEBUG [main] - Reader entry: ���� 4 ^ E
DEBUG [main] - Checking to see if class cn.ys.model.Items matches criteria [is assignable to Object]
DEBUG [main] - Checking to see if class cn.ys.model.Order matches criteria [is assignable to Object]
DEBUG [main] - Checking to see if class cn.ys.model.OrderDetail matches criteria [is assignable to Object]
DEBUG [main] - Checking to see if class cn.ys.model.Orders matches criteria [is assignable to Object]
DEBUG [main] - Checking to see if class cn.ys.model.User matches criteria [is assignable to Object]
DEBUG [main] - Find JAR URL: file:/C:/Users/ys951/Desktop/JavaSETest/JavaEE/out/production/mybatis/cn/ys/vo
DEBUG [main] - Not a JAR: file:/C:/Users/ys951/Desktop/JavaSETest/JavaEE/out/production/mybatis/cn/ys/vo
DEBUG [main] - Reader entry: UserQueryVO.class
DEBUG [main] - Listing file:/C:/Users/ys951/Desktop/JavaSETest/JavaEE/out/production/mybatis/cn/ys/vo
DEBUG [main] - Find JAR URL: file:/C:/Users/ys951/Desktop/JavaSETest/JavaEE/out/production/mybatis/cn/ys/vo/UserQueryVO.class
DEBUG [main] - Not a JAR: file:/C:/Users/ys951/Desktop/JavaSETest/JavaEE/out/production/mybatis/cn/ys/vo/UserQueryVO.class
DEBUG [main] - Reader entry: ���� 4 -
DEBUG [main] - Checking to see if class cn.ys.vo.UserQueryVO matches criteria [is assignable to Object]
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - Find JAR URL: file:/C:/Users/ys951/Desktop/JavaSETest/JavaEE/out/production/mybatis/cn/ys/mapper
DEBUG [main] - Not a JAR: file:/C:/Users/ys951/Desktop/JavaSETest/JavaEE/out/production/mybatis/cn/ys/mapper
DEBUG [main] - Reader entry: OrderMapper.class
DEBUG [main] - Reader entry: OrderMapper.xml
DEBUG [main] - Reader entry: UserMapper.class
DEBUG [main] - Reader entry: UserMapper.xml
DEBUG [main] - Listing file:/C:/Users/ys951/Desktop/JavaSETest/JavaEE/out/production/mybatis/cn/ys/mapper
DEBUG [main] - Find JAR URL: file:/C:/Users/ys951/Desktop/JavaSETest/JavaEE/out/production/mybatis/cn/ys/mapper/OrderMapper.class
DEBUG [main] - Not a JAR: file:/C:/Users/ys951/Desktop/JavaSETest/JavaEE/out/production/mybatis/cn/ys/mapper/OrderMapper.class
DEBUG [main] - Reader entry: ���� 4
DEBUG [main] - Find JAR URL: file:/C:/Users/ys951/Desktop/JavaSETest/JavaEE/out/production/mybatis/cn/ys/mapper/OrderMapper.xml
DEBUG [main] - Not a JAR: file:/C:/Users/ys951/Desktop/JavaSETest/JavaEE/out/production/mybatis/cn/ys/mapper/OrderMapper.xml
DEBUG [main] - Reader entry: <?xml version="1.0" encoding="UTF-8" ?>
DEBUG [main] - Find JAR URL: file:/C:/Users/ys951/Desktop/JavaSETest/JavaEE/out/production/mybatis/cn/ys/mapper/UserMapper.class
DEBUG [main] - Not a JAR: file:/C:/Users/ys951/Desktop/JavaSETest/JavaEE/out/production/mybatis/cn/ys/mapper/UserMapper.class
DEBUG [main] - Reader entry: ���� 4
DEBUG [main] - Find JAR URL: file:/C:/Users/ys951/Desktop/JavaSETest/JavaEE/out/production/mybatis/cn/ys/mapper/UserMapper.xml
DEBUG [main] - Not a JAR: file:/C:/Users/ys951/Desktop/JavaSETest/JavaEE/out/production/mybatis/cn/ys/mapper/UserMapper.xml
DEBUG [main] - Reader entry: <?xml version="1.0" encoding="UTF-8" ?>
DEBUG [main] - Checking to see if class cn.ys.mapper.OrderMapper matches criteria [is assignable to Object]
DEBUG [main] - Checking to see if class cn.ys.mapper.UserMapper matches criteria [is assignable to Object]
DEBUG [main] - Opening JDBC Connection
DEBUG [main] - Created connection 489279267.
DEBUG [main] - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@1d29cf23]
DEBUG [main] - ==> Preparing: SELECT u.id, u.username, u.address, o.id order_id, o.number, o.createtime, o.note, od.id detail_id, od.items_id, od.items_num, it.name, it.price, it.detail FROM user u, orders o, orderdetail od, items it WHERE o.user_id = u.id AND o.id = od.orders_id AND od.items_id = it.id
DEBUG [main] - ==> Parameters:
DEBUG [main] - <== Total: 4
用户信息:User [id=1, username=王五, sex=null, birthday=null, address=null]
定单信息:Orders{id=3, user_id=null, note='null', number='1000010', createtime=Wed Feb 04 13:22:35 CST 2015}
订单详情:
OrderDetail{id=1, itemsId=1, itemsNum=1}:Items{id=1, name='台式机', price='3000.0', detail='该电脑质量非常好！！！！'}
OrderDetail{id=2, itemsId=2, itemsNum=3}:Items{id=2, name='笔记本', price='6000.0', detail='笔记本性能好，质量好！！！！！'}
------------------------------
定单信息:Orders{id=4, user_id=null, note='null', number='1000011', createtime=Tue Feb 03 13:22:41 CST 2015}
订单详情:
OrderDetail{id=3, itemsId=3, itemsNum=4}:Items{id=3, name='背包', price='200.0', detail='名牌背包，容量大质量好！！！！'}
OrderDetail{id=4, itemsId=2, itemsNum=3}:Items{id=2, name='笔记本', price='6000.0', detail='笔记本性能好，质量好！！！！！'}
------------------------------
DEBUG [main] - Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@1d29cf23]
DEBUG [main] - Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@1d29cf23]
DEBUG [main] - Returned connection 489279267 to pool.
Process finished with exit code 0
总结
- resultType：将查询结果按照sql列名pojo属性名一致性映射到pojo中。
- resultMap：使用association和collection完成一对一和一对多高级映射（对结果有特殊的映射要求）。
- association：将关联查询信息映射到一个pojo对象中。
- collection：将关联查询信息映射到一个list集合中。