我如何用Jooq抽象模式？

Question

首先，我从一个限定名和表开始：

Name myTableName = DSL.name("schema", "myTable");
Table<Record> myTable = DSL.table(myTableName);

然后我用模式映射构建上下文：

Configuration configuration = new DefaultConfiguration();
configuration.set(SQLDialect.HSQLDB);
Settings settings = new Settings()
    .withRenderNameStyle(RenderNameStyle.QUOTED)
    .withRenderSchema(true)
    .withRenderMapping(
        new RenderMapping()
            .withSchemata(
                new MappedSchema()
                    .withInput("schema")
                    .withOutput("PUBLIC")
            )
    );
configuration.set(settings);
return DSL.using(configuration);

然后构建一个SQL字符串来创建表：

context.createTable(myTable)....getSQL();

invalid schema name: schema in statement [create table "schema"."myTable"(
    ...

从更大的角度来看，我试图编写跨不同方言可移植的SQL，但我必须为其构建的每个环境都使用不同的模式。我试图在Java中抽象一个通用模式，然后可以使用jOOQ根据目标环境进行映射。

Answer 1

这是一个已知问题:https://github.com/jooq/jooq/issues/5344

从jOOQ 3.9.5开始，模式映射和表映射不应用于普通SQL表和自定义命名表。虽然不会有任何映射应用于普通SQL字符串，但后者在Jooq3.10中得到了修正。

有两种变通办法：

Name myTableName = DSL.name(schema(), "myTable");

public String schema() {
    if (something)
        return "schema";
    else
        return "PUBLIC";
}

public class BookRecord extends CustomRecord<BookRecord> {
    protected BookRecord() {
        super(BookTable.BOOK);
    }
}

public class BookTable extends CustomTable<BookRecord> {
    public static final BookTable                      BOOK       = new BookTable();

    public static final TableField<BookRecord, Short>  ID         
      = createField("ID", SQLDataType.SMALLINT, BOOK);
    public static final TableField<BookRecord, String> TITLE      
      = createField("TITLE", SQLDataType.VARCHAR, BOOK);

    protected BookTable() {
        super("BOOK", DSL.schema(DSL.name("schema")));
    }

    @Override
    public Class<? extends BookRecord> getRecordType() {
        return BookRecord.class;
    }
}