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 goodsDetails;
public List getGoodsDetails() {
    return goodsDetails;
}
public void setGoodsDetails(List goodsDetails) {
    this.goodsDetails = goodsDetails;
}

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

    
    <resultMap id="rmGoods1" type="com.MyBatis.entity.Goods">
        
        <id column="goods_id" property="goodsId">id>
        
        <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声明

    
    <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 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>
        
        <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 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);
        }
    }