BindBeanList to insert with cast

[Julian Sackmann]

Hello. Thank you all for your support. I have a question regarding kotlin and jdbi.

I need to insert multiple records onto a table in our DB at once. One of the columns in the DB is defined as an enum, so it needs a cast to insert into it. I can do this if I use JDBI to insert records individually, but I don't know how to do it inserting multiple. 

Let me provide a minimal example. Let's assume we have a listOf<myObject> and I need to insert it into a table that holds myObjects. One of the fields of myObject is a reason.

In SQL:

CREATE TYPE myReason as enum (

    'option_1',

    'option_2',

...

);

create table if not exists myObjects (

    ...

    reason    myReason NOT NULL

);

In Kotlin:

data class myObject(

    ...

    val reason: myReasonEnum,

)

enum class myReasonEnum {

    OPTION_1,

    OPTION_2,

    ...;

}

I need to write a method that will insert a listOf(<MyObject>) into the myObjects DB in one single insert (INSERT INTO myObects (…,myReason) VALUES (…,'option_1'),(…'option_2')… )

For performance reasons, I'd like to do this in one go as oppose to multiple inserts.

If I were doing single inserts, I would do:

@SqlUpdate("INSERT INTO myObjects(...,reason) VALUES (..., CAST(LOWER(:reason) AS myReason);")

fun insertIntoMyObjects(thing: myObject)

I don't know how to do this in order to insert multiple records at once. The closest I’ve gotten to has been this version:

@SqlUpdate("INSERT INTO myObjects (...,reason)VALUES <values>")

fun insertIntoMyObjects(@BindBeanList(

    value="values",

    propertyNames = [...,"reasonString"]

) tasks: List<myObjects>)

The reasonString is actually an added param in the object that basically stores the same value of the enum, but in a string and converted to lowercase.

The problem is that when executing this, SQL fails with:

ERROR: column \“reason\” is of type myReason but expression is of type character varying\n 

Does anyone know how can I add a cast to one of the propertyNames when using BindBeanList? Or another alternative to do this insert more or less cleanly?

Thanks a lot in advance for your help!

Julian.-