mysql中的模糊查询

原文链接：http://www.cnblogs.com/cyttina/p/3894428.html

http://blog.sina.com.cn/s/blog_667bef380101f2da.html

1.  参数中直接加入%%

　　param.setUsername("%CD%");
      param.setPassword("%11%");

<select  id="selectPersons" resultType="person" parameterType="person">select id,sex,age,username,password from person where true <if test="username!=null"> AND username LIKE #{username}</if><if test="password!=null">AND password LIKE #{password}</if></select>

2.  bind标签

<select id="selectPersons" resultType="person" parameterType="person">  <bind name="pattern" value="'%' + _parameter.username + '%'" />  select id,sex,age,username,password   from person  where username LIKE #{pattern}</select>

 

 

3. CONCAT

where username LIKE concat(concat('%',#{username}),'%')

模糊查询：

工作中用到，写三种用法吧，第四种为大小写匹配查询

 

1. sql中字符串拼接

   SELECT * FROM tableName WHERE name LIKE CONCAT(CONCAT('%', #{text}), '%');

 

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

   SELECT * FROM tableName WHERE name LIKE '%${text}%'; 

 

3. 程序中拼接

   Java

   // or String searchText = "%" + text + "%";

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

   parameterMap.put("text", searchText);

 

   SqlMap.xml

   SELECT * FROM tableName WHERE name LIKE #{text};

 

4. 大小写匹配查询

[sql] view plain copy

1. SELECT *  FROM TABLENAME  WHERE UPPER(SUBSYSTEM) LIKE '%' || UPPER('jz') || '%'  
2.  --或者是  
3. SELECT *   FROM TABLENAME  WHERE LOWER(SUBSYSTEM) LIKE '%' || LOWER('jz') || '%' 
   

最后一个是在自己写的时候，由于关键字为空，查询不出来的情况

<select id="queryPassMember" parameterType="map" resultMap="BaseResultMap">select<include refid="Base_Column_List" />from shop_employee_relation<where><if test="shopId != null">SHOP_ID= #{shopId}and delete_flag=1</if><if test="mobile !=null and mobile !=''">AND shopkeeper_phone LIKEconcat(concat('%',#{mobile}),'%')</if><if test="memberName !=null and memberName !=''">AND member_name LIKE concat(concat('%',#{memberName}),'%')</if></where>order by ROLE asc</select>

                                                           题外话：今天下午状态突然下降，有点饿，坐等下班中。