Spring Boot MyBatis 将 PostgreSQL jsonb 转为 Jackson 3 JsonNode

类型处理器

import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedJdbcTypes;
import org.apache.ibatis.type.MappedTypes;
import tools.jackson.databind.JsonNode;
import tools.jackson.databind.ObjectMapper;
import org.postgresql.util.PGobject;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

@MappedJdbcTypes(JdbcType.OTHER)
@MappedTypes(JsonNode.class)
public class JsonNodeTypeHandler extends BaseTypeHandler<JsonNode> {

    private static final ObjectMapper MAPPER = new ObjectMapper();

    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, JsonNode parameter, JdbcType jdbcType) throws SQLException {
        PGobject pg = new PGobject();
        pg.setType("jsonb");
        pg.setValue(parameter.toString());
        ps.setObject(i, pg);
    }

    @Override
    public JsonNode getNullableResult(ResultSet rs, String columnName) throws SQLException {
        Object obj = rs.getObject(columnName);
        return parse(obj);
    }

    @Override
    public JsonNode getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        Object obj = rs.getObject(columnIndex);
        return parse(obj);
    }

    @Override
    public JsonNode getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        Object obj = cs.getObject(columnIndex);
        return parse(obj);
    }

    private JsonNode parse(Object obj) throws SQLException {
        if (obj == null) return null;
        try {
            return MAPPER.readTree(String.valueOf(obj));
        } catch (Exception e) {
            throw new SQLException("Parse jsonb to JsonNode failed", e);
        }
    }
}

MyBatis-Plus 设置

MyBatis-Plus 3.5后不需要设置autoResultMap。

@Setter
@Getter
@ToString
@TableName(value = "test")
public class TestEntity {

    @TableField(value = "raw_json",
            typeHandler = JsonNodeTypeHandler.class,
            jdbcType = JdbcType.OTHER)
    private JsonNode rawJson;

}