Mybatis学习第8节 -- 动态sql-if

需求

1. 查询id小于10,并且shopname包含"关键词"的记录
2. 如果用户没有输入任何关键词, 那么列出所有结果

 

接口

List<Shop> get10ShopByTitle(String value);

映射

<select id="get10ShopByTitle" resultMap="simpleResultMap">
    select * from tb_shop
    <where>
        <if test="value != null">
            `shop_name` LIKE #{value} limit 10
        </if>
    </where>
</select>

测试用例

@Test
public void testGet10ShopByTitle() {
    String template = "查询结果: %s\n";
    SqlSession session = MyBatisUtil.getSqlSession();
    ShopMapper mapper = session.getMapper(ShopMapper.class);
    System.out.printf(template, mapper.get10ShopByTitle("%ello%"));
    String nullString = null;
    System.out.printf(template, mapper.get10ShopByTitle(nullString));
    session.close();
}

测试结果

Opening JDBC Connection
Created connection 288615534.
Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@1133ec6e]
==> Preparing: select * from tb_shop WHERE `shop_name` LIKE ? limit 10 
==> Parameters: %ello%(String)
<== 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: 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: 2
查询结果: [Shop{id=40, ownerId=1, areaId=null, categoryId=null, name='Hello Jack', desc='null', addr='null', phone='null', image='null', priority=null, createTime=null, lastEditTime=null, enableStatus=0, advice='null'}
, Shop{id=41, ownerId=1, areaId=null, categoryId=null, name='Hello Jack', desc='null', addr='null', phone='null', image='null', priority=null, createTime=null, lastEditTime=null, enableStatus=0, advice='null'}
]
==> 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
查询结果: [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='审核中'}
, Shop{id=28, ownerId=1, areaId=2, categoryId=22, name='小黄人主题奶茶店', desc='不接受预订，请直接来店里进行消费', addr='位于东苑2号', phone='13810524086', image='/upload/images/item/shop/28/2017092601041469991.png', priority=50, createTime=2017-09-26, lastEditTime=2017-09-26, enableStatus=1, advice='null'}
, Shop{id=29, ownerId=1, areaId=3, categoryId=22, name='暴漫奶茶店', desc='过来喝喝就知道啦，你是我的奶茶', addr='西苑1号', phone='1211334565', image='/upload/images/item/shop/29/2017092601054939287.jpg', priority=40, createTime=2017-09-26, lastEditTime=2017-09-26, enableStatus=1, advice='null'}
, Shop{id=30, ownerId=1, areaId=2, categoryId=20, name='彪哥大排档', desc='敢说不好吃吗', addr='东苑1号', phone='13628763625', image='/upload/images/item/shop/30/2017092601063878278.jpg', priority=30, createTime=2017-09-26, lastEditTime=2017-09-26, enableStatus=1, advice='null'}
, Shop{id=31, ownerId=1, areaId=2, categoryId=20, name='威哥大排档', desc='干掉彪哥大排档', addr='东苑南路', phone='126554437261', image='/upload/images/item/shop/31/2017092601072177572.jpg', priority=20, createTime=2017-09-26, lastEditTime=2017-09-26, enableStatus=1, advice='null'}
, Shop{id=32, ownerId=1, areaId=2, categoryId=22, name='你是我的奶茶', desc='奶茶店再次来袭', addr='东苑六路', phone='13652384615', image='/upload/images/item/shop/32/2017092601081463136.jpg', priority=10, createTime=2017-09-26, lastEditTime=2017-09-26, enableStatus=1, advice='null'}
, Shop{id=35, ownerId=8, areaId=2, categoryId=22, name='奶茶来了', desc='奶茶来了', addr='西苑7路', phone='null', image='null', priority=0, createTime=null, lastEditTime=null, enableStatus=0, advice='null'}
, Shop{id=38, ownerId=1, areaId=2, categoryId=10, name='杰克的店铺abcd', desc='etc test', addr='etc test', phone='etc test', image='etc test', priority=null, createTime=2018-06-02, lastEditTime=null, enableStatus=1, advice='审核中-'}
, Shop{id=40, ownerId=1, areaId=null, categoryId=null, name='Hello Jack', desc='null', addr='null', phone='null', image='null', priority=null, createTime=null, lastEditTime=null, enableStatus=0, advice='null'}
, Shop{id=41, ownerId=1, areaId=null, categoryId=null, name='Hello Jack', desc='null', addr='null', phone='null', image='null', priority=null, createTime=null, lastEditTime=null, enableStatus=0, advice='null'}
]

<wiz_tmp_tag class="wiz-block-scroll">

 

 

总结

在if的test属性中可以直接使用接口的parameter, 而bind比较特别, 他使用的是传入参数的属性