JPA实体类映射PostgreSQL中的jsonb字段-526互联

前言

有时候我们需要在PostgreSQL表中存储jsonb类型的数据，JPA实体类中如何定义这个属性与之对应呢？

本篇介绍两种方式：

① 自定义数据库方言和自定义类型

② 引入hibernate-types依赖

方式一

自定义数据库方言和自定义类型

自定义方言

public class CustomPostgreSqlDialect extends PostgreSQL9Dialect {
    public CustomPostgreSqlDialect() {
        super();
        this.registerColumnType(Types.JAVA_OBJECT, "jsonb");
    }
}

自定义类型

public class JsonbType implements UserType {

    private static final XxxLogger LOGGER = XxxLoggerFactory.getLogger(JsonbType.class);

    private final ObjectMapper mapper = new ObjectMapper();

    @Override
    public int[] sqlTypes() {
        return new int[]{Types.JAVA_OBJECT};
    }

    @Override
    public Class<?> returnedClass() {
        return Map.class;
    }

    @Override
    public boolean equals(Object x, Object y) throws HibernateException {
        return ObjectUtils.nullSafeEquals(x, y);
    }

    @Override
    public int hashCode(Object x) throws HibernateException {
        return x == null ? 0 : x.hashCode();
    }

    @Override
    public Object nullSafeGet(ResultSet rs, String[] names, SharedSessionContractImplementor sharedSessionContractImplementor, Object owner) throws HibernateException, SQLException {
        Object obj = rs.getObject(names[0]);
        if (obj == null) {
            return null;
        }
        PGobject o = (PGobject) obj;
        if (o.getValue() != null) {
            try {
                // return JSON.parse(o.getValue(), Map.class);
                return mapper.readValue(o.getValue(), Map.class);
            } catch (IOException e) {
                LOGGER.error();
            }
        }
        return new HashMap<String, Object>();
    }

    @Override
    public void nullSafeSet(PreparedStatement st, Object value, int index, SharedSessionContractImplementor sharedSessionContractImplementor) throws HibernateException, SQLException {
        if (value == null) {
            st.setNull(index, Types.OTHER);
        } else {
            try {
                // st.setObject(index, JSON.toJSON(value), Types.OTHER);
                st.setObject(index, mapper.writeValueAsString(value), Types.OTHER);
            } catch (IOException e) {
                LOGGER.error();
            }
        }
    }

    @Override
    public Object deepCopy(Object originalValue) throws HibernateException {
        if (originalValue != null) {
            try {
                // 替换为其他JSON解析器
                // return JSON.parse(JSON.toJSON(originalValue), returnedClass());
                return mapper.readValue(mapper.writeValueAsString(originalValue), returnedClass());
            } catch (IOException e) {
                LOGGER.error();
                throw new HibernateException("Failed to deep copy object", e);
            }
        }
        return null;
    }

    @Override
    public boolean isMutable() {
        return true;
    }

    @Override
    public Serializable disassemble(Object value) throws HibernateException {
        Object copy = deepCopy(value);

        if (copy instanceof Serializable) {
            return (Serializable) copy;
        }
        throw new SerializationException(String.format("Cannot serialize '%s', %s is not Serializable.", value, value.getClass()), null);
    }


    @Override
    public Object assemble(Serializable cached, Object o) throws HibernateException {
        return deepCopy(cached);
    }

    @Override
    public Object replace(Object original, Object target, Object owner) throws HibernateException {
        return deepCopy(original);
    }

}

自定义方言在application.properties中声明

# CustomPostgreSqlDialect的全限定名
spring.jpa.database-platform=com.xx.CustomPostgreSqlDialect

实体中的字段

@Data
@Entity
@Table(name = "test_jsonb_entity")
@TypeDef(name = "JsonbType", typeClass = JsonbType.class) // 自定义类型
public class TestJsonbEntity {

	// 主键...
    private Integer id;
    
    /**
     * jsonb类型的字段
     */
    @Column(columnDefinition = "jsonb")
    @Type(type = "JsonbType") // @TypeDef中的name
    private Map<String, Object> clusterInfo;
    
}

方式二

引入hibernate-types依赖

maven坐标

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

实体类

@Data
@Entity
@Table(name = "test_jsonb_entity")
@TypeDef(name = "jsonb", typeClass = JsonBinaryType.class) // hibernate-types 包下的JsonBinaryType
public class TestJsonbEntity implements Serializable {
    
    // 主键...
    private Integer id;
    
    @Column(columnDefinition = "jsonb", name = "cluster_info")
    @Type(type = "jsonb") // @TypeDef中的name
    private ClusterInfo clusterInfo; // ClusterInfo类根据自己需要定义
    
}

查询

可以使用JPA的原生sql查询，参考：JSON 函数和操作符