mybatis技巧

撒的阿坎努斯

集合和in的表示
<delete id="deletePrjMembers" parameterType="map" >
delete from tpm_prj_member
where id in
<foreach collection="ids" open="(" close=")" item="item" separator=",">
${item}
</foreach>
</delete>

不等于及if判断
<select id="judgePorjectExisted" parameterType="map" resultType="int">
select count(id)
from tpm_project
where code = #{code}
<if test="id!=null and id!=''">
and id <![CDATA[<>]]> #{id}
</if>
</select>

单个参数
<select id="selectOwner" resultType="com.XXX.ZZZ.api.model.pm.Project" parameterType="string">
SELECT id, name, code FROM tpm_project where 1=1
<if test="_parameter!=null and _parameter!=''">
AND
(         
id IN (SELECT DISTINCT prjId  FROM  tpm_prj_member  WHERE  status=1 and  USER=#{_parameter} )
OR 'ZZZ@XXX.com.cn'=#{_parameter}
)     
</if>
</select>

动态排序，并根据字符串转为数字排序，例：不转为数字升序，“111”排在"29"之前
ORDER BY cast(${orderField} as unsigned) ${orderFieldBy}

like操作
WHERE ta.mainId = #{mainId}
<if test="name != null and name != ''">
and r.name like '%${name}%'
</if>


<update id="updateTaskCount" parameterType="map">
update tpm_require set
<choose>
<when test="operatorType=='insert'">
taskNum = taskNum + 1
</when>
<otherwise>
taskNum = taskNum - 1
</otherwise>
</choose>
where id = #{reqId}
</update>