How to implement batch processing in Grails

Question

I have a Grails application which implement multiple datasource and its task to process and save excel data(xlsx type) in corresponding datasource tables. But I got a problem when excel file contains huge data and it taken more time to save. So I have used SQL and insert values through batch and it works fine and taken least time to save values but it save values into common datasource tables. So again I have a problem how we use in multiple datasource.

This is working fine but how can we use in multiple datasource. I am describing our query -

def dataSource      
def sql = new Sql(dataSource)        
sql.withBatch { stmt ->
    stmt.addBatch("INSERT INTO TableName(Fields...) values(.......)
    stmt.executeBatch() 
}

I do not want to implement it through list because it taken approximately same time to save i.e.

def dataSourceName = 'lookup'     
List list= []
(0..10000).each{
    Domain dom = new Domain('some property associated with domain class')
    batch.add(dom)
    if(list.size()>1000){ 
        for(Domain object in list){
            object."$dataSourceName".save()
        }
    }
}

Answer 1

This is quite different from the original question, so if I understand it properly, you need to load different information into tables accessed through different dataSources. You have multiple dataSources defined in your project. And you want to use groovy.sql.Sql with these dataSources.

All your dataSources are available in your grails application context through dataSource_dataSourceName, so it is as easy as injecting the dataSource and creating the Sql object in avery similar manner as your first code snippet:

def dataSource_lookup
def dataSource_otherName

static transactional=false

def doWork() {
  def sql=new Sql(dataSource_lookup)
}

Be aware that to be able to be transactional over different dataSources, you need to put XA transactions in place.

http://grails.github.io/grails-doc/2.2.1/guide/conf.html#multipleDatasources