Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Insert an enum value into an unknown table with jOOQ

Tags:

Given a table with an enum column, like this:

CREATE TYPE DIRECTION AS ENUM ('NORTH', 'EAST', 'SOUTH', 'WEST');

CREATE TABLE enum_table (
    direction DIRECTION NOT NULL
);

How do I insert into the said table using jOOQ without generating Java code for the whole table? For this particular instance, I cannot (yet) simply generate the code due to other technical restrictions. I could copy-paste a piece of generated code (a type definition e.g.) if that helps, but the whole table is too much.

What I tried:

  • No typing at all:

    context.insertInto(table("enum_table"))
        .columns(field("direction"))
        .values("west")
        .execute();
    

    As expected, this throws on incompatible types:

    org.jooq.exception.DataAccessException: SQL [insert into enum_table (direction) values (?)]; ERROR: column "direction" is of type direction but expression is of type character varying.

  • Column type as Enum.class + coercing or casting to Enum.class:

    context.insertInto(table("enum_table"))
        .columns(field("direction", Enum.class))
        .values(DSL.coerce("west", Enum.class))  // or DSL.cast(), same result
        .execute();
    

    which throws this:

    org.jooq.exception.SQLDialectNotSupportedException: Type class java.lang.Enum is not supported in dialect DEFAULT.

    (Wut? I absolutely have set my dialect to SQLDialect.POSTGRES_9_5.)

  • Creating an ad-hoc enum in Java:

    private enum Direction implements EnumType {
        NORTH, EAST, SOUTH, WEST;
    
        @Override
        public String getLiteral() {
            return this.name();
        }
    
        @Override
        public String getName() {
            return "direction";
        }
    }
    
    // and then
    context.insertInto(table("enum_table"))
        .columns(field("direction", Direction.class))
        .values(Direction.WEST)
        .execute();
    

    Also tried an alternative - same result:

    .columns(field("direction", SQLDataType.VARCHAR.nullable(false).asEnumDataType(Direction.class)))
    

    Throws the incompatible types exception again:

    org.jooq.exception.DataAccessException: SQL [insert into enum_table (direction) values (?)]; ERROR: column "direction" is of type direction but expression is of type character varying.


Is there any way to insert into an "unknown" (not generated) table with an enum column using jOOQ?

like image 663
Petr Janeček Avatar asked Oct 15 '18 09:10

Petr Janeček


People also ask

Can we use @value in enum?

No you can't !

Can enums be subclassed?

We've learned that we can't create a subclass of an existing enum. However, an interface is extensible. Therefore, we can emulate extensible enums by implementing an interface.

Can you set enum to null Java?

An enum can be null. When an enum (like Color in this program) is a field of a class, it is by default set to null. It is not initialized to a value in the enum. It has no value.

Can enum be null SQL?

Empty or NULL Enumeration ValuesAn enumeration value can also be the empty string ( '' ) or NULL under certain circumstances: If you insert an invalid value into an ENUM (that is, a string not present in the list of permitted values), the empty string is inserted instead as a special error value.


1 Answers

Comments on your existing attempts:

No typing at all

That doesn't work. jOOQ (or rather PostgreSQL) needs type information to bind an enum variable. This is a shame, of course, because the conversion from strings to enums could be seen as straight forward, so it could be done implicitly. But PostgreSQL currently doesn't work this way.

Column type as Enum.class + coercing or casting to Enum.class

This still doesn't work, because of the same reason. Now, jOOQ knows that we're dealing with an enum (it knew before, if the value was non-null), but we don't know the PostgreSQL enum type, which we need to cast the bind variable to.

Regarding "(Wut? I absolutely have set my dialect to SQLDialect.POSTGRES_9_5.)":

If you look at where the stack trace originates, it's when you pass Enum.class to DSL.field(). In that static method, there's no dialect in the context, so that's why this error message appears.

Solution

You were close:

Creating an ad-hoc enum in Java

When using EnumType in PostgreSQL, you need to also return the Schema reference. This is to distinguish between EnumType instances that have been generated with PostgreSQL or MariaDB/MySQL. This probably shouldn't be strictly necessary. Will check that through https://github.com/jOOQ/jOOQ/issues/7941

For now, try this:

private enum Direction implements EnumType {
    NORTH, EAST, SOUTH, WEST;

    @Override
    public String getLiteral() {
        return this.name();
    }

    @Override
    public Schema getSchema() {
        return MY_SCHEMA;
    }

    @Override
    public String getName() {
        return "direction";
    }
}
like image 129
Lukas Eder Avatar answered Oct 05 '22 21:10

Lukas Eder