Mybatis的模糊查询方法

今天写了个模糊查询，mybatis+mysql，将sql的写法做个总结吧。

1.sql中字符串拼接

  SELECT * FROM person WHERE name LIKE CONCAT(CONCAT('%', #{user.name}), '%');

另外，orcle

2.使用 ${…} 代替 #{…}

SELECT * FROM person WHERE name LIKE '%${user.name}%';

3.程序中拼接

Java代码：

  // String username = "%" + Tom + "%";

  String username= new StringBuilder("%").append(username).append("%").toString();

  parameterMap.put("name", username);

SqlMap.xml配置文件：

  SELECT * FROM person WHERE name LIKE #{name};

4.大小写匹配查询

 SELECT *  FROM person WHERE UPPER(SUBSYSTEM) LIKE '%' || UPPER('abc') || '%'

  或者 

 SELECT *   FROM person WHERE LOWER(SUBSYSTEM) LIKE '%' || LOWER('abc') || '%'

5.blind标签

<select id="selectPersons" resultType="person" parameterType="person">
  <bind name="text" value="'%' + user.name + '%'" />
  select *
  from person
  where username LIKE #{text}
</select>

6.参数中直接加入%

    param.setName("%CD%");

    <select  id="selectPersons" resultType="person" parameterType="person">
        select * 
        from person where true

        <if test="username!=null"> AND nameLIKE #{user.name}</if>

    </select>