SQLite 64bit integers recognized as ints in jooq

Question

I have an SQLite database that I am using with jOOQ. When I use jOOQ's code generation tool, it builds all of the table and record classes as expected. However, all of the SQLite INTEGER columns turn into java.lang.Integer fields in the generated code.

The problem is that SQLite INTEGER's can store up to a 64 bit signed integer, where java.lang.Integer is only a 32 bit signed integer.

Is it possible to tell jOOQ to use java.lang.Long (which is 64 bit signed) for these columns?

Answer 1

In fact, there is no such thing as an actual INTEGER data type in SQLite, only an INTEGER affinity, which can indeed store up to 64 bit signed integers. However, when you use DDL, you will typically use one of these (mostly) standard "type names", which can be used in CREATE TABLE statements and CAST expressions:

• INT
• INTEGER
• TINYINT
• SMALLINT
• MEDIUMINT
• BIGINT
• UNSIGNED BIG INT
• INT2
• INT8

In order to get jOOQ to generate a java.lang.Long, you will need to use one of BIGINT or INT8 in your CREATE TABLE statement. If you use INT or INTEGER, jOOQ will assume that you really intended for storing 32 bit signed integers, and thus java.lang.Integer values.

Rewriting data types

Note that jOOQ's code generator configuration also allows you to rewrite data types based on column names or column types:

<forcedType>
  <!-- Specify any data type from org.jooq.impl.SQLDataType -->
  <name>BIGINT</name>

  <!-- Add a Java regular expression matching fully-qualified columns.
       Use the pipe (union operator) to separate several expressions.
       If provided, both "expressions" and "types" must match. -->
  <expression>.*\.IS_VALID</expression>

  <!-- Add a Java regular expression matching data types to be forced
       to have this type. If provided, both "expressions" and "types" must match. -->
  <types>.*</types>
</forcedType>

Answer 2

To add to @lukas Eder's response, this is a concrete example of how i solved the issue for my case.

{
  "forcedTypes": {
    "forcedType": {
      /*
        Transform Sqlite's INTEGER PRIMARY KEY AUTOINCREMENT columns to Long instead of Int in
            Jooq generated code.
        I name all my id columns as `[table_name]_id` (eg. select user.user_id from user),
            so I'm using a sql to return all those columns.
 */

      "name": "BIGINT",

      //language=SQLite
      "sql": """
          WITH all_tables AS (SELECT name FROM sqlite_master WHERE type = 'table')
          SELECT pti.name
          FROM all_tables at INNER JOIN pragma_table_info(at.name) pti
             WHERE lower(pti.name) = lower(at.name || '_id')
      """
    }
}

Note: This is adapted from my build.gradle.kts file to make it a bit more readable. Unfortunately the actual version has more boilerplate code, because the nu.studer.jooq gradle plugin is not that kotlin friendly ¯\_(?)_/¯.