Are JPA (EclipseLink) custom types possible?

Question

In particular I am interested in using PostgreSQLs json type.

The core of the problem seems to be that there is no internal mapping in Eclipselink to json type. So, using a naive approach with:

@Column(name = "json", columnDefinition = "json")
public String getJson() {
    return json;
}

... and trying to insert an object, I get an exception:

Internal Exception: org.postgresql.util.PSQLException: ERROR: column "json" is of type json but expression is of type character varying

Fair enough I suppose.

Looking through the EclipseLink docs, it seems that the applicable customizations (Transformation Mappings, Native Queries, Converters) rely on the data being made up of the supported mappings (numbers, dates, strings etc) so it makes this quite awkward to get around using vendor specific types.

The main reason this is so frustrating is that json type in posgresql is expressed the same as text/varchar and I believe (at the moment, but not forever) is just an alias of that type - therefore the driver is more than capable of transmitting this, it's just validation rules in my way.

In terms of the solution, I don't mind losing portability (in terms of being database agnostic and using vendor specific types) but just want a solution that allows me to use a json type as an attribute on a normal JPA Entity and retain all the other behavior it is accustomed to (schema generation, merge, persist, transactional code

Sasa7812 · Accepted Answer · 2015-02-26T06:40:59.317

Walking through SO I've found many questions like this regarding JSON or XML types for mapping into Postgres. It looks like nobody have faced the problem of reading from custom Postgres type, so here the solution for both reading and writing using pure JPA type conversion mechanism.

Postgres JDBC driver maps all attributes for unknown (to Java) types into org.postgresql.util.PGobject object, so it is enough to make converter for this type. Here is entity example:

@Entity
public class Course extends AbstractEntity {
    @Column(name = "course_mapped", columnDefinition = "json")
    @Convert(converter = CourseMappedConverter.class)
    private CourseMapped courseMapped;  // have no idea why would you use String json instead of the object to map

    // getters and setters
}

Here the converter example:

@Converter
public class CourseMappedConverter implements AttributeConverter<CourseMapped, PGobject> {
    @Override
    public PGobject convertToDatabaseColumn(CourseMapped courseMapped) {
        try {
            PGobject po = new PGobject();
            // here we tell Postgres to use JSON as type to treat our json
            po.setType("json");
            // this is Jackson already added as dependency to project, it could be any JSON marshaller
            po.setValue((new ObjectMapper()).writeValueAsString(courseMapped));
            return po;
        } catch (JsonProcessingException e) {
            e.printStackTrace();
            return null;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    @Override
    public CourseMapped convertToEntityAttribute(PGobject po) {
        try {
            return (new ObjectMapper()).readValue(po.getValue(),CourseMapped.class);
        } catch (IOException e) {
            e.printStackTrace();
            return null;
        }
    }
}

If you really need to stick to String JSON representation in your entity, you can make converter like this for String type

implements AttributeConverter<String, PGobject>

Here is very dirty (though working) proof of concept, it also uses fake object serialization to tell JPA that object was changed if it was

https://github.com/sasa7812/psql-cache-evict-POC

Great example thx for it, I have checked using Postgres 9.3, EclipseLink 2.6.2, Postgres JDBC 9.4-1208, TomEE 1.7.0 container and it worked perfect. I have tried also with XML datatype but unfortunately the XML type converted automatically into String and not returned as PGobject when reading from database (writing used PGobject was strange for me), so in this case you need to use Object (with small instanceof check probably) not PGobject in the specific AttributeConverter class. — Miklos Krivan, Mar 18 '16 at 10:34
I still go this error: ERROR: column "my_column" is of type json but expression is of type bytea. Any Idea why ? — Thermech, Oct 09 '16 at 12:52

score 2 · Answer 2 · edited May 23 '17 at 12:25

2

PostgreSQL is too strict about implicit casts between text-like types. The simplest way is a workaround by creating a cast; see this answer.

The clean way to do it would be to create a JPA provider extension that calls setObject(my_json), and/or teach your JPA provider to explicitly add CAST('myvalue' AS json) when it generates queries. This is a pain, as it requires JPA provider specific extensions.

This Stack Overflow search will find a bunch of related questions for the xml type, which people have similar problems with.

edited May 23 '17 at 12:25

Community

1
1

answered Dec 03 '13 at 01:28

Craig Ringer

307,061
76
688
778

Thanks for the answer. Regarding `setObject`, what function / method is that? [setObject](http://jdbc.postgresql.org/development/privateapi/org/postgresql/jdbc3g/AbstractJdbc3gStatement.html) from the PostgreSQL jdbc driver? – markdsievers Dec 03 '13 at 02:59
@markdsievers From the JDBC API, actually. http://docs.oracle.com/javase/6/docs/api/java/sql/PreparedStatement.html#setObject(int, java.lang.Object) – Craig Ringer Dec 03 '13 at 03:26

Are JPA (EclipseLink) custom types possible?

2 Answers2

Linked