Mybatis学习第10节 -- 动态sql trim set

where元素会在90%的情况下管用,但是如果where不管用,可以试试trim元素

和where等价的trim

<trim prefix="WHERE" prefixOverrides="AND |OR ">
  ... 
</trim>

接口

List<Shop> getShopListByConditionTrim(Shop condition);

映射

<select id="getShopListByConditionTrim" resultMap="simpleResultMap">

    select * from tb_shop
    <trim prefix="where" prefixOverrides="AND | OR">
        <choose>
            <when test="name != null">
                AND `shop_name` LIKE concat('%', #{name}, '%')
            </when>
            <when test="addr != null">
                AND `shop_name` LIKE concat('%', #{addr}, '%')
            </when>
            <otherwise>
            </otherwise>
        </choose>
    </trim>
</select>

测试

@Test
public void testGetShopListByConditionTrim() {
    String template = "查询结果: %s\n";
    SqlSession session = MyBatisUtil.getSqlSession();
    ShopMapper mapper = session.getMapper(ShopMapper.class);

    Shop condition = new Shop();
    condition.setName("ello");

    System.out.printf(template, mapper.getShopListByConditionTrim(condition));

    Shop condition1 = new Shop();

    System.out.printf(template, mapper.getShopListByConditionTrim(condition1));
    session.close();
}

结果

==> Preparing: select * from tb_shop 
==> Parameters: 
<== Columns: shop_id, owner_id, area_id, shop_category_id, shop_name, shop_desc, shop_addr, phone, shop_img, priority, create_time, last_edit_time, enable_status, 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, 审核中
<== Row: 28, 1, 2, 22, 小黄人主题奶茶店, 不接受预订，请直接来店里进行消费, 位于东苑2号, 13810524086, /upload/images/item/shop/28/2017092601041469991.png, 50, 2017-09-26 01:04:13, 2017-09-26 01:04:13, 1, null
<== Row: 29, 1, 3, 22, 暴漫奶茶店, 过来喝喝就知道啦，你是我的奶茶, 西苑1号, 1211334565, /upload/images/item/shop/29/2017092601054939287.jpg, 40, 2017-09-26 01:05:49, 2017-09-26 01:05:49, 1, null
<== Row: 30, 1, 2, 20, 彪哥大排档, 敢说不好吃吗, 东苑1号, 13628763625, /upload/images/item/shop/30/2017092601063878278.jpg, 30, 2017-09-26 01:06:37, 2017-09-26 01:06:37, 1, null
<== Row: 31, 1, 2, 20, 威哥大排档, 干掉彪哥大排档, 东苑南路, 126554437261, /upload/images/item/shop/31/2017092601072177572.jpg, 20, 2017-09-26 01:07:21, 2017-09-26 01:07:21, 1, null
<== Row: 32, 1, 2, 22, 你是我的奶茶, 奶茶店再次来袭, 东苑六路, 13652384615, /upload/images/item/shop/32/2017092601081463136.jpg, 10, 2017-09-26 01:08:13, 2017-09-26 01:08:13, 1, null
<== Row: 35, 8, 2, 22, 奶茶来了, 奶茶来了, 西苑7路, null, null, 0, null, null, 0, null
<== Row: 38, 1, 2, 10, 杰克的店铺abcd, etc test, etc test, etc test, etc test, null, 2018-06-02 22:04:58, null, 1, 审核中-
<== Row: 40, 1, null, null, Hello Jack, null, null, null, null, null, null, null, 0, null
<== Row: 41, 1, null, null, Hello Jack, null, null, null, null, null, null, null, 0, null
<== Total: 10

与while元素的效果相同

 

set使用, set和while相似, 只需要更新部分字段

接口

int updateShopBySet(Shop shop);

映射

<update id="updateShopBySet" parameterType="Shop">
    UPDATE
    `tb_shop`
    <set>
        <if test="name != null">
            `shop_name` = #{name},
        </if>
        <if test="desc != null">
          `shop_desc` = #{desc},
        </if>
    </set>
    WHERE `shop_id` = #{id} ;
</update>

测试

@Test
public void testUpdateShopBySet() {
    String template = "更新结果: %s\n";
    SqlSession session = MyBatisUtil.getSqlSession();
    ShopMapper mapper = session.getMapper(ShopMapper.class);

    Shop shop = mapper.getShopById(38);
    shop.setName("杰克的小屋");
    shop.setDesc("欢迎你哦");

    mapper.updateShopBySet(shop);

    System.out.printf(template, shop);
    session.commit();
    session.close();
}

结果

==> Preparing: select * from tb_shop where `shop_id` = ? 
==> Parameters: 38(Integer)
<== Columns: shop_id, owner_id, area_id, shop_category_id, shop_name, shop_desc, shop_addr, phone, shop_img, priority, create_time, last_edit_time, enable_status, advice
<== Row: 38, 1, 2, 10, 杰克的小屋, etc test, etc test, etc test, etc test, null, 2018-06-02 22:04:58, null, 1, 审核中-
<== Total: 1
==> Preparing: UPDATE `tb_shop` SET `shop_name` = ?, `shop_desc` = ? WHERE `shop_id` = ? ; 
==> Parameters: 杰克的小屋(String), 欢迎你哦(String), 38(Integer)
<== Updates: 1
更新结果: Shop{id=38, ownerId=1, areaId=2, categoryId=10, name='杰克的小屋', desc='欢迎你哦', addr='etc test', phone='etc test', image='etc test', priority=null, createTime=2018-06-02, lastEditTime=null, enableStatus=1, advice='审核中-'}