JOOQ H2 (DDLDatabase) code generaton fails because KEY is a reserved word

Question

In newer versions of H2, KEY is a reserved word. JOOQ code generation following this doc fails because we have KEY as column name in some tables:

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "create table if not exists FACT_ORDER ([*]KEY varchar not null, ...)"; expected "identifier";
SQL statement:
create table if not exists FACT_ORDER (KEY varchar not null, ...) [42001-214]

Renaming the column is not a feasible solution. The SQL DDL file cannot be edited either as the same file is used by Liquibase.

Tried but not working: H2 has a configuration command to not consider KEY as a reserved word using SET NON_KEYWORDS KEY command (H2 doc).

I tried adding the command to the generator, but still am getting the same error:

    val target = new Target();
    target.setPackageName("com.mycompany.codegen.orderdb");
    target.setDirectory(targetPath);
    val generator =
        new Generator()
            .withDatabase(
                new Database()
                    .withName("org.jooq.meta.extensions.ddl.DDLDatabase")
                    .withProperties(
                        new Property().withKey("sql").withValue("SET NON_KEYWORDS KEY;"),
                        new Property().withKey("scripts").withValue(tempDir + "/*.sql"),
                        new Property().withKey("sort").withValue("alphanumeric")))
            .withTarget(target);
    val generate = new Generate();
    generate.setPojos(true);
    generate.setRecords(true);
    generate.setPojosEqualsAndHashCode(true);
    generate.setFluentSetters(true);
    generator.setGenerate(generate);
    GenerationTool.generate(
        new Configuration().withGenerator(generator).withLogging(getLogLevel()));

score 1 · Accepted Answer · answered Mar 10 '23 at 14:26

You can specify the defaultNameCase flag on the DDLDatabase:

<!-- The default name case for unquoted objects:

     - as_is: unquoted object names are kept unquoted
     - upper: unquoted object names are turned into upper case (most databases)
     - lower: unquoted object names are turned into lower case (e.g. PostgreSQL) -->
<property>
  <key>defaultNameCase</key>
  <value>lower</value>
</property>

Or with your programmatic configuration, just add:

new Property().withKey("defaultNameCase").withValue("lower")

Just pick lower or upper depending on what case you prefer on your target RDBMS. Behind the scenes, the DDLDatabase will transform all DDL to be quoted and lower/upper case. Once the identifiers are quoted, they will work on the in-memory H2 database used by the DDLDatabase.

Of course, you can also quote the identifier in your DDL directly, instead.

JOOQ H2 (DDLDatabase) code generaton fails because KEY is a reserved word

1 Answers1