[GROOVY-1725] SQL pipeline improvement

• Type: Improvement
• Status: Closed
• Priority: Trivial
• Resolution: Fixed
• Affects Version/s: 1.5.6, 1.6-beta-1
• Fix Version/s: 1.6-beta-2
• Component/s: SQL processing
• Assignee: Paul King
• Reporter: Marc DeXeT
• Created: 13/Feb/07 11:16
• Updated: 12/Oct/08 08:49
• Resolved: 28/Aug/08 06:31

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);
            }
...