mybatis的注意事项

最新推荐文章于 2022-04-16 11:58:14 发布

nikkty

最新推荐文章于 2022-04-16 11:58:14 发布

阅读量460

点赞数

CC 4.0 BY-SA版权

分类专栏： mybatis 文章标签： ibatis

本文链接：https://blog.youkuaiyun.com/nidayu/article/details/44591803

mybatis 专栏收录该内容

5 篇文章

订阅专栏

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类型！