mybatis多对多关系映射

记录Mybatis多对多关系映射

以商品和订单为例，一个订单有多件商品，一件商品可以属于多个订单；商品表为t_goods，订单表为t_order，中间表为t_order_goods；

1.准备

1.1订单表t_order

CREATE TABLE `t_order` (  `id` bigint(20) NOT NULL AUTO_INCREMENT,  `order_code` varchar(100) DEFAULT NULL,  `order_total` double DEFAULT NULL,  PRIMARY KEY (`id`))insert  into `t_order`(`id`,`order_code`,`order_total`) values (1,'1123456789',30),(2,'2123456789',30);

1.2商品表t_goods

CREATE TABLE `t_goods` (  `id` bigint(20) NOT NULL AUTO_INCREMENT,  `goods_name` varchar(100) DEFAULT NULL,  `goods_price` double DEFAULT NULL,  `goods_remark` varchar(100) DEFAULT NULL,  PRIMARY KEY (`id`))insert  into `t_goods`(`id`,`goods_name`,`goods_price`,`goods_remark`) values (1,'JAVA编程思想',80,'JAVA'),(2,'HEAD FIRST设计模式',60,'JAVA'),(3,'数据库原理及应用',40,'数据库');

1.3中间表t_order_goods

CREATE TABLE `t_order_goods` (  `goods_id` bigint(20) DEFAULT NULL,  `order_id` bigint(20) DEFAULT NULL,  KEY `FK_t_order_goods_order_id_t_order_id` (`order_id`),  KEY `FK_t_order_goods_goods_id_t_goods_id` (`goods_id`),  CONSTRAINT `FK_t_order_goods_goods_id_t_goods_id` FOREIGN KEY (`goods_id`) REFERENCES `t_goods` (`id`),  CONSTRAINT `FK_t_order_goods_order_id_t_order_id` FOREIGN KEY (`order_id`) REFERENCES `t_order` (`id`))insert  into `t_order_goods`(`goods_id`,`order_id`) values (1,1),(2,1),(1,2),(3,2);

1.4pojo简单java类

1.4.1.Order.java

public class Order implements Serializable {private static final long serialVersionUID = -3023313052473405086L;private Long id;private String order_code;private Double order_total;private List<Goods> goodsList;//get、set...}

1.4.2Goods.java

public class Goods implements Serializable {private static final long serialVersionUID = 2833866038521626456L;private Long id;private String goods_name;private Double goods_price;private String goods_remark;private List<Order> orderList;//get、set...}

2.mybatis配置

2.1mybatis.xml

<!-- 打开延迟加载的开关 --><setting name="lazyLoadingEnabled" value="true"/><!-- 将积极加载改为消息加载即按需加载 -->  <setting name="aggressiveLazyLoading" value="false"/>

2.2OrderMapper.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.edu.njit.mapper.OrderMapper"><resultMap type="cn.edu.njit.pojo.Order" id="orderMap"><!-- 主键 --><id property="id" column="order_id"/><!-- 普通属性 --><result property="order_code" column="order_code"/><result property="order_total" column="order_total"/><!-- 与商品多对多，配置成一对多 --><collection property="goodsList" javaType="java.util.ArrayList" column="order_id" select="cn.edu.njit.mapper.GoodsMapper.findGoodsByOrderId" ofType="cn.edu.njit.pojo.Goods" fetchType="lazy"><id property="id" column="goods_id"/><result property="goods_name" column="goods_name"/><result property="goods_price" column="goods_price"/><result property="goods_remark" column="goods_remark"/> </collection></resultMap><select id="findById" parameterType="long" resultMap="orderMap">select o.id order_id,o.*from t_order owhere o.id=#{id}</select><!-- 根据商品id查询订单 --><select id="findOrderByGoodsId" parameterType="long" resultMap="orderMap">select o.id order_id,o.*from t_order owhere o.id in (select order_id from t_order_goods where goods_id=#{goodsId})</select></mapper>

2.3GoodsMapper.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.edu.njit.mapper.GoodsMapper"><resultMap type="cn.edu.njit.pojo.Goods" id="goodsMap"><!-- 主键 --><id property="id" column="goods_id"/><!-- 普通属性 --><result property="goods_name" column="goods_name"/><result property="goods_price" column="goods_price"/><result property="goods_remark" column="goods_remark"/><!-- 与订单多对多，配置成一对多形式 --><collection property="orderList" javaType="java.util.ArrayList" column="goods_id" select="cn.edu.njit.mapper.OrderMapper.findOrderByGoodsId" ofType="cn.edu.njit.pojo.Order" fetchType="lazy"><id property="id" column="order_id"/><result property="order_code" column="order_code"/><result property="order_total" column="order_total"/> </collection></resultMap><select id="findById" parameterType="long" resultMap="goodsMap">select g.id goods_id,g.*from t_goods gwhere g.id=#{id}</select><select id="findGoodsByOrderId" parameterType="long" resultMap="goodsMap">select g.id goods_id,g.*from t_goods gwhere g.id in (select goods_id from t_order_goods where order_id=#{orderId})</select></mapper>

2.4测试代码

2.4.1.接口类OrderMapper.java

public interface OrderMapper {/** * 根据id查询订单信息 * @param id 订单id * @return 订单信息，未查询到返回null * */public Order findById(Long id);}

2.4.2测试类ManyToMany.java

@Testpublic void testMantToMany() throws Exception {InputStream inputStream = Resources.getResourceAsStream("mybatis.xml");SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);SqlSession session = sqlSessionFactory.openSession();//获取mapper接口的代理对象OrderMapper orderMapper = session.getMapper(OrderMapper.class);Order order = orderMapper.findById(1L);System.out.println(order.getOrder_total());//商品信息//List<Goods> goodsList = order.getGoodsList();//for(Goods goods : goodsList){//System.out.println(goods.getGoods_name());//}session.commit();session.close();}

2.4.3测试结果及说明

(1).对于多对多，可以看为两个一对多。当查询时，一般设置懒加载，不是直接加载多方数据。

(2).懒加载时，通过设置总开关

<!-- 打开延迟加载的开关 --><setting name="lazyLoadingEnabled" value="true"/><!-- 将积极加载改为消息加载即按需加载 -->  <setting name="aggressiveLazyLoading" value="false"/>

也可以在assocation、collection等标签中设置fetchType属性

fetchType="lazy"<!-- 懒加载 -->fetchType="eager"<!-- 立即加载 -->

(3).以订单为例，多方为goodsList，通过设置column=“order_id” select=" selectOrderByGoodsId "，此时在查询出订单时，若用到商品信息，此时再生成根据订单id查询商品信息的sql，订单id由column属性值提供，查询语句调用select中指向的sql

(4).本例执行结果，此时未用到商品信息，所以未执行查询商品信息的sql

DEBUG - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@11758f2a]
DEBUG - ==>  Preparing: select o.id order_id,o.* from t_order o where o.id=? 
DEBUG - ==> Parameters: 1(Long)
DEBUG - <==      Total: 1
30.0
DEBUG - Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@11758f2a]
(5).取消注释的语句，此时用到商品名称，执行了查询商品信息的sql

DEBUG - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@11758f2a]
DEBUG - ==>  Preparing: select o.id order_id,o.* from t_order o where o.id=? 
DEBUG - ==> Parameters: 1(Long)
DEBUG - <==      Total: 1
30.0
DEBUG - ==>  Preparing: select g.id goods_id,g.* from t_goods g where g.id in (select goods_id from t_order_goods where order_id=?) 
DEBUG - ==> Parameters: 1(Long)
DEBUG - <==      Total: 2
JAVA编程思想
HEAD FIRST设计模式
DEBUG - Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@11758f2a]