mybatis的注意事项

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中某些特殊符号作为内容时需要转义

&lt; <   
&gt; >   
&amp; &   
&apos; '   
&quot; " 

在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类型!








  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值