CODEWITHSUNDEEP
postgresqltype-conversionjooq
Victor Shpyrka
Victor Shpyrka

Reputation: 341

Forced PostgreSQL type convertion using JOOQ tool

Is there a way to configure JOOQ tool to convert smallint to Boolean using 'forcedTypes' tag for PostgresSQL database, without providing org.jooq.Converter implementation?

This is how the current configuration looks like:

<forcedTypes>
    <forcedType>
        <name>BOOLEAN</name>
        <types>smallint.*</types>
    </forcedType>
<forcedTypes>

JOOQ v3.9.1 is being used. PostgreSQL v9.6.6.

And unfortunately receives the next exception while storing information into the database:

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

Also tried with MySQL database and similar convertion from tinyint to Boolean works fine without any errors:

<forcedTypes>
    <forcedType>
        <name>BOOLEAN</name>
        <types>tinyint.*</types>
    </forcedType>
</forcedTypes>

Upvotes: 4

Views: 628

Answers (2)

Łukasz Kamiński
Łukasz Kamiński

Reputation: 5930

You could do that, but I doubt that this is what you had in mind. You will have to create custom cast in PostgreSQL:

CREATE FUNCTION bool2int2(IN bool, OUT int2)
LANGUAGE SQL
AS $$
SELECT CASE WHEN $1 THEN 1::int2 WHEN $1 IS NOT NULL THEN 0::int2 END
$$;

DROP CAST IF EXISTS (bool AS int2);
CREATE CAST (bool AS int2)
WITH FUNCTION bool2int2(bool)
AS ASSIGNMENT;

Then this will work:

DROP TABLE IF EXISTS booltest;
CREATE TABLE booltest (id serial, boolval int2);
INSERT INTO booltest (boolval) VALUES(true),(false),(null::bool);
SELECT * FROM booltest;
 id | boolval
----+---------
  1 |       1
  2 |       0
  3 | (null);

Upvotes: 0

Lukas Eder
Lukas Eder

Reputation: 220762

No, this doesn't work as you're expecting (and it shouldn't). In jOOQ, the BOOLEAN data type is bound to JDBC as the native BOOLEAN type if the database supports it, e.g. PostgreSQL.

If the database doesn't support the type (e.g. MySQL / Oracle), then jOOQ will bind 0/1/NULL number values. But you cannot enforce this behaviour for a dialect that would otherwise support BOOLEAN types. But then again, why not just write that converter? It's really simple. Just add:

<forcedTypes>
    <forcedType>
        <userType>java.lang.Boolean</userType>
        <converter>com.example.BooleanAsSmallintConverter</converter>
        <!-- A bit risky. Are all smallints really booleans in your database? -->
        <types>smallint.*</types>
    </forcedType>
<forcedTypes>

And then:

class BooleanAsSmallintConverter extends AbstractConverter<Short, Boolean> {
    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);
    }
}

Upvotes: 1

Related Questions