Mybatis 模糊查询

作者：範宗雲来源：原创发布时间：2015-05-09 归档：mybatis

环境： JDK 7 Mybatis 3.2.7 Maven 3 Junit 4.11 Spring 4.1.5 Eclipse Luna

方式 1

            <select id="find" resultType="Person">
              select * from person where address like '%${address}%'
            </select>

DEBUG [main] - ==> Preparing: select * from person where address like '%茂名%'
DEBUG [main] - ==> Parameters:
DEBUG [main] - <== Total: 2

方式 2

            <select id="find" resultType="Person">
              select * from person where address like "%"#{address}"%"
            </select>

DEBUG [main] - ==> Preparing: select * from person where address like "%"?"%"
DEBUG [main] - ==> Parameters: 茂名(String)
DEBUG [main] - <== Total: 2

方式 3

            <select id="find" resultType="Person">
              select * from person where address like concat(concat('%', #{address}), '%')
            </select>

DEBUG [main] - ==> Preparing: select * from person where address like concat(concat('%', ?), '%')
DEBUG [main] - ==> Parameters: 茂名(String)
DEBUG [main] - <== Total: 2

方式 4

            <select id="find" resultType="Person">
              select * from person where address like #{address}
            </select>

            List<Person> persons = personMapper.find("%茂名%");

DEBUG [main] - ==> Preparing: select * from person where address like ?
DEBUG [main] - ==> Parameters: %茂名%(String)
DEBUG [main] - <== Total: 2

方式 5

            <select id="find" resultType="Person">
              <bind name="pattern" value="'%' + address + '%'" />
              select * from person where address like #{pattern}
            </select>

DEBUG [main] - ==> Preparing: select * from person where address like ?
DEBUG [main] - ==> Parameters: %茂名%(String)
DEBUG [main] - <== Total: 2

这种方式是将传过来的 address 参数进行了 '%' + address + '%' 处理, 然后绑定到 pattern 中, 接下来就可以使用 #{pattern} 来替代这个表达式了。