Mybatis模糊查询

mybatis中的模糊查询的使用：

mapper.xml文件：

<select id="getDishesCountByParam" resultType="java.lang.Integer">
    select count(*)
    from dishes_tbl dt 
	left join dishes_detail_tbl ddt
	on dt.id = ddt.dishes_id
	left join dishes_detail_picture_tbl ddpt
	on ddt.id = ddpt.detail_id
	where dt.is_del = '0' and is_sale = #{is_sale,jdbcType=VARCHAR}	and ddpt.ismain = '1'
	<if test="dishesname != null ">
		<span style="color:#FF0000;"><strong>and dt.name like '%${dishesname}%'</strong></span>
	</if>
  </select>

mapper.java文件：

int getDishesCountByParam(@Param("is_sale") String is_sale, @Param("dishesname") String dishesname);

还有其他的三种方式：

1·sql中字符串的拼接：

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

2·程序中拼接：

 Java

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

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

   parameterMap.put("text", searchText);

3·大小写匹配查询：

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