文章预览
mybatis批量插入、批量更新常规写法,及升级写法
null value in column “xxx” violates not-null constraint mybatis批量操作报错问题处理
批量插入
<insert id="insertUser" parameterType="com.test.UserEntity">
insert into t_com_user(user_name, age, gender)
values
<foreach collection ="list" item="item" index="index" open="(" close= ")" separator= "),(">
#{item.userName, jdbcType=VARCHAR},
#{item.age, jdbcType=INTEGER},
#{item.gender, jdbcType=INTEGER}
</foreach>
</insert>
假如我们在批量插入数据的时候,还想做一下关联查询,该怎么办
?
如下写法,免去了先查询数据,然后遍历list赋值,再批量插入的操作
<insert id="insertUser" parameterType="com.test.UserEntity">
insert into t_com_user(user_name, age, gender, dept_name)
select
t1.user_name, t1.age, t1.gender, t2.dept_name
from (
VALUES
<foreach collection ="list" item="item" index="index" open="(" close= ")" separator= "),(">
#{item.userName, jdbcType=VARCHAR},
#{item.age, jdbcType=INTEGER},
#{item.gender, jdbcType=INTEGER},
#{item.deptId, jdbcType=INTEGER}
</foreach>
) as t1 (user_name, age, gender, dept_id)
left_join t_com_dept t2 on t1.dept_id = t2.id
</insert>
批量更新
-
常规写法
这种写法实际执行过程是单条执行,即使使用的是id查找数据,但是效率较差
<update id="updateUser" parameterType="com.test.UserEntity">
<foreach collection ="list" item="item" index="index" separator= ";">
update t_com_user set
user_name = #{item.userName, jdbcType=VARCHAR},
age = #{item.age, jdbcType=INTEGER},
gender = #{item.gender, jdbcType=INTEGER}
where id = #{item.id, jdbcType=INTEGER}
</foreach>
</update>
<update id="updateUser" parameterType="com.test.UserEntity">
update t_com_user t1 set
user_name = t2.user_name,
age = t2.age,
gender = t2.gender
from (
values
<foreach collection ="list" item="item" index="index" open="(" close= ")" separator= "),(">
#{item.id, jdbcType=INTEGER},
#{item.userName, jdbcType=VARCHAR},
#{item.age, jdbcType=INTEGER},
#{item.gender, jdbcType=INTEGER}
</foreach>
) as t2 (id, user_name, age, gender)
where t1.id = t2.id
</update>
ERROR: null value in column “user_name” violates not-null constraint mybatis 批量操作报错问题处理
mybatis的批量操作过程,传入的list对象集合中,对象的某个属性难免会为null,如果数据库表该列恰好有not-null限制,则会报错;处理办法,本文以pgsql的批量更新为例,亲测可行:
方法一:
使用
COALESCE(#{item.userName, jdbcType=VARCHAR}, '默认名称')
或者:
user_name = COALESCE(t2.user_name, '默认名称')
二选一即可
<update id="updateUser" parameterType="com.test.UserEntity">
update t_com_user t1 set
user_name = COALESCE(t2.user_name, '默认名称'),
age = t2.age,
gender = t2.gender
from (
values
<foreach collection ="list" item="item" index="index" open="(" close= ")" separator= "),(">
#{item.id, jdbcType=INTEGER},
COALESCE(#{item.userName, jdbcType=VARCHAR}, '默认名称'),
#{item.age, jdbcType=INTEGER},
#{item.gender, jdbcType=INTEGER}
</foreach>
) as t2 (id, user_name, age, gender)
where t1.id = t2.id
</update>
mysql 使用IFNULL函数 pgsql 使用COALESCE函数
方法二:
在foreach中使用if标签判断
<if test= "item.userName != null">
#{item.userName, jdbcType=VARCHAR}
</if>
<if test= "item.userName == null">
'默认名称'
</if>
整理编写不易,觉得作者写的好,麻烦给个赞!
………………………………