Sql (groovy 2.2.0 API)

Overview

Package

Class

Deprecated

Index

Help

Groovy 2.2.0

FRAMES NO FRAMES

SUMMARY: NESTED | FIELD | CONSTR | METHOD

DETAIL: FIELD | CONSTR | METHOD

groovy.sql
[Java] Class Sql

java.lang.Object
  groovy.sql.Sql

public class 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

Field Summary
`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`

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

Method Summary
`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)`
`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)` Performs a stored procedure call with the given parameters, calling the closure once with all result objects.
`SqlWithParams`	`checkForNamedParams(String sql, List params)`
`void`	`close()`
`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.

groovy.sql [Java] Class Sql

Typical usage

Avoiding SQL injection

Named and named ordinal parameters

More details

ARRAY

BIGINT

BINARY

BIT

BLOB

BOOLEAN

CHAR

CLOB

DATALINK

DATE

DECIMAL

DISTINCT

DOUBLE

FLOAT

INTEGER

JAVA_OBJECT

LOG

LONGVARBINARY

LONGVARCHAR

NULL

NUMERIC

OTHER

REAL

REF

SMALLINT

STRUCT

TIME

TIMESTAMP

TINYINT

VARBINARY

VARCHAR

Sql

Sql

Sql

ARRAY

BIGINT

BINARY

BIT

BLOB

BOOLEAN

CHAR

CLOB

DATALINK

DATE

DECIMAL

DISTINCT

DOUBLE

FLOAT

INTEGER

JAVA_OBJECT

LONGVARBINARY

LONGVARCHAR

NULL

NUMERIC

OTHER

REAL

REF

SMALLINT

STRUCT

TIME

TIMESTAMP

TINYINT

VARBINARY

VARCHAR

asList

asList

asList

asSql

buildSqlWithIndexedProps

cacheConnection

cacheStatements

call

call

call

call

groovy.sql
[Java] Class Sql