How to make jOOQ quote table names that are reserved keywords?

Question

On selecting from a table called user with jOOQ I get the following exception:

jOOQ; bad SQL grammar [insert into user (user_id, account_type) values (?, ?)]; nested exception is org.postgresql.util.PSQLException: ERROR: syntax error at or near "user"

My jOOQ settings are:

private static final Settings jooqSettings = new Settings()
    .withRenderSchema(false)
    .withRenderNameStyle(RenderNameStyle.LOWER);

I create a DSLContext from that and construct a query in a transaction as follows:

ctx.insertInto(USER)
      .set(USER.USER_ID, userId)
      .set(USER.ACCOUNT_TYPE, "U")
      .execute()

USER is imported as <jooq-generated-package>.tables.USER.

Does jOOQ have a config property to escape table names (all or just reserved keywords)? I couldn't find anything in the docs or source.

Answer 1

Well, you turned that quoting off by setting RenderNameStyle.LOWER... That's how it works :)

By removing that setting or by setting it to RenderNameStyle.QUOTED, jOOQ will generate those double quotes around all identifiers.

From the specification:

<simpleType name="RenderNameStyle">
  <restriction base="string">
    <!--
     Render object names quoted, as defined in the database. Use this
     to stay on the safe side with case-sensitivity and special
     characters. For instance:
     Oracle    : "SYS"."ALL_TAB_COLS"
     MySQL     : `information_schema`.`TABLES`
     SQL Server: [INFORMATION_SCHEMA].[TABLES] 
     -->
    <enumeration value="QUOTED"/>

    <!--
     Render object names, as defined in the database. For instance:
     Oracle    : SYS.ALL_TAB_COLS
     MySQL     : information_schema.TABLES
     SQL Server: INFORMATION_SCHEMA.TABLES 
     -->
    <enumeration value="AS_IS"/>

    <!--
     Force rendering object names in lower case. For instance:
     Oracle    : sys.all_tab_cols
     MySQL     : information_schema.tables
     SQL Server: information_schema.tables 
     -->
    <enumeration value="LOWER"/>

    <!--
     Force rendering object names in upper case. For instance:
     Oracle    : SYS.ALL_TAB_COLS
     MySQL     : INFORMATION_SCHEMA.TABLES
     SQL Server: INFORMATION_SCHEMA.TABLES 
     -->
    <enumeration value="UPPER"/>
  </restriction>
</simpleType>

Note, there are feature requests to add more documentation to the Javadoc (#2830) and the manual (#5231)