Search code examples
javasqljooq

jOOQ - DefaultRecordMapper - Case sensitivity

Can the DefaultRecordMapper work case insensitive?

The reason for the question is that Oracle and H2 have the default behavior of captilazing identifiers if not quoted (see https://seeq.atlassian.net/wiki/spaces/KB/pages/443088907/SQL+Column+Names+and+Case+Sensitivity and https://community.oracle.com/tech/developers/discussion/895094/is-there-anyway-to-make-oracle-field-names-case-insensitive-or-is-it-just). Unlike SQL Server which uses the case sensitivity as the table was created.

The case sensitivity of the SQL output then does not match with the field of the POJO for Oracle and H2.

The only 2 solutions I could think of are:

  • Aliasing every column with quotes via an own helper method to ensure the correct case sensitivity (ITEMUNIT as "itemUnit").
  • Implement an own record mapper which works case-insensitive.

The first solution however has a disadvantage. In case fetch is used instead of fetchInto to work raw with the result, the get method for a column value would need the alias too. So, the developer needs to know that there is a distinction between using the helper method only for fetchInto and the normal select for fetch.

Have I missed something? Is there a better/easier solution to this?

I have found https://www.jooq.org/doc/latest/manual/sql-building/dsl-context/custom-settings/settings-name-style/ but this does not solve the problem.

Solution

  • You can't make the DefaultRecordMapper case insensitive, there's currently (jOOQ 3.16) no such configuration, and there aren't any plans on implementing this as it seems unnecessary, except for edge cases like yours.

    Some takes:

    Fixing the underlying problem: Inconsistent naming

    • If your database column is called ITEMUNIT, then why not call your attribute itemunit? It's the most canonical translation. You're not really gaining much by making that artificially camel cased here, but not snake cased there
    • If your attribute really needs to be called itemUnit, then why not consistently name your database column ITEM_UNIT as well?

    In both of the above approaches, you wouldn't require case insensitivity as a workaround, but rather, have consistent naming across your database and application.

    Working around the naming inconsistency

    If the above isn't viable, you can still use various means to work around this:

    • You could annotate your attribute with the JPA @Column annotation, which jOOQ supports
    • You could use a view or computed column that "fixes" the ill chosen name in your database and projects ITEMUNIT AS ITEM_UNIT for everyone
    • You can indeed alias columns manually every time you use the reflective DefaultRecordMapper, but that's laborious (you don't have to alias all columns, just the ones that are inconsistently named)
    • You can override the DefaultRecordMapper behaviour using a RecordMapperProvider
    • You can avoid using reflective mapping and map things using

    But in all of this, I think the underlying problem is the naming inconsistency. It would be far easier if you just named the column/attribute consistently. There's no value in making this particular distinction, it just causes trouble.