Enforce PostgreSQL type conversion using Jooq tools

 
 BOOLEAN< /name>
 smallint.*
 

Caused by: org.postgresql. util.PSQLException: ERROR: column "is_complete" is of type smallint but expression is of type boolean

 
 BOOLEAN
 tinyint.*
 

 
 java.lang.Boolean
 com.example.BooleanAsSmallintConverter
 
 smallint.*
 

class BooleanAsSmallintConverter extends AbstractConverter { 
 public BooleanAsSmallintConverter() {
 super(Short.class, Boolean.class);
 }

 @Override
 public Boolean from(Short t) {
 return t == null? null: t.shortValue() != (short) 0;
 }

 @Override
 public Short to(Boolean u ) {
 return u == null? null: u? Short.valueOf((short) 1): Short.valueOf((short) 0);
 }
}

Is there a way to configure the JOOQ tool to use the’forcedTypes’ tag of the PostgresSQL database to convert smallint to Boolean without providing org.jooq.Conv erter implementation?

This is what the current configuration looks like:

 
 BOOLEAN< /name>
 smallint.*
 

Using JOOQ v3.9.1.
PostgreSQL v9.6.6.

Unfortunately, I receive the next exception when storing information in the database:

Caused by: org.postgresql. util.PSQLException: ERROR: column "is_complete" is of type smallint but expression is of type boolean

Also try to use MySQL database and similar conversion from tinyint to Boolean works fine without any errors:

 
 BOOLEAN
 tinyint.*
 

No, this won’t work as you expect (and it shouldn’t). In jOOQ, if the database supports the BOOLEAN data type, bind it to JDBC as the native BOOLEAN type, for example, PostgreSQL.

If the database does not support the type (e.g. MySQL / Oracle ), then jOOQ will bind 0/1 / NULL values. However, for dialects that would otherwise support the BOOLEAN type, you cannot enforce this behavior. But then again, why not write that converter? It’s very simple. Just add:

 
 java.lang.Boolean
 com.example.BooleanAsSmallintConverter
 
 smallint.*
 

Then:

class BooleanAsSmallintConverter extends AbstractConverter { 
 public BooleanAsSmallintConverter() {
 super(Short.class, Boolean.class);
 }

 @Override
 public Boolean from(Short t) {
 return t == null? null: t.shortValue() != (short) 0;
 }

 @Override
 public Short to(Boolean u ) {
 return u == null? null: u? Short.valueOf((short) 1): Short.valueOf((short) 0);
 }
}