【12】mybatis 多对多关联查询
实际应用中，由于多对多的关系比较复杂，会增加理解和关联的复杂度，例如，一个订单可以有多种商品，一种商品可以对应多个订单，订单与商品就是多对多的级联关系。可以使用一个中间表（订单记录表）将多对多级联转换成两个一对多的关系。
准备工作，mysql数据库中创建4个表：vuser用户表 ，vproduct商品表，vorder订单表，vorders_detail订单详情表，各表的SQL语句如下：
CREATE TABLE `vuser` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`pwd` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
INSERT INTO `vuser` VALUES ('1', '编程帮', '123');
INSERT INTO `vuser` VALUES ('2', 'C语言中文网', '456');
INSERT INTO `vuser` VALUES ('3', '赵小红', '123');
INSERT INTO `vuser` VALUES ('4', '李晓明', '345');
INSERT INTO `vuser` VALUES ('5', '杨小胤', '123');
INSERT INTO `vuser` VALUES ('6', '谷小乐', '789');
CREATE TABLE `vproduct` (
`pid` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(25) DEFAULT NULL,
`price` double DEFAULT NULL,
PRIMARY KEY (`pid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
INSERT INTO `vproduct` VALUES ('1', 'Java教程', '128');
INSERT INTO `vproduct` VALUES ('2', 'C语言教程', '138');
INSERT INTO `vproduct` VALUES ('3', 'Python教程', '132.35');
CREATE TABLE `vorder` (
`oid` int(11) NOT NULL AUTO_INCREMENT,
`ordernum` int(25) DEFAULT NULL,
`userId` int(11) DEFAULT NULL,
PRIMARY KEY (`oid`),
KEY `userId` (`userId`),
CONSTRAINT `order_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `vuser` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
INSERT INTO `vorder` VALUES ('1', '20200107', '1');
INSERT INTO `vorder` VALUES ('2', '20200806', '2');
INSERT INTO `vorder` VALUES ('3', '20206702', '3');
INSERT INTO `vorder` VALUES ('4', '20200645', '1');
INSERT INTO `vorder` VALUES ('5', '20200711', '2');
INSERT INTO `vorder` VALUES ('6', '20200811', '2');
INSERT INTO `vorder` VALUES ('7', '20201422', '3');
INSERT INTO `vorder` VALUES ('8', '20201688', '4');
INSERT INTO `vorder` VALUES ('9', null, '5');
CREATE TABLE `vorders_detail` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`orderId` int(11) DEFAULT NULL,
`productId` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
INSERT INTO `vorders_detail` VALUES ('1', '1', '1');
INSERT INTO `vorders_detail` VALUES ('2', '1', '2');
INSERT INTO `vorders_detail` VALUES ('3', '1', '3');
INSERT INTO `vorders_detail` VALUES ('4', '2', '3');
INSERT INTO `vorders_detail` VALUES ('5', '2', '1');
INSERT INTO `vorders_detail` VALUES ('6', '3', '2');
1，创建工程并配置所需环境
在eclipse中创建动态web项目mybatisDemoA11，将MyBatis目录下lib目录下的jar包，核心包mybatis-3.5.6.jar以及 MySQL 数据库的驱动jar包（mysql-connector-java-5.1.38-bin.jar）复制到WebContent /WEB-INF/lib 目录中，选择这些包，右键添加到编译路径中。
2，创建表对应的实体对象
在src目录下创建net.biancheng.po包，在包下添加实体类VOrder，代码如下：
package net.biancheng.po;
import java.util.List;
public class VOrder {
private int userId;
public int getUserId() {
return userId;
}
public void setUserId(int userId) {
this.userId = userId;
}
private int oid;
private int ordernum;
public int getOid() {
return oid;
}
public void setOid(int oid) {
this.oid = oid;
}
public int getOrdernum() {
return ordernum;
}
public void setOrdernum(int ordernum) {
this.ordernum = ordernum;
}
public List<VProduct> getProducts() {
return products;
}
public void setProducts(List<VProduct> products) {
this.products = products;
}
//订单对应哪些商品
private List<VProduct> products;
public String toString() {
return "Order [id=" + oid + ", userid=" + userId + ",ordernum="
+ ordernum + ", products=" + products + "]";
}
}
注意：在订单表vorder中创建了一个products属性，用于表示订单对应的商品列表
3，在src目录下创建net.biancheng.po包，在包下添加实体类VProduct，代码如下：
package net.biancheng.po;
import java.util.List;
public class VProduct {
//商品对应哪些订单
private List<VOrder> orders;
public List<VOrder> getOrders() {
return orders;
}
public void setOrders(List<VOrder> orders) {
this.orders = orders;
}
private int pid;
public int getPid() {
return pid;
}
public void setPid(int pid) {
this.pid = pid;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Double getPrice() {
return price;
}
public void setPrice(Double price) {
this.price = price;
}
private String name;
private Double price;
public String toString() {
return "Product [id=" + pid + ", name=" + name + ", price=" + price + "]";
}
public String toShow() {
return "Product [id=" + pid + ", name=" + name + ", price=" + price + ",orders="+orders+"]";
}
}
注意：在商品表vproduct中创建了一个orders属性，用于表示商品对应的订单列表
4，创建Mapper接口类
在src下创建net.biancheng.mapper包，在包下添加接口类OrderMapper，配置代码如下：
package net.biancheng.mapper;
import java.util.List;
import net.biancheng.po.VOrder;
public interface OrderMapper {
//获取所有订单与订单商品信息
public List<VOrder> selectAllOrdersAndProducts();
}
在net.biancheng.mapper包下添加接口类ProductMapper，配置代码如下：
package net.biancheng.mapper;
import java.util.List;
import net.biancheng.po.VProduct;
public interface ProductMapper {
// 获取所有商品与商品订单信息
public List<VProduct> selectAllProductsAndOrders();
}
5，创建mapper配置文件
在net.biancheng.mapper包下，添加配置文件OrderMapper.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="net.biancheng.mapper.OrderMapper">
<!-- 配置结果映射节点，指定主键列id及属性列表 -->
<resultMap type="net.biancheng.po.VOrder" id="orderMap">
<id property="oid" column="oid" />
<result property="ordernum" column="ordernum" />
<result property="userId" column="userId" />
<collection property="products" ofType="net.biancheng.po.VProduct">
<id property="pid" column="pid" />
<result property="name" column="name" />
<result property="price" column="price" />
</collection>
</resultMap>
<!-- 一次查询得到结果，查询id必须对应接口OrderMapper类中的方法 -->
<select id="selectAllOrdersAndProducts" parameterType="Integer"
resultMap="orderMap">
SELECT o.oid,o.ordernum,o.userId,p.pid,p.name,p.price FROM
vorder o
INNER JOIN vorders_detail od ON o.oid=od.orderId
INNER JOIN vproduct p ON p.pid = od.productId
</select>
</mapper>
在net.biancheng.mapper包下，添加配置文件ProductMapper.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="net.biancheng.mapper.ProductMapper">
<!-- 配置结果映射节点，指定主键列id及属性列表 -->
<resultMap type="net.biancheng.po.VProduct" id="productMap">
<id property="pid" column="pid" />
<result property="name" column="name" />
<result property="price" column="price" />
<collection property="orders" ofType="net.biancheng.po.VOrder">
<id property="oid" column="oid" />
<result property="ordernum" column="ordernum" />
<result property="userId" column="userId" />
</collection>
</resultMap>
<!-- 一次查询得到结果，查询id必须对应接口OrderMapper类中的方法 -->
<select id="selectAllProductsAndOrders" parameterType="Integer"
resultMap="productMap">
SELECT p.pid,p.name,p.price,o.oid,o.ordernum,o.userId FROM
vproduct p
INNER JOIN vorders_detail dd ON p.pid = dd.productId
INNER JOIN vorder o ON o.oid=dd.orderId
</select>
</mapper>
6，创建mybatis配置文件
在src目录下添加mybatis配置文件，mybatis-config.xml，内容如下：
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!-- 配置mybatis运行环境 -->
<environments default="development">
<environment id="development">
<transactionManager type="JDBC" />
<dataSource type="POOLED">
<!-- MySQL数据库驱动 -->
<property name="driver" value="com.mysql.jdbc.Driver" />
<!-- 连接数据库的URL -->
<property name="url"
value="jdbc:mysql://localhost:3306/cctv?characterEncoding=utf8" />
<property name="username" value="root" />
<property name="password" value="root" />
</dataSource>
</environment>
</environments>
<mappers>
<!-- 映射器，将mapper文件加入到配置文件中 -->
<mapper resource="net/biancheng/mapper/OrderMapper.xml" />
<mapper resource="net/biancheng/mapper/ProductMapper.xml" />
</mappers>
</configuration>
7，编写测试类
在src下创建net.biancheng.test包，在包下创建test类，代码如下：
package net.biancheng.test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import net.biancheng.mapper.OrderMapper;
import net.biancheng.mapper.ProductMapper;
import net.biancheng.po.VOrder;
import net.biancheng.po.VProduct;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
public class test {
public static void main(String[] args) {
InputStream config;
try {
config = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory ssf = new SqlSessionFactoryBuilder()
.build(config);
SqlSession ss = ssf.openSession();
OrderMapper mapper = ss.getMapper(OrderMapper.class);
List<VOrder> orderList= mapper.selectAllOrdersAndProducts();
System.out.println("订单数量："+orderList.size());
for (VOrder v : orderList) {
System.out.println(v.toString());
}
System.out.println("*****************************************");
ProductMapper productmapp = ss.getMapper(ProductMapper.class);
List<VProduct> pList= productmapp.selectAllProductsAndOrders();
System.out.println("商品数量："+pList.size());
for (VProduct p : pList) {
System.out.println(p.toShow());
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
项目结构如图：
运行程序代码，结果如下：
本节代码 mybatisDemoA11，下节继续。
浙公网安备 33010602011771号