MyBatis批量更新问题

今天碰到个奇怪的问题,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,那么属于该类的字段应该能够默认添加类型才对?

问题出现原因:

  1. 经验不足或者框架不熟悉导致
  2. 自测不够充分,如果只更新一条应该是可以测出来的
  3. 由于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写了批量更新。