Mybatis多表级联查询

　多表关联查询是两个表通过主外键在一条SQL中完成所有数据的提取，多表级联查询是指通过一个对象来获取与他关联的另外一个对象，执行的SQL语句分为多条。

首先确定实体关系是一对多或是多对一

OneToMany对象关联查询

1.在Many的Mapper XML文件添加SQL语句

<?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="com.MyBatis.entity.GoodsDetail">
        select * from t_goods_detail where goods_id = #{value}
    </select>
</mapper>

2.用对象的方式表达一对多关系，在Goods(One)实体类中增加GoodsDetail(Many)的List集合

private List<GoodsDetail> goodsDetails;
public List<GoodsDetail> getGoodsDetails() {
    return goodsDetails;
}
public void setGoodsDetails(List<GoodsDetail> goodsDetails) {
    this.goodsDetails = goodsDetails;
}

3.在One的Mapper XML文件中描述对象的关联

    <!--
        resultMap可用于说明一对多或多对一的映射逻辑
        id 是resultMap属性引用的标志
        type 指向One的实体(Goods)
    -->
    <resultMap id="rmGoods1" type="com.MyBatis.entity.Goods">
        <!-- 映射goods对象的主键到goods_id字段-->
        <id column="goods_id" property="goodsId"></id>
        <!--
            collection的含义是，在
            select * from t_goods limit 0,1 得到结果后，对所有Goods对象遍历得到Goods_id字段，
            并代入到goodsDetail命名空间的findByGoodsId的SQL中执行查询，
            将得到的"商品详情"集合赋值给goodsDetails List对象
        -->
        <collection property="goodsDetails" select="goodsDetail.selectByGoodsId"
                column="goods_id"></collection>
    </resultMap>
    <select id="selectOneToMany" resultMap="rmGoods1">
        select * from t_goods limit 0,10
    </select>

4.在mybatis-config.xml中增加mapper声明

    <!--增加mapper声明-->
    <mappers>
        <mapper resource="mappers/goods.xml"></mapper>
        <mapper resource="mappers/goods_detail.xml"></mapper>
    </mappers>

5.测试

    @Test
    public void testOneToMany(){
        SqlSession sqlSession=null;
        try{
            sqlSession=MyBatisUtils.openSession();
            List<Goods> list = sqlSession.selectList("goods.selectOneToMany");
            for(Goods g:list){
                System.out.println(g.getTitle()+":"+g.getGoodsDetails().size());
            }
        }catch (Exception e){
            if(sqlSession!=null){
                sqlSession.rollback();
            }
            throw e;
        }finally {
            MyBatisUtils.closeSession(sqlSession);
        }
    }

ManyToOne对象关联查询 

1.在One的Mapper XML文件添加SQL语句

 

<?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="goods">
    <select id="selectById" parameterType="Integer" resultType="com.MyBatis.entity.Goods">
        select * from t_goods where goods_id = #{value}
    </select>
</mapper>

 

2.用对象的方式表达多对一关系，在GoodsDetail(Many)实体类中增加Goods(One)对象

private Goods goods;
public Goods getGoods() {
    return goods;
}
public void setGoods(Goods goods) {
    this.goods = goods;
}

3.在Many的Mapper XML中描述对象的关联

    <resultMap id="rmGoodsDetail" type="com.MyBatis.entity.GoodsDetail">
        <id column="gd_id" property="gdId"></id>
        <result column="goods_id" property="goodsId"></result>
        <!--其他字段只要符合驼峰命名就不需要result标签-->
        <association property="goods" select="goods.selectById"
                     column="goods_id"></association>
    </resultMap>
    <select id="selectManyToOne" resultMap="rmGoodsDetail">
        select * from t_goods_detail limit 0,20
    </select>

4.在mybatis-config.xml中增加mapper声明

5.测试

    @Test
    public void testManyToOne(){
        SqlSession sqlSession=null;
        try{
            sqlSession=MyBatisUtils.openSession();
            List<GoodsDetail> list = sqlSession.selectList("goodsDetail.selectManyToOne");
            for(GoodsDetail gd:list){
                System.out.println(gd.getGdPicUrl()+":"+gd.getGoods().getTitle());
            }
        }catch (Exception e){
            if(sqlSession!=null){
                sqlSession.rollback();
            }
            throw e;
        }finally {
            MyBatisUtils.closeSession(sqlSession);
        }
    }