CODEWITHSUNDEEP
javajsonpostgresqlapache-cayenne
ikevin8me
ikevin8me

Reputation: 4313

How to: Java object-mapping with PostgreSQL's json and jsonb data types

I tried to use Apache Cayenne to map to my PostgreSQL schema which has json and jsonb data types. The Apache Cayenne modeler does not have the data type and changed to "OTHER". When I execute the SQL to re-create the database (necessary for utmost compatibility), it threw errors.

I then tried to look for alternative, like DataNucleus, to map to PostgreSQL's json and jsonb data types, but only to find that the Java driver does not support PostgreSQL 9.x. (So I assume DataNucleus JPO does not support PostgreSQL json and jsonb types.)

So, what are the solutions to map to PostgreSQL's json and jsonb and interoperate with data writing/retrieval for a Java-based application?

Upvotes: 3

Views: 1952

Answers (1)

andrus_a
andrus_a

Reputation: 2563

Reading and writing PostgreSQL JSON columns works in Apache Cayenne out of the box. You can map them as String attributes on the Java end. But that's pretty much it. No special magic. I just tried the following using GitHub master version (Cayenne 4.0) and PostgreSQL 9.5 Docker image:

CREATE TABLE json_test (
    id SERIAL,
    x json
);

Now Cayenne code:

ObjectContext context = ...
JsonTest t = context.newObject(JsonTest.class);
t.setX("{\"m\":1}");
context.commitChanges();

List<JsonTest> list =
     ObjectSelect.query(JsonTest.class).select(context);

"Forward-engineering" of the database schema won't work though, as Cayenne stores its model in terms of JDBC in a DB-independent manner and hence has no information about DB-specific types.

Upvotes: 4

Related Questions