0050 MyBatis关联映射--一对多关系
一对多关系更加常见，比如用户和订单，一个用户可以有多个订单
DROP TABLE IF EXISTS customer;
/*用户表*/
CREATE TABLE customer(
`pk` INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(16),
`email` VARCHAR(16),
`password` VARCHAR(16)
);
/*插入三条测试数据*/
INSERT INTO customer(`name`,`email`,`password`) VALUES('张三','2A@b.com','123456');
INSERT INTO customer(`name`,`email`,`password`) VALUES('李六','MNO@b.com','qazwer');
INSERT INTO customer(`name`,`email`,`password`) VALUES('王五','XYZ@b.com','edcrfv');
DROP TABLE IF EXISTS `order`;
/*订单表*/
CREATE TABLE `order`(
`pk` INT PRIMARY KEY AUTO_INCREMENT,
`customer_pk` INT,
`order_id` int,
`order_total` float,
FOREIGN KEY (customer_pk) REFERENCES customer(pk)
);
/*插入8条测试数据*/
INSERT INTO `order`(`customer_pk`,`order_id`,`order_total`) VALUES('1','1','462.9');
INSERT INTO `order`(`customer_pk`,`order_id`,`order_total`) VALUES('3','2','32.5');
INSERT INTO `order`(`customer_pk`,`order_id`,`order_total`) VALUES('2','3','216.93');
INSERT INTO `order`(`customer_pk`,`order_id`,`order_total`) VALUES('1','4','5323.32');
INSERT INTO `order`(`customer_pk`,`order_id`,`order_total`) VALUES('3','5','533.1');
INSERT INTO `order`(`customer_pk`,`order_id`,`order_total`) VALUES('2','6','924.5');
INSERT INTO `order`(`customer_pk`,`order_id`,`order_total`) VALUES('1','7','121.3');
INSERT INTO `order`(`customer_pk`,`order_id`,`order_total`) VALUES('2','8','65.34');
实体类：Customer和Order
package net.sonng.onetomany;
import java.util.List;
//用户实体类
public class Customer {
private int pk;
private String name;
private String email;
private String password;
private List<Order> orders;//一个用户可拥有多个订单
//省略getter、setter、toString
}
package net.sonng.onetomany;
//订单实体类
public class Order {
private int pk;
private int customer_pk;
private int order_id;
private Float order_total;
private Customer customer; //一条订单只属于一个用户
//省略getter、setter、toString
}
假如要根据用户的主键，查询用户的信息，然后再查询其所有的订单信息，sqlMapper配置如下
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//ibatis.apache.org//DTDMapper 3.0//EN" "http://ibatis.apache.org/dtd/ibatis-3-mapper.dtd">
<mapper namespace="net.sonng.onetomany.CustomerDao">
<!-- 根据用户主键查询用户信息，按『costomerMap』封装 -->
<select id="selectCustomerByPk" parameterType="int" resultMap="customerMap" >
SELECT * FROM customer WHERE pk=#{pk}
</select>
<resultMap id="customerMap" type="net.sonng.onetomany.Customer">
<id property="pk" column="pk" />
<result property="name" column="name" />
<result property="email" column="email" />
<result property="password" column="password" />
<!-- 根据查到的customer表的主键pk，再用另一个sql查询所有的订单，封装成ArrayList<Order>，再映射到orders属性 -->
<!-- collection解决了一对多关联，该元素下还有个属性fetchType="lazy/eager"，用于定义是否立即进行进一步查询 -->
<!-- Customer类的属性 查询结果列 封装类型 封装元素类型 -->
<collection property="orders" column="pk" javaType="ArrayList" ofType="net.sonng.onetomany.Order"
select="net.sonng.onetomany.OrderDao.selectOrderByCustomerPk" >
<id property="pk" column="pk" />
<result property="order_id" column="order_id" />
<result property="order_total" column="order_total" />
</collection>
</resultMap>
</mapper>
根据订单主键查询订单信息，同时查询到其所属的用户信息
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//ibatis.apache.org//DTDMapper 3.0//EN" "http://ibatis.apache.org/dtd/ibatis-3-mapper.dtd">
<mapper namespace="net.sonng.onetomany.OrderDao">
<!-- 根据订单的主键，查询订单的信息，因为一条订单只属于一个用户，数据量不大，因而用联合查询一次查出来 -->
<select id="selectOrderByPk" parameterType="int" resultMap="orderMap">
SELECT * FROM customer,`order` WHERE customer.pk=order.customer_pk AND order.pk=#{pk}
</select>
<!-- 下面这条select就是上面的resultMap要引用的，可以用resultMap或者resultType封装，resultMap看似形成了循环嵌套 -->
<select id="selectOrderByCustomerPk" parameterType="int" resultMap="orderMap" >
SELECT * FROM `order` WHERE customer_pk=#{pk}
</select>
<resultMap type="net.sonng.onetomany.Order" id="orderMap">
<id property="pk" column="pk" />
<result property="costomer_id" column="costomer_id" />
<result property="order_id" column="order_id" />
<result property="order_total" column="order_total" />
<!--这是多对一关联，多个订单都属于一个用户，跟一对一一样还是用association元素-->
<association property="customer" javaType="net.sonng.onetomany.Customer">
<id property="pk" column="pk" />
<result property="name" column="name" />
<result property="email" column="email" />
<result property="password" column="password" />
</association>
</resultMap>
</mapper>
测试类：
package net.sonng.test;
import java.util.List;
import net.sonng.onetomany.Customer;
import net.sonng.onetomany.CustomerDao;
import net.sonng.onetomany.Order;
import net.sonng.onetomany.OrderDao;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
public class Test {
public static void main(String[] args){
ApplicationContext ac=new ClassPathXmlApplicationContext("ac.xml");
CustomerDao customerDao=ac.getBean("customerDao",CustomerDao.class);
OrderDao orderDao=ac.getBean("orderDao",OrderDao.class);
Customer ct=customerDao.selectCustomerByPk(2);//这个查询会将用户信息连带其所有订单信息一起查询出来
System.out.println(ct);
List<Order> orders=ct.getOrders(); //如果加了fetch=lazy，那么要到访问订单信息的时候，才去查询
for (Order order:orders) {
System.out.println(order);
}
Order order=orderDao.selectOrderByPk(1);
System.out.println(order);
System.out.println(order.getCustomer());
}
}
输出：
Customer [pk=2, name=李六, email=MNO@b.com, password=qazwer //根据用户主键查询到用户信息
Order [pk=3, customer_pk=2, order_id=3, order_total=216.93 //这三条order都是查询到用户信息后，进一步查询到的所有订单信息
Order [pk=6, customer_pk=2, order_id=6, order_total=924.5
Order [pk=8, customer_pk=2, order_id=8, order_total=65.34
Order [pk=1, customer_pk=0, order_id=1, order_total=462.9 //根据订单的主键查询到的订单信息
Customer [pk=1, name=张三, email=2A@b.com, password=123456//查询到订单信息后，进一步查到其所属的用户信息
