今天写了个模糊查询,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>