OneToMany 查询

一对多查询

需求场景

tgoods中每一个商品(goodsid 字段)在 t_goods_detail 中对对应了多个商品详情(gd_pic_url 字段)， 如何通过goods_id 查询到其对应的多个 gd_pic_url 字段呢？

创建 GoodsDetail 实体类

创建MyBatis\src\main\java\indi\chester\mybatis\entity\GoodsDetail.java. 成员变量即为 t_goods_detail 表内经过驼峰转换后的字段名

package indi.chester.mybatis.entity;

public class GoodsDetail {
    private Integer gdId;
    private Integer goodsId;
    private String gdPicUrl;
    private Integer gdOrder;
    

    public Integer getGdId() {
        return gdId;
    }

    public void setGdId(Integer gdId) {
        this.gdId = gdId;
    }

    public Integer getGoodsId() {
        return goodsId;
    }

    public void setGoodsId(Integer goodsId) {
        this.goodsId = goodsId;
    }

    public String getGdPicUrl() {
        return gdPicUrl;
    }

    public void setGdPicUrl(String gdPicUrl) {
        this.gdPicUrl = gdPicUrl;
    }

    public Integer getGdOrder() {
        return gdOrder;
    }

    public void setGdOrder(Integer gdOrder) {
        this.gdOrder = gdOrder;
    }
}

Goods 实体类中增加存储 GoodsDetail 对象的列表

MyBatis\src\main\java\indi\chester\mybatis\entity\Goods.java中添加下面这个成员属性, 并为其设置 getter 和 setter 方法:

    private List<GoodsDetail> goodsDetailList;

    public List<GoodsDetail> getGoodsDetailList() {
        return goodsDetailList;
    }

    public void setGoodsDetailList(List<GoodsDetail> goodsDetailList) {
        this.goodsDetailList = goodsDetailList;
    }

添加 selectOneToMany 标签

MyBatis\src\main\resources\mappers\goods.xml 添加下面两个标签:

    <!-- 将SELECT * FROM t_goods LIMIT 0,1 的结果转换为一个个Goods 对象, 然后将goods_id传入到
    goodsDetail.selectByGoodsId 中的查询语句进行查询,查询结果放回到 goodsDetailList-->
    <resultMap id="rmGoods1" type="indi.chester.mybatis.entity.Goods">
        <id column="goods_id" property="goodsId"/> <!-- 这一句也可以不写 -->
        <collection property="goodsDetailList" select="goodsDetail.selectByGoodsId"
                    column="goods_id"/>
    </resultMap>
    <select id="selectOneToMany" resultMap="rmGoods1">
        SELECT * FROM t_goods LIMIT 0,1;
    </select>

创建goods_detail.xml

创建 MyBatis\src\main\resources\mappers\goods_detail.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="goodsDetail">
    
    <select id="selectByGoodsId" parameterType="Integer" resultType="indi.chester.mybatis.entity.GoodsDetail">
        SELECT  * FROM  t_goods_detail WHERE goods_id = #{value}
    </select>
    
</mapper>     

xml</mapper​

并在D:\introJava\MyBatis\src\main\resources\mybatis-config.xml的 <mappers> 标签下将 goodsdetail.xml 加入进去。

    <mappers>
        <mapper resource="mappers/goods.xml" />
        <mapper resource="mappers/goods_detail.xml" />
    </mappers>

修改 MyBatisTest.java

MyBatis\src\test\java\MyBatisTest.java 下添加这个方法:

    @Test
    public void testOneToMany () throws Exception{
        SqlSession sqlSession=null;
        try {
            sqlSession=MyBatisUtils.openSession();
            List<Goods> list= sqlSession.selectList("goods.selectOneToMany");
            for (Goods goods:list){
                System.out.println(goods.getTitle()+":"+ goods.getGoodsDetailList().size());
            }

        }catch (Exception e){
            e.printStackTrace();
            throw e;
        }finally {
            MyBatisUtils.closeSession(sqlSession);
        }
    }

这个方法也就是选中一个商品, 根据它的 goods_id 查询到它对应的 多个 gd_pic_url 并且 存储到 Goods 对象的 goodsDetailList 当中。最后将goodsDetailList 的 size 打印出来，以证明确实查询到了多个 gd_pic_url。

运行结果:

12:03:32:981 [main]  DEBUG goods.selectOneToMany -==>  Preparing: SELECT * FROM t_goods LIMIT 0,1;  
12:03:33:019 [main]  DEBUG goods.selectOneToMany -==> Parameters:  
12:03:33:043 [main]  DEBUG goodsDetail.selectByGoodsId -====>  Preparing: SELECT * FROM t_goods_detail WHERE goods_id = ?  
12:03:33:043 [main]  DEBUG goodsDetail.selectByGoodsId -====> Parameters: 739(Integer) 
12:03:33:051 [main]  DEBUG goodsDetail.selectByGoodsId -<====      Total: 14 
12:03:33:055 [main]  DEBUG goods.selectOneToMany -<==      Total: 1 
亲润 孕妇护肤品豆乳大米盈润保湿胶原蚕丝面膜（18片装）:14