Mybatis plus update PG json 类型 报错解决
- 1. 定义的PG数据库对象
- 2. 自定义 JSON Handler
- 3. update Wrapper
- 4. update 报错信息
- 4.1 No hstore extension installed.
- 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.
- 5. 解决方案
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();
@Override
public void setNonNullParameter(PreparedStatement ps, int i, JSONObject parameter, JdbcType jdbcType) throws SQLException {
jsonObject.setType("json");
jsonObject.setValue(parameter.toString());
ps.setObject(i, jsonObject);
}
@Override
public JSONObject getNullableResult(ResultSet rs, String columnName) throws SQLException {
String sqlJson = rs.getString(columnName);
if (null != sqlJson) {
return JSONObject.parseObject(sqlJson);
}
return null;
}
@Override
public JSONObject getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
String sqlJson = rs.getString(columnIndex);
if (null != sqlJson) {
return JSONObject.parseObject(sqlJson);
}
return null;
}
@Override
public 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");
