3

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?

ikevin8me
  • 4,023
  • 5
  • 38
  • 73

1 Answers1

4

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.

andrus_a
  • 2,418
  • 1
  • 14
  • 10
  • Andrus, thanks for your answer and tip that "forward-engineering" won't work! I'd like to clarify more: how do I maintain the mapping information in the datamap file (ie. in Cayenne Modeler)? - I'm quite a newbie on Apache Cayenne, even though I was a WebObjects developer more than a decade ago :-) – ikevin8me Aug 30 '16 at 12:51
  • The best approach is DB-first - you design your DB schema, use some migrations framework to maintain schema versioning, and use reverse-engineering to keep your ORM model in sync, and class generation - to keep the Java layer in sync. Feel free to subscribe to cayenne user list for general discussion on Cayenne best practices. – andrus_a Aug 31 '16 at 06:23