Grails 3.1 - Multiple inserts into domain

Question

When adding a single Book, you can just do:

String author = 'John Smith'
String title = 'First Book'
def book = new Book(author: author, title: title)
book.save(flush: flush, insert: true)

Say I want to add a number of books at once, for instance if the input is:

String author = 'John Smith'
String title = ['First Book', 'Second Book', 'Third Book']

How can I save all the books with one call to the database?

Don't use `flush: true` and let Grails send them all at once — doelleri, Jul 01 '16 at 19:39

score 0 · Answer 1 · answered Jul 01 '16 at 19:44

This blog post may help: http://www.tothenew.com/blog/batch-processing-in-grails/ . Basically, there is no batch save and you need to watch out for the session growing too much in size. The final code the blog author ended up with looks like this:

List  batch = []
(0..50000).each {
    Person person = new Person(....)
    batch.add(person)
    if (batch.size() > 1000) {
        Person.withTransaction {
            for (Person p in batch) {
                p.save()
            }
        }
        batch.clear()
    }
    session = sessionFactory.getCurrentSession()
    session.clear()             
}

srage · Accepted Answer · 2016-07-01T22:07:52.490

A Groovy SQL Solution:

def author = 'John Smith'
def titles = ['First Book', 'Second Book', 'Third Book']

def bookClassMetadata = sessionFactory.getClassMetadata(Book)

def bookTableName = bookClassMetadata.tableName

def authorColumnName = bookClassMetadata.propertyMapping.getColumnNames('author')[0]
def titleColumnName  = bookClassMetadata.propertyMapping.getColumnNames('title')[0]

def batchSize = 500

def batchSQL = "INSERT INTO ${bookTableName} (${authorColumnName}, ${titleColumnName})" +
               " VALUES (?,?)"

def sql = new Sql(dataSource)

sql.withBatch(batchSize, batchSQL) { preparedStatement ->

    titles.each { title ->

                preparedStatement.addBatch([author, title])
    }
}

Grails 3.1 - Multiple inserts into domain

2 Answers2