JPA实体类映射PostgreSQL中的jsonb字段

前言

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

本篇介绍两种方式：

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

② 引入hibernate-types依赖

方式一

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

1. 自定义方言

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

2. 自定义类型

   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);
       }
   
   }
   

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

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

4. 实体中的字段

   @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依赖

1. maven坐标

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

2. 实体类

   @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 函数和操作符