1,查询的时候不为空的判断,使用choose+多个when
<select id="findByCondition" resultType="com.lkb.bean.mail.CountEmailBank"
parameterType="com.lkb.bean.mail.CountEmailBank">
select * from t_countemailbank
where bankname=#{bankname,jdbcType=VARCHAR}
<choose>
<when test="statementdate!=null">
and statementDate = #{statementdate, jdbcType=TIMESTAMP}
</when>
<otherwise>
and statementDate is null
</otherwise>
</choose>
and mouldId = #{mouldid, jdbcType=INTEGER} limit 1
</select>
2,更新时候不为空的判断用set+多个if
<update id="updateNumByCondition" parameterType="com.lkb.bean.mail.CountEmailBank">
update t_countemailbank
<set>
<if test="num != null">
num = #{num, jdbcType=INTEGER},
</if>
<if test="lastmodifydate != null">
lastModifyDate = #{lastmodifydate, jdbcType=TIMESTAMP}
</if>
</set>
where bankname=#{bankname, jdbcType=VARCHAR}
<choose>
<when test="statementdate!=null">
and statementDate = #{statementdate, jdbcType=TIMESTAMP}
</when>
<otherwise>
and statementDate is null
</otherwise>
</choose>
and mouldId = #{mouldid, jdbcType=INTEGER}
</update>
3,参数为array集合时
<select id="getStudentListByIds_foreach_array" resultMap="resultMap_studentEntity">
select * from student s
where s.id in
<foreach collection="array" item="ids" open="(" separator="," close=")">
#{ids}
</foreach>
</select>
4,参数为list集合时
<select id="getStudentListByIds_foreach_list" resultMap="resultMap_studentEntity">
select * from student s
where s.id in
<foreach collection="list" item="idList" open="(" separator="," close=")">
#{idList}
</foreach>
</select>
5,xml中某些特殊符号作为内容时需要转义
< <
> >
& &
' '
" "
在mapper文件中写sql语句时,为避免不必要的麻烦(如上述特殊符号等),建议用<![CDATA[ ]]>来标记不应有xml解析器的进行解析的文本内容,由<![CDATA[ ]]>包含的内容都会被及西区忽略,如:
<select id="getAccountsByBranch" resultType="Account" parameterType="string">
<![CDATA[SELECT * FROM t_acctreg_accounts where acctno < #{acctno}]]>
</select>
一般情况下是可以的,但是如果这样写:
<select id="getAccountErrorCount" resultType="int" parameterType="map">
<![CDATA[
select count(*) from t_acctreg_accounterror
<where>
<if test="enddate != null and enddate != ''">
createdate <= #{enddate}
</if>
<if test="acctno != null and acctno != ''">
AND acctno LIKE '%'||#{acctno}||'%'
</if>
</where>
]]>
</select>
则会报错,这是由于该sql含有动态语句,如where+if的动态语句不能放在 <![CDATA[ ]]>中,否则将导致不能识别sql动态判断部分而报错,可以进行如下调整:
<select id="getAccountErrorCount" resultType="int" parameterType="map">
select count(*) from t_acctreg_accounterror
<where>
<if test="enddate != null and enddate != ''">
<![CDATA[createdate <= #{enddate}]]>
</if>
<if test="acctno != null and acctno != ''">
<![CDATA[AND acctno LIKE '%'||#{acctno}||'%']]>
</if>
</where>
</select>
在一般的传参过程中,最好给参数指定jdbcType类型!