动态查询时,为了方便直接用的是where 1=1后面跟<if>标签,但是这样有局限,可能会使得我们数据库里设置的索引失效,当数据过多时,导致查询效率下降。用mybatis提供的<where>标签就能规避这样的问题,且<where>标签可判断其是不是第一个查询条件,而选择是否去掉<if>标签包含的sql语句前的and连接。
话不多说直接上代码:
1.where 1=1 和<if>
SELECT
*
FROM
store_adjust
WHERE
1=1
<if test="id != null and id != ''">
AND ID = #{id}
</if>
<if test="fromWarehouseId != null and fromWarehouseId != ''">
AND FROM_WAREHOUSE_ID = #{fromWarehouseId}
</if>
<if test="toWarehouseId != null and toWarehouseId != ''">
AND TO_WAREHOUSE_ID = #{toWarehouseId}
</if>
<if test="targetType != null and targetType != ''">
AND TARGET_TYPE = #{targetType}
</if>
<if test="startDate != null">
AND DATE_FORMAT(CREATE_TIME,'%Y%m%d') >= DATE_FORMAT(#{startDate},'%Y%m%d')
</if>
<if test="endDate != null">
AND DATE_FORMAT(CREATE_TIME,'%Y%m%d') <![CDATA[ <= ]]>
DATE_FORMAT(#{endDate},'%Y%m%d')
</if>
ORDER BY ID DESC
<if test="limitBegin != null">
limit #{limitBegin},#{pageSize}
</if>
2. <where>和<if>标签
SELECT
*
FROM
collection_cost_detail
<where>
<if test="collectionBillMainId != null and collectionBillMainId != ''">
AND collection_bill_main_id = #{collectionBillMainId}
</if>
<if test="collectionSubBillType != null and collectionSubBillType != ''">
AND collection_sub_bill_type = #{collectionSubBillType}
</if>
<if test="costType != null and costType != ''">
AND cost_type = #{costType}
</if>
</where>