arrays - JOOQ 以 PostgreSQL 自定义类型作为数组 : ERROR: malformed record literal 失败

Question

我在 Postgres 上有以下自定义类型:

CREATE TYPE my_custom_type AS (
 field_a VARCHAR,
 field_b NUMERIC(10,3)
 );

和下表:

CREATE TABLE my_table
(
 COL1 VARCHAR(120) NOT NULL,
 CUSTOM_COLUMN  my_custom_type,
 CUSTOM_COLUMN_ARRAY my_custom_type[]
);

当我将自定义类型与 JOOQ 一起使用时，一切正常:

@Test
public void testWithoutArray(){
    MyTableRecord record = dsl.newRecord(MyTable.MY_TABLE);

    record.setCol1("My Col1");
    MyCustomType customType = new MyCustomType();
    customType.setFieldA("Field A Val");
    customType.setFieldB(BigDecimal.ONE);
    record.setCustomColumn(customType);

    record.store();
}

但是，当我尝试在映射到自定义类型数组的字段中设置一些值时，出现以下错误:

@Test
public void testWithArray(){
    MyTableRecord record = dsl.newRecord(MyTable.MY_TABLE);

    record.setCol1("My Col1");
    MyCustomTypeRecord customType = new MyCustomTypeRecord();
    customType.setFieldA("Field A Val 1");
    customType.setFieldB(BigDecimal.ONE);

    MyCustomTypeRecord customType2 = new MyCustomTypeRecord();
    customType2.setFieldA("Field A Val 2");
    customType2.setFieldB(BigDecimal.TEN);

    record.setCustomColumnArray(new MyCustomTypeRecord[]{customType, customType2});
    record.store();
}

org.jooq.exception.DataAccessException: SQL [insert into "my_table" ("col1", "custom_column_array") values (?, ?::my_custom_type[]) returning "my_table"."col1"]; ERROR: malformed record literal: "my_custom_type"(Field A Val 1, 1)"
 Detail: Missing left parenthesis.
    at org.jooq.impl.Utils.translate(Utils.java:1553)
    at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:571)
    at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:347)
    at org.jooq.impl.TableRecordImpl.storeInsert0(TableRecordImpl.java:176)
    at org.jooq.impl.TableRecordImpl$1.operate(TableRecordImpl.java:142)
    at org.jooq.impl.RecordDelegate.operate(RecordDelegate.java:123)
    at org.jooq.impl.TableRecordImpl.storeInsert(TableRecordImpl.java:137)
    at org.jooq.impl.UpdatableRecordImpl.store0(UpdatableRecordImpl.java:185)
    at org.jooq.impl.UpdatableRecordImpl.access$000(UpdatableRecordImpl.java:85)
    at org.jooq.impl.UpdatableRecordImpl$1.operate(UpdatableRecordImpl.java:135)
    at org.jooq.impl.RecordDelegate.operate(RecordDelegate.java:123)
    at org.jooq.impl.UpdatableRecordImpl.store(UpdatableRecordImpl.java:130)
    at org.jooq.impl.UpdatableRecordImpl.store(UpdatableRecordImpl.java:123)

JOOQ debugg 生成的查询如下:

DEBUG [main] org.jooq.tools.LoggerListener#debug:255 - Executing query          : insert into "my_table" ("col1", "custom_column_array") values (?, ?::my_custom_type[]) returning "my_table"."col1"
DEBUG [main] org.jooq.tools.LoggerListener#debug:255 - -> with bind values      : insert into "my_table" ("col1", "custom_column_array") values ('My Col1', array[[UDT], [UDT]]) returning "my_table"."col1"

我是否遗漏了一些配置或者这是一个错误？

干杯

Answer 1

如相关问题 (https://github.com/jOOQ/jOOQ/issues/4162) 中所述，这是对此类 PostgreSQL 功能的支持缺失部分。目前该issue给出的答案是:

Unfortunately, this is an area where we have to work around a couple of limitations of the PostgreSQL JDBC driver, which doesn't implement SQLData and other API (see also pgjdbc/pgjdbc#63).

Currently, jOOQ binds arrays and UDTs as strings. It seems that this particular combination is not yet supported. You will probably be able to work around this limitation by implementing your own custom data type Binding:

http://www.jooq.org/doc/latest/manual/code-generation/custom-data-type-bindings/