Spring Data JPA与PostgreSQL的jsonb类型集成与支持
在我们项目中经常会遇到数据结构不定的情况，这时普通的关系型数据库不能满足我们的要求。Postgres为我们提供了jsonb数据类型，我们可在此类型的字段存储json数据，并可对此数据进行查询。本例将结合hibernate,Spring Data JPA,Spring Boot来实现。
1. 自定义SQLDialect
package com.call.show.common.jpa; import org.hibernate.dialect.PostgreSQL95Dialect; import java.sql.Types; public class StringToTextPostgreSQLDialect extends PostgreSQL95Dialect { public StringToTextPostgreSQLDialect() { this.registerColumnType(Types.VARCHAR, "TEXT"); } }
指定SQLDialect
spring.jpa.database-platform=com.call.show.common.utils.StringToTextPostgreSQLDialect
2、自定义jsonb数据类型
这里主要实现了Map映射PGObject(postgres对象类型)，通过ObjectMapper来实现两个数据类型的转换。
package com.call.show.common.jpa; import com.fasterxml.jackson.databind.ObjectMapper; import lombok.SneakyThrows; import org.hibernate.HibernateException; import org.hibernate.engine.spi.SharedSessionContractImplementor; import org.hibernate.type.SerializationException; import org.hibernate.usertype.UserType; import org.postgresql.util.PGobject; import org.springframework.util.StringUtils; import java.io.*; import java.lang.reflect.Field; import java.lang.reflect.ParameterizedType; import java.math.BigInteger; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Types; import java.util.*; import java.util.function.Consumer; import java.util.function.Predicate; import java.util.stream.Collectors; public class JsonType implements UserType { private final static ObjectMapper OBJECT_MAPPER = new ObjectMapper(); @Override public Object assemble(Serializable cached, Object owner) throws HibernateException { return deepCopy(cached); } @Override @SneakyThrows public Object deepCopy(Object originalValue) throws HibernateException { //将对象写到流里 ByteArrayOutputStream bo = new ByteArrayOutputStream(); ObjectOutputStream oo = new ObjectOutputStream(bo); oo.writeObject(originalValue); //从流里读出来 ByteArrayInputStream bi = new ByteArrayInputStream(bo.toByteArray()); ObjectInputStream oi = new ObjectInputStream(bi); return oi.readObject(); } @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 boolean equals(Object x, Object y) throws HibernateException { if (x instanceof List && y instanceof List) { List xList = ((List) x); List yList = ((List) y); if (xList.size() > 0 && yList.size() > 0 && xList.size() == yList.size()) { if (xList.get(0).getClass().isEnum() || yList.get(0).getClass().isEnum()) { List<String> a = (List<String>) xList.stream().map(o -> o.toString()).collect(Collectors.toList()); Collections.sort(a); List<String> b = (List<String>) yList.stream().map(o -> o.toString()).collect(Collectors.toList()); Collections.sort(b); return a.equals(b); } } } else if (x instanceof Map && y instanceof Map) { Map xMap = ((Map) x); Map yMap = ((Map) y); if (xMap.size() > 0 && yMap.size() > 0 && xMap.size() == yMap.size()) { boolean keyN = xMap.keySet().stream().allMatch(o -> o instanceof Integer || o instanceof Long); boolean valueN = xMap.values().stream().allMatch(o -> o instanceof Integer || o instanceof Long); if (keyN || valueN) { Map newX = new HashMap(); Map newY = new HashMap(); xMap.entrySet().stream().forEach((Consumer<Map.Entry>) entry -> { Object key = entry.getKey(); Object value = entry.getValue(); if (keyN) { key = key.toString(); } if (valueN) { value = value.toString(); } newX.put(key, value); }); yMap.entrySet().stream().forEach((Consumer<Map.Entry>) entry -> { Object key = entry.getKey(); Object value = entry.getValue(); if (keyN) { key = key.toString(); } if (valueN) { value = value.toString(); } newY.put(key, value); }); return newX.entrySet().stream().allMatch((Predicate<Map.Entry>) xme -> xme.getValue() == newY.get(xme.getKey()) || xme.getValue().equals(newY.get(xme.getKey()))); } } } if (x == null && y == null) { return true; } else if (x != null && y == null) { return false; } else if (x == null && y != null) { return false; } else { return x.toString().equals(y.toString()); } } @Override public int hashCode(Object x) throws HibernateException { if (x == null) { return 0; } return x.hashCode(); } @Override public boolean isMutable() { return true; } @Override @SneakyThrows public Object nullSafeGet(ResultSet rs, String[] names, SharedSessionContractImplementor session, Object owner) throws HibernateException, SQLException { PGobject o = (PGobject) rs.getObject(names[0]); if (o != null && o.getValue() != null) { Object data = OBJECT_MAPPER.readValue(o.getValue().toString(), Map.class).get("data"); if (data instanceof List) { String dbFiledName = names[0]; String[] dbFiledNames = dbFiledName.split("_"); StringBuffer filedNamePrefixBuffer = new StringBuffer(); for (int i = 0; i < dbFiledNames.length; i++) { if (i == 0) { filedNamePrefixBuffer.append(dbFiledNames[0]); } else if (dbFiledNames[i].matches("^[0-9]$")) { break; } else { filedNamePrefixBuffer.append(StringUtils.capitalize(dbFiledNames[i])); } } String fileNamePrefix = filedNamePrefixBuffer.toString().replaceAll("[0-9]", ""); List<Field> fields = new ArrayList<>(); fields.addAll(Arrays.asList(owner.getClass().getSuperclass().getDeclaredFields())); if (owner.getClass().getSuperclass().equals(Object.class)) {//针对实体类上级类是Object情况，直接从实体类中获取声明的字段 fields.addAll(Arrays.asList(owner.getClass().getDeclaredFields())); } else if (!owner.getClass().getSuperclass().getSuperclass().equals(Object.class)) {//针对实体类是继承View类情况，直接从View类的上级模型类中获取声明字段 fields.addAll(Arrays.asList(owner.getClass().getSuperclass().getSuperclass().getDeclaredFields())); } for (Field field : fields) { if (field.getName().startsWith(fileNamePrefix)) { if (field.getGenericType() instanceof ParameterizedType) { String typeName = ((ParameterizedType) field.getGenericType()).getActualTypeArguments()[0].getTypeName(); if (!typeName.contains("<")) { Class type = Class.forName(typeName); if (type.isEnum()) { return ((List) data).stream().map(o1 -> Enum.valueOf(type, o1.toString())).collect(Collectors.toList()); } } break; } } } return filterList((List) data); } else if (data instanceof Map) { return filterMap((Map) data); } else { return data; } } return null; } @Override @SneakyThrows public void nullSafeSet(PreparedStatement st, Object value, int index, SharedSessionContractImplementor session) throws HibernateException, SQLException { if (value == null) { st.setNull(index, Types.OTHER); } else { Map<String, Object> map = new HashMap<>(); map.put("data", value); value = OBJECT_MAPPER.writeValueAsString(map); st.setObject(index, value, Types.OTHER); } } @Override public Object replace(Object original, Object target, Object owner) throws HibernateException { return deepCopy(original); } @Override public Class<?> returnedClass() { return Object.class; } @Override public int[] sqlTypes() { return new int[] { Types.OTHER }; } private List filterList(List list) { return (List) list.stream().map(o -> { if (o instanceof Integer || o instanceof BigInteger) { return Long.valueOf(o.toString()); } return o; }).collect(Collectors.toList()); } private Map filterMap(Map map) { if (map.keySet().stream().allMatch(o -> o instanceof Integer) || map.values().stream().allMatch(o -> o instanceof Integer)) { map.entrySet().forEach(o -> { Map.Entry e = (Map.Entry) o; Object key = e.getKey(); Object value = e.getValue(); if (key instanceof Integer) { key = Long.valueOf(key.toString()); } if (value instanceof Integer) { value = Long.valueOf(value.toString()); } map.put(key, value); }); } return map; } }
3. 声明使用
先定义数据类型，再在字段上使用
@Entity @Data @AllArgsConstructor @NoArgsConstructor @TypeDef(name = "JsonbType", typeClass = JsonbType.class) public class Person { @Id @GeneratedValue private Long id; @Column(columnDefinition = "jsonb") @Type(type = "JsonbType") private Map<String,Object> info; }
4.Repository
通过postgres原生sql语句查询，本例含义为json数据info的一个key为name的值等于。具体的JSON的sql查询方式请参考：
public interface PersonRepository extends JpaRepository<Person,Long> { @Query(value = "select * from person where info ->> 'name' = :name" , nativeQuery = true) List<Person> findByName(@Param("name") String name); }
5. 保存和读取测试
@Bean CommandLineRunner saveAndReadJsonb(PersonRepository personRepository){ return e -> { Person p = new Person(); Map m = new HashMap(); m.put("name","汪云飞"); m.put("age",11); p.setInfo(m); Person returnPerson = personRepository.save(p); Map returnMap = returnPerson.getInfo(); for(Object entry :returnMap.entrySet()){ log.info(entry.toString()); } }; }
6. 查询测试
@Bean CommandLineRunner queryJsonb(PersonRepository personRepository){ return e -> { List<Person> people = personRepository.findByName("test"); for (Person person : people){ Map info = person.getInfo(); log.info(person.getId() + "/" + info.get("name") + "/" +info.get("age")); } }; }
