CODEWITHSUNDEEP
jooqpostgresql-9.6
deinspanjer
deinspanjer

Reputation: 515

How can I call this jOOQ generated function taking a custom bound type?

I originally had the following SQL function:

CREATE FUNCTION resolve_device(query JSONB) RETURNS JSONB...

and the following code calling the method generated by jOOQ:

final JsonArray jsonArray = jooqDWH.select(resolveDevice(queryJson)).fetchOne().value1().getAsJsonArray();
final JsonObject o = jsonArray.get(0).getAsJsonObject();

This worked fine. I needed to return a real device object rather than a JSON blob though, so I changed the SQL function to:

CREATE FUNCTION resolve_device(query JSONB) RETURNS SETOF device...

and the code to:

final ResolveDeviceRecord deviceRecord = jooqDWH.fetchOne(resolveDevice(queryJson));

but I am getting a runtime error:

org.jooq.exception.SQLDialectNotSupportedException: Type class com.google.gson.JsonElement is not supported in dialect DEFAULT

Many other parts of my code continue to work fine with the custom binding I have converting JsonElement to JSONB, but something about the change to this function's signature caused it to stop working.

I tried a few different variants of DSL.field() and DSL.val() to try to force it to be recognized but have not had any luck so far.

Upvotes: 1

Views: 149

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 220762

This could be a bug in jOOQ or a misconfiguration in your code generator. I'll update my answer once it is clear what went wrong.

Workaround:

Meanwhile, here's a workaround using plain SQL:

// Manually create a data type from your custom JSONB binding first:
final DataType<JsonObject> jsonb = SQLDataType.OTHER.asConvertedDataType(jsonBinding);

// Then, create an explicit bind variable using that data type:
final ResolveDeviceRecord deviceRecord = 
    jooqDWH.fetchOptional(table("resolve_device({0})", val(queryJson, jsonb)))
           .map(r -> r.into(ResolveDeviceRecord.class))
           .orElse(null);

Upvotes: 1

Related Questions