spring boot jpa 映射postgres 的jsonb 类型

使用 jpa-hibernate 处理postgres 的jsonb类型

• 首先需要自定一个Dialect，然后注册到hibernate框架中
• 自定义一个Type用于java和数据库之间的mapping
• 在jsonb的字段上使用自定义的类型进行映射

具体的步骤

• CustomPostgreSqlDialect.java

public class CustomPostgreSqlDialect extends PostgreSQL9Dialect {

    public CustomPostgreSqlDialect() {
        this.registerColumnType(Types.JAVA_OBJECT, "jsonb");
    }
}

• 配置文件中使用自定义的方言
  spring.jpa.properties.hibernate.dialect=org.jsonb.CustomPostgreSqlDialect
• 自定义类型JsonDataUserType.java

public class JsonDataUserType implements UserType {


    @Override
    public void nullSafeSet(PreparedStatement st, Object value, int index, SessionImplementor session) throws HibernateException, SQLException {
        if (value == null) {
            st.setNull(index, Types.OTHER);
        } else {
            st.setObject(index, value, Types.OTHER);
        }
    }

    @Override
    public Object nullSafeGet(ResultSet rs, String[] names, SessionImplementor session, Object owner) throws HibernateException, SQLException {
        PGobject o = (PGobject) rs.getObject(names[]);
        if (o.getValue() != null) {
            return o.getValue();
        }
        return null;
    }

    @Override
    public Object deepCopy(Object originalValue) throws HibernateException {
        if (originalValue == null) {
            return null;
        }
        return originalValue.toString();
    }



    @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 owner) throws HibernateException {
        return deepCopy(cached);
    }

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

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

    @Override
    public int hashCode(Object x) throws HibernateException {
        if (x == null) {
            return ;
        }

        return x.hashCode();
    }

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

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

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

}

• 在实体中的字段上使用自定义的类型

@Entity
@Table(name = "device")
@TypeDef(name = "JsonDataUserType", typeClass = JsonDataUserType.class)
public class DeviceEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    @Column(name = "deviceNum")
    private String deviceNum;

    @Column(name = "jsonb")
    @Type(type = "JsonDataUserType")
    private String jsonb;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getDeviceNum() {
        return deviceNum;
    }

    public void setDeviceNum(String deviceNum) {
        this.deviceNum = deviceNum;
    }

    public String getJsonb() {
        return jsonb;
    }

    public void setJsonb(String jsonb) {
        this.jsonb = jsonb;
    }
}

实例 pg-jsonb-jpa