Mybatis学习第12节 -- sql片段

如果想要复用sql语句,就需要了解sql片段

改写先前的代码

接口

Shop getShopByIdAlias(Integer id);

映射文件

<sql id="columnAliasSql">

      `shop_id` as `id`,
      `owner_id` as `ownerId`,
      `area_id` as `areaId`,
      `shop_category_id` as `categoryId`,
      `shop_name` as `name`,
      `shop_desc` as `desc`,
      `shop_addr` as `addr`,
      `phone`,
      `shop_img` as `image`,
      `priority` ,
      `create_time` as `createTime`,
      `last_edit_time` as `lastEditTime`,
      `enable_status` as `enableStatus`,
      `advice`
</sql>

<select id="getShopByIdAlias" parameterType="int" resultType="Shop" >
    SELECT <include refid="columnAliasSql" />
    from tb_shop where `shop_id` = #{id}
</select>

测试

@Test
public void testGetShopByIdAlias() {
    SqlSession session = MyBatisUtil.getSqlSession();
    ShopMapper mapper = session.getMapper(ShopMapper.class);
    System.out.println(mapper.getShopByIdAlias(1));
    session.close();
}

结果

==> Preparing: SELECT `shop_id` as `id`, `owner_id` as `ownerId`, `area_id` as `areaId`, `shop_category_id` as `categoryId`, `shop_name` as `name`, `shop_desc` as `desc`, `shop_addr` as `addr`, `phone`, `shop_img` as `image`, `priority` , `create_time` as `createTime`, `last_edit_time` as `lastEditTime`, `enable_status` as `enableStatus`, `advice` from tb_shop where `shop_id` = ? 
==> Parameters: 1(Integer)
<== Columns: id, ownerId, areaId, categoryId, name, desc, addr, phone, image, priority, createTime, lastEditTime, enableStatus, advice
<== Row: 1, 1, 3, 14, 正式店铺名称, 测试描述, 正式地址, 13810524086, /upload/item/shop/1/2017091621545314507.jpg, 10, 2017-08-03 00:08:32, 2017-09-16 21:54:53, 0, 审核中
<== Total: 1
Shop{id=1, ownerId=1, areaId=3, categoryId=14, name='正式店铺名称', desc='测试描述', addr='正式地址', phone='13810524086', image='/upload/item/shop/1/2017091621545314507.jpg', priority=10, createTime=2017-08-03, lastEditTime=2017-09-16, enableStatus=0, advice='审核中'}

 

一切都没有问题, 但是如果我把sql定义片段放在使用之后会怎样

<select id="getShopByIdAlias" parameterType="int" resultType="Shop" >
    SELECT <include refid="columnAliasSql" />
    from tb_shop where `shop_id` = #{id}
</select>

<sql id="columnAliasSql">

    `shop_id` as `id`,
    `owner_id` as `ownerId`,
    `area_id` as `areaId`,
    `shop_category_id` as `categoryId`,
    `shop_name` as `name`,
    `shop_desc` as `desc`,
    `shop_addr` as `addr`,
    `phone`,
    `shop_img` as `image`,
    `priority` ,
    `create_time` as `createTime`,
    `last_edit_time` as `lastEditTime`,
    `enable_status` as `enableStatus`,
    `advice`
</sql>

结果还是一样的