Fullmenu null

 

WithBatch

Miguel Rueda, miguel.rueda.garcia@gmail.com

Descargar el script ../scripts/bbdd/InsertWithBatch.groovy

31 August 2017

Sometimes we want to extract data from a database and insert them into other. Most of the database has tools to perfom an export/import but if you need to do some transformation, filter some data, etc it’s not so easy to use these tools

With this script we’ll read rows from a table, transform the row in base some business logic and insert them into the destination database using batch in order to optimize the performance of the inserts

The size of the batch can be adjust with the first arguments of the method withBatch

@Grab('mysql:mysql-connector-java:5.1.6')(1)
@GrabConfig(systemClassLoader=true)
import groovy.sql.Sql

def sql_orig = Sql.newInstance( "jdbc:mysql://localhost:3306/origen?jdbcCompliantTruncation=false", "user", "password", "com.mysql.jdbc.Driver")(2)
def sql_dest =  Sql.newInstance( "jdbc:mysql://localhost:3306/destino?jdbcCompliantTruncation=false", "user", "password", "com.mysql.jdbc.Driver")

batchSize=20

sql_dest.withBatch( batchSize, "insert into TABLE_DESTINO(a,b,c) values(?,?,?)"){ ps->   (3)

   sql_orig.eachRow"select a,b,c from TABLE_ORIGEN",{ row ->   (4)

	row.a = row.a.toUpperCase().reverse()  (5)

	ps.addBatch(row) (6)
   }
}
(7)
1 Our dependencies
2 The connection to the database.
3 The insert to perfom in batch mode
4 Read every row with a cursor
5 Execute our business logic
6 Every batchSize addBatch Groovy will do the inserts
7 At the end Groovy will do the reamins inserts and close the resources
Script
@Grab('mysql:mysql-connector-java:5.1.6')(1)
@GrabConfig(systemClassLoader=true)
import groovy.sql.Sql

def sql_orig = Sql.newInstance( "jdbc:mysql://localhost:3306/origen?jdbcCompliantTruncation=false", "user", "password", "com.mysql.jdbc.Driver")(2)
def sql_dest =  Sql.newInstance( "jdbc:mysql://localhost:3306/destino?jdbcCompliantTruncation=false", "user", "password", "com.mysql.jdbc.Driver")

batchSize=20

sql_dest.withBatch( batchSize, "insert into TABLE_DESTINO(a,b,c) values(?,?,?)"){ ps->   (3)

   sql_orig.eachRow"select a,b,c from TABLE_ORIGEN",{ row ->   (4)

	row.a = row.a.toUpperCase().reverse()  (5)

	ps.addBatch(row) (6)
   }
}
(7)