Search code examples
javasqljooq

jOOQ using one database for generating code and another for executing it

We are changing databases, from one that supports an 8 bit int to one that does not. Our code breaks when Liquibase creates a DB that causes jOOQ to generate "short" variables but our code uses byte/Byte - this breaks code signatures.

Rather than recode, somebody suggested that we continue to use the previous database (HSQLDB) to generate the code and it "should" run with the new database. There are dissenting opinions, I cannot find anything definitive beyond intuition and it seems to be counter to what jOOQ was designed for. Has anyone done this successfully?

Solution

  • There is obviously no absolute yes/no answer to such a question, but there are several solutions/workarounds:

    Use the previous database product to generate code

    This will work for a short period of time, e.g. right now, but as you move on, it will be an extremely limiting factor for your schema design. You will continue tailoring your DDL and some other design decisions around what HSQLDB can do, and you won't be able to leverage other features of your new database product. This can be especially limiting when migrating data, as ALTER TABLE statements are quite different between dialects.

    I would recommend this approach only for a very short period of time, e.g. if you can't thoroughly fix this right away.

    Use jOOQ's <forcedType/> mechanism to rewrite your data types

    jOOQ's code generator allows for rewriting data types prior to loading the meta data of your schema into the code generator. This way, you can pretend your byte types are TINYINT on your new database product, even if your new database product doesn't support TINYINT.

    This is a thorough solution that you may want to implement regardless of what product you're using, as it will give you a way to re-define parts of your schema just for jOOQ's code generator, independently of how you're generating your code.

    The feature is documented here: https://www.jooq.org/doc/latest/manual/code-generation/codegen-advanced/codegen-config-database/codegen-database-forced-types

    This is definitely a more long term solution for your case.

    Notice, a future jOOQ will be able to use CHECK constraints as input meta data to decide whether to apply such a <forcedType/>. I would imagine that you will place a CHECK (my_smallint BETWEEN -128 AND 127) constraint on every such column, so you could easily recognise which columns to apply the <forcedType/> to: https://github.com/jOOQ/jOOQ/issues/8843

    Until that feature is available, you can implement it yourself via programmatic code generator configuration: https://www.jooq.org/doc/latest/manual/code-generation/codegen-programmatic/

    Or, starting with jOOQ 3.12, by using a SQL expression to produce the regular expression that <forcedType/> matches. E.g. in Oracle:

    <forcedType>
  <name>TINYINT</name>
  <sql>
    select listagg(owner || '.' || table_name || '.' 
      || regexp_replace(search_condition_vc, ' between.*', ''), '|')
    from user_constraints
    where constraint_type = 'C'
    and regexp_like(search_condition_vc, '.* between -128 and 127');
  </sql>
</forcedType>

    You could use a file based meta data source

    jOOQ doesn't have to connect to a live database instance to reverse engineer your schema. You can also pass DDL code to jOOQ, or XML files:

    This is not really solving your problem directly, but maybe, it might make solving it a bit easier. However, there are other limitations to these approaches, e.g. stored procedures aren't currently (jOOQ 3.12) supported, so I'm just adding this for completeness' sake here, not to suggest you use it right now.