MyBatis-Plus 实现PostgreSQL数据库jsonb类型的保存与查询

新建Jsonb处理类

方式 1

/*** PostgreSQL jsonb 数据处理器** @author CoderKK* @date 2025/09/08*/
@MappedTypes({Object.class})
@MappedJdbcTypes(JdbcType.OTHER)//JSONB对应JdbcType.OTHER
public class JsonbTypeHandler extends BaseTypeHandler<Object> {private static final PGobject jsonObject = new PGobject();private static final String JSONB_STR = "jsonb";private static final String JSON_STR = "json";/*** 写数据库时，把Java对象转成JSONB类型*/@Overridepublic void setNonNullParameter(PreparedStatement preparedStatement, int i, Object o, JdbcType jdbcType) throws SQLException {if (preparedStatement != null) {jsonObject.setType(JSONB_STR);jsonObject.setValue(JSON.toJSONString(o));preparedStatement.setObject(i, jsonObject);}}@Overridepublic Object getNullableResult(ResultSet resultSet, String s) throws SQLException {return JSON.parse(resultSet.getString(s));}@Overridepublic Object getNullableResult(ResultSet resultSet, int i) throws SQLException {return JSON.parse(resultSet.getString(i));}@Overridepublic Object getNullableResult(CallableStatement callableStatement, int i) throws SQLException {return JSON.parse(callableStatement.getString(i));}
}

方式 2

/*** PostgreSQL数据库中的JSON、JSONB字段类型的处理程序** @author CoderKK* @date 2025/09/08*/
@MappedTypes({Object.class})
@MappedJdbcTypes(JdbcType.OTHER)// JSONB对应JdbcType.OTHER
public class JsonbTypeHandler extends JacksonTypeHandler {private static final PGobject jsonObject = new PGobject();private static final String JSONB = "jsonb";private static final String JSON = "json";public JsonbTypeHandler(Class<?> type) {super(type);}/*** 写数据库时，把java对象转成JSONB类型*/@Overridepublic void setNonNullParameter(PreparedStatement ps, int i, Object parameter, JdbcType jdbcType) throws SQLException {if (ps != null) {jsonObject.setType(JSONB);jsonObject.setValue(toJson(parameter));ps.setObject(i, jsonObject);}}/*** 读数据时，把JSONB类型的字段转成java对象*/@Overridepublic Object getNullableResult(ResultSet rs, String columnName) throws SQLException {Object v = rs.getObject(columnName);return convertDbToJavaObject(v);}/*** 读数据时，把JSONB类型的字段转成java对象*/@Overridepublic Object getNullableResult(ResultSet rs, int columnIndex) throws SQLException {Object v = rs.getObject(columnIndex);return convertDbToJavaObject(v);}/*** 读数据时，把JSONB类型的字段转成java对象*/@Overridepublic Object getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {Object v = cs.getObject(columnIndex);return convertDbToJavaObject(v);}/*** 读数据时，把JSONB类型的字段转成java对象*/private Object convertDbToJavaObject(Object v) {if (Objects.isNull(v)) {return null;}if (!PGobject.class.isAssignableFrom(v.getClass())) {return v;}PGobject p = (PGobject) v;String type = p.getType();if (type == null) {return v;}if (!JSONB.equalsIgnoreCase(type) && !JSON.equalsIgnoreCase(type)) {return v;}String pv = p.getValue();if (StringUtils.isBlank(pv)) {return v;}try {return parse(pv);} catch (Exception e) {// 根据实际业务需求，可以选择返回null、抛出异常或返回原始值return v;}}}

推荐方式 2 可以兼容所有类型自动转换，其他类型例如数组类型等都可以写自定义转换器实现

PostgreSQL 整数数组类型处理程序

/*** PostgreSQL 整数数组类型处理程序** @author CoderKK* @date 2025/09/08*/
public class IntegerArrayTypeHandler extends BaseTypeHandler<Integer[]> {@Overridepublic void setNonNullParameter(PreparedStatement ps, int i, Integer[] parameter, JdbcType jdbcType) throws SQLException {if (parameter != null && parameter.length > 0) {StringBuilder sb = new StringBuilder("{");for (int j = 0; j < parameter.length; j++) {sb.append(parameter[j]);if (j < parameter.length - 1) {sb.append(",");}}sb.append("}");ps.setString(i, sb.toString());} else {ps.setNull(i, java.sql.Types.VARCHAR);}}@Overridepublic Integer[] getNullableResult(ResultSet rs, String columnName) throws SQLException {String str = rs.getString(columnName);return parseString(str);}@Overridepublic Integer[] getNullableResult(ResultSet rs, int columnIndex) throws SQLException {String str = rs.getString(columnIndex);return parseString(str);}@Overridepublic Integer[] getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {String str = cs.getString(columnIndex);return parseString(str);}private Integer[] parseString(String str) {if (str == null || !str.startsWith("{") || !str.endsWith("}")) {return null;}// 去掉首尾的大括号String content = str.substring(1, str.length() - 1);if (content.isEmpty()) {return new Integer[0];}String[] parts = content.split(",");Integer[] result = new Integer[parts.length];for (int i = 0; i < parts.length; i++) {result[i] = Integer.parseInt(parts[i]);}return result;}
}

创建实体类

@Data
@NoArgsConstructor
@AllArgsConstructor
@SuperBuilder(toBuilder = true)
@TableName(value = "t_uz_word_assess", autoResultMap = true)
public class WordAssessPO implements Serializable {/*** 主键ID*/@TableId(type = IdType.AUTO)private Long id;/*** 定位点(x1,x2,y1,y2)*/@TableField(value = "point", typeHandler = IntegerArrayTypeHandler.class)private Integer[] point;/*** 各维度得分*/@TableField(value = "dim_score", typeHandler = JsonbTypeHandler.class)private EvalDim dimScore;/*** 创建时间*/@TableField(fill = FieldFill.INSERT)private LocalDateTime createTime;}