mybatis中SQL块使用/动态条件查询

<!-- 字段 -->  
<sql id="field">  
    `id`,  
    `username`,  
    `password`  
</sql> 

使用：

<include refid="field"/> <pre name="code" class="java"><!-- 通过一个ID搜索 -->  
<select id="getById" parameterType="int" resultType="UserPojo">  
    SELECT <include refid="field"/> FROM user WHERE id = #{id}  
</select>

或者 

<sql id="dynamicCondition">
		<if test="templateType != null"> AND r.template_type=#{templateType} </if>
		<if test="roleType != null"> AND r.role_type=#{roleType}  </if>
		<if test="trialId != null"> AND r.trial_id= #{trialId}  </if>
		<if test="status != null"> AND r.status = #{status}  </if>
		<if test="typeId != null"> AND r.type_id= #{typeId}  </if>
		<if test="keyWords != null"> AND r.org_name like #{keyWords}  </if>
		<if test="schoolType != null"> AND r.school_type = #{schoolType} </if>
		<if test="orgId != null "> AND r.org_id = #{orgId} </if>
		<if test="orgIdList != null "> AND r.org_id in (${orgIdList}) </if>
	</sql>

使用：

<select id="getCountyListByCityId" resultMap="reportVOMap" parameterType="map">
		select DISTINCT(r.org_id) ,r.org_name,r.view_url from tarea t 
		INNER JOIN report_divided_html_liususu r
		on t.id = r.org_id
		where t.pid  = #{areaId}
		<include refid="dynamicCondition" />
	</select>

或者：

<sql id="dynamicWhere">
		<where>
			<if test="@Ognl@isNotEmpty(id)"> AND id  =#{id} </if>
			<if test="@Ognl@isNotEmpty(orgId)"> AND org_id  = #{orgId}  </if>
			<if test="@Ognl@isNotEmpty(orgName)"> AND org_name  LIKE #{orgName}  </if>
			<if test="@Ognl@isNotEmpty(templateType)"> AND template_type  = #{templateType}  </if>
			<if test="@Ognl@isNotEmpty(roleType)"> AND role_type  = #{roleType}  </if>
			<if test="@Ognl@isNotEmpty(trialId)"> AND trial_id  = #{trialId}  </if>
			<if test="@Ognl@isNotEmpty(typeId)"> AND type_id  LIKE #{typeId}  </if>
			<if test="@Ognl@isNotEmpty(viewUrl)"> AND view_url  LIKE #{viewUrl}  </if>
			<if test="@Ognl@isNotEmpty(reportName)"> AND report_name  LIKE #{reportName}  </if>
			<if test="@Ognl@isNotEmpty(provinceId)"> AND province_id  =#{provinceId} </if>
			<if test="@Ognl@isNotEmpty(provinceName)"> AND province_name  = #{provinceName}  </if>
			<if test="@Ognl@isNotEmpty(cityId)"> AND city_id  =#{cityId} </if>
			<if test="@Ognl@isNotEmpty(cityName)"> AND city_name  LIKE #{cityName}  </if>
			<if test="@Ognl@isNotEmpty(countyId)"> AND county_id  =#{countyId} </if>
			<if test="@Ognl@isNotEmpty(countyName)"> AND county_name  LIKE #{countyName}  </if>
			<if test="@Ognl@isNotEmpty(generatedTime)"> AND generated_time  =#{generatedTime} </if>
			<if test="@Ognl@isNotEmpty(begingeneratedTime)"> AND generated_time  >=#{begingeneratedTime,jdbcType=TIMESTAMP} </if>
			<if test="@Ognl@isNotEmpty(endgeneratedTime)"> AND generated_time <![CDATA[ <#{endgeneratedTime,jdbcType=TIMESTAMP}]]> </if>
			<if test="@Ognl@isNotEmpty(generatedUserId)"> AND generated_user_id  =#{generatedUserId} </if>
			<if test="@Ognl@isNotEmpty(generatedUserName)"> AND generated_user_name  LIKE #{generatedUserName}  </if>
			<if test="@Ognl@isNotEmpty(releaseTime)"> AND release_time  =#{releaseTime} </if>
			<if test="@Ognl@isNotEmpty(beginreleaseTime)"> AND release_time  >=#{beginreleaseTime,jdbcType=TIMESTAMP} </if>
			<if test="@Ognl@isNotEmpty(endreleaseTime)"> AND release_time <![CDATA[ <#{endreleaseTime,jdbcType=TIMESTAMP}]]> </if>
			<if test="@Ognl@isNotEmpty(releaseUserId)"> AND release_user_id  =#{releaseUserId} </if>
			<if test="@Ognl@isNotEmpty(releaseUserName)"> AND release_user_name  LIKE #{releaseUserName}  </if>
			<if test="@Ognl@isNotEmpty(status)"> AND status  =#{status} </if>
			
		</where>
	</sql>

使用：

<select id="queryListCount" parameterType="map" resultType="Integer">
		select count(id) from report_divided_html 
		<include refid="dynamicWhere" />
	</select>