CODEWITHSUNDEEP
javajsonpostgresqljooq
helgeg
helgeg

Reputation: 480

jOOQ - How to update a jsonb column with an array in a Postgres table to avoid SQLDialectNotSupportedException

I have a PostgreSQL table with a jsonb column which contains an array of strings that are tag values, like so:["tag1", "tag2", "tag3"]. I have a custom binding that converts JSON to and from jackson JsonNode.

The custom binding is defined here: https://gist.github.com/HelgeG/0c0b14228f75e91b7542bd6979a05b49

The record is read with a basic find and then I extract the JSON column as follows:

VRelease release = releaseModel.find(id);
JsonNode tagNode = release.getTags();

The node is converted to a List<String>

  List<String> tagList = objectMapper.readValue(
      tagNode.toString(),
      objectMapper.getTypeFactory().constructCollectionType(
          List.class, String.class));

I then add a string to the list and convert the list back to a JSON formatted string

  tagList.add(tag);
  String jsonString = objectMapper.writeValueAsString(tagList);

Then I convert the JSON formatted string into a JsonNode, and try to write it back to the database:

  releaseModel.writeTags(id, objectMapper.readValue(jsonString, JsonNode.class));

The writeTags method simply does the following:

  public int writeTags(Integer id, JsonNode tagList) {
    return ctx.update(TABLE).set(field(TAG_FIELD_NAME), tagList).where(PRIMARY_KEY.eq(id))
        .execute();
  }

However, on executing this query, I am getting the following exception:

org.jooq.exception.SQLDialectNotSupportedException: Type class com.fasterxml.jackson.databind.node.ArrayNode is not supported in dialect POSTGRES

Inspecting the JsonNode object in the debugger, it has the value:

JsonNode in debugger

I have been struggling with this for quite some time now, and would appreciate any pointers from jOOQers with more experience than me on how I can successfully write the JSON array to the database.

This is with jOOQ v3.9.5 and PostgreSQL 9.6.5.

Upvotes: 1

Views: 3212

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 220877

I'm assuming that TAG_FIELD_NAME is just a simple String that describes your column name. In that case, the explanation is very simple. jOOQ doesn't know anything about your custom Binding for the JsonNode type, let alone for a List<ArrayNode>.

Your code compiles because you're using DSL.field(String) which returns a Field<Object>. But such a field can only be used with an "ordinary" JDBC type that both jOOQ and the JDBC driver know what to do with. In order to actually bind the data type binding to your field, you have to provide it to jOOQ. Write this instead:

DataType<List<ArrayNode>> ARRAY_NODE_LIST = SQLDataType.OTHER.asConvertedDataType(
    new ArrayNodeListBinding());

Then use it as follows:

ctx.update(TABLE)
   .set(field(TAG_FIELD_NAME, ARRAY_NODE_LIST), tagList)
   // your new data type here ^^^^^^^^^^^^^^^
   .where(PRIMARY_KEY.eq(id))
   .execute();

Now you only have to write that binding. The binding you posted doesn't work here, because it binds JsonNode, not List<ArrayNode>

Upvotes: 3

Related Questions