JDBI Kotlin Postgresql How to execute all queries in @SqlBatch and get the list of rows in error?

Question

I'm trying to make a Kotlin library to transfer data from a DB to another. To do so, I'm using a upsertAll method with the @SqlBatch annotation.

interface WriteRepository<Item> {
    @SqlBatch("INSERT INTO your_table (column1, column2) VALUES (:item.column1, :item.column2) " +
              "ON CONFLICT (column1) DO UPDATE SET column2 = :item.column2")
    fun upsertAll(@BindBean("item") items: List<Item>): IntArray
}

But I'm facing a problem: if I'm upserting 1000 rows but the 500th crash, then the 499 first are not commit and the 500 next are not executed. To test that, I did something like that:

private fun testBatchErreur() {
    val entities = listOf(
        Item(1,"goodValue"),
        Item(2,"goodValue"),
        Item(3,"badValue"),
        Item(4,"goodValue"),
        Item(5,"goodValue"),
        Item(6,"goodValue"),
        
    )
    var result : IntArray? = null
    try {
         result = repository.upsertAll(entities)
        println(result)
    } catch (e: Exception) {
        println(result)
        println(e)
    }
}

The Batch throw a BatchUpdateException at the 3th Batch entry, result is still null and Items 1,2 are not in DB (neither 4,5,6)

What I want to do is: if there is an error in a query of the batch, add the associated item to an errorList and continue to execute all of the other queries. Do you know if there's a way to do that?

Answer 1

In JDBI with the @SqlBatch annotation, when an error occurs during the batch execution, it typically rolls back the entire batch, and you won't have access to the results of the successfully executed queries. However, you can achieve your desired behavior by implementing a custom solution that handles batch execution differently and keeps track of rows that encounter errors. Here's a way to do it:

class CustomBatchHandler(val handle: Handle) {
    private val errorList = mutableListOf<Item>()

    fun executeBatch(items: List<Item>) {
        val batchSize = 100 // Adjust the batch size as needed

        for (i in 0 until items.size step batchSize) {
            val batch = items.subList(i, minOf(i + batchSize, items.size))

            try {
                handle.inTransaction<Any, Exception> { h ->
                    val batchUpdate = h.createBatch()

                    for (item in batch) {
                        try {
                            batchUpdate
                                .add("INSERT INTO your_table (column1, column2) VALUES (:column1, :column2) " +
                                     "ON CONFLICT (column1) DO UPDATE SET column2 = :column2")
                                .bindBean(item)
                                .execute()
                        } catch (e: Exception) {
                            // Handle the error as needed
                            errorList.add(item)
                            println("Error executing batch for item: $item")
                        }
                    }

                    batchUpdate.execute()
                }
            } catch (e: Exception) {
                // Handle transaction-level error, if any
                println("Transaction error: $e")
            }
        }
    }

    fun getErrorList(): List<Item> {
        return errorList
    }
}

In this code:

1. We create a CustomBatchHandler class that takes a JDBI Handle as a parameter.

2. We define a executeBatch method that splits the list of items into smaller batches (adjust batchSize as needed) and executes them one batch at a time.

3. Within each batch, we attempt to execute the INSERT query for each item. If an exception occurs during execution, we catch it, add the item to the errorList, and continue processing the rest of the items.

4. After each batch, we commit the changes using a transaction.

5. The getErrorList method allows you to retrieve the list of items that encountered errors during batch execution.

With this custom batch handler, you can execute your upsert operations in batches, handle errors for individual items, and continue processing the remaining items. The errorList will contain the items that encountered errors during execution.