Field Detail

• public static final int ALL_RESULT_SETS

• public static final OutParameter ARRAY

• public static final OutParameter BIGINT

• public static final OutParameter BINARY

• public static final OutParameter BIT

• public static final OutParameter BLOB

• public static final OutParameter BOOLEAN

• public static final OutParameter CHAR

• public static final OutParameter CLOB

• public static final OutParameter DATALINK

• public static final OutParameter DATE

• public static final OutParameter DECIMAL

• public static final OutParameter DISTINCT

• public static final OutParameter DOUBLE

• public static final int FIRST_RESULT_SET

• public static final OutParameter FLOAT

• public static final OutParameter INTEGER

• public static final OutParameter JAVA_OBJECT

• protected static final Logger LOG

  Hook to allow derived classes to access the log

• public static final OutParameter LONGVARBINARY

• public static final OutParameter LONGVARCHAR

• public static final int NO_RESULT_SETS

• public static final OutParameter NULL

• public static final OutParameter NUMERIC

• public static final OutParameter OTHER

• public static final OutParameter REAL

• public static final OutParameter REF

• public static final OutParameter SMALLINT

• public static final OutParameter STRUCT

• public static final OutParameter TIME

• public static final OutParameter TIMESTAMP

• public static final OutParameter TINYINT

• public static final OutParameter VARBINARY

• public static final OutParameter VARCHAR

Constructor Detail

• public Sql(DataSource dataSource)

  Constructs an SQL instance using the given DataSource. Each operation will use a Connection from the DataSource pool and close it when the operation is completed putting it back into the pool.

  Parameters:

  dataSource - the DataSource to use

• public Sql(Connection connection)

  Constructs an SQL instance using the given Connection. It is the caller's responsibility to close the Connection after the Sql instance has been used. Depending on which features you are using, you may be able to do this on the connection object directly but the preferred approach is to call the close() method which will close the connection but also free any cached resources.

  Parameters:

  connection - the Connection to use

• public Sql(Sql parent)

Method Detail

• public static InParameter ARRAY(Object value)

• public static InParameter BIGINT(Object value)

• public static InParameter BINARY(Object value)

• public static InParameter BIT(Object value)

• public static InParameter BLOB(Object value)

• public static InParameter BOOLEAN(Object value)

• public static InParameter CHAR(Object value)

• public static InParameter CLOB(Object value)

• public static InParameter DATALINK(Object value)

• public static InParameter DATE(Object value)

• public static InParameter DECIMAL(Object value)

• public static InParameter DISTINCT(Object value)

• public static InParameter DOUBLE(Object value)

• public static InParameter FLOAT(Object value)

• public static InParameter INTEGER(Object value)

• public static InParameter JAVA_OBJECT(Object value)

• public static InParameter LONGVARBINARY(Object value)

• public static InParameter LONGVARCHAR(Object value)

• public static InParameter NULL(Object value)

• public static InParameter NUMERIC(Object value)

• public static InParameter OTHER(Object value)

• public static InParameter REAL(Object value)

• public static InParameter REF(Object value)

• public static InParameter SMALLINT(Object value)

• public static InParameter STRUCT(Object value)

• public static InParameter TIME(Object value)

• public static InParameter TIMESTAMP(Object value)

• public static InParameter TINYINT(Object value)

• public static InParameter VARBINARY(Object value)

• public static InParameter VARCHAR(Object value)

• protected List<GroovyRowResult> asList(String sql, ResultSet rs)

  Hook to allow derived classes to override list of result collection behavior. The default behavior is to return a list of GroovyRowResult objects corresponding to each row in the ResultSet.

  throws:

  SQLException if a database error occurs

  Parameters:

  sql - query to execute

  rs - the ResultSet to process

  Returns:

  the resulting list of rows

• protected List<GroovyRowResult> asList(String sql, ResultSet rs, @ClosureParams(value=SimpleType.class, options="java.sql.ResultSetMetaData") Closure metaClosure)

  Hook to allow derived classes to override list of result collection behavior. The default behavior is to return a list of GroovyRowResult objects corresponding to each row in the ResultSet.

  throws:

  SQLException if a database error occurs

  Parameters:

  sql - query to execute

  rs - the ResultSet to process

  metaClosure - called for meta data (only once after sql execution)

  Returns:

  the resulting list of rows

• protected List<GroovyRowResult> asList(String sql, ResultSet rs, int offset, int maxRows, @ClosureParams(value=SimpleType.class, options="java.sql.ResultSetMetaData") Closure metaClosure)

• protected String asSql(GString gstring, List<Object> values)

  Hook to allow derived classes to override sql generation from GStrings.

  Parameters:

  gstring - a GString containing the SQL query with embedded params

  values - the values to embed

  Returns:

  the SQL version of the given query using ? instead of any parameter

  See Also:

  expand(Object)

• protected SqlWithParams buildSqlWithIndexedProps(String sql)

  Hook to allow derived classes to override behavior associated with the parsing and indexing of parameters from a given sql statement.

  Parameters:

  sql - the sql statement to process

  Returns:

  a SqlWithParams instance containing the parsed sql and parameters containing the indexed location and property name of parameters or null if no parsing of the sql was performed.

• public void cacheConnection(@ClosureParams(value=SimpleType.class, options="java.sql.Connection") Closure closure)

  Caches the connection used while the closure is active. If the closure takes a single argument, it will be called with the connection, otherwise it will be called with no arguments.

  throws:

  SQLException if a database error occurs

  Parameters:

  closure - the given closure

• public void cacheStatements(@ClosureParams(value=SimpleType.class, options="java.sql.Connection") Closure closure)

  Caches every created preparedStatement in Closure closure Every cached preparedStatement is closed after closure has been called. If the closure takes a single argument, it will be called with the connection, otherwise it will be called with no arguments.

  throws:

  SQLException if a database error occurs

  Parameters:

  closure - the given closure

  See Also:

  setCacheStatements(boolean)

• public int call(String sql)

  Performs a stored procedure call.

  Example usage (tested with MySQL) - suppose we have the following stored procedure:

   sql.execute """
       CREATE PROCEDURE HouseSwap(_first1 VARCHAR(50), _first2 VARCHAR(50))
       BEGIN
           DECLARE _loc1 INT;
           DECLARE _loc2 INT;
           SELECT location_id into _loc1 FROM PERSON where firstname = _first1;
           SELECT location_id into _loc2 FROM PERSON where firstname = _first2;
           UPDATE PERSON
           set location_id = case firstname
               when _first1 then _loc2
               when _first2 then _loc1
           end
           where (firstname = _first1 OR firstname = _first2);
       END
   """
   

  then you can invoke the procedure as follows:

   def rowsChanged = sql.call("{call HouseSwap('Guillaume', 'Paul')}")
   assert rowsChanged == 2
   

  throws:

  SQLException if a database access error occurs

  Parameters:

  sql - the SQL statement

  Returns:

  the number of rows updated or 0 for SQL statements that return nothing

• public int call(GString gstring)

  Performs a stored procedure call with the given embedded parameters.

  Example usage - see call(String) for more details about creating a HouseSwap(IN name1, IN name2) stored procedure. Once created, it can be called like this:

   def p1 = 'Paul'
   def p2 = 'Guillaume'
   def rowsChanged = sql.call("{call HouseSwap($p1, $p2)}")
   assert rowsChanged == 2
   

  Resource handling is performed automatically where appropriate.

  throws:

  SQLException if a database access error occurs

  Parameters:

  gstring - a GString containing the SQL query with embedded params

  Returns:

  the number of rows updated or 0 for SQL statements that return nothing

  See Also:

  expand(Object)

  call(String)

• public int call(String sql, List<Object> params)

  Performs a stored procedure call with the given parameters.

  Example usage - see call(String) for more details about creating a HouseSwap(IN name1, IN name2) stored procedure. Once created, it can be called like this:

   def rowsChanged = sql.call("{call HouseSwap(?, ?)}", ['Guillaume', 'Paul'])
   assert rowsChanged == 2
   

  Resource handling is performed automatically where appropriate.

  throws:

  SQLException if a database access error occurs

  Parameters:

  sql - the SQL statement

  params - a list of parameters

  Returns:

  the number of rows updated or 0 for SQL statements that return nothing

  See Also:

  call(String)

• public int call(String sql, Object[] params)

  Performs a stored procedure call with the given parameters.

  An Object array variant of call(String, List).

  throws:

  SQLException if a database access error occurs

  Parameters:

  sql - the SQL statement

  params - an array of parameters

  Returns:

  the number of rows updated or 0 for SQL statements that return nothing

  See Also:

  call(String)

• public void call(String sql, List<Object> params, @ClosureParams(value=SimpleType.class, options="java.lang.Object[]") Closure closure)

  Performs a stored procedure call with the given parameters. The closure is called once with all the out parameters.

  Example usage - suppose we create a stored procedure (ignore its simplistic implementation):

   // Tested with MySql 5.0.75
   sql.execute """
       CREATE PROCEDURE Hemisphere(
           IN p_firstname VARCHAR(50),
           IN p_lastname VARCHAR(50),
           OUT ans VARCHAR(50))
       BEGIN
       DECLARE loc INT;
       SELECT location_id into loc FROM PERSON where firstname = p_firstname and lastname = p_lastname;
       CASE loc
           WHEN 40 THEN
               SET ans = 'Southern Hemisphere';
           ELSE
               SET ans = 'Northern Hemisphere';
       END CASE;
       END;
   """
   

  we can now call the stored procedure as follows:

   sql.call '{call Hemisphere(?, ?, ?)}', ['Guillaume', 'Laforge', Sql.VARCHAR], { dwells ->
       println dwells
   }
   

  which will output 'Northern Hemisphere'.

  We can also access stored functions with scalar return values where the return value will be treated as an OUT parameter. Here are examples for various databases for creating such a procedure:

   // Tested with MySql 5.0.75
   sql.execute """
       create function FullName(p_firstname VARCHAR(40)) returns VARCHAR(80)
       begin
           declare ans VARCHAR(80);
           SELECT CONCAT(firstname, ' ', lastname) INTO ans FROM PERSON WHERE firstname = p_firstname;
           return ans;
       end
   """
  
   // Tested with MS SQLServer Express 2008
   sql.execute """
       create function FullName(@firstname VARCHAR(40)) returns VARCHAR(80)
       begin
           declare @ans VARCHAR(80)
           SET @ans = (SELECT firstname + ' ' + lastname FROM PERSON WHERE firstname = @firstname)
           return @ans
       end
   """
  
   // Tested with Oracle XE 10g
   sql.execute """
       create function FullName(p_firstname VARCHAR) return VARCHAR is
       ans VARCHAR(80);
       begin
           SELECT CONCAT(CONCAT(firstname, ' '), lastname) INTO ans FROM PERSON WHERE firstname = p_firstname;
           return ans;
       end;
   """
   

  and here is how you access the stored function for all databases:

   sql.call("{? = call FullName(?)}", [Sql.VARCHAR, 'Sam']) { name ->
       assert name == 'Sam Pullara'
   }
   

  Resource handling is performed automatically where appropriate.

  throws:

  SQLException if a database access error occurs

  Parameters:

  sql - the sql statement

  params - a list of parameters

  closure - called for each row with a GroovyResultSet

• public void call(GString gstring, @ClosureParams(value=SimpleType.class, options="java.lang.Object[]") Closure closure)

  Performs a stored procedure call with the given parameters, calling the closure once with all result objects.

  See call(String, List, Closure) for more details about creating a Hemisphere(IN first, IN last, OUT dwells) stored procedure. Once created, it can be called like this:

   def first = 'Scott'
   def last = 'Davis'
   sql.call "{call Hemisphere($first, $last, ${Sql.VARCHAR})}", { dwells ->
       println dwells
   }
   

  As another example, see call(String, List, Closure) for more details about creating a FullName(IN first) stored function. Once created, it can be called like this:

   def first = 'Sam'
   sql.call("{$Sql.VARCHAR = call FullName($first)}") { name ->
       assert name == 'Sam Pullara'
   }
   

  Resource handling is performed automatically where appropriate.

  throws:

  SQLException if a database access error occurs

  Parameters:

  gstring - a GString containing the SQL query with embedded params

  closure - called for each row with a GroovyResultSet

  See Also:

  call(String, List, Closure)

  expand(Object)

• public List<List<GroovyRowResult>> callWithAllRows(GString gstring, @ClosureParams(value=SimpleType.class, options="java.lang.Object[]") Closure closure)

  Performs a stored procedure call with the given parameters, calling the closure once with all result objects, and also returning a list of lists with the rows of the ResultSet(s).

  Use this when calling a stored procedure that utilizes both output parameters and returns multiple ResultSets.

  Once created, the stored procedure can be called like this:

   def first = 'Jeff'
   def last = 'Sheets'
   def rowsList = sql.callWithAllRows "{call Hemisphere2($first, $last, ${Sql.VARCHAR})}", { dwells ->
       println dwells
   }
   

  Resource handling is performed automatically where appropriate.

  throws:

  SQLException if a database access error occurs

  Parameters:

  gstring - a GString containing the SQL query with embedded params

  closure - called once with all out parameter results

  Returns:

  a list containing lists of GroovyRowResult objects

  See Also:

  callWithAllRows(String, List, Closure)

• public List<List<GroovyRowResult>> callWithAllRows(String sql, List<Object> params, @ClosureParams(value=SimpleType.class, options="java.lang.Object[]") Closure closure)

  Performs a stored procedure call with the given parameters, calling the closure once with all result objects, and also returning a list of lists with the rows of the ResultSet(s).

  Use this when calling a stored procedure that utilizes both output parameters and returns multiple ResultSets.

  Once created, the stored procedure can be called like this:

   def rowsList = sql.callWithAllRows '{call Hemisphere2(?, ?, ?)}', ['Guillaume', 'Laforge', Sql.VARCHAR], { dwells ->
       println dwells
   }
   

  Resource handling is performed automatically where appropriate.

  throws:

  SQLException if a database access error occurs

  Parameters:

  sql - the sql statement

  params - a list of parameters

  closure - called once with all out parameter results

  Returns:

  a list containing lists of GroovyRowResult objects

  See Also:

  callWithRows(GString, Closure)

• public List<GroovyRowResult> callWithRows(GString gstring, @ClosureParams(value=SimpleType.class, options="java.lang.Object[]") Closure closure)

  Performs a stored procedure call with the given parameters, calling the closure once with all result objects, and also returning the rows of the ResultSet.

  Use this when calling a stored procedure that utilizes both output parameters and returns a single ResultSet.

  Once created, the stored procedure can be called like this:

   def first = 'Jeff'
   def last = 'Sheets'
   def rows = sql.callWithRows "{call Hemisphere2($first, $last, ${Sql.VARCHAR})}", { dwells ->
       println dwells
   }
   

  Resource handling is performed automatically where appropriate.

  throws:

  SQLException if a database access error occurs

  Parameters:

  gstring - a GString containing the SQL query with embedded params

  closure - called once with all out parameter results

  Returns:

  a list of GroovyRowResult objects

  See Also:

  callWithRows(String, List, Closure)

• public List<GroovyRowResult> callWithRows(String sql, List<Object> params, @ClosureParams(value=SimpleType.class, options="java.lang.Object[]") Closure closure)

  Performs a stored procedure call with the given parameters, calling the closure once with all result objects, and also returning the rows of the ResultSet.

  Use this when calling a stored procedure that utilizes both output parameters and returns a single ResultSet.

  Once created, the stored procedure can be called like this:

   def rows = sql.callWithRows '{call Hemisphere2(?, ?, ?)}', ['Guillaume', 'Laforge', Sql.VARCHAR], { dwells ->
       println dwells
   }
   

  Resource handling is performed automatically where appropriate.

  throws:

  SQLException if a database access error occurs

  Parameters:

  sql - the sql statement

  params - a list of parameters

  closure - called once with all out parameter results

  Returns:

  a list of GroovyRowResult objects

  See Also:

  callWithRows(GString, Closure)

• protected List<List<GroovyRowResult>> callWithRows(String sql, List<Object> params, int processResultsSets, @ClosureParams(value=SimpleType.class, options="java.lang.Object[]") Closure closure)

  Base internal method for call(), callWithRows(), and callWithAllRows() style of methods.

  Performs a stored procedure call with the given parameters, calling the closure once with all result objects, and also returning the rows of the ResultSet(s) (if processResultSets is set to Sql.FIRST_RESULT_SET, Sql.ALL_RESULT_SETS)

  Main purpose of processResultSets param is to retain original call() method performance when this is set to Sql.NO_RESULT_SETS

  Resource handling is performed automatically where appropriate.

  throws:

  SQLException if a database access error occurs

  Parameters:

  sql - the sql statement

  params - a list of parameters

  processResultsSets - the result sets to process, either Sql.NO_RESULT_SETS, Sql.FIRST_RESULT_SET, or Sql.ALL_RESULT_SETS

  closure - called once with all out parameter results

  Returns:

  a list of GroovyRowResult objects

  See Also:

  callWithRows(String, List, Closure)

• public SqlWithParams checkForNamedParams(String sql, List<Object> params)

• @Override public void close()

  If this SQL object was created with a Connection then this method closes the connection. If this SQL object was created from a DataSource then this method only frees any cached objects (statements in particular).

• protected void closeResources(Connection connection, Statement statement, ResultSet results)

  An extension point allowing derived classes to change the behavior of resource closing.

  Parameters:

  connection - the connection to close

  statement - the statement to close

  results - the results to close

• protected void closeResources(Connection connection, Statement statement)

  An extension point allowing the behavior of resource closing to be overridden in derived classes.

  Parameters:

  connection - the connection to close

  statement - the statement to close

• protected void closeResources(Connection connection)

  An extension point allowing the behavior of resource closing to be overridden in derived classes.

  Parameters:

  connection - the connection to close

• public void commit()

  If this SQL object was created with a Connection then this method commits the connection. If this SQL object was created from a DataSource then this method does nothing.

  throws:

  SQLException if a database access error occurs

• protected void configure(Statement statement)

  Provides a hook for derived classes to be able to configure JDBC statements. Default behavior is to call a previously saved closure, if any, using the statement as a parameter.

  Parameters:

  statement - the statement to configure

• protected Connection createConnection()

  An extension point allowing derived classes to change the behavior of connection creation. The default behavior is to either use the supplied connection or obtain it from the supplied datasource.

  throws:

  java.sql.SQLException if a SQL error occurs

  Returns:

  the connection associated with this Sql

• protected AbstractQueryCommand createPreparedQueryCommand(String sql, List<Object> queryParams)

  Factory for the PreparedQueryCommand command pattern object allows subclass to supply implementations of the command class.

  Parameters:

  sql - statement to be executed, including optional parameter placeholders (?)

  queryParams - List of parameter values corresponding to parameter placeholders

  Returns:

  a command - invoke its execute() and closeResource() methods

  See Also:

  createQueryCommand(String)

• protected AbstractQueryCommand createQueryCommand(String sql)

  Factory for the QueryCommand command pattern object allows subclasses to supply implementations of the command class. The factory will be used in a pattern similar to:

   AbstractQueryCommand q = createQueryCommand("update TABLE set count = 0) where count is null");
   try {
       ResultSet rs = q.execute();
       return asList(rs);
   } finally {
       q.closeResources();
   }
   

  Parameters:

  sql - statement to be executed

  Returns:

  a command - invoke its execute() and closeResource() methods

• public DataSet dataSet(String table)

• public DataSet dataSet(Class<?> type)

• public void eachRow(String sql, @ClosureParams(value=SimpleType.class, options="groovy.sql.GroovyResultSet") Closure closure)

  Performs the given SQL query calling the given Closure with each row of the result set. The row will be a GroovyResultSet which is a ResultSet that supports accessing the fields using property style notation and ordinal index values.

  Example usages:

   sql.eachRow("select * from PERSON where firstname like 'S%'") { row ->
      println "$row.firstname ${row[2]}}"
   }
  
   sql.eachRow "call my_stored_proc_returning_resultset()", {
       println it.firstname
   }
   

  Resource handling is performed automatically where appropriate.

  throws:

  SQLException if a database access error occurs

  Parameters:

  sql - the sql statement

  closure - called for each row with a GroovyResultSet

• public void eachRow(String sql, int offset, int maxRows, @ClosureParams(value=SimpleType.class, options="groovy.sql.GroovyResultSet") Closure closure)

  Performs the given SQL query calling the given closure with each row of the result set starting at the provided offset, and including up to maxRows number of rows. The row will be a GroovyResultSet which is a ResultSet that supports accessing the fields using property style notation and ordinal index values.

  Note that the underlying implementation is based on either invoking ResultSet.absolute(), or if the ResultSet type is ResultSet.TYPE_FORWARD_ONLY, the ResultSet.next() method is invoked equivalently. The first row of a ResultSet is 1, so passing in an offset of 1 or less has no effect on the initial positioning within the result set.

  Note that different database and JDBC driver implementations may work differently with respect to this method. Specifically, one should expect that ResultSet.TYPE_FORWARD_ONLY may be less efficient than a "scrollable" type.

  Resource handling is performed automatically where appropriate.

  throws:

  SQLException if a database access error occurs

  Parameters:

  sql - the sql statement

  offset - the 1-based offset for the first row to be processed

  maxRows - the maximum number of rows to be processed

  closure - called for each row with a GroovyResultSet

• public void eachRow(String sql, @ClosureParams(value=SimpleType.class, options="java.sql.ResultSetMetaData") Closure metaClosure, @ClosureParams(value=SimpleType.class, options="groovy.sql.GroovyResultSet") Closure rowClosure)

  Performs the given SQL query calling the given rowClosure with each row of the result set. The row will be a GroovyResultSet which is a ResultSet that supports accessing the fields using property style notation and ordinal index values. In addition, the metaClosure will be called once passing in the ResultSetMetaData as argument.

  Example usage:

   def printColNames = { meta ->
       (1..meta.columnCount).each {
           print meta.getColumnLabel(it).padRight(20)
       }
       println()
   }
   def printRow = { row ->
       row.toRowResult().values().each{ print it.toString().padRight(20) }
       println()
   }
   sql.eachRow("select * from PERSON", printColNames, printRow)
   

  Resource handling is performed automatically where appropriate.

  throws:

  SQLException if a database access error occurs

  Parameters:

  sql - the sql statement

  metaClosure - called for meta data (only once after sql execution)

  rowClosure - called for each row with a GroovyResultSet

• public void eachRow(String sql, @ClosureParams(value=SimpleType.class, options="java.sql.ResultSetMetaData") Closure metaClosure, int offset, int maxRows, @ClosureParams(value=SimpleType.class, options="groovy.sql.GroovyResultSet") Closure rowClosure)

  Performs the given SQL query calling the given rowClosure with each row of the result set starting at the provided offset, and including up to maxRows number of rows. The row will be a GroovyResultSet which is a ResultSet that supports accessing the fields using property style notation and ordinal index values.

  In addition, the metaClosure will be called once passing in the ResultSetMetaData as argument.

  Note that the underlying implementation is based on either invoking ResultSet.absolute(), or if the ResultSet type is ResultSet.TYPE_FORWARD_ONLY, the ResultSet.next() method is invoked equivalently. The first row of a ResultSet is 1, so passing in an offset of 1 or less has no effect on the initial positioning within the result set.

  Note that different database and JDBC driver implementations may work differently with respect to this method. Specifically, one should expect that ResultSet.TYPE_FORWARD_ONLY may be less efficient than a "scrollable" type.

  Resource handling is performed automatically where appropriate.

  throws:

  SQLException if a database access error occurs

  Parameters:

  sql - the sql statement

  offset - the 1-based offset for the first row to be processed

  maxRows - the maximum number of rows to be processed

  metaClosure - called for meta data (only once after sql execution)

  rowClosure - called for each row with a GroovyResultSet

• public void eachRow(String sql, List<Object> params, @ClosureParams(value=SimpleType.class, options="java.sql.ResultSetMetaData") Closure metaClosure, int offset, int maxRows, @ClosureParams(value=SimpleType.class, options="groovy.sql.GroovyResultSet") Closure rowClosure)

  Performs the given SQL query calling the given rowClosure with each row of the result set starting at the provided offset, and including up to maxRows number of rows. The row will be a GroovyResultSet which is a ResultSet that supports accessing the fields using property style notation and ordinal index values.

  In addition, the metaClosure will be called once passing in the ResultSetMetaData as argument. The query may contain placeholder question marks which match the given list of parameters.

  Note that the underlying implementation is based on either invoking ResultSet.absolute(), or if the ResultSet type is ResultSet.TYPE_FORWARD_ONLY, the ResultSet.next() method is invoked equivalently. The first row of a ResultSet is 1, so passing in an offset of 1 or less has no effect on the initial positioning within the result set.

  Note that different database and JDBC driver implementations may work differently with respect to this method. Specifically, one should expect that ResultSet.TYPE_FORWARD_ONLY may be less efficient than a "scrollable" type.

  throws:

  SQLException if a database access error occurs

  Parameters:

  sql - the sql statement

  params - a list of parameters

  offset - the 1-based offset for the first row to be processed

  maxRows - the maximum number of rows to be processed

  metaClosure - called for meta data (only once after sql execution)

  rowClosure - called for each row with a GroovyResultSet

• public void eachRow(String sql, Map map, @ClosureParams(value=SimpleType.class, options="java.sql.ResultSetMetaData") Closure metaClosure, int offset, int maxRows, @ClosureParams(value=SimpleType.class, options="groovy.sql.GroovyResultSet") Closure rowClosure)

  A variant of eachRow(String, java.util.List, groovy.lang.Closure, int, int, groovy.lang.Closure) allowing the named parameters to be supplied in a map.

  throws:

  SQLException if a database access error occurs

  Parameters:

  sql - the sql statement

  map - a map containing the named parameters

  offset - the 1-based offset for the first row to be processed

  maxRows - the maximum number of rows to be processed

  metaClosure - called for meta data (only once after sql execution)

  rowClosure - called for each row with a GroovyResultSet

  Since:

  1.8.7

• public void eachRow(Map map, String sql, @ClosureParams(value=SimpleType.class, options="java.sql.ResultSetMetaData") Closure metaClosure, int offset, int maxRows, @ClosureParams(value=SimpleType.class, options="groovy.sql.GroovyResultSet") Closure rowClosure)

  A variant of eachRow(String, java.util.List, groovy.lang.Closure, int, int, groovy.lang.Closure) allowing the named parameters to be supplied as named arguments.

  throws:

  SQLException if a database access error occurs

  Parameters:

  map - a map containing the named parameters

  sql - the sql statement

  offset - the 1-based offset for the first row to be processed

  maxRows - the maximum number of rows to be processed

  metaClosure - called for meta data (only once after sql execution)

  rowClosure - called for each row with a GroovyResultSet

  Since:

  1.8.7

• public void eachRow(String sql, List<Object> params, @ClosureParams(value=SimpleType.class, options="java.sql.ResultSetMetaData") Closure metaClosure, @ClosureParams(value=SimpleType.class, options="groovy.sql.GroovyResultSet") Closure rowClosure)

  Performs the given SQL query calling the given Closure with each row of the result set. The row will be a GroovyResultSet which is a ResultSet that supports accessing the fields using property style notation and ordinal index values. In addition, the metaClosure will be called once passing in the ResultSetMetaData as argument. The query may contain placeholder question marks which match the given list of parameters.

  Example usage:

   def printColNames = { meta ->
       (1..meta.columnCount).each {
           print meta.getColumnLabel(it).padRight(20)
       }
       println()
   }
   def printRow = { row ->
       row.toRowResult().values().each{ print it.toString().padRight(20) }
       println()
   }
   sql.eachRow("select * from PERSON where lastname like ?", ['%a%'], printColNames, printRow)
   

  This method supports named and named ordinal parameters. See the class Javadoc for more details.

  Resource handling is performed automatically where appropriate.

  throws:

  SQLException if a database access error occurs

  Parameters:

  sql - the sql statement

  params - a list of parameters

  metaClosure - called for meta data (only once after sql execution)

  rowClosure - called for each row with a GroovyResultSet

• public void eachRow(String sql, Map params, @ClosureParams(value=SimpleType.class, options="java.sql.ResultSetMetaData") Closure metaClosure, @ClosureParams(value=SimpleType.class, options="groovy.sql.GroovyResultSet") Closure rowClosure)

  A variant of eachRow(String, java.util.List, groovy.lang.Closure, groovy.lang.Closure) useful when providing the named parameters as a map.

  throws:

  SQLException if a database access error occurs

  Parameters:

  sql - the sql statement

  params - a map of named parameters

  metaClosure - called for meta data (only once after sql execution)

  rowClosure - called for each row with a GroovyResultSet

  Since:

  1.8.7

• public void eachRow(Map params, String sql, @ClosureParams(value=SimpleType.class, options="java.sql.ResultSetMetaData") Closure metaClosure, @ClosureParams(value=SimpleType.class, options="groovy.sql.GroovyResultSet") Closure rowClosure)

  A variant of eachRow(String, java.util.List, groovy.lang.Closure, groovy.lang.Closure) useful when providing the named parameters as named arguments.

  throws:

  SQLException if a database access error occurs

  Parameters:

  params - a map of named parameters

  sql - the sql statement

  metaClosure - called for meta data (only once after sql execution)

  rowClosure - called for each row with a GroovyResultSet

  Since:

  1.8.7

• public void eachRow(String sql, List<Object> params, @ClosureParams(value=SimpleType.class, options="groovy.sql.GroovyResultSet") Closure closure)

  Performs the given SQL query calling the given Closure with each row of the result set. The row will be a GroovyResultSet which is a ResultSet that supports accessing the fields using property style notation and ordinal index values. The query may contain placeholder question marks which match the given list of parameters.

  Example usage:

   sql.eachRow("select * from PERSON where lastname like ?", ['%a%']) { row ->
       println "${row[1]} $row.lastname"
   }
   

  Resource handling is performed automatically where appropriate.

  throws:

  SQLException if a database access error occurs

  Parameters:

  sql - the sql statement

  params - a list of parameters

  closure - called for each row with a GroovyResultSet

• public void eachRow(String sql, Map params, @ClosureParams(value=SimpleType.class, options="groovy.sql.GroovyResultSet") Closure closure)

  A variant of eachRow(String, java.util.List, groovy.lang.Closure) useful when providing the named parameters as a map.

  throws:

  SQLException if a database access error occurs

  Parameters:

  sql - the sql statement

  params - a map of named parameters

  closure - called for each row with a GroovyResultSet

  Since:

  1.8.7

• public void eachRow(Map params, String sql, @ClosureParams(value=SimpleType.class, options="groovy.sql.GroovyResultSet") Closure closure)

  A variant of eachRow(String, java.util.List, groovy.lang.Closure) useful when providing the named parameters as named arguments.

  throws:

  SQLException if a database access error occurs

  Parameters:

  params - a map of named parameters

  sql - the sql statement

  closure - called for each row with a GroovyResultSet

  Since:

  1.8.7

• public void eachRow(String sql, List<Object> params, int offset, int maxRows, @ClosureParams(value=SimpleType.class, options="groovy.sql.GroovyResultSet") Closure closure)

  Performs the given SQL query calling the given closure with each row of the result set starting at the provided offset, and including up to maxRows number of rows. The row will be a GroovyResultSet which is a ResultSet that supports accessing the fields using property style notation and ordinal index values. The query may contain placeholder question marks which match the given list of parameters.

  Note that the underlying implementation is based on either invoking ResultSet.absolute(), or if the ResultSet type is ResultSet.TYPE_FORWARD_ONLY, the ResultSet.next() method is invoked equivalently. The first row of a ResultSet is 1, so passing in an offset of 1 or less has no effect on the initial positioning within the result set.

  Note that different database and JDBC driver implementations may work differently with respect to this method. Specifically, one should expect that ResultSet.TYPE_FORWARD_ONLY may be less efficient than a "scrollable" type.

  throws:

  SQLException if a database access error occurs

  Parameters:

  sql - the sql statement

  params - a list of parameters

  offset - the 1-based offset for the first row to be processed

  maxRows - the maximum number of rows to be processed

  closure - called for each row with a GroovyResultSet

• public void eachRow(String sql, Map params, int offset, int maxRows, @ClosureParams(value=SimpleType.class, options="groovy.sql.GroovyResultSet") Closure closure)

  A variant of eachRow(String, java.util.List, int, int, groovy.lang.Closure) useful when providing the named parameters as a map.

  throws:

  SQLException if a database access error occurs

  Parameters:

  sql - the sql statement

  params - a map of named parameters

  offset - the 1-based offset for the first row to be processed

  maxRows - the maximum number of rows to be processed

  closure - called for each row with a GroovyResultSet

  Since:

  1.8.7

• public void eachRow(Map params, String sql, int offset, int maxRows, @ClosureParams(value=SimpleType.class, options="groovy.sql.GroovyResultSet") Closure closure)

  A variant of eachRow(String, java.util.List, int, int, groovy.lang.Closure) useful when providing the named parameters as named arguments.

  throws:

  SQLException if a database access error occurs

  Parameters:

  params - a map of named parameters

  sql - the sql statement

  offset - the 1-based offset for the first row to be processed

  maxRows - the maximum number of rows to be processed

  closure - called for each row with a GroovyResultSet

  Since:

  1.8.7

• public void eachRow(GString gstring, @ClosureParams(value=SimpleType.class, options="java.sql.ResultSetMetaData") Closure metaClosure, @ClosureParams(value=SimpleType.class, options="groovy.sql.GroovyResultSet") Closure rowClosure)

  Performs the given SQL query calling the given Closure with each row of the result set. The row will be a GroovyResultSet which is a ResultSet that supports accessing the fields using property style notation and ordinal index values.

  In addition, the metaClosure will be called once passing in the ResultSetMetaData as argument. The query may contain GString expressions.

  Example usage:

   def location = 25
   def printColNames = { meta ->
       (1..meta.columnCount).each {
           print meta.getColumnLabel(it).padRight(20)
       }
       println()
   }
   def printRow = { row ->
       row.toRowResult().values().each{ print it.toString().padRight(20) }
       println()
   }
   sql.eachRow("select * from PERSON where location_id < $location", printColNames, printRow)
   

  Resource handling is performed automatically where appropriate.

  throws:

  SQLException if a database access error occurs

  Parameters:

  gstring - a GString containing the SQL query with embedded params

  metaClosure - called for meta data (only once after sql execution)

  rowClosure - called for each row with a GroovyResultSet

  See Also:

  expand(Object)

• public void eachRow(GString gstring, @ClosureParams(value=SimpleType.class, options="java.sql.ResultSetMetaData") Closure metaClosure, int offset, int maxRows, @ClosureParams(value=SimpleType.class, options="groovy.sql.GroovyResultSet") Closure rowClosure)

  Performs the given SQL query calling the given closure with each row of the result set starting at the provided offset, and including up to maxRows number of rows. The row will be a GroovyResultSet which is a ResultSet that supports accessing the fields using property style notation and ordinal index values. In addition, the metaClosure will be called once passing in the ResultSetMetaData as argument. The query may contain GString expressions.

  Note that the underlying implementation is based on either invoking ResultSet.absolute(), or if the ResultSet type is ResultSet.TYPE_FORWARD_ONLY, the ResultSet.next() method is invoked equivalently. The first row of a ResultSet is 1, so passing in an offset of 1 or less has no effect on the initial positioning within the result set.

  Note that different database and JDBC driver implementations may work differently with respect to this method. Specifically, one should expect that ResultSet.TYPE_FORWARD_ONLY may be less efficient than a "scrollable" type.

  throws:

  SQLException if a database access error occurs

  Parameters:

  gstring - a GString containing the SQL query with embedded params

  metaClosure - called for meta data (only once after sql execution)

  offset - the 1-based offset for the first row to be processed

  maxRows - the maximum number of rows to be processed

  rowClosure - called for each row with a GroovyResultSet

• public void eachRow(GString gstring, int offset, int maxRows, @ClosureParams(value=SimpleType.class, options="groovy.sql.GroovyResultSet") Closure closure)

  Performs the given SQL query calling the given closure with each row of the result set starting at the provided offset, and including up to maxRows number of rows. The row will be a GroovyResultSet which is a ResultSet that supports accessing the fields using property style notation and ordinal index values. The query may contain GString expressions.

  Note that the underlying implementation is based on either invoking ResultSet.absolute(), or if the ResultSet type is ResultSet.TYPE_FORWARD_ONLY, the ResultSet.next() method is invoked equivalently. The first row of a ResultSet is 1, so passing in an offset of 1 or less has no effect on the initial positioning within the result set.

  Note that different database and JDBC driver implementations may work differently with respect to this method. Specifically, one should expect that ResultSet.TYPE_FORWARD_ONLY may be less efficient than a "scrollable" type.

  throws:

  SQLException if a database access error occurs

  Parameters:

  gstring - a GString containing the SQL query with embedded params

  offset - the 1-based offset for the first row to be processed

  maxRows - the maximum number of rows to be processed

  closure - called for each row with a GroovyResultSet

• public void eachRow(GString gstring, @ClosureParams(value=SimpleType.class, options="groovy.sql.GroovyResultSet") Closure closure)

  Performs the given SQL query calling the given Closure with each row of the result set. The row will be a GroovyResultSet which is a ResultSet that supports accessing the fields using property style notation and ordinal index values. The query may contain GString expressions.

  Example usage:

   def location = 25
   sql.eachRow("select * from PERSON where location_id < $location") { row ->
       println row.firstname
   }
   

  Resource handling is performed automatically where appropriate.

  throws:

  SQLException if a database access error occurs

  Parameters:

  gstring - a GString containing the SQL query with embedded params

  closure - called for each row with a GroovyResultSet

  See Also:

  expand(Object)

• public boolean execute(String sql)

  Executes the given piece of SQL. Also saves the updateCount, if any, for subsequent examination.

  Example usages:

   sql.execute "DROP TABLE IF EXISTS person"
  
   sql.execute """
       CREATE TABLE person (
           id INTEGER NOT NULL,
           firstname VARCHAR(100),
           lastname VARCHAR(100),
           location_id INTEGER
       )
   """
  
   sql.execute """
       INSERT INTO person (id, firstname, lastname, location_id) VALUES (4, 'Paul', 'King', 40)
   """
   assert sql.updateCount == 1
   

  Resource handling is performed automatically where appropriate.

  throws:

  SQLException if a database access error occurs

  Parameters:

  sql - the SQL to execute

  Returns:

  true if the first result is a ResultSet object; false if it is an update count or there are no results

• public void execute(String sql, @ClosureParams(value=FromString.class, options={"boolean,java.util.List", "boolean,int"}) Closure processResults)

  Executes the given piece of SQL. Also calls the provided processResults Closure to process any ResultSet or UpdateCount results that executing the SQL might produce.

  Example usages:

   boolean first = true
   sql.execute "{call FindAllByFirst('J')}", { isResultSet, result ->
     if (first) {
       first = false
       assert !isResultSet && result == 0
     } else {
       assert isResultSet && result == [[ID:1, FIRSTNAME:'James', LASTNAME:'Strachan'], [ID:4, FIRSTNAME:'Jean', LASTNAME:'Gabin']]
     }
   }
   

  Resource handling is performed automatically where appropriate.

  throws:

  SQLException if a database access error occurs

  Parameters:

  sql - the SQL to execute

  processResults - a Closure which will be passed two parameters: either true plus a list of GroovyRowResult values derived from statement.getResultSet() or false plus the update count from statement.getUpdateCount(). The closure will be called for each result produced from executing the SQL.

  Since:

  2.3.2

• public boolean execute(String sql, List<Object> params)

  Executes the given piece of SQL with parameters. Also saves the updateCount, if any, for subsequent examination.

  Example usage:

   sql.execute """
       insert into PERSON (id, firstname, lastname, location_id) values (?, ?, ?, ?)
   """, [1, "Guillaume", "Laforge", 10]
   assert sql.updateCount == 1
   

  This method supports named and named ordinal parameters. See the class Javadoc for more details.

  Resource handling is performed automatically where appropriate.

  throws:

  SQLException if a database access error occurs

  Parameters:

  sql - the SQL statement

  params - a list of parameters

  Returns:

  true if the first result is a ResultSet object; false if it is an update count or there are no results

• public void execute(String sql, List<Object> params, @ClosureParams(value=FromString.class, options={"boolean,java.util.List", "boolean,int"}) Closure processResults)

  Executes the given piece of SQL with parameters. Also calls the provided processResults Closure to process any ResultSet or UpdateCount results that executing the SQL might produce.

  This method supports named and named ordinal parameters. See the class Javadoc for more details.

  Resource handling is performed automatically where appropriate.

  throws:

  SQLException if a database access error occurs

  Parameters:

  sql - the SQL statement

  params - a list of parameters

  processResults - a Closure which will be passed two parameters: either true plus a list of GroovyRowResult values derived from statement.getResultSet() or false plus the update count from statement.getUpdateCount(). The closure will be called for each result produced from executing the SQL.

  See Also:

  execute(String, Closure)

  Since:

  2.3.2

• public boolean execute(Map params, String sql)

  A variant of execute(String, java.util.List) useful when providing the named parameters as named arguments.

  throws:

  SQLException if a database access error occurs

  Parameters:

  params - a map containing the named parameters

  sql - the SQL statement

  Returns:

  true if the first result is a ResultSet object; false if it is an update count or there are no results

  Since:

  1.8.7

• public void execute(Map params, String sql, @ClosureParams(value=FromString.class, options={"boolean,java.util.List", "boolean,int"}) Closure processResults)

  A variant of execute(String, java.util.List, Closure) useful when providing the named parameters as named arguments.

  throws:

  SQLException if a database access error occurs

  Parameters:

  params - a map containing the named parameters

  sql - the SQL statement

  processResults - a Closure which will be passed two parameters: either true plus a list of GroovyRowResult values derived from statement.getResultSet() or false plus the update count from statement.getUpdateCount(). The closure will be called for each result produced from executing the SQL.

  Since:

  2.3.2

• public boolean execute(String sql, Object[] params)

  Executes the given piece of SQL with parameters.

  An Object array variant of execute(String, List).

  This method supports named and named ordinal parameters by supplying such parameters in the params array. See the class Javadoc for more details.

  throws:

  SQLException if a database access error occurs

  Parameters:

  sql - the SQL statement

  params - an array of parameters

  Returns:

  true if the first result is a ResultSet object; false if it is an update count or there are no results

• public void execute(String sql, Object[] params, @ClosureParams(value=FromString.class, options={"boolean,java.util.List", "boolean,int"}) Closure processResults)

  Executes the given piece of SQL with parameters.

  An Object array variant of execute(String, List, Closure).

  This method supports named and named ordinal parameters by supplying such parameters in the params array. See the class Javadoc for more details.

  throws:

  SQLException if a database access error occurs

  Parameters:

  sql - the SQL statement

  params - an array of parameters

  processResults - a Closure which will be passed two parameters: either true plus a list of GroovyRowResult values derived from statement.getResultSet() or false plus the update count from statement.getUpdateCount(). The closure will be called for each result produced from executing the SQL.

  See Also:

  execute(String, List, Closure)

  Since:

  2.3.2

• public boolean execute(GString gstring)

  Executes the given SQL with embedded expressions inside. Also saves the updateCount, if any, for subsequent examination.

  Example usage:

   def scott = [firstname: "Scott", lastname: "Davis", id: 5, location_id: 50]
   sql.execute """
       insert into PERSON (id, firstname, lastname, location_id) values ($scott.id, $scott.firstname, $scott.lastname, $scott.location_id)
   """
   assert sql.updateCount == 1
   

  Resource handling is performed automatically where appropriate.

  throws:

  SQLException if a database access error occurs

  Parameters:

  gstring - a GString containing the SQL query with embedded params

  Returns:

  true if the first result is a ResultSet object; false if it is an update count or there are no results

  See Also:

  expand(Object)

• public void execute(GString gstring, @ClosureParams(value=FromString.class, options={"boolean,java.util.List", "boolean,int"}) Closure processResults)

  Executes the given SQL with embedded expressions inside. Also calls the provided processResults Closure to process any ResultSet or UpdateCount results that executing the SQL might produce. Resource handling is performed automatically where appropriate.

  throws:

  SQLException if a database access error occurs

  Parameters:

  gstring - a GString containing the SQL query with embedded params

  processResults - a Closure which will be passed two parameters: either true plus a list of GroovyRowResult values derived from statement.getResultSet() or false plus the update count from statement.getUpdateCount(). The closure will be called for each result produced from executing the SQL.

  See Also:

  expand(Object)

  execute(String, List, Closure)

  Since:

  2.3.2

• public List<List<Object>> executeInsert(String sql)

  Executes the given SQL statement (typically an INSERT statement). Use this variant when you want to receive the values of any auto-generated columns, such as an autoincrement ID field. See executeInsert(GString) for more details.

  Resource handling is performed automatically where appropriate.

  throws:

  SQLException if a database access error occurs

  Parameters:

  sql - The SQL statement to execute

  Returns:

  A list of the auto-generated column values for each inserted row (typically auto-generated keys)

• public List<List<Object>> executeInsert(String sql, List<Object> params)

  Executes the given SQL statement (typically an INSERT statement). Use this variant when you want to receive the values of any auto-generated columns, such as an autoincrement ID field. The query may contain placeholder question marks which match the given list of parameters. See executeInsert(GString) for more details.

  This method supports named and named ordinal parameters. See the class Javadoc for more details.

  Resource handling is performed automatically where appropriate.

  throws:

  SQLException if a database access error occurs

  Parameters:

  sql - The SQL statement to execute

  params - The parameter values that will be substituted into the SQL statement's parameter slots

  Returns:

  A list of the auto-generated column values for each inserted row (typically auto-generated keys)

• public List<GroovyRowResult> executeInsert(String sql, List<Object> params, List<String> keyColumnNames)

  Executes the given SQL statement (typically an INSERT statement). Use this variant when you want to receive the values of any auto-generated columns, such as an autoincrement ID field (or fields) and you know the column name(s) of the ID field(s). The query may contain placeholder question marks which match the given list of parameters. See executeInsert(GString) for more details.

  This method supports named and named ordinal parameters. See the class Javadoc for more details.

  Resource handling is performed automatically where appropriate.

  throws:

  SQLException if a database access error occurs

  Parameters:

  sql - The SQL statement to execute

  params - The parameter values that will be substituted into the SQL statement's parameter slots

  keyColumnNames - a list of column names indicating the columns that should be returned from the inserted row or rows (some drivers may be case sensitive, e.g. may require uppercase names)

  Returns:

  A list of the auto-generated row results for each inserted row (typically auto-generated keys)

  See Also:

  Connection.prepareStatement

  Since:

  2.3.2

• public List<List<Object>> executeInsert(Map params, String sql)

  A variant of executeInsert(String, java.util.List) useful when providing the named parameters as named arguments.

  throws:

  SQLException if a database access error occurs

  Parameters:

  params - a map containing the named parameters

  sql - The SQL statement to execute

  Returns:

  A list of the auto-generated column values for each inserted row (typically auto-generated keys)

  Since:

  1.8.7

• public List<GroovyRowResult> executeInsert(Map params, String sql, List<String> keyColumnNames)

  A variant of executeInsert(String, List, List) useful when providing the named parameters as named arguments. This variant allows you to receive the values of any auto-generated columns, such as an autoincrement ID field (or fields) when you know the column name(s) of the ID field(s).

  throws:

  SQLException if a database access error occurs

  Parameters:

  params - a map containing the named parameters

  sql - The SQL statement to execute

  keyColumnNames - a list of column names indicating the columns that should be returned from the inserted row or rows (some drivers may be case sensitive, e.g. may require uppercase names)

  Returns:

  A list of the auto-generated row results for each inserted row (typically auto-generated keys)

  See Also:

  Connection.prepareStatement

  Since:

  2.3.2

• public List<List<Object>> executeInsert(String sql, Object[] params)

  Executes the given SQL statement (typically an INSERT statement).

  An Object array variant of executeInsert(String, List).

  This method supports named and named ordinal parameters by supplying such parameters in the params array. See the class Javadoc for more details.

  throws:

  SQLException if a database access error occurs

  Parameters:

  sql - The SQL statement to execute

  params - The parameter values that will be substituted into the SQL statement's parameter slots

  Returns:

  A list of the auto-generated column values for each inserted row (typically auto-generated keys)

• public List<GroovyRowResult> executeInsert(String sql, String[] keyColumnNames)

  Executes the given SQL statement (typically an INSERT statement). This variant allows you to receive the values of any auto-generated columns, such as an autoincrement ID field (or fields) when you know the column name(s) of the ID field(s).

  This method supports named and named ordinal parameters by supplying such parameters in the params array. See the class Javadoc for more details.

  throws:

  SQLException if a database access error occurs

  Parameters:

  sql - The SQL statement to execute

  keyColumnNames - an array of column names indicating the columns that should be returned from the inserted row or rows (some drivers may be case sensitive, e.g. may require uppercase names)

  Returns:

  A list of the auto-generated row results for each inserted row (typically auto-generated keys)

  Since:

  2.3.2

• public List<GroovyRowResult> executeInsert(String sql, String[] keyColumnNames, Object[] params)

  Executes the given SQL statement (typically an INSERT statement). This variant allows you to receive the values of any auto-generated columns, such as an autoincrement ID field (or fields) when you know the column name(s) of the ID field(s).

  An array variant of executeInsert(String, List, List).

  This method supports named and named ordinal parameters by supplying such parameters in the params array. See the class Javadoc for more details.

  throws:

  SQLException if a database access error occurs

  Parameters:

  sql - The SQL statement to execute

  keyColumnNames - an array of column names indicating the columns that should be returned from the inserted row or rows (some drivers may be case sensitive, e.g. may require uppercase names)

  params - The parameter values that will be substituted into the SQL statement's parameter slots

  Returns:

  A list of the auto-generated row results for each inserted row (typically auto-generated keys)

  Since:

  2.3.2

• public List<List<Object>> executeInsert(GString gstring)

  Executes the given SQL statement (typically an INSERT statement). Use this variant when you want to receive the values of any auto-generated columns, such as an autoincrement ID field. The query may contain GString expressions.

  Generated key values can be accessed using array notation. For example, to return the second auto-generated column value of the third row, use keys[3][1]. The method is designed to be used with SQL INSERT statements, but is not limited to them.

  The standard use for this method is when a table has an autoincrement ID column and you want to know what the ID is for a newly inserted row. In this example, we insert a single row into a table in which the first column contains the autoincrement ID:

   def sql = Sql.newInstance("jdbc:mysql://localhost:3306/groovy",
                             "user",
                             "password",
                             "com.mysql.jdbc.Driver")
  
   def keys = sql.executeInsert("insert into test_table (INT_DATA, STRING_DATA) "
                         + "VALUES (1, 'Key Largo')")
  
   def id = keys[0][0]
  
   // 'id' now contains the value of the new row's ID column.
   // It can be used to update an object representation's
   // id attribute for example.
   ...
   

  Resource handling is performed automatically where appropriate.

  throws:

  SQLException if a database access error occurs

  Parameters:

  gstring - a GString containing the SQL query with embedded params

  Returns:

  A list of the auto-generated column values for each inserted row (typically auto-generated keys)

  See Also:

  expand(Object)

• public List<GroovyRowResult> executeInsert(GString gstring, List<String> keyColumnNames)

  Executes the given SQL statement (typically an INSERT statement). Use this variant when you want to receive the values of any auto-generated columns, such as an autoincrement ID field (or fields) and you know the column name(s) of the ID field(s).

  Resource handling is performed automatically where appropriate.

  throws:

  SQLException if a database access error occurs

  Parameters:

  gstring - a GString containing the SQL query with embedded params

  keyColumnNames - a list of column names indicating the columns that should be returned from the inserted row or rows (some drivers may be case sensitive, e.g. may require uppercase names)

  Returns:

  A list of the auto-generated row results for each inserted row (typically auto-generated keys)

  See Also:

  Connection.prepareStatement

  expand(Object)

  Since:

  2.3.2

• protected final ResultSet executePreparedQuery(String sql, List<Object> params)

  Useful helper method which handles resource management when executing a prepared query which returns a result set. Derived classes of Sql can override "createPreparedQueryCommand" and then call this method to access the ResultSet returned from the provided query.

  throws:

  SQLException if a database error occurs

  Parameters:

  sql - query to execute

  params - parameters matching question mark placeholders in the query

  Returns:

  the resulting ResultSet

• protected final ResultSet executeQuery(String sql)

  Useful helper method which handles resource management when executing a query which returns a result set. Derived classes of Sql can override "createQueryCommand" and then call this method to access the ResultSet returned from the provided query or alternatively can use the higher-level method of Sql which return result sets which are funnelled through this method, e.g. eachRow, query.

  throws:

  SQLException if a database error occurs

  Parameters:

  sql - query to execute

  Returns:

  the resulting ResultSet

• public int executeUpdate(String sql)

  Executes the given SQL update.

  Resource handling is performed automatically where appropriate.

  throws:

  SQLException if a database access error occurs

  Parameters:

  sql - the SQL to execute

  Returns:

  the number of rows updated or 0 for SQL statements that return nothing

• public int executeUpdate(String sql, List<Object> params)

  Executes the given SQL update with parameters.

  This method supports named and named ordinal parameters. See the class Javadoc for more details.

  Resource handling is performed automatically where appropriate.

  throws:

  SQLException if a database access error occurs

  Parameters:

  sql - the SQL statement

  params - a list of parameters

  Returns:

  the number of rows updated or 0 for SQL statements that return nothing

• public int executeUpdate(Map params, String sql)

  A variant of executeUpdate(String, java.util.List) useful when providing the named parameters as named arguments.

  throws:

  SQLException if a database access error occurs

  Parameters:

  params - a map containing the named parameters

  sql - the SQL statement

  Returns:

  the number of rows updated or 0 for SQL statements that return nothing

  Since:

  1.8.7

• public int executeUpdate(String sql, Object[] params)

  Executes the given SQL update with parameters.

  An Object array variant of executeUpdate(String, List).

  throws:

  SQLException if a database access error occurs

  Parameters:

  sql - the SQL statement

  params - an array of parameters

  Returns:

  the number of rows updated or 0 for SQL statements that return nothing

• public int executeUpdate(GString gstring)

  Executes the given SQL update with embedded expressions inside.

  Resource handling is performed automatically where appropriate.

  throws:

  SQLException if a database access error occurs

  Parameters:

  gstring - a GString containing the SQL query with embedded params

  Returns:

  the number of rows updated or 0 for SQL statements that return nothing

  See Also:

  expand(Object)

• public static ExpandedVariable expand(Object object)

  When using GString SQL queries, allows a variable to be expanded in the Sql string rather than representing an sql parameter.

  Example usage:

   def fieldName = 'firstname'
   def fieldOp = Sql.expand('like')
   def fieldVal = '%a%'
   sql.query "select * from PERSON where ${Sql.expand(fieldName)} $fieldOp ${fieldVal}", { ResultSet rs ->
       while (rs.next()) println rs.getString('firstname')
   }
   // query will be 'select * from PERSON where firstname like ?'
   // params will be [fieldVal]
   

  Parameters:

  object - the object of interest

  Returns:

  the expanded variable

  See Also:

  expand(Object)

• protected int findWhereKeyword(String sql)

  Hook to allow derived classes to override where clause sniffing. Default behavior is to find the first 'where' keyword in the sql doing simple avoidance of the word 'where' within quotes.

  Parameters:

  sql - the SQL statement

  Returns:

  the index of the found keyword or -1 if not found

• public GroovyRowResult firstRow(String sql)

  Performs the given SQL query and return the first row of the result set.

  Example usage:

   def ans = sql.firstRow("select * from PERSON where firstname like 'S%'")
   println ans.firstname
   

  Resource handling is performed automatically where appropriate.

  throws:

  SQLException if a database access error occurs

  Parameters:

  sql - the SQL statement

  Returns:

  a GroovyRowResult object or null if no row is found

• public GroovyRowResult firstRow(GString gstring)

  Performs the given SQL query and return the first row of the result set. The query may contain GString expressions.

  Example usage:

   def location = 25
   def ans = sql.firstRow("select * from PERSON where location_id < $location")
   println ans.firstname
   

  Resource handling is performed automatically where appropriate.

  throws:

  SQLException if a database access error occurs

  Parameters:

  gstring - a GString containing the SQL query with embedded params

  Returns:

  a GroovyRowResult object or null if no row is found

  See Also:

  expand(Object)

• public GroovyRowResult firstRow(String sql, List<Object> params)

  Performs the given SQL query and return the first row of the result set. The query may contain placeholder question marks which match the given list of parameters.

  Example usages:

   def ans = sql.firstRow("select * from PERSON where lastname like ?", ['%a%'])
   println ans.firstname
   

  If your database returns scalar functions as ResultSets, you can also use firstRow to gain access to stored procedure results, e.g. using hsqldb 1.9 RC4:

   sql.execute """
       create function FullName(p_firstname VARCHAR(40)) returns VARCHAR(80)
       BEGIN atomic
       DECLARE ans VARCHAR(80);
       SET ans = (SELECT firstname || ' ' || lastname FROM PERSON WHERE firstname = p_firstname);
       RETURN ans;
       END
   """
  
   assert sql.firstRow("{call FullName(?)}", ['Sam'])[0] == 'Sam Pullara'
   

  This method supports named and named ordinal parameters by supplying such parameters in the params list. See the class Javadoc for more details.

  Resource handling is performed automatically where appropriate.

  throws:

  SQLException if a database access error occurs

  Parameters:

  sql - the SQL statement

  params - a list of parameters

  Returns:

  a GroovyRowResult object or null if no row is found

• public GroovyRowResult firstRow(Map params, String sql)

  A variant of firstRow(String, java.util.List) useful when providing the named parameters as named arguments.

  throws:

  SQLException if a database access error occurs

  Parameters:

  params - a map containing the named parameters

  sql - the SQL statement

  Returns:

  a GroovyRowResult object or null if no row is found

  Since:

  1.8.7

• public GroovyRowResult firstRow(String sql, Object[] params)

  Performs the given SQL query and return the first row of the result set.

  An Object array variant of firstRow(String, List).

  This method supports named and named ordinal parameters by supplying such parameters in the params array. See the class Javadoc for more details.

  throws:

  SQLException if a database access error occurs

  Parameters:

  sql - the SQL statement

  params - an array of parameters

  Returns:

  a GroovyRowResult object or null if no row is found

• public Connection getConnection()

  If this instance was created with a single Connection then the connection is returned. Otherwise if this instance was created with a DataSource then this method returns null

  Returns:

  the connection wired into this object, or null if this object uses a DataSource

• public DataSource getDataSource()

• @Override public Object getObject()

• protected List<Object> getParameters(GString gstring)

  Hook to allow derived classes to override behavior associated with extracting params from a GString.

  Parameters:

  gstring - a GString containing the SQL query with embedded params

  Returns:

  extracts the parameters from the expression as a List

  See Also:

  expand(Object)

• public int getResultSetConcurrency()

  Gets the resultSetConcurrency for statements created using the connection.

  Returns:

  the current resultSetConcurrency value

  Since:

  1.5.2

• public int getResultSetHoldability()

  Gets the resultSetHoldability for statements created using the connection.

  Returns:

  the current resultSetHoldability value or -1 if not set

  Since:

  1.5.2

• public int getResultSetType()

  Gets the resultSetType for statements created using the connection.

  Returns:

  the current resultSetType value

  Since:

  1.5.2

• @Override public int getType()

• @Override public int getType()

• @Override public int getType()

• @Override public int getType()

• @Override public int getType()

• @Override public int getType()

• @Override public int getType()

• @Override public int getType()

• @Override public int getType()

• @Override public int getType()

• @Override public int getType()

• @Override public int getType()

• @Override public int getType()

• @Override public int getType()

• @Override public int getType()

• @Override public int getType()

• @Override public int getType()

• @Override public int getType()

• @Override public int getType()

• @Override public int getType()

• @Override public int getType()

• @Override public int getType()

• @Override public int getType()

• @Override public int getType()

• @Override public int getType()

• @Override public int getType()

• @Override public int getType()

• @Override public int getType()

• @Override public int getType()

• @Override public int getType()

• @Override public int getType()

• @Override public int getType()

• @Override public int getType()

• @Override public int getType()

• public int getUpdateCount()

  Returns:

  Returns the updateCount.

• public List<Object> getUpdatedParams(List<Object> params, List<Tuple> indexPropList)

• @Override public Object getValue()

• @Override public Object getValue()

• public static InParameter in(int type, Object value)

  Create a new InParameter

  Parameters:

  type - the JDBC data type

  value - the object value

  Returns:

  an InParameter

• public static InOutParameter inout(InParameter in)

  Create an inout parameter using this in parameter.

  Parameters:

  in - the InParameter of interest

  Returns:

  the resulting InOutParameter

• public boolean isCacheNamedQueries()

  Returns:

  boolean true if caching is enabled (the default is true)

• public boolean isCacheStatements()

  Returns:

  boolean true if cache is enabled (default is false)

• public boolean isEnableNamedQueries()

  Returns:

  boolean true if named query processing is enabled (the default is true)

• public boolean isWithinBatch()

  Returns true if the current Sql object is currently executing a withBatch method call.

  Returns:

  true if a withBatch call is currently being executed.

• public static void loadDriver(String driverClassName)

  Attempts to load the JDBC driver on the thread, current or system class loaders

  throws:

  ClassNotFoundException if the class cannot be found or loaded

  Parameters:

  driverClassName - the fully qualified class name of the driver class

• public static Sql newInstance(String url)

  Creates a new Sql instance given a JDBC connection URL.

  throws:

  SQLException if a database access error occurs

  Parameters:

  url - a database url of the form jdbc:subprotocol:subname

  Returns:

  a new Sql instance with a connection

• public static Sql newInstance(String url, Properties properties)

  Creates a new Sql instance given a JDBC connection URL and some properties.

  throws:

  SQLException if a database access error occurs

  Parameters:

  url - a database url of the form jdbc:subprotocol:subname

  properties - a list of arbitrary string tag/value pairs as connection arguments; normally at least a "user" and "password" property should be included

  Returns:

  a new Sql instance with a connection

• public static Sql newInstance(String url, Properties properties, String driverClassName)

  Creates a new Sql instance given a JDBC connection URL, some properties and a driver class name.

  throws:

  SQLException if a database access error occurs

  throws:

  ClassNotFoundException if the driver class cannot be found or loaded

  Parameters:

  url - a database url of the form jdbc:subprotocol:subname

  properties - a list of arbitrary string tag/value pairs as connection arguments; normally at least a "user" and "password" property should be included

  driverClassName - the fully qualified class name of the driver class

  Returns:

  a new Sql instance with a connection

• public static Sql newInstance(String url, String user, String password)

  Creates a new Sql instance given a JDBC connection URL, a username and a password.

  throws:

  SQLException if a database access error occurs

  Parameters:

  url - a database url of the form jdbc:subprotocol:subname

  user - the database user on whose behalf the connection is being made

  password - the user's password

  Returns:

  a new Sql instance with a connection

• public static Sql newInstance(String url, String user, String password, String driverClassName)

  Creates a new Sql instance given a JDBC connection URL, a username, a password and a driver class name.

  throws:

  SQLException if a database access error occurs

  throws:

  ClassNotFoundException if the driver class cannot be found or loaded

  Parameters:

  url - a database url of the form jdbc:subprotocol:subname

  user - the database user on whose behalf the connection is being made

  password - the user's password

  driverClassName - the fully qualified class name of the driver class

  Returns:

  a new Sql instance with a connection

• public static Sql newInstance(String url, String driverClassName)

  Creates a new Sql instance given a JDBC connection URL and a driver class name.

  throws:

  SQLException if a database access error occurs

  throws:

  ClassNotFoundException if the driver class cannot be found or loaded

  Parameters:

  url - a database url of the form jdbc:subprotocol:subname

  driverClassName - the fully qualified class name of the driver class

  Returns:

  a new Sql instance with a connection

• public static Sql newInstance(@NamedParam(value = "url", type = String.class, required = true) @NamedParam(value = "properties", type = Properties.class) @NamedParam(value = "driverClassName", type = String.class) @NamedParam(value = "driver", type = String.class) @NamedParam(value = "user", type = String.class) @NamedParam(value = "password", type = String.class) @NamedParam(value = "cacheNamedQueries", type = Boolean.class) @NamedParam(value = "cacheStatements", type = Boolean.class) @NamedParam(value = "enableNamedQueries", type = Boolean.class) @NamedParam(value = "resultSetConcurrency", type = Integer.class) @NamedParam(value = "resultSetHoldability", type = Integer.class) @NamedParam(value = "resultSetType", type = Integer.class) Map<String, Object> args)

  Creates a new Sql instance given parameters in a Map. Recognized keys for the Map include:

   driverClassName the fully qualified class name of the driver class
   driver          a synonym for driverClassName
   url             a database url of the form: jdbc:subprotocol:subname
   user            the database user on whose behalf the connection is being made
   password        the user's password
   properties      a list of arbitrary string tag/value pairs as connection arguments;
                   normally at least a "user" and "password" property should be included
   other           any of the public setter methods of this class may be used with property notation
                   e.g. cacheStatements: true, resultSetConcurrency: ResultSet.CONCUR_READ_ONLY
   

  Of these, 'url' is required. Others may be needed depending on your database.
  If 'properties' is supplied, neither 'user' nor 'password' should be supplied.
  If one of 'user' or 'password' is supplied, both should be supplied.

  Example usage:

   import groovy.sql.Sql
   import static java.sql.ResultSet.*
  
   def sql = Sql.newInstance(
       url:'jdbc:hsqldb:mem:testDB',
       user:'sa',
       password:'',
       driver:'org.hsqldb.jdbc.JDBCDriver',
       cacheStatements: true,
       resultSetConcurrency: CONCUR_READ_ONLY
   )
   

  throws:

  SQLException if a database access error occurs

  throws:

  ClassNotFoundException if the driver class cannot be found or loaded

  Parameters:

  args - a Map contain further arguments

  Returns:

  a new Sql instance with a connection

• protected String nullify(String sql)

  Hook to allow derived classes to override null handling. Default behavior is to replace ?'"? references with NULLish

  Parameters:

  sql - the SQL statement

  Returns:

  the modified SQL String

• public static OutParameter out(int type)

  Create a new OutParameter

  Parameters:

  type - the JDBC data type.

  Returns:

  an OutParameter

• @Deprecated public SqlWithParams preCheckForNamedParams(String sql)

  deprecated:

  Use buildSqlWithIndexedProps(String) instead

• public void query(String sql, @ClosureParams(value=SimpleType.class, options="java.sql.ResultSet") Closure closure)

  Performs the given SQL query, which should return a single ResultSet object. The given closure is called with the ResultSet as its argument.

  Example usages:

   sql.query("select * from PERSON where firstname like 'S%'") { ResultSet rs ->
       while (rs.next()) println rs.getString('firstname') + ' ' + rs.getString(3)
   }
  
   sql.query("call get_people_places()") { ResultSet rs ->
       while (rs.next()) println rs.toRowResult().firstname
   }
   

  All resources including the ResultSet are closed automatically after the closure is called.

  throws:

  SQLException if a database access error occurs

  Parameters:

  sql - the sql statement

  closure - called for each row with a ResultSet

• public void query(String sql, List<Object> params, @ClosureParams(value=SimpleType.class, options="java.sql.ResultSet") Closure closure)

  Performs the given SQL query, which should return a single ResultSet object. The given closure is called with the ResultSet as its argument. The query may contain placeholder question marks which match the given list of parameters.

  Example usage:

   sql.query('select * from PERSON where lastname like ?', ['%a%']) { ResultSet rs ->
       while (rs.next()) println rs.getString('lastname')
   }
   

  This method supports named and named ordinal parameters. See the class Javadoc for more details.

  All resources including the ResultSet are closed automatically after the closure is called.

  throws:

  SQLException if a database access error occurs

  Parameters:

  sql - the sql statement

  params - a list of parameters

  closure - called for each row with a ResultSet

• public void query(String sql, Map map, @ClosureParams(value=SimpleType.class, options="java.sql.ResultSet") Closure closure)

  A variant of query(String, java.util.List, groovy.lang.Closure) useful when providing the named parameters as a map.

  throws:

  SQLException if a database access error occurs

  Parameters:

  sql - the sql statement

  map - a map containing the named parameters

  closure - called for each row with a ResultSet

  Since:

  1.8.7

• public void query(Map map, String sql, @ClosureParams(value=SimpleType.class, options="java.sql.ResultSet") Closure closure)

  A variant of query(String, java.util.List, groovy.lang.Closure) useful when providing the named parameters as named arguments.

  throws:

  SQLException if a database access error occurs

  Parameters:

  map - a map containing the named parameters

  sql - the sql statement

  closure - called for each row with a ResultSet

  Since:

  1.8.7

• public void query(GString gstring, @ClosureParams(value=SimpleType.class, options="java.sql.ResultSet") Closure closure)

  Performs the given SQL query, which should return a single ResultSet object. The given closure is called with the ResultSet as its argument. The query may contain GString expressions.

  Example usage:

   def location = 25
   sql.query "select * from PERSON where location_id < $location", { ResultSet rs ->
       while (rs.next()) println rs.getString('firstname')
   }
   

  All resources including the ResultSet are closed automatically after the closure is called.

  throws:

  SQLException if a database access error occurs

  Parameters:

  gstring - a GString containing the SQL query with embedded params

  closure - called for each row with a ResultSet

  See Also:

  expand(Object)

• public static ResultSetOutParameter resultSet(int type)

  Create a new ResultSetOutParameter

  Parameters:

  type - the JDBC data type.

  Returns:

  a ResultSetOutParameter

• public void rollback()

  If this SQL object was created with a Connection then this method rolls back the connection. If this SQL object was created from a DataSource then this method does nothing.

  throws:

  SQLException if a database access error occurs

• public List<GroovyRowResult> rows(String sql)

  Performs the given SQL query and return the rows of the result set.

  Example usage:

   def ans = sql.rows("select * from PERSON where firstname like 'S%'")
   println "Found ${ans.size()} rows"
   

  Resource handling is performed automatically where appropriate.

  throws:

  SQLException if a database access error occurs

  Parameters:

  sql - the SQL statement

  Returns:

  a list of GroovyRowResult objects

• public List<GroovyRowResult> rows(String sql, int offset, int maxRows)

  Performs the given SQL query and return a "page" of rows from the result set. A page is defined as starting at a 1-based offset, and containing a maximum number of rows.

  Note that the underlying implementation is based on either invoking ResultSet.absolute(), or if the ResultSet type is ResultSet.TYPE_FORWARD_ONLY, the ResultSet.next() method is invoked equivalently. The first row of a ResultSet is 1, so passing in an offset of 1 or less has no effect on the initial positioning within the result set.

  Note that different database and JDBC driver implementations may work differently with respect to this method. Specifically, one should expect that ResultSet.TYPE_FORWARD_ONLY may be less efficient than a "scrollable" type.

  Resource handling is performed automatically where appropriate.

  throws:

  SQLException if a database access error occurs

  Parameters:

  sql - the SQL statement

  offset - the 1-based offset for the first row to be processed

  maxRows - the maximum number of rows to be processed

  Returns:

  a list of GroovyRowResult objects

• public List<GroovyRowResult> rows(String sql, @ClosureParams(value=SimpleType.class, options="java.sql.ResultSetMetaData") Closure metaClosure)

  Performs the given SQL query and return the rows of the result set. In addition, the metaClosure will be called once passing in the ResultSetMetaData as argument.

  Example usage:

   def printNumCols = { meta -> println "Found $meta.columnCount columns" }
   def ans = sql.rows("select * from PERSON", printNumCols)
   println "Found ${ans.size()} rows"
   

  Resource handling is performed automatically where appropriate.

  throws:

  SQLException if a database access error occurs

  Parameters:

  sql - the SQL statement

  metaClosure - called with meta data of the ResultSet

  Returns:

  a list of GroovyRowResult objects

• public List<GroovyRowResult> rows(String sql, int offset, int maxRows, @ClosureParams(value=SimpleType.class, options="java.sql.ResultSetMetaData") Closure metaClosure)

  Performs the given SQL query and return a "page" of rows from the result set. A page is defined as starting at a 1-based offset, and containing a maximum number of rows. In addition, the metaClosure will be called once passing in the ResultSetMetaData as argument.

  Note that the underlying implementation is based on either invoking ResultSet.absolute(), or if the ResultSet type is ResultSet.TYPE_FORWARD_ONLY, the ResultSet.next() method is invoked equivalently. The first row of a ResultSet is 1, so passing in an offset of 1 or less has no effect on the initial positioning within the result set.

  Note that different database and JDBC driver implementations may work differently with respect to this method. Specifically, one should expect that ResultSet.TYPE_FORWARD_ONLY may be less efficient than a "scrollable" type.

  Resource handling is performed automatically where appropriate.

  throws:

  SQLException if a database access error occurs

  Parameters:

  sql - the SQL statement

  offset - the 1-based offset for the first row to be processed

  maxRows - the maximum number of rows to be processed

  metaClosure - called for meta data (only once after sql execution)

  Returns:

  a list of GroovyRowResult objects

• public List<GroovyRowResult> rows(String sql, List<Object> params)

  Performs the given SQL query and return the rows of the result set. The query may contain placeholder question marks which match the given list of parameters.

  Example usage:

   def ans = sql.rows("select * from PERSON where lastname like ?", ['%a%'])
   println "Found ${ans.size()} rows"
   

  This method supports named and named ordinal parameters by supplying such parameters in the params list. See the class Javadoc for more details.

  Resource handling is performed automatically where appropriate.

  throws:

  SQLException if a database access error occurs

  Parameters:

  sql - the SQL statement

  params - a list of parameters

  Returns:

  a list of GroovyRowResult objects

• public List<GroovyRowResult> rows(Map params, String sql)

  A variant of rows(String, java.util.List) useful when providing the named parameters as named arguments.

  throws:

  SQLException if a database access error occurs

  Parameters:

  params - a map containing the named parameters

  sql - the SQL statement

  Returns:

  a list of GroovyRowResult objects

  Since:

  1.8.7

• public List<GroovyRowResult> rows(String sql, List<Object> params, int offset, int maxRows)

  Performs the given SQL query and return a "page" of rows from the result set. A page is defined as starting at a 1-based offset, and containing a maximum number of rows. The query may contain placeholder question marks which match the given list of parameters.

  Note that the underlying implementation is based on either invoking ResultSet.absolute(), or if the ResultSet type is ResultSet.TYPE_FORWARD_ONLY, the ResultSet.next() method is invoked equivalently. The first row of a ResultSet is 1, so passing in an offset of 1 or less has no effect on the initial positioning within the result set.

  Note that different database and JDBC driver implementations may work differently with respect to this method. Specifically, one should expect that ResultSet.TYPE_FORWARD_ONLY may be less efficient than a "scrollable" type.

  This method supports named and named ordinal parameters by supplying such parameters in the params list. See the class Javadoc for more details.

  Resource handling is performed automatically where appropriate.

  throws:

  SQLException if a database access error occurs

  Parameters:

  sql - the SQL statement

  params - a list of parameters

  offset - the 1-based offset for the first row to be processed

  maxRows - the maximum number of rows to be processed

  Returns:

  a list of GroovyRowResult objects

• public List<GroovyRowResult> rows(String sql, Map params, int offset, int maxRows)

  A variant of rows(String, java.util.List, int, int) useful when providing the named parameters as a map.

  throws:

  SQLException if a database access error occurs

  Parameters:

  sql - the SQL statement

  params - a map of named parameters

  offset - the 1-based offset for the first row to be processed

  maxRows - the maximum number of rows to be processed

  Returns:

  a list of GroovyRowResult objects

  Since:

  1.8.7

• public List<GroovyRowResult> rows(Map params, String sql, int offset, int maxRows)

  A variant of rows(String, java.util.List, int, int) useful when providing the named parameters as named arguments.

  throws:

  SQLException if a database access error occurs

  Parameters:

  params - a map of named parameters

  sql - the SQL statement

  offset - the 1-based offset for the first row to be processed

  maxRows - the maximum number of rows to be processed

  Returns:

  a list of GroovyRowResult objects

  Since:

  1.8.7

• public List<GroovyRowResult> rows(String sql, Object[] params)

  Performs the given SQL query and return the rows of the result set.

  This method supports named and named ordinal parameters by supplying such parameters in the params array. See the class Javadoc for more details.

  An Object array variant of rows(String, List).

  throws:

  SQLException if a database access error occurs

  Parameters:

  sql - the SQL statement

  params - an array of parameters

  Returns:

  a list of GroovyRowResult objects

• public List<GroovyRowResult> rows(String sql, Object[] params, int offset, int maxRows)

  Performs the given SQL query and return the rows of the result set.

  This method supports named and named ordinal parameters by supplying such parameters in the params array. See the class Javadoc for more details.

  An Object array variant of rows(String, List, int, int).

  throws:

  SQLException if a database access error occurs

  Parameters:

  sql - the SQL statement

  params - an array of parameters

  offset - the 1-based offset for the first row to be processed

  maxRows - the maximum number of rows to be processed

  Returns:

  a list of GroovyRowResult objects

• public List<GroovyRowResult> rows(String sql, List<Object> params, @ClosureParams(value=SimpleType.class, options="java.sql.ResultSetMetaData") Closure metaClosure)

  Performs the given SQL query and return the rows of the result set. In addition, the metaClosure will be called once passing in the ResultSetMetaData as argument. The query may contain placeholder question marks which match the given list of parameters.

  Example usage:

   def printNumCols = { meta -> println "Found $meta.columnCount columns" }
   def ans = sql.rows("select * from PERSON where lastname like ?", ['%a%'], printNumCols)
   println "Found ${ans.size()} rows"
   

  This method supports named and named ordinal parameters by supplying such parameters in the params list. Here is an example:

   def printNumCols = { meta -> println "Found $meta.columnCount columns" }
  
   def mapParam = [foo: 'Smith']
   def domainParam = new MyDomainClass(bar: 'John')
   def qry = 'select * from PERSON where lastname=?1.foo and firstname=?2.bar'
   def ans = sql.rows(qry, [mapParam, domainParam], printNumCols)
   println "Found ${ans.size()} rows"
  
   def qry2 = 'select * from PERSON where firstname=:first and lastname=:last'
   def ans2 = sql.rows(qry2, [[last:'Smith', first:'John']], printNumCols)
   println "Found ${ans2.size()} rows"
   

  See the class Javadoc for more details.

  Resource handling is performed automatically where appropriate.

  throws:

  SQLException if a database access error occurs

  Parameters:

  sql - the SQL statement

  params - a list of parameters

  metaClosure - called for meta data (only once after sql execution)

  Returns:

  a list of GroovyRowResult objects

• public List<GroovyRowResult> rows(String sql, Map params, @ClosureParams(value=SimpleType.class, options="java.sql.ResultSetMetaData") Closure metaClosure)

  A variant of rows(String, java.util.List, groovy.lang.Closure) useful when providing the named parameters as a map.

  throws:

  SQLException if a database access error occurs

  Parameters:

  sql - the SQL statement

  params - a map of named parameters

  metaClosure - called for meta data (only once after sql execution)

  Returns:

  a list of GroovyRowResult objects

  Since:

  1.8.7

• public List<GroovyRowResult> rows(Map params, String sql, @ClosureParams(value=SimpleType.class, options="java.sql.ResultSetMetaData") Closure metaClosure)

  A variant of rows(String, java.util.List, groovy.lang.Closure) useful when providing the named parameters as named arguments.

  throws:

  SQLException if a database access error occurs

  Parameters:

  params - a map of named parameters

  sql - the SQL statement

  metaClosure - called for meta data (only once after sql execution)

  Returns:

  a list of GroovyRowResult objects

  Since:

  1.8.7

• public List<GroovyRowResult> rows(String sql, List<Object> params, int offset, int maxRows, @ClosureParams(value=SimpleType.class, options="java.sql.ResultSetMetaData") Closure metaClosure)

  Performs the given SQL query and return a "page" of rows from the result set. A page is defined as starting at a 1-based offset, and containing a maximum number of rows. In addition, the metaClosure will be called once passing in the ResultSetMetaData as argument. The query may contain placeholder question marks which match the given list of parameters.

  Note that the underlying implementation is based on either invoking ResultSet.absolute(), or if the ResultSet type is ResultSet.TYPE_FORWARD_ONLY, the ResultSet.next() method is invoked equivalently. The first row of a ResultSet is 1, so passing in an offset of 1 or less has no effect on the initial positioning within the result set.

  Note that different database and JDBC driver implementations may work differently with respect to this method. Specifically, one should expect that ResultSet.TYPE_FORWARD_ONLY may be less efficient than a "scrollable" type.

  This method supports named and named ordinal parameters by supplying such parameters in the params list. See the class Javadoc for more details.

  Resource handling is performed automatically where appropriate.

  throws:

  SQLException if a database access error occurs

  Parameters:

  sql - the SQL statement

  params - a list of parameters

  offset - the 1-based offset for the first row to be processed

  maxRows - the maximum number of rows to be processed

  metaClosure - called for meta data (only once after sql execution)

  Returns:

  a list of GroovyRowResult objects

• public List<GroovyRowResult> rows(String sql, Map params, int offset, int maxRows, @ClosureParams(value=SimpleType.class, options="java.sql.ResultSetMetaData") Closure metaClosure)

  A variant of rows(String, java.util.List, int, int, groovy.lang.Closure) useful when providing the named parameters as a map.

  throws:

  SQLException if a database access error occurs

  Parameters:

  sql - the SQL statement

  params - a map of named parameters

  offset - the 1-based offset for the first row to be processed

  maxRows - the maximum number of rows to be processed

  metaClosure - called for meta data (only once after sql execution)

  Returns:

  a list of GroovyRowResult objects

  Since:

  1.8.7

• public List<GroovyRowResult> rows(Map params, String sql, int offset, int maxRows, @ClosureParams(value=SimpleType.class, options="java.sql.ResultSetMetaData") Closure metaClosure)

  A variant of rows(String, java.util.List, int, int, groovy.lang.Closure) useful when providing the named parameters as named arguments.

  throws:

  SQLException if a database access error occurs

  Parameters:

  params - a map of named parameters

  sql - the SQL statement

  offset - the 1-based offset for the first row to be processed

  maxRows - the maximum number of rows to be processed

  metaClosure - called for meta data (only once after sql execution)

  Returns:

  a list of GroovyRowResult objects

  Since:

  1.8.7

• public List<GroovyRowResult> rows(GString sql, int offset, int maxRows)

  Performs the given SQL query and return a "page" of rows from the result set. A page is defined as starting at a 1-based offset, and containing a maximum number of rows. The query may contain GString expressions.

  Note that the underlying implementation is based on either invoking ResultSet.absolute(), or if the ResultSet type is ResultSet.TYPE_FORWARD_ONLY, the ResultSet.next() method is invoked equivalently. The first row of a ResultSet is 1, so passing in an offset of 1 or less has no effect on the initial positioning within the result set.

  Note that different database and JDBC driver implementations may work differently with respect to this method. Specifically, one should expect that ResultSet.TYPE_FORWARD_ONLY may be less efficient than a "scrollable" type.

  Resource handling is performed automatically where appropriate.

  throws:

  SQLException if a database access error occurs

  Parameters:

  sql - the SQL statement

  offset - the 1-based offset for the first row to be processed

  maxRows - the maximum number of rows to be processed

  Returns:

  a list of GroovyRowResult objects

• public List<GroovyRowResult> rows(GString gstring)

  Performs the given SQL query and return the rows of the result set. The query may contain GString expressions.

  Example usage:

   def location = 25
   def ans = sql.rows("select * from PERSON where location_id < $location")
   println "Found ${ans.size()} rows"
   

  Resource handling is performed automatically where appropriate.

  throws:

  SQLException if a database access error occurs

  Parameters:

  gstring - a GString containing the SQL query with embedded params

  Returns:

  a list of GroovyRowResult objects

  See Also:

  expand(Object)