mybatis动态查询:＜if＞和＜where＞标签的使用

        动态查询时，为了方便直接用的是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>