Hi, I am trying to do batch upsert with the help of this article https://medium.com/@OhadShai/first-steps-with-kotlin-exposed-cb361a9bf5ac but I get

KotlinNullPointerException

whenever I attempt to use it for inserting a new data which doesn't have an id, the id being null is the cause of the error based on what I see so far, the id is auto_increment so I cannot set it explicitly, is there a way for me to fix this or Am I doing something wrong?

Does your table object extend

IdTable

etc

Yes it does

any chance you could paste the code how you’re using it?

Okay I will post it in a while

Hmm, I could be quite wrong on this but look at the code from the blog post, it’s a bit confusing what’s going on because they accept a

data

parameter in the function, BUT there is a

data

property in

BatchInsertStatement

. I am wondering if there is something weird going on with that inner

data.forEach

expression. Which

data

is it referring to?

The data is just a list of

data

class from which I am supposed to bind values for each column of the table

Could you share a complete sample with test which fails?

Okay I have the following class and function:

class BatchInsertUpdateOnDuplicate(table: Table, private val onDupUpdate: List<Column<*>>) : BatchInsertStatement(table, false) {
    override fun prepareSQL(transaction: Transaction): String {
        val onUpdateSQL = if (onDupUpdate.isNotEmpty()) {
            " ON DUPLICATE KEY UPDATE " + onDupUpdate.joinToString { "${transaction.identity(it)}=VALUES(${transaction.identity(it)})" }
        } else ""
        val statement = super.prepareSQL(transaction) + onUpdateSQL
        print(statement)
        return statement
    }
}

fun <T : Table, E> T.batchInsertOnDuplicateKeyUpdate(data: List<E>, onDupUpdateColumns: List<Column<*>>, body: T.(BatchInsertUpdateOnDuplicate, E) -> Unit) {
    data.
        takeIf {
            it.isNotEmpty()
        }?.let {
            val insert = BatchInsertUpdateOnDuplicate(this, onDupUpdateColumns)
            data.forEach {
                insert.addBatch()
                body(insert, it)
            }
            TransactionManager.current().exec(insert)
        }
}

I am using it like this:

ProductVariants.batchInsertOnDuplicateKeyUpdate(
                variants,
                listOf(ProductVariants.sizeId, ProductVariants.colorId, ProductVariants.price )
            ) { batch, variant ->
                batch[id] = EntityID(variant.id, ProductVariants)
                batch[productId] = EntityID(variant.productId, Products)
                batch[sizeId] = EntityID(variant.sizeId, Sizes)
                batch[colorId] = EntityID(variant.colorId, Colors)
                batch[price] = variant.price
            }

It works fine for existing variants but not for new ones. Please let me now if you need more info.

You should not mix both nullable and non nullable variant's ids in a

variants

collection. If you expect to make

update

when your

id

already exists in database then you should provide that id.

Yes I always provide the existing ids, but what if it is a new variant that is not existing in the database, upsert won’t work for it?

Not in a single query as there is already defined that you should provide ID in the fields list.

Okay I see. Thanks. I ended up using another query for the non-existing ones