Skip to content

Instantly share code, notes, and snippets.

@mloza
Last active Apr 2, 2020
Embed
What would you like to do?
Kod źródłowy do wpisu o polach JSONB w PostgreSQL i mapowaniu ich na encje Hibernate oraz Spring Data JPA znajdujący się pod adresem: https://blog.mloza.pl/wsparcie-dla-pola-typu-jsonb-w-postgresql-dla-spring-data-jpa-hibernate/
blog_post=> select * from jsonb_entity;
id | jsonb_object
----+-----------------------------------------------------------------------------------------------------------------------------------------------
2 | {"intField": 10, "mapField": {"test": "value", "test2": "another value"}, "stringField": "String field tests", "intArrayField": [1, 3, 5, 9]}
(1 row)
blog_post=> SELECT jsonb_object->>'stringField' FROM jsonb_entity WHERE jsonb_object->>'intField'='10';
?column?
String field tests
(1 row)
@Column
@Type(type = "pl.mloza.hibernate.CustomType")
private JsonbObject jsonbObject;
public class CustomPostgreSQL94Dialect extends PostgreSQL94Dialect {
public CustomPostgreSQL94Dialect() {
this.registerColumnType(Types.JAVA_OBJECT, "jsonb");
}
}
public class CustomType implements UserType {
@Override
public int[] sqlTypes() {
return new int[]{Types.JAVA_OBJECT};
}
@Override
public Class returnedClass() {
return JsonbObject.class;
}
@Override
public boolean equals(Object o, Object o1) throws HibernateException {
return Objects.equals(o, o1);
}
@Override
public int hashCode(Object o) throws HibernateException {
return o.hashCode();
}
@Override
public Object nullSafeGet(ResultSet resultSet, String[] names, SharedSessionContractImplementor sharedSessionContractImplementor, Object owner) throws HibernateException, SQLException {
final String cellContent = resultSet.getString(names[0]);
if (cellContent == null) {
return null;
}
try {
final ObjectMapper mapper = new ObjectMapper();
return mapper.readValue(cellContent.getBytes(Charset.defaultCharset()), returnedClass());
} catch (final Exception ex) {
throw new RuntimeException("Failed to convert String to Invoice: " + ex.getMessage(), ex);
}
}
@Override
public void nullSafeSet(PreparedStatement preparedStatement, Object value, int idx, SharedSessionContractImplementor sharedSessionContractImplementor) throws HibernateException, SQLException {
if (value == null) {
preparedStatement.setNull(idx, Types.OTHER);
return;
}
try {
final ObjectMapper mapper = new ObjectMapper();
final StringWriter w = new StringWriter();
mapper.writeValue(w, value);
w.flush();
preparedStatement.setObject(idx, w.toString(), Types.OTHER);
} catch (final Exception ex) {
throw new RuntimeException("Failed to convert Invoice to String: " + ex.getMessage(), ex);
}
}
@Override
public Object deepCopy(Object value) throws HibernateException {
try {
// use serialization to create a deep copy
ByteArrayOutputStream bos = new ByteArrayOutputStream();
ObjectOutputStream oos = new ObjectOutputStream(bos);
oos.writeObject(value);
oos.flush();
oos.close();
bos.close();
ByteArrayInputStream bais = new ByteArrayInputStream(bos.toByteArray());
return new ObjectInputStream(bais).readObject();
} catch (ClassNotFoundException | IOException ex) {
throw new HibernateException(ex);
}
}
@Override
public boolean isMutable() {
return true;
}
@Override
public Serializable disassemble(Object o) throws HibernateException {
return (Serializable) o;
}
@Override
public Object assemble(Serializable serializable, Object o) throws HibernateException {
return serializable;
}
@Override
public Object replace(Object original, Object target, Object owner) throws HibernateException {
return original;
}
}
Entity
public class JsonbEntity {
@Id
@GeneratedValue
private long id;
@Column
private JsonbObject jsonbObject; //pole które będzie zapisane jako JSONB
// setters, getters, toString...
}
public class JsonbObject implements Serializable {
private int intField;
private String stringField;
private int[] intArrayField;
private Map<String, String> mapField = new HashMap<>();
// setters, getters etc.
}
public interface JsonbRepository extends CrudRepository<JsonbEntity, Long> { }
@SpringBootTest
public class RepositoryTest extends AbstractTestNGSpringContextTests {
@Autowired
private JsonbRepository repository;
@Test
public void shouldSaveDataInJSONB() {
JsonbObject jsonbObject = new JsonbObject()
.setIntArrayField(new int[]{1, 3, 5, 9})
.setIntField(10)
.setStringField("String field tests");
jsonbObject
.getMapField()
.put("test", "value");
jsonbObject.getMapField().put("test2", "another value");
JsonbEntity jsonbEntity = new JsonbEntity()
.setJsonbObject(jsonbObject);
JsonbEntity entity = repository.save(jsonbEntity);
Optional<JsonbEntity> jsonbObjectNew = repository.findById(entity.getId());
System.out.println(jsonbObjectNew);
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment