Batch insert with entity properties

[ashokgelal]

I've the following method (I've removed some lines for brevity):

fun save(entity: E, attrsToOverride: Map<String, Any?>): Any {
      return table.insertAndGenerateKey { builder ->
          entity.properties.forEach { (key, value) ->
                  builder[key] to attrsToOverride[key] ?: value
          }
      }
 }

I can call this method by passing an entity and a map of properties to overide:

val dept = Departmant {
 name = "dept1"
 location = "usa"
}

save(dept, mapOf(dept, mapOf("location" to "Asia))

This is all good. Works just fine. Now, what I want to do is the same thing but with multiple entities and using batchInsert (for performance reasons). I couldn't figure out how to achieve this.

fun saveMultiple(entities: List<E>, attrsToOverride: Map<String, Any?>): Any {
     table.batchInsert {
        entities.forEach { 
          // I am lost here
            item { 
           
            }
        }
    }
}

Any idea how I can go about achieving this? Thank you!

[vincentlauvlwj]

fun <E : Entity<E>, T : Table<E>> T.saveMultiple(entities: List<E>, attrsToOverride: Map<String, Any>) {
    batchInsert {
        for (entity in entities) {
            item {
                for ((name, value) in entity.properties) {
                    it[name] to (attrsToOverride[name] ?: value)
                }
            }
        }
    }
}

[ashokgelal]

I had tried something very similar but I must have done something silly as I couldn't make it to work (I kept getting a list of proxy objects instead of list of ids). This seems to be working.

Related question: once you have done a batch insert, is there anyway to get the ids of the inserted rows in this batch?

[vincentlauvlwj]

It's possible. JDBC supports getting multiple generated keys by Statement.getGeneratedKeys() alfter Statement.executeBatch().

But Ktorm dosen't support it yet.

[ashokgelal]

I think in practice returning an array of generated keys is more helpful then returning an array of affected rows. Exposed also returns the generated IDs.

An array of affected rows is useful for debugging, in which case it could just log it instead.

But just in case we want to return both, we can wrap it in a data class so we could have both. If we don't want to allocate more objects then, honestly, I think returning the IDs is more useful than the affected rows.