<select id="selectCountByLikeandDate" parameterType="map" resultType="int">
select count(*) from table
<trim prefix="WHERE" prefixOverrides="AND |OR">
<if test="titleName != null and titleName != ''">
title like CONCAT(CONCAT('%',#{titleName}),'%')
</if>
<if test="startDate != null and startDate != ''">
<![CDATA[
and DATE_FORMAT(create_Date, '%Y-%m-%d') >= DATE_FORMAT(#{startDate}, '%Y-%m-%d')
]]>
</if>
<if test="endDate != null and endDate != ''">
<![CDATA[
and DATE_FORMAT(create_Date, '%Y-%m-%d') <= DATE_FORMAT(#{endDate}, '%Y-%m-%d')
]]>
</if>
</trim>
select count(*) from table
<trim prefix="WHERE" prefixOverrides="AND |OR">
<if test="titleName != null and titleName != ''">
title like CONCAT(CONCAT('%',#{titleName}),'%')
</if>
<if test="startDate != null and startDate != ''">
<![CDATA[
and DATE_FORMAT(create_Date, '%Y-%m-%d') >= DATE_FORMAT(#{startDate}, '%Y-%m-%d')
]]>
</if>
<if test="endDate != null and endDate != ''">
<![CDATA[
and DATE_FORMAT(create_Date, '%Y-%m-%d') <= DATE_FORMAT(#{endDate}, '%Y-%m-%d')
]]>
</if>
</trim>
</select>
自动增加where条件,并消除if中前面多余的and或or
<where>
<if test="">xxx</test>
<if test="">and xxx</test>
<if test="">or xxx</test>
</where>
等同于
<trim prefix="where" prefixOverrides="AND |OR">
<if test="">xxx</test>
<if test="">and xxx</test>
<if test="">or xxx</test>
</trim>
模糊查询:
like CONCAT(CONCAT('%',#{titleName}),'%')
时间段:
select * from table where date between date1 and date2;