SQLite 64bit integers recognized as ints in jooq

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

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>