SQL with Groovy .

SQL with Groovy .

Groovy provides a handy way to handle jdbc when compared to java. Playing with database becomes very easy with groovy. To use SQL scripting with groovy we have to use the  groovy.sql.Sql package.

import groovy.sql.Sql

sql = Sql.newInstance( 'jdbc:jtds:sqlserver://serveripName/databaseName-CLASS;domain=yourdomainname', 'dbusername', 'dbpassword', 'driverclass' )

sql.eachRow( 'select * from tableName' ) {
println "$it.slno -- ${it.userName} --"
}

The Sql.newInstance method helps to create database connection. It takes the parameters like database username, database password, driver class and jdbc url.
The sql.eachRow method is used to fire SQL query and iterate the resultset aswell.

Groovy has provision to handle performance with transaction based query. We can use the batch withBatch method to handle the performance issue.

sql.eachRow("SELECT * FROM employee WHERE filter_criteria ", { employee ->
// Process employee and add update statement to batch
def updateStmts = processEmployee(employee)
stmt.addBatch(updateStmts)
})
})

 

For the sake of integrity we can define the withTransaction block along withBatch

sql.withTransaction {
def result = sql.withBatch({ ... })
...
}

 

When we know that the result set is only one row.

row = sql.firstRow('select columnA, columnB from tableName')
firstRow method helps us to retrive the first row.
def ans = sql.firstRow("select * from PERSON where location_id < $location")
println ans.firstname

groovy insert statement with closure

sql.execute("insert into employee (employeName, empAddress) values (${employeeName}, ${empAddress})")

sql.close().

This method closes the sql connection, and takes care of the connection.

These were only few methods. To know more we can explore the groovy.sql package.

Batch-Updation in Hibernate

Batch Updation in Hibernate

JDBC has long been offering support for DML statement batching. By default, all statements are sent one after the other, each one in a separate network round-trip. Batching allows us to send multiple statements in one-shot, saving unnecessary socket stream flushing.

Hibernate hides the database statements behind a transactional write-behind abstraction layer. An intermediate layer allows us to hide the JDBC batching semantics from the persistence layer logic. This way, we can change the JDBC batching strategy without altering the data access code.

Update code snippet look like this ,

Session session = sessionFactory.openSession();
Transaction txInstance = session.beginTransaction();
ScrollableResults studentInstance = session.createQuery("FROM STUDENT").scroll();
int count =0;
while( studentInstance.next())
{
   Student student =(Student) studentInstance.get(Student.class,StudentID); 
   student.setregNo( regNO );
   session.update(student);
  // 50 - size of batch which you set earlier.
  // For Detail "http://helicaltech.com/batch-insertion-in-hibernate/"
   if(++count %50==0)
   {
      session.flush();
      session.clear();
   }
}
txInstance.commit();
session.close();

----------------------------------

 

PUSHPRAJ KUMAR (BI Developer)