tjohnn

    tjohnn

    3 years ago
    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?
    r

    runjorel

    3 years ago
    Does your table object extend
    IdTable
    etc
    tjohnn

    tjohnn

    3 years ago
    Yes it does
    LongIdTable
    precisely
    This is the SQL query that is being generated
    INSERT INTO product_variants (available_quantity, color_id, created_at, discount_price, id, price, product_id, size_id, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE size_id=VALUES(size_id), color_id=VALUES(color_id), price=VALUES(price), discount_price=VALUES(discount_price), available_quantity=VALUES(available_quantity), updated_at=VALUES(updated_at)
    I do not seem to understand why this is failing. Any help is appreciated.
    r

    runjorel

    3 years ago
    any chance you could paste the code how you’re using it?
    specifically the part where you call:
    .batchInsertOnDuplicateKeyUpdate(data, columns){ .. }
    tjohnn

    tjohnn

    3 years ago
    Okay I will post it in a while
    @runjorel this is my code:
    ProductVariants.batchInsertOnDuplicateKeyUpdate(product.variants!!,
                    listOf(ProductVariants.sizeId, ProductVariants.colorId,
                        ProductVariants.price, ProductVariants.discountPrice, ProductVariants.availableQuantity,
                        ProductVariants.updatedAt)
                ) { batch, variant ->
    
                    if(variant.id ?: 0 > 0) batch[id] = EntityID(variant.id, Products)
    
                    batch[productId] = EntityID(variant.productId, Products)
                    batch[sizeId] = EntityID(variant.sizeId, Sizes)
                    batch[colorId] = EntityID(variant.colorId, Colors)
                    batch[price] = variant.price!!
                    batch[discountPrice] = variant.discountPrice ?: 0.0
                    batch[availableQuantity] = variant.availableQuantity ?: 0
                    batch[createdAt] = DateTime.now()
                    batch[updatedAt] = DateTime.now()
                }
    r

    runjorel

    3 years ago
    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?
    T.batchInsertOnDuplicateKeyUpdate(data: List<E>...
    Also have you verified all your ids are not null?
    tjohnn

    tjohnn

    3 years ago
    The data is just a list of
    data
    class from which I am supposed to bind values for each column of the table
    Some of the ids are null because they are not existing and I want to insert them, the ids are not available until I insert them first cos ids are
    auto_increment
    @tapac can you help on this? I am still having this issue. Or is there an alternative that is as optimized as this upsert method?
    tapac

    tapac

    3 years ago
    Could you share a complete sample with test which fails?
    tjohnn

    tjohnn

    3 years ago
    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.
    tapac

    tapac

    3 years ago
    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.
    tjohnn

    tjohnn

    3 years ago
    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?
    tapac

    tapac

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

    tjohnn

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