mybatis for标签 手写肯定是不可能,复制又经常忘记哪里有,记录一下复制方便
(oracle数据库)
批量添加 ,修改 ,拼接(xx,xx)in , union
添加,修改:
<foreach item="list" index="index" collection="list" open="begin" close="end;"> INSERT INTO XXX( xxx ) VALUES( #{list.XXX,jdbcType=VARCHAR}, #{xxx} //值不在list对象里,需要在mapper上加@param注解 ); </foreach>
(字段,字段) in :
(<foreach collection="paramList" index="index" item="item" open="(字段,字段,字段) in(" separator="," close=")"> <if test="index%900==0">(#{item.XX},#{item.XX},#{item.XXX})) or (XX,XX,XX) in(</if> (#{item.XX},#{item.XX},#{item.XX}) < /foreach> )
注意拼接in时 要用()包住for标签
union:
select a.xx xx
from(
<foreach collection="query.list" item="item" separator="union all">
select list.XX
from XXX list
<where>
list.XX= #{item}
< if test="query.xx!= null and query.xx!= ''">
and list.xx= #{query.xx}
< /if>
< /where>
</foreach>
) a
order by a.xx
mysql数据库:
使用批量添加修改的时候需要在数据库地址加上: allowMultiQueries=true
url: jdbc:mysql://192.xxx.xx.xx:xxxx/test?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowMultiQueries=true
删除重复数据保留最大的id
select *
FROM
table AS ta
WHERE
ta.id <> (
SELECT
t.maxid
FROM
( SELECT max( tb.id ) AS maxid FROM table AS tb WHERE ta.job_id = tb.job_id ) t
);
mybatis 偶然出现if判断失效:
传入字符串是数字时,一定要单引号包双引号,(之前是双引号包单引号:<if test="slStatus !=null and slStatus =='1' ">
也是正常,但是不知道是啥原因突然失效)血的教训
<if test='slStatus !=null and slStatus =="1" '>
</if>
<if test='slStatus !=null and slStatus =="2" '>
</if>
字符串不是数字时,双引号包单引号依然是好用的,不知原因保险还是改成单引号包双引号
<if test='dateType != null and dateType == "confirm"'>
confirm_user = #{item.updateUser},
confirm_date = now(),
</if>
<if test='dateType != null and dateType == "putaway"'>
putaway_user = #{item.updateUser},
putaway_date = now(),
</if>