Mybatis plus update PG json 类型 报错解决

1. 定义的PG数据库对象

@TableName("t_demo")
public class DemoPO {@TableField(value = "content", typeHandler = JSONTypeHandler.class)private JSONObject content;}

2. 自定义 JSON Handler

package com.xxx.handler;import com.alibaba.fastjson2.JSONObject;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedTypes;
import org.postgresql.util.PGobject;import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;@MappedTypes(JSONObject.class)
public class JSONTypeHandler extends BaseTypeHandler<JSONObject> {private static final PGobject jsonObject = new PGobject();@Overridepublic void setNonNullParameter(PreparedStatement ps, int i, JSONObject parameter, JdbcType jdbcType) throws SQLException {jsonObject.setType("json");jsonObject.setValue(parameter.toString());ps.setObject(i, jsonObject);}@Overridepublic JSONObject getNullableResult(ResultSet rs, String columnName) throws SQLException {String sqlJson = rs.getString(columnName);if (null != sqlJson) {return JSONObject.parseObject(sqlJson);}return null;}@Overridepublic JSONObject getNullableResult(ResultSet rs, int columnIndex) throws SQLException {String sqlJson = rs.getString(columnIndex);if (null != sqlJson) {return JSONObject.parseObject(sqlJson);}return null;}@Overridepublic JSONObject getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {String sqlJson = cs.getString(columnIndex);if (null != sqlJson) {return JSONObject.parseObject(sqlJson);}return null;}
}

3. update Wrapper

public boolean update(String id, JSONObject content) {LambdaUpdateWrapper<DemoPO> lambdaUpdate = new LambdaUpdateWrapper<>();lambdaUpdate.eq(DemoPO::getId, id)lambdaUpdate.set(DemoPO::getContent, content);return update(lambdaUpdate);
}

4. update 报错信息

Caused by: org.apache.ibatis.type.TypeException: Error setting non null for parameter #1 with JdbcType null . Try setting a different JdbcType for this parameter or a different configuration property. Cause: org.apache.ibatis.type.TypeException: Error setting non null for parameter #1 with JdbcType null . Try setting a different JdbcType for this parameter or a different configuration property. Cause: org.postgresql.util.PSQLException: No hstore extension installed.at org.apache.ibatis.type.BaseTypeHandler.setParameter(BaseTypeHandler.java:76)at com.baomidou.mybatisplus.core.MybatisParameterHandler.setParameters(MybatisParameterHandler.java:296)... 209 common frames omitted
Caused by: org.apache.ibatis.type.TypeException: Error setting non null for parameter #1 with JdbcType null . Try setting a different JdbcType for this parameter or a different configuration property. Cause: org.postgresql.util.PSQLException: No hstore extension installed.at org.apache.ibatis.type.BaseTypeHandler.setParameter(BaseTypeHandler.java:76)at org.apache.ibatis.type.UnknownTypeHandler.setNonNullParameter(UnknownTypeHandler.java:71)at org.apache.ibatis.type.BaseTypeHandler.setParameter(BaseTypeHandler.java:74)... 210 common frames omitted
Caused by: org.postgresql.util.PSQLException: No hstore extension installed.at org.postgresql.jdbc.PgPreparedStatement.setMap(PgPreparedStatement.java:559)at org.postgresql.jdbc.PgPreparedStatement.setObject(PgPreparedStatement.java:1063)at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.setObject(HikariProxyPreparedStatement.java)at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)at java.base/java.lang.reflect.Method.invoke(Method.java:568)at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:67)at jdk.proxy3/jdk.proxy3.$Proxy121.setObject(Unknown Source)at org.apache.ibatis.type.ObjectTypeHandler.setNonNullParameter(ObjectTypeHandler.java:31)at org.apache.ibatis.type.BaseTypeHandler.setParameter(BaseTypeHandler.java:74)... 212 common frames omitted

4.1 No hstore extension installed.

hstore 是 PG 扩展的一种数据结构，但实际上我们 PG 数据库定义的为 json 类型，所以请忽略这个报错。

4.2 Error setting non null for parameter #1 with JdbcType null . Try setting a different JdbcType for this parameter or a different configuration property.

关注一下这个报错，其实错误指向也不是很明确，但大体可以 GET 到类型转换出问题了。

so 我们在JSONTypeHandler的setNonNullParameter方法添加断点，发现insert都会走到这个方法，但是update却不会走到这边。

    public void setNonNullParameter(PreparedStatement ps, int i, JSONObject parameter, JdbcType jdbcType) throws SQLException {jsonObject.setType("json"); // 这一行添加断点，检查 update 是否经过这边jsonObject.setValue(parameter.toString());ps.setObject(i, jsonObject);}

5. 解决方案

update 的时候手动添加typeHandler

lambdaUpdate.set(DemoPO::getContent, content);
==>
lambdaUpdate.set(DemoPO::getContent, content, "typeHandler=com.xxx.handler.JSONTypeHandler");