What are the ways to translate fields of jpa-entity classes into json-type postgres tables?

Question

PostgreSQL

Hello, tell me what are the ways to translate fields of jpa-entity classes (specifically, we use hibernate) into json-type properties of postgres tables. Ideally, I would like to have a field with the type of some pojo class and, when saving the entity through the repository, have automatic conversion to the json-type field of the table and vice versa.

Answer 1

You can use the standard AttributeConverter from JPA. Something like this:

public class ShippingAddressConverter implements AttributeConverter<ShippingAddressData, String> {

    private final ObjectMapper objectMapper = new ObjectMapper();
    private final JavaType shippingAddressType =
            this.objectMapper.getTypeFactory().constructSimpleType(ShippingAddressData.class, null);

    @Override
    public String convertToDatabaseColumn(ShippingAddressData attribute) {
        if (attribute == null) {
            return null;
        }
        try {
            return this.objectMapper.writeValueAsString(attribute);
        } catch (JsonProcessingException e) {
            throw new PersistenceException("Failed to serialize shipping address", e);
        }
    }

    @Override
    public ShippingAddressData convertToEntityAttribute(String dbData) {
        if (dbData == null) {
            return new ShippingAddressData();
        }

        try {
            return this.objectMapper.readValue(dbData, shippingAddressType);
        } catch (IOException e) {
            throw new PersistenceException("Failed to deserialize shipping address", e);
        }
    }
}

and declare a field in Entity :

@Column(name = "shipping_address")
@Convert(converter = ShippingAddressConverter.class)
private ShippingAddressData shippingAddress;

However, there is one ambush here: PostgreSQL does not cast strings to json or jsonb (or just jsonb?) by default. So the easiest way is to declare type casting rules in the database:

CREATE CAST (varchar AS jsonb) WITH INOUT AS IMPLICIT;
CREATE CAST (text AS jsonb) WITH INOUT AS IMPLICIT;

It was necessary to declare a cast to both varchar and text, because sometimes Hibernate explicitly specifies data types for some reason, substituting SQL query parameters. And what type it will be (text or varchar) depends only on Hibernate. So, just in case, I announce both.