How to Map MySQL’s TINYINT(1) to Boolean in jOOQ

MySQL 8 does not yet support the BOOLEAN type as specified in the SQL standard. There is a DDL “type” called BOOL, which is just an alias for TINYINT:

 
create table t(b bool);
 

 
select 
 
  table_name, 
 
  column_name, 
 
  data_type, 
 
  column_type
 
from information_schema.columns
 
where table_name = 't';

The above produces:

 
TABLE_NAME|COLUMN_NAME|DATA_TYPE|COLUMN_TYPE|
 
----------|-----------|---------|-----------|
 
t         |b          |tinyint  |tinyint(1) |

Notice that BOOL translates to a specific “type” of TINYINT, a TINYINT(1), where we might be inclined to believe that the (1) corresponds to some sort of precision, as with NUMERIC types.

However, counter intuitively, that is not the case. It corresponds to the display width of the type, when fetching it, using some deprecated modes. Consider:

 
insert into t(b) values (0), (1), (10);
 
select * from t;

We’re getting:

 
b |
 
--|
 
 0|
 
 1|
 
10|

Notice also that MySQL can process non-boolean types as booleans. Running the following statement:

 
select * from t where b;

We’re getting:

 
b |
 
--|
 
 1|
 
10|

Using this column as a Boolean column in jOOQ

By default, jOOQ doesn’t recognise such TINYINT(1) columns as boolean columns, because it is totally possible that a user has created such a column without thinking of boolean types, as the above example has shown.

In previous versions of jOOQ, the data type rewriting feature could be used on arbitrary expressions that match the boolean column name, e.g. the below would treat all columns named "B" as BOOLEAN:

 
<forcedTypes>
 
  <forcedType>
 
    <name>BOOLEAN</name>
 
    <includeExpression>B</includeExpression>
 
  </forcedType>
 
</forcedTypes>

With jOOQ 3.12.0 (issue #7719), we can now match this display width as well for MySQL types. That way, you can write this single data type rewriting configuration to treat all integer types of display width 1 as booleans:

 
<forcedTypes>
 
  <forcedType>
 
    <name>BOOLEAN</name>
 
    <includeTypes>
 
      (?i:(TINY|SMALL|MEDIUM|BIG)?INT(UNSIGNED)?\(1\))
 
    </includeTypes>
 
  </forcedType>
 
</forcedTypes>

Using this configuration in the code generator, the above query: