如何在JOOQ 3.11中转换JSONB

Question

我正试图从JSONB转换为Google GSON JSONElement。我使用的转换器来自:https://www.jooq.org/doc/3.11/manual/code-generation/custom-data-type-bindings/

< forcedTypes >
    < forcedType >
        < userType>com.google.gson.JsonElement< /userType >
        < binding  >connection.JsonType< /binding >
        < types >.*json.*< /types >
    < /forcedType >
< /forcedTypes >

List<JooqPojo> data = DBConnection.sqlFetch(sql).into(JooqPojo.class)

public static Result<Record> sqlFetch(String query) {
    Result<Record> val = createDSL(conn).fetch(query);
    return val;
}

Exception in thread "pool-18-thread-5" org.jooq.exception.MappingException: An error ocurred when mapping record to class public.tables.pojos.RawMSObj
at org.jooq.impl.DefaultRecordMapper$MutablePOJOMapper.map(DefaultRecordMapper.java:828)
at org.jooq.impl.DefaultRecordMapper.map(DefaultRecordMapper.java:481)
at org.jooq.impl.ResultImpl.into(ResultImpl.java:1372)
at tables.RawMSTable.getMatches(RawMSTable.java:61)
at task.MSTask$update$runnable$1.run(MSTask.kt:38)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
Caused by: org.jooq.exception.DataTypeException: Cannot convert from {"json": data} (class org.postgresql.util.PGobject) to class com.google.gson.JsonElement
at org.jooq.tools.Convert$ConvertAll.fail(Convert.java:1166)
at org.jooq.tools.Convert$ConvertAll.from(Convert.java:1055)
at org.jooq.tools.Convert.convert0(Convert.java:321)
at org.jooq.tools.Convert.convert(Convert.java:313)
at org.jooq.tools.Convert.convert(Convert.java:385)
at org.jooq.impl.AbstractRecord.get(AbstractRecord.java:245)
at org.jooq.impl.DefaultRecordMapper$MutablePOJOMapper.map(DefaultRecordMapper.java:790)
... 7 more

TableImpl中的列：

 public final TableField<RawfixturesRecord, JsonElement> FIXTURE = createField("fixture", org.jooq.impl.DefaultDataType.getDefaultDataType("\"pg_catalog\".\"jsonb\""), this, "", new JsonType());

Answer 1

您使用的是普通的SQL API，在这种情况下，jOOQ只是获取SQL输入字符串并运行它。在运行语句时，它不能应用任何转换器或绑定，因此JSONB映射信息不能应用。这会导致结果集中出现字符串值（我怀疑），这些值不能自动映射到JSONElement。

您必须使用jOOQ DSL来运行查询。