EclipseLink JPA Postgres JsonObject to JSONB Converter

Question

I am relatively new to the "extended" use of EclipseLink and Postgres so I hope someone could help me.

I have the following Model:

package co.nayo.backend.models;

import co.nayo.backend.models.helpers.JSONConverter;
import co.nayo.backend.models.helpers.UUIDConverter;
import org.eclipse.persistence.annotations.Convert;
import org.eclipse.persistence.annotations.Converter;

import javax.json.JsonObject;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
import java.util.Date;
import java.util.UUID;

@Entity
@Table(name = "subscriptions")
@Converter(name = "uuid_converter", converterClass = UUIDConverter.class)
@Converter(name = "json_converter", converterClass = JSONConverter.class)
public class Subscription {
    @Id
    @Column(name = "subscription_id", updatable = false)
    @Convert("uuid_converter")
    private UUID subscriptionId;

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

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

    @Column(name = "billing_cycle")
    private String billingCycle;

    @Column(name = "amount")
    private double amount;

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

    @Column(name = "invoice_title")
    private String invoiceTitle;

    @Column(name = "invoice_description")
    private String invoiceDescription;

    @Column(name = "public")
    private boolean isPublic;

    @Column(name = "permissions")
    @Convert("json_converter")
    private JsonObject permissions;

    @Column(name = "active")
    private boolean active;

    @Column(name = "created_at")
    private Date createdAt;

    // Getters & Setters
}

In which I need/want a javax.json.JsonObject for the permissions field that is mapped to the JSONB postgres data type. Therefore I wrote this Converter to handle the conversion.

package co.nayo.backend.models.helpers;

import org.eclipse.persistence.internal.helper.DatabaseField;
import org.eclipse.persistence.mappings.DatabaseMapping;
import org.eclipse.persistence.mappings.DirectCollectionMapping;
import org.eclipse.persistence.mappings.converters.Converter;
import org.eclipse.persistence.sessions.Session;

import javax.json.JsonObject;
import java.sql.Types;

public class JSONConverter implements Converter {
    @Override
    public JsonObject convertObjectValueToDataValue(Object objectValue, Session session) {
        return (JsonObject)objectValue;
    }

    @Override
    public JsonObject convertDataValueToObjectValue(Object dataValue, Session session) {
        return (JsonObject)dataValue;
    }

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

    @Override
    public void initialize(DatabaseMapping databaseMapping, Session session) {
        final DatabaseField field;
        if(databaseMapping instanceof DirectCollectionMapping) {
            field = ((DirectCollectionMapping)databaseMapping).getDirectField();
        } else {
            field = databaseMapping.getField();
        }

        field.setSqlType(Types.OTHER);
        field.setTypeName("JsonObject");
        field.setType(JsonObject.class);
        field.setColumnDefinition("jsonb");
    }
}

When I try to persist a new Subscription I get the following error:

Internal Exception: org.postgresql.util.PSQLException: ERROR: column "permissions" is of type jsonb but expression is of type hstore
Hint: You will need to rewrite or cast the expression. Position: 224 Error Code: 0

So for some reason (I don't get yet) EclipseLink seems to ignore the JSONB binding.

When I change the field.setColumnDefinition("jsonb"); to field.setColumnDefinition("hstore"); everything works fine but of course the postgres data type is now hstore.

Could anyone lead me in the right direction how I can get it to work with the JSONB data type?

Thanks in advance and kind regards, Bob

Have a look at this https://stackoverflow.com/questions/39830842/how-to-use-postgres-jsonb-datatype-with-jpa — Simon Martinelli, Jul 06 '18 at 13:18

score 0 · Answer 1 · answered Jul 11 '18 at 07:48

For me, I had to extend the JpaBaseConfiguration to get the converter working:

@Configuration
public class JpaConfiguration extends JpaBaseConfiguration {
    protected JpaConfiguration(DataSource dataSource, JpaProperties properties,
                               ObjectProvider<JtaTransactionManager> jtaTransactionManagerProvider,
                               ObjectProvider<TransactionManagerCustomizers> transactionManagerCustomizers) {
        super(dataSource, properties, jtaTransactionManagerProvider, transactionManagerCustomizers);
    }

    @Override
    protected AbstractJpaVendorAdapter createJpaVendorAdapter() {
        return new EclipseLinkJpaVendorAdapter();
    }

    @Override
    protected Map<String, Object> getVendorProperties() {
        final HashMap<String, Object> map = new HashMap<>();
        map.put(PersistenceUnitProperties.WEAVING, detectWeavingMode());
        map.put(PersistenceUnitProperties.VALIDATION_MODE, ValidationMode.NONE.toString());
        return map;
    }

    private String detectWeavingMode() {
        return InstrumentationLoadTimeWeaver.isInstrumentationAvailable() ? "true" : "static";
    }
}

EclipseLink JPA Postgres JsonObject to JSONB Converter

1 Answers1