今天碰到个奇怪的问题,MyBatis批量更新数据时,在MySQL可以正常执行,但在PostgreSQL数据库上,有时会出现错误。
更新的sql语句如下,用户名称如果不为null才更新,age和sex无论是否为null都会更新:
<update id="updateBatch" parameterType="com.xwl41.demo.mybatis.model.entity.UserInfo">
UPDATE demo.user_info
<trim prefix="set" suffixOverrides=",">
<trim prefix="username =case" suffix="end,">
<foreach collection="list" item="item" index="index">
<if test="item.username != null">
when id = #{item.id} then #{item.username}
</if>
</foreach>
</trim>
<trim prefix="age =case" suffix="end,">
<foreach collection="list" item="item" index="index">
when id = #{item.id} then #{item.age}
</foreach>
</trim>
<trim prefix="sex =case" suffix="end,">
<foreach collection="list" item="item" index="index">
when id = #{item.id} then #{item.sex}
</foreach>
</trim>
update_time = CURRENT_TIMESTAMP
</trim>
WHERE id IN
<foreach collection="list" item="item" index="index" separator="," open="(" close=")">
#{item.id}
</foreach>
</update>
错误提示大概如下:
org.postgresql.util.PSQLException: ERROR: column "age" is of type integer but expression is of type text
建议:You will need to rewrite or cast the expression.
位置:198
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:356)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:496)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:413)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:190)
at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:177)
at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:566)
at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59)
at com.sun.proxy.$Proxy86.execute(Unknown Source)
at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:47)
这里如果只是简单的更新出错,那么倒没有什么可说。奇怪的地方在于,在PostgreSQL数据库批量更新时,如果列表中任意一条记录age和sex都有值,仍然可以正常更新,但如果列表中所有记录的age和sex都为null,那么就会出现上面的错误。出现错误的原因估计是因为列表所有元素两个字段值都为null时,更新代码不能判断要更新字段值的类型,转换成text类型进行处理了。
解决的方法很简单,就是在更新字段上面指明类型,即修改下面两处即可:
when id = #{item.id} then #{item.age, jdbcType=INTEGER
}
when id = #{item.id} then #{item.sex, jdbcType=SMALLINT}
猜测问题可能是在MyBatis框架?既然已经参数类型parameterType=UserInfo,那么属于该类的字段应该能够默认添加类型才对?
问题出现原因:
- 经验不足或者框架不熟悉导致
- 自测不够充分,如果只更新一条应该是可以测出来的
- 由于mapper xml文件中的参数字段不一定需要指明类型,省略也没有问题,所以跟随他人写法,或者从他处拷贝或者贪图省事不写。
其他备忘:
突然想起当时为啥使用sql来进行更新了。
这个问题是在项目中碰到的,之所以使用mapper写sql进行更新还是有原因的。一开始的写法不仅简单,而且还不会出现这个问题,就是在实体中使用注解标识,为null也进行更新,例如:
@TableField(value = “age”, updateStrategy = FieldStrategy.IGNORED)
但是有人觉得项目是他接手的,就得按照他的规则来,然后说使用mybatis自动生成的就不能修改,如果有修改,代码自动生成后会毫不犹豫覆盖。当时代码一开始是我生成,后面他有修改过,在做新需求时,发现表都重建了,新增时会自动生成默认值的status字段、创建日期,更新日期字段都变成无默认值了,实体类也被直接覆盖了,根本不看原来的代码是怎样的,实体注解标识id为自动生成、以及设置字段更新策略的注解都没了。
@TableId(value = "id", type = IdType.AUTO) @TableField(updateStrategy = FieldStrategy.IGNORED)
当时也很傻,觉得跟这种人打交道很烦,就问了下他数据库表字段定义是不是有变更了,也没有怎么说他就默默的在生成器添加了设置id自动生成的注解重新生成,新增对象时也手动设置了状态、创建时间和更新时间,还重新使用sql写了批量更新。