MyBatis 关联查询的实现：多对多
2个实体：订单、商品，一个订单可以包含多种商品，同时一种商品可以属于多个订单，即多对多。
商品表goods_tb：
订单表order_tb：
no是订单编号，user_id与用户表的id关联。
需要新建一张中间表order_item_tb，引入2个“多”的主键作为外键，把这2个“多”联系起来：
purchase_amount是该种商品的购买数量。
使用嵌套结果
（一）编写pojo类
package com.chy.pojo; public class Goods { private Integer id; //商品id private String goodsName; //商品名称 private float goodsPrice; //商品单价 private Integer purchaseAmount; //购买数量 public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getGoodsName() { return goodsName; } public void setGoodsName(String goodsName) { this.goodsName = goodsName; } public float getGoodsPrice() { return goodsPrice; } public void setGoodsPrice(float goodsPrice) { this.goodsPrice = goodsPrice; } public Integer getPurchaseAmount() { return purchaseAmount; } public void setPurchaseAmount(Integer purchaseAmount) { this.purchaseAmount = purchaseAmount; } @Override public String toString() { return "Goods{" + "id=" + id + ", goodsName='" + goodsName + '\'' + ", goodsPrice=" + goodsPrice + ", purchaseAmount=" + purchaseAmount + '}'; } }
package com.chy.pojo; import java.util.List; public class Order { private Integer no; private Integer userId; private List<Goods> goodsList; //包含的商品 public Integer getNo() { return no; } public void setNo(Integer no) { this.no = no; } public Integer getUserId() { return userId; } public void setUserId(Integer userId) { this.userId = userId; } public List<Goods> getGoodsList() { return goodsList; } public void setGoodsList(List<Goods> goodsList) { this.goodsList = goodsList; } @Override public String toString() { return "Order{" + "no=" + no + ", userId=" + userId + ", goodsList=" + goodsList + '}'; } }
在哪个pojo中使用List来关联其他实体，需要根据业务需求来确定。
（二）编写Mapper接口、映射文件
package com.chy.mapper; import com.chy.pojo.Order; public interface OrderMapper { //根据orderId查询订单信息 public Order queryOrderByOrderNo(Integer orderNo); }
<?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="com.chy.mapper.OrderMapper"> <select id="queryOrderByOrderNo" parameterType="integer" resultMap="orderResultWithGoods"> SELECT order_tb.*,goods_tb.*,order_item_tb.purchase_amount FROM order_tb,goods_tb,order_item_tb WHERE order_tb.no=#{no} AND order_item_tb.order_no=order_tb.no AND goods_tb.id=order_item_tb.goods_id </select> <resultMap id="orderResultWithGoods" type="order"> <id property="no" column="no"/> <result property="userId" column="user_id"/> <collection property="goodsList" ofType="goods"> <id property="id" column="id"/> <result property="goodsName" column="goods_name"/> <result property="goodsPrice" column="goods_price"/> <result property="purchaseAmount" column="purchase_amount"/> </collection> </resultMap> </mapper>
三表联合查询，sql语句很长，如果觉得表名、字段名不好写，可以用as设置表名、字段名的别名。
不是每个pojo类都要写对应的Mapper接口、映射文件，写哪些，看业务需求。
（三）使用
package com.chy.utils; 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 java.io.IOException; import java.io.InputStream; public class MyBatisUtils { private static SqlSessionFactory sqlSessionFactory; static { try { InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml"); sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); } catch (IOException e) { e.printStackTrace(); } } public static SqlSession getSqlSession(){ return sqlSessionFactory.openSession(); } }
package com.chy.test; import com.chy.mapper.OrderMapper; import com.chy.pojo.Order; import com.chy.utils.MyBatisUtils; import org.apache.ibatis.session.*; public class Test { public static void main(String[] args) { SqlSession sqlSession = MyBatisUtils.getSqlSession(); OrderMapper mapper = sqlSession.getMapper(OrderMapper.class); Order order = mapper.queryOrderByOrderNo(1); System.out.println(order); sqlSession.close(); } }
结果：
Order{no=1, userId=1, goodsList=[Goods{id=1, goodsName='抽纸', goodsPrice=6.5, purchaseAmount=1}, Goods{id=2, goodsName='中华', goodsPrice=80.0, purchaseAmount=2}]}
mybatis让开发人员专注于数据库的设计、sql语句的编写，而不需要花费过多精力在jdbc的底层操作上。
使用嵌套查询
与嵌套结果大体相同，不同的只有第二步：
（二）编写双方的Mapper接口、映射文件
package com.chy.mapper; import com.chy.pojo.Order; public interface OrderMapper { //根据orderId查询订单信息 public Order queryOrderByOrderNo(Integer orderNo); }
<?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="com.chy.mapper.OrderMapper"> <select id="queryOrderByOrderNo" parameterType="integer" resultMap="orderResultWithGoods"> SELECT * FROM order_tb WHERE no=#{no} </select> <resultMap id="orderResultWithGoods" type="order"> <id property="no" column="no"/> <result property="userId" column="user_id"/> <collection property="goodsList" select="com.chy.mapper.GoodsMapper.queryGoodsByGoodsId" column="no" ofType="goods" /> </resultMap> </mapper>
package com.chy.mapper; import com.chy.pojo.Goods; import java.util.List; public interface GoodsMapper { public List<Goods> queryGoodsByGoodsId(Integer goodsId); }
<?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="com.chy.mapper.GoodsMapper"> <select id="queryGoodsByGoodsId" parameterType="integer" resultMap="goodsMapper"> SELECT * FROM goods_tb WHERE id IN ( SELECT goods_id FROM order_item_tb WHERE order_no=#{no} ) </select> <resultMap id="goodsMapper" type="goods"> <id property="id" column="id"/> <result property="goodsName" column="goods_name"/> <result property="goodsPrice" column="goods_price"/> </resultMap> </mapper>
子查询的常见写法：in（查中间表）。
此处配置了resultMap，因为表的字段名和pojo类的成员变量名不一致，需要手动配置。
我为了图方便，把每种商品的购买数量写在了Goods类中，如果要映射purchaseAmount，可以这样写：
<?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="com.chy.mapper.GoodsMapper"> <select id="queryGoodsByGoodsId" parameterType="integer" resultMap="goodsMapper"> SELECT goods_tb.*,order_item_tb.purchase_amount FROM goods_tb,order_item_tb WHERE goods_tb.id IN ( SELECT goods_id FROM order_item_tb WHERE order_no=#{no} ) AND order_item_tb.order_no=#{no} </select> <resultMap id="goodsMapper" type="goods"> <id property="id" column="id"/> <result property="goodsName" column="goods_name"/> <result property="goodsPrice" column="goods_price"/> <result property="purchaseAmount" column="purchase_amount"/> </resultMap> </mapper>
注意
如果关联对象的表的字段和pojo类的成员变量不一致，·比如表字段是goods_price，成员变量是goodsPrice，这需要手动配置关联对象的映射。
嵌套结果是在<association> | <collection>中配置，嵌套查询是在子查询的mapper中使用resultMap进行配置。
