7

I am trying to batch insert records into an SQL table using Kotlin Exposed. I have set up the code as per the Exposed documentation, however, the SQL statements being executed are individual insert statements rather than 1 batch insert statement.

The documentation located here: https://github.com/JetBrains/Exposed/wiki/DSL has the following on Batch Inserting:

Batch Insert

Batch Insert allow mapping a list of entities into DB raws in one sql statement. It is more efficient than inserting one by one as it initiates only one statement. Here is an example:

    val cityNames = listOf("Paris", "Moscow", "Helsinki")
    val allCitiesID = cities.batchInsert(cityNames) { name ->
      this[cities.name] = name
    }

My code is as follows: 

    val mappings: List<Triple<String, String, String>> = listOf(triple1, triple2, triple3)
    transaction {
        TableName.batchInsert(mappings) {
            this[TableName.value1] = it.first
            this[TableName.value2] = it.second
            this[TableName.value3] = it.third
        }
    }

What I expect to see printed out is 1 batch insert statement which follows the syntax of 

INSERT INTO TableName (value1, value2, value3) values
(triple1value1, triple1value2, triple1value3),
(triple2value1, triple2value2, triple2value3),
(triple3value1, triple3value2, triple3value3), ...

but instead it prints 3 individual insert statements with the following syntax

INSERT INTO TableName (value1, value2, value3) values (triple1value1, triple1value2, triple1value3)
INSERT INTO TableName (value1, value2, value3) values (triple2value1, triple2value2, triple2value3)
INSERT INTO TableName (value1, value2, value3) values (triple3value1, triple3value2, triple3value3)

As this seems like the documented correct way to batch insert, what am I doing incorrectly here?

Christian
  • 27,509
  • 17
  • 111
  • 155
Owen Kirkeby
  • 115
  • 2
  • 7

1 Answers1

6

The docs explain:

NOTE: The batchInsert function will still create multiple INSERT statements when interacting with your database. You most likely want to couple this with the rewriteBatchedInserts=true (or rewriteBatchedStatements=true) option of your relevant JDBC driver, which will convert those into a single bulkInsert. You can find the documentation for this option for MySQL here and PostgreSQL here.

https://github.com/JetBrains/Exposed/wiki/DSL#batch-insert

Christophe
  • 328
  • 5
  • 15