Details
-
Improvement
-
Status: Closed
-
Trivial
-
Resolution: Fixed
-
1.5.6, 1.6-beta-1
-
None
Description
groovy.sql.Sql is very usefull for SQL pipeline as
/* Fetch Person */ sql.eachRow('select * from person') { person -> /* Fetch unit */ ...process person data ... if ( person.status == 'active' ) { sql.eachRow('select * from unit where unit_id = ? and seq = ?', [ person.unit , ] ) { unit -> ... process unit data ... /* For each Job, fetchs job description */ [ person.job0, person.job1, person.job2].each { jobDes -> sql.eachRow('select * from job where job_id = ? , [ jobDes ] ) { ... process job description ... } } else { .... } }
But in groovy.sql.Sql#eachRow(String, List, Closure) preparedStatement is ever evaluated at each iteration. It would be very easy to add a PreparedStatement Cache feature to improve speed and avoid useless evaluation.
Maybe could we create a new top wrapping statement as 'persistent' for example :
sql.persistent() {
sql.eachRow('select * from person') { person ->
....
}
}
as
public void persistent(Closure clos) { setPersistent(true) clos.call(); setPersistent(false); closeResources(); }
Therefore we could have
public void eachRow(String sql, List params, Closure closure) throws SQLException { Connection connection = createConnection(); PreparedStatement statement = null; ResultSet results = null; try { log.fine(sql); if ( isPersistent() ) { statement = (PreparedStatement) cache.get(sql); if ( statement == null ) { statement = connection.prepareStatement(sql); cache.put(sql, statement); } } else { statement = connection.prepareStatement(sql); } ...