SpringBoot(版本:2.x)中人大金仓(kingbase)jsonb数据类型写入处理

mybatis

1. 自定义JsonbTypeHandler

/**
 * @author BYREF
 */
@MappedTypes({Object.class})
@MappedJdbcTypes(JdbcType.OTHER)
public class JsonbTypeHandler extends BaseTypeHandler<Object> {
    private static final String JSONB_STR = "jsonb";
    private static final String JSON_STR = "json";

    private static final ObjectMapper OBJECT_MAPPER = new ObjectMapper();

    public static String toJsonString(Object obj) {
        try {
            return OBJECT_MAPPER.writeValueAsString(obj);
        } catch (JsonProcessingException e) {
            throw new RuntimeException(e);
        }
    }

    public static Object parseObject(String json) {
        try {
            return OBJECT_MAPPER.readValue(json, new TypeReference<Map<String, Object>>() {
            });
        } catch (Exception e) {
            return null;
        }
    }

    /**
     * 写数据库时，把Java对象转成JSONB类型
     */
    @Override
    public void setNonNullParameter(PreparedStatement preparedStatement, int i, Object o, JdbcType jdbcType) throws SQLException {
        if (preparedStatement != null) {
            KBobject jsonObject = new KBobject();
            jsonObject.setType(JSONB_STR);
            jsonObject.setValue(toJsonString(o));
            preparedStatement.setObject(i, jsonObject);
        }
    }

    @Override
    public Object getNullableResult(ResultSet resultSet, String s) throws SQLException {
        return parseObject(resultSet.getString(s));
    }

    @Override
    public Object getNullableResult(ResultSet resultSet, int i) throws SQLException {
        return parseObject(resultSet.getString(i));
    }

    @Override
    public Object getNullableResult(CallableStatement callableStatement, int i) throws SQLException {
        return parseObject(callableStatement.getString(i));
    }
} 

2. 调整springboot里的mybatis配置

全局配置typehandlers包

mybatis:
  mapper-locations: classpath:mapper/*.xml
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
    map-underscore-to-camel-case: true
  type-handlers-package: sdfcom.byref.kingbasedemo.config # 指定<JsonbTypeHandler>所在包

3. mapper插入数据示例

指定jdbcType=OTHER

<insert id="insert">
        insert into test01(username,ext_data,ext_info) values(#{model.username},#{model.extData,jdbcType=OTHER},#{model.extInfo,jdbcType=OTHER})
</insert>

JPA

1. 新增maven依赖

<dependency>
    <groupId>com.vladmihalcea</groupId>
    <artifactId>hibernate-types-52</artifactId>
    <version>2.21.1</version>
</dependency> 

2. 实体示例

@Entity
@TypeDefs({
        @TypeDef(name = "jsonb", typeClass = JsonBinaryType.class)
})
@Table(name = "test01")
@Getter
@Setter
@ToString
public class TestModel {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    private String username;

    @JsonRawValue
    @Column(columnDefinition = "jsonb")
    @Type(type = "jsonb")
    private Object extData;


    @JsonRawValue
    @Column(columnDefinition = "jsonb")
    @Type(type = "jsonb")
    private Object extInfo;
}