Sql (groovy 2.3.0-rc-1 API)

groovy.sql.Sql

A facade over Java's normal JDBC APIs providing greatly simplified resource management and result set handling. Under the covers the facade hides away details associated with getting connections, constructing and configuring statements, interacting with the connection, closing resources and logging errors. Special features of the facade include using closures to iterate through result sets, a special GString syntax for representing prepared statements and treating result sets like collections of maps with the normal Groovy collection methods available.

Typical usage

First you need to set up your sql instance. There are several constructors and a few newInstance factory methods available to do this. In simple cases, you can just provide the necessary details to set up a connection (e.g. for hsqldb):

 def db = [url:'jdbc:hsqldb:mem:testDB', user:'sa', password:'', driver:'org.hsqldb.jdbc.JDBCDriver']
 def sql = Sql.newInstance(db.url, db.user, db.password, db.driver)

or if you have an existing connection (perhaps from a connection pool) or a datasource use one of the constructors:

 def sql = new Sql(datasource)

Now you can invoke sql, e.g. to create a table:

 sql.execute '''
     create table PROJECT (
         id integer not null,
         name varchar(50),
         url varchar(100),
     )
 '''

Or insert a row using JDBC PreparedStatement inspired syntax:

 def params = [10, 'Groovy', 'http://groovy.codehaus.org']
 sql.execute 'insert into PROJECT (id, name, url) values (?, ?, ?)', params

Or insert a row using GString syntax:

 def map = [id:20, name:'Grails', url:'http://grails.codehaus.org']
 sql.execute "insert into PROJECT (id, name, url) values ($map.id, $map.name, $map.url)"

Or a row update:

 def newUrl = 'http://grails.org'
 def project = 'Grails'
 sql.executeUpdate "update PROJECT set url=$newUrl where name=$project"

Now try a query using eachRow:

 println 'Some GR8 projects:'
 sql.eachRow('select * from PROJECT') { row ->
     println "${row.name.padRight(10)} ($row.url)"
 }

Which will produce something like this:

 Some GR8 projects:
 Groovy     (http://groovy.codehaus.org)
 Grails     (http://grails.org)
 Griffon    (http://griffon.codehaus.org)
 Gradle     (http://gradle.org)

Now try a query using rows:

 def rows = sql.rows("select * from PROJECT where name like 'Gra%'")
 assert rows.size() == 2
 println rows.join('\n')

with output like this:

 [ID:20, NAME:Grails, URL:http://grails.org]
 [ID:40, NAME:Gradle, URL:http://gradle.org]

Also, eachRow and rows support paging. Here's an example:

 sql.eachRow('select * from PROJECT', 2, 2) { row ->
     println "${row.name.padRight(10)} ($row.url)"
 }

Which will start at the second row and return a maximum of 2 rows. Here's an example result:

 Grails     (http://grails.org)
 Griffon    (http://griffon.codehaus.org)

Finally, we should clean up:

 sql.close()

If we are using a DataSource and we haven't enabled statement caching, then strictly speaking the final close() method isn't required - as all connection handling is performed transparently on our behalf; however, it doesn't hurt to have it there as it will return silently in that case.

If instead of newInstance you use withInstance, then close() will be called automatically for you.

Avoiding SQL injection

If you find yourself creating queries based on any kind of input from the user or a 3rd party application you might wish to avoid the pure string method variants in this class. While this is safe: sql.firstRow('select * from PersonTable') This example is potentially at risk of SQL injection: sql.firstRow('select * from PersonTable where SurnameColumn = ' + userInput) This in turn will be fine if 'userInput' is something like 'Smith' but maybe not so fine if 'userInput' is something like 'Smith; DROP table PersonTable'. Instead, use one of the variants with parameters and placeholders: sql.firstRow("select * from PersonTable where SurnameColumn = ?", [userInput]) or the GString variants which will be converted to the placeholder variants under the covers: sql.firstRow("select * from PersonTable where SurnameColumn = $userInput") or the named parameter variants discussed next.

Named and named ordinal parameters

Several of the methods in this class (ones which have a String-based sql query and params in a List

Modifiers	Name	Description
`static OutParameter`	`ARRAY`
`static OutParameter`	`BIGINT`
`static OutParameter`	`BINARY`
`static OutParameter`	`BIT`
`static OutParameter`	`BLOB`
`static OutParameter`	`BOOLEAN`
`static OutParameter`	`CHAR`
`static OutParameter`	`CLOB`
`static OutParameter`	`DATALINK`
`static OutParameter`	`DATE`
`static OutParameter`	`DECIMAL`
`static OutParameter`	`DISTINCT`
`static OutParameter`	`DOUBLE`
`static OutParameter`	`FLOAT`
`static OutParameter`	`INTEGER`
`static OutParameter`	`JAVA_OBJECT`
`protected static Logger`	`LOG`	Hook to allow derived classes to access the log
`static OutParameter`	`LONGVARBINARY`
`static OutParameter`	`LONGVARCHAR`
`static OutParameter`	`NULL`
`static OutParameter`	`NUMERIC`
`static OutParameter`	`OTHER`
`static OutParameter`	`REAL`
`static OutParameter`	`REF`
`static OutParameter`	`SMALLINT`
`static OutParameter`	`STRUCT`
`static OutParameter`	`TIME`
`static OutParameter`	`TIMESTAMP`
`static OutParameter`	`TINYINT`
`static OutParameter`	`VARBINARY`
`static OutParameter`	`VARCHAR`

Type	Name and description
`static InParameter`	`ARRAY(Object value)`
`static InParameter`	`BIGINT(Object value)`
`static InParameter`	`BINARY(Object value)`
`static InParameter`	`BIT(Object value)`
`static InParameter`	`BLOB(Object value)`
`static InParameter`	`BOOLEAN(Object value)`
`static InParameter`	`CHAR(Object value)`
`static InParameter`	`CLOB(Object value)`
`static InParameter`	`DATALINK(Object value)`
`static InParameter`	`DATE(Object value)`
`static InParameter`	`DECIMAL(Object value)`
`static InParameter`	`DISTINCT(Object value)`
`static InParameter`	`DOUBLE(Object value)`
`static InParameter`	`FLOAT(Object value)`
`static InParameter`	`INTEGER(Object value)`
`static InParameter`	`JAVA_OBJECT(Object value)`
`static InParameter`	`LONGVARBINARY(Object value)`
`static InParameter`	`LONGVARCHAR(Object value)`
`static InParameter`	`NULL(Object value)`
`static InParameter`	`NUMERIC(Object value)`
`static InParameter`	`OTHER(Object value)`
`static InParameter`	`REAL(Object value)`
`static InParameter`	`REF(Object value)`
`static InParameter`	`SMALLINT(Object value)`
`static InParameter`	`STRUCT(Object value)`
`static InParameter`	`TIME(Object value)`
`static InParameter`	`TIMESTAMP(Object value)`
`static InParameter`	`TINYINT(Object value)`
`static InParameter`	`VARBINARY(Object value)`
`static InParameter`	`VARCHAR(Object value)`
`protected List`	`asList(String sql, ResultSet rs)` Hook to allow derived classes to override list of result collection behavior.
`protected List`	`asList(String sql, ResultSet rs, Closure metaClosure)`
`protected List`	`asList(String sql, ResultSet rs, int offset, int maxRows, Closure metaClosure)`
`protected String`	`asSql(GString gstring, List values)` Hook to allow derived classes to override sql generation from GStrings.
`protected SqlWithParams`	`buildSqlWithIndexedProps(String sql)`
`void`	`cacheConnection(Closure closure)`
`void`	`cacheStatements(Closure closure)` Caches every created preparedStatement in Closure closure Every cached preparedStatement is closed after closure has been called.
`int`	`call(String sql)`
`int`	`call(GString gstring)`
`int`	`call(String sql, List params)`
`int`	`call(String sql, Object[] params)` Performs a stored procedure call with the given parameters.
`void`	`call(String sql, List params, Closure closure)`
`void`	`call(GString gstring, Closure closure)`
`List`	`callWithRows(GString gstring, Closure closure)`
`List`	`callWithRows(String sql, List params, Closure closure)`
`protected List`	`callWithRows(String sql, List params, boolean processResultSet, Closure closure)`
`SqlWithParams`	`checkForNamedParams(String sql, List params)`
`void`	`close()` If this SQL object was created with a Connection then this method closes the connection.
`protected void`	`closeResources(Connection connection, Statement statement, ResultSet results)` An extension point allowing derived classes to change the behavior of resource closing.
`protected void`	`closeResources(Connection connection, Statement statement)`
`protected void`	`closeResources(Connection connection)` Provides a hook for derived classes to be able to configure JDBC statements.
`void`	`commit()`
`protected void`	`configure(Statement statement)`
`protected Connection`	`createConnection()` An extension point allowing derived classes to change the behavior of connection creation.
`protected Sql.AbstractQueryCommand`	`createPreparedQueryCommand(String sql, List queryParams)`
`protected Sql.AbstractQueryCommand`	`createQueryCommand(String sql)` Stub needed for testing.
`DataSet`	`dataSet(String table)`
`DataSet`	`dataSet(Class type)`
`void`	`eachRow(String sql, Closure closure)`
`void`	`eachRow(String sql, int offset, int maxRows, Closure closure)`
`void`	`eachRow(String sql, Closure metaClosure, Closure rowClosure)`
`void`	`eachRow(String sql, Closure metaClosure, int offset, int maxRows, Closure rowClosure)`
`void`	`eachRow(String sql, List params, Closure metaClosure, int offset, int maxRows, 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.
`void`	`eachRow(String sql, Map map, Closure metaClosure, int offset, int maxRows, 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.
`void`	`eachRow(Map map, String sql, Closure metaClosure, int offset, int maxRows, Closure rowClosure)`
`void`	`eachRow(String sql, List params, Closure metaClosure, Closure rowClosure)`
`void`	`eachRow(String sql, Map params, Closure metaClosure, Closure rowClosure)`
`void`	`eachRow(Map params, String sql, Closure metaClosure, Closure rowClosure)`
`void`	`eachRow(String sql, List params, Closure closure)`
`void`	`eachRow(String sql, Map params, Closure closure)`
`void`	`eachRow(Map params, String sql, Closure closure)`
`void`	`eachRow(String sql, List params, int offset, int maxRows, Closure closure)`
`void`	`eachRow(String sql, Map params, int offset, int maxRows, Closure closure)`
`void`	`eachRow(Map params, String sql, int offset, int maxRows, Closure closure)`
`void`	`eachRow(GString gstring, Closure metaClosure, Closure rowClosure)`
`void`	`eachRow(GString gstring, Closure metaClosure, int offset, int maxRows, Closure rowClosure)`
`void`	`eachRow(GString gstring, int offset, int maxRows, Closure closure)`
`void`	`eachRow(GString gstring, Closure closure)`
`boolean`	`execute(String sql)`
`boolean`	`execute(String sql, List params)` Executes the given piece of SQL with parameters.
`boolean`	`execute(Map params, String sql)` A variant of execute(String, java.util.List) useful when providing the named parameters as named arguments.
`boolean`	`execute(String sql, Object[] params)`
`boolean`	`execute(GString gstring)`
`List`	`executeInsert(String sql)`
`List`	`executeInsert(String sql, List params)` Executes the given SQL statement (typically an INSERT statement).
`List`	`executeInsert(Map params, String sql)` A variant of firstRow(String, java.util.List) useful when providing the named parameters as named arguments.
`List`	`executeInsert(String sql, Object[] params)`
`List`	`executeInsert(GString gstring)`
`protected ResultSet`	`executePreparedQuery(String sql, List params)`
`protected ResultSet`	`executeQuery(String sql)` Useful helper method which handles resource management when executing a query which returns a result set.
`int`	`executeUpdate(String sql)`
`int`	`executeUpdate(String sql, List params)` Executes the given SQL update with parameters.
`int`	`executeUpdate(Map params, String sql)` A variant of executeUpdate(String, java.util.List) useful when providing the named parameters as named arguments.
`int`	`executeUpdate(String sql, Object[] params)`
`int`	`executeUpdate(GString gstring)`
`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.
`protected int`	`findWhereKeyword(String sql)` Hook to allow derived classes to override where clause sniffing.
`GroovyRowResult`	`firstRow(String sql)`
`GroovyRowResult`	`firstRow(GString gstring)` Performs the given SQL query and return the first row of the result set.
`GroovyRowResult`	`firstRow(String sql, List params)`
`GroovyRowResult`	`firstRow(Map params, String sql)` A variant of firstRow(String, java.util.List) useful when providing the named parameters as named arguments.
`GroovyRowResult`	`firstRow(String sql, Object[] params)`
`Connection`	`getConnection()`
`DataSource`	`getDataSource()` If this SQL object was created with a Connection then this method commits the connection.
`protected List`	`getParameters(GString gstring)` Hook to allow derived classes to override behavior associated with extracting params from a GString.
`int`	`getResultSetConcurrency()` Gets the resultSetConcurrency for statements created using the connection.
`int`	`getResultSetHoldability()` Gets the resultSetHoldability for statements created using the connection.
`int`	`getResultSetType()` Gets the resultSetType for statements created using the connection.
`int`	`getUpdateCount()` @return Returns the updateCount.
`List`	`getUpdatedParams(List params, List indexPropList)` @return boolean true if caching is enabled (the default is true)
`static InParameter`	`in(int type, Object value)`
`static InOutParameter`	`inout(InParameter in)` Create an inout parameter using this in parameter.
`boolean`	`isCacheNamedQueries()`
`boolean`	`isCacheStatements()` Caches the connection used while the closure is active.
`boolean`	`isEnableNamedQueries()`
`boolean`	`isWithinBatch()` Returns true if the current Sql object is currently executing a withBatch method call.
`static void`	`loadDriver(String driverClassName)` Attempts to load the JDBC driver on the thread, current or system class loaders
`static Sql`	`newInstance(String url)` Creates a new Sql instance given a JDBC connection URL.
`static Sql`	`newInstance(String url, Properties properties)` Creates a new Sql instance given a JDBC connection URL and some properties.
`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.
`static Sql`	`newInstance(String url, String user, String password)` Creates a new Sql instance given a JDBC connection URL, a username and a password.
`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.
`static Sql`	`newInstance(String url, String driverClassName)` Creates a new Sql instance given a JDBC connection URL and a driver class name.
`static Sql`	`newInstance(Map args)` Creates a new Sql instance given parameters in a Map.
`protected String`	`nullify(String sql)` Hook to allow derived classes to override null handling.
`static OutParameter`	`out(int type)` Create a new OutParameter
`SqlWithParams`	`preCheckForNamedParams(String sql)`
`void`	`query(String sql, Closure closure)`
`void`	`query(String sql, List params, Closure closure)` Performs the given SQL query, which should return a single `ResultSet` object.
`void`	`query(String sql, Map map, Closure closure)` A variant of query(String, java.util.List, groovy.lang.Closure) useful when providing the named parameters as a map.
`void`	`query(Map map, String sql, Closure closure)`
`void`	`query(GString gstring, Closure closure)`
`static ResultSetOutParameter`	`resultSet(int type)` Create a new ResultSetOutParameter
`void`	`rollback()` If this SQL object was created with a Connection then this method rolls back the connection.
`List`	`rows(String sql)`
`List`	`rows(String sql, int offset, int maxRows)`
`List`	`rows(String sql, Closure metaClosure)`
`List`	`rows(String sql, int offset, int maxRows, Closure metaClosure)`
`List`	`rows(String sql, List params)` Performs the given SQL query and return the rows of the result set.
`List`	`rows(Map params, String sql)`
`List`	`rows(String sql, List params, int offset, int maxRows)`
`List`	`rows(String sql, Map params, int offset, int maxRows)`
`List`	`rows(Map params, String sql, int offset, int maxRows)`
`List`	`rows(String sql, Object[] params)`
`List`	`rows(String sql, Object[] params, int offset, int maxRows)`
`List`	`rows(String sql, List params, Closure metaClosure)`
`List`	`rows(String sql, Map params, Closure metaClosure)`
`List`	`rows(Map params, String sql, Closure metaClosure)`
`List`	`rows(String sql, List params, int offset, int maxRows, Closure metaClosure)`
`List`	`rows(String sql, Map params, int offset, int maxRows, Closure metaClosure)` A variant of rows(String, java.util.List, int, int, groovy.lang.Closure) useful when providing the named parameters as a map.
`List`	`rows(Map params, String sql, int offset, int maxRows, Closure metaClosure)`
`List`	`rows(GString sql, int offset, int maxRows)`
`List`	`rows(GString gstring)`
`List`	`rows(GString gstring, Closure metaClosure)`
`List`	`rows(GString gstring, int offset, int maxRows, Closure metaClosure)` Performs the given SQL query and return a "page" of rows from the result set.
`void`	`setCacheNamedQueries(boolean cacheNamedQueries)`
`void`	`setCacheStatements(boolean cacheStatements)` @return boolean true if cache is enabled (default is false)
`void`	`setEnableNamedQueries(boolean enableNamedQueries)`
`protected void`	`setInternalConnection(Connection conn)`
`protected void`	`setObject(PreparedStatement statement, int i, Object value)`
`protected void`	`setParameters(List params, PreparedStatement statement)` Strategy method allowing derived classes to handle types differently such as for CLOBs etc.
`void`	`setResultSetConcurrency(int resultSetConcurrency)` Sets the resultSetConcurrency for statements created using the connection.
`void`	`setResultSetHoldability(int resultSetHoldability)` Sets the resultSetHoldability for statements created using the connection.
`void`	`setResultSetType(int resultSetType)` Sets the resultSetType for statements created using the connection.
`int[]`	`withBatch(Closure closure)`
`int[]`	`withBatch(int batchSize, Closure closure)`
`int[]`	`withBatch(String sql, Closure closure)` Performs the closure (containing batch operations specific to an associated prepared statement) within a batch.
`int[]`	`withBatch(int batchSize, String sql, Closure closure)`
`static void`	`withInstance(String url, Closure c)` Invokes a closure passing it a new Sql instance created from the given JDBC connection URL.
`static void`	`withInstance(String url, Properties properties, Closure c)` Invokes a closure passing it a new Sql instance created from the given JDBC connection URL and properties.
`static void`	`withInstance(String url, Properties properties, String driverClassName, Closure c)` Invokes a closure passing it a new Sql instance created from the given JDBC connection URL, properties and driver classname.
`static void`	`withInstance(String url, String user, String password, Closure c)` Invokes a closure passing it a new Sql instance created from the given JDBC connection URL, user and password.
`static void`	`withInstance(String url, String user, String password, String driverClassName, Closure c)` Invokes a closure passing it a new Sql instance created from the given JDBC connection URL.
`static void`	`withInstance(String url, String driverClassName, Closure c)` Invokes a closure passing it a new Sql instance created from the given JDBC connection URL.
`static void`	`withInstance(Map args, Closure c)` Invokes a closure passing it a new Sql instance created from the given map of arguments.
`void`	`withStatement(Closure configureStatement)` Enables statement caching.
`void`	`withTransaction(Closure closure)` Performs the closure within a transaction using a cached connection.

Constructors
Constructor and description
`Sql (DataSource dataSource)` Constructs an SQL instance using the given DataSource.
`Sql (Connection connection)`
`Sql (Sql parent)`

[Java] Class Sql

Typical usage

Avoiding SQL injection

Named and named ordinal parameters

More details

Nested Class Summary

Field Summary

Constructor Summary

Methods Summary

Inherited Methods Summary

Field Detail

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 OutParameter FLOAT

public static final OutParameter INTEGER

public static final OutParameter JAVA_OBJECT

protected static final Logger LOG

public static final OutParameter LONGVARBINARY

public static final OutParameter LONGVARCHAR

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)

public Sql(Connection connection)

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 asList(String sql, ResultSet rs)

protected List asList(String sql, ResultSet rs, Closure metaClosure)

protected List asList(String sql, ResultSet rs, int offset, int maxRows, Closure metaClosure)