多表关联查询

无字段映射

　　

        <!--多表查询-->
        <!--返回的是数据库的字段属性无法和javaBean的属性一一映射-->
        <select id="goodsAndcategory" resultType="java.util.LinkedHashMap">
            SELECT g.*,c.category_name FROM t_goods AS g , t_category AS c
                WHERE g.category_id = c.category_id limit 5
        </select>

    public void selectGoodsAndCategory() throws Exception {
        SqlSession sqlSession = null;
        try {
            //获取sql对象
            sqlSession = MybatisUtils.openSession();
            //执行sql
            List<Map> list = sqlSession.selectList("goods.goodsAndcategory");
            for (Map map:list){
                System.out.println(map);
            }
            //查看连接状态
            Connection conn = MybatisUtils.getConnection(sqlSession);

        }catch (Exception e){
            throw e;
        }finally {
            MybatisUtils.release(sqlSession);
        }
    }

字段一一映射javabean 多表查询

<!--多表查询-->
        <!--resultMap根据javaBean一一映射-->
        <!--select中resultMap的名称和resultMap的id名称一一映射，type是数据传输对象-->
        <resultMap id="goodsResultMap" type="com.imooc.mybatis.dto.GoodsDto">
            <!--id主键property是javaBean对象属性映射，column对数据库字段映射-->
            <!--column数据库字段映射到property上-->
            <id property="good.goodsId" column="goods_id"/>
            <result property="good.title" column="title"/>
            <result property="good.categoryId" column="category_id"/>
            <result property="good.subTitle" column="sub_title"/>
            <result property="good.originalCost" column="original_cost"/>
            <result property="good.currentPrice" column="current_price"/>
            <result property="good.discount" column="discount"/>
            <result property="good.isFreeDelivery" column="is_free_delivery"/>

            <result property="category.categoryId" column="category_id"/>
            <result property="category.categoryName" column="category_name"/>
            <result property="category.categoryLevel" column="category_level"/>
        </resultMap>
        <select id="jointQuery" resultMap="goodsResultMap">
            select g.goods_id,g.title,g.sub_title,g.original_cost,g.current_price,g.discount,g.is_free_delivery,
            c.category_name,c.category_level from t_goods as g, t_category as c where g.category_id = c.category_id
            limit 5
        </select>

    public void selectGoodsDto() throws Exception {
        SqlSession sqlSession = null;
        try {
            //获取sql对象
            sqlSession = MybatisUtils.openSession();
            //执行sql
            List<GoodsDto> list = sqlSession.selectList("goods.jointQuery");
            for (GoodsDto goodsDto:list){
                System.out.println(goodsDto.getGood().getTitle());
                System.out.println(goodsDto.getCategory().getCategoryName());
                System.out.println(goodsDto.getCategory().getCategoryLevel());
            }
            //查看连接状态
            Connection conn = MybatisUtils.getConnection(sqlSession);

        }catch (Exception e){
            throw e;
        }finally {
            MybatisUtils.release(sqlSession);
        }
    }