Sql (Groovy 1.7.11)

Overview

Package

Class

Deprecated

Index

Help

Groovy 1.7.11

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
extends Object

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.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]

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.

Named and named ordinal parameters

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

Nested Class Summary
`protected class`	`Sql.AbstractQueryCommand`
`protected class`	`Sql.PreparedQueryCommand`
`protected class`	`Sql.QueryCommand`

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)` Hook to allow derived classes to override list of result collection behavior.
`protected List`	`asList(String sql, ResultSet rs, Closure metaClosure)`
`protected String`	`asSql(GString gstring, List values)` Hook to allow derived classes to override sql generation from GStrings.
`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)`
`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, Closure metaClosure, Closure rowClosure)`
`void`	`eachRow(String sql, List params, Closure metaClosure, Closure closure)` Performs the given SQL query calling the given Closure with each row of the result set.
`void`	`eachRow(String sql, List params, Closure closure)` Performs the given SQL query calling the given Closure with each row of the result set.
`void`	`eachRow(GString gstring, Closure metaClosure, 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(String sql, Object[] params)` Executes the given piece of SQL with parameters.
`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(String sql, Object[] params)` Executes the given SQL statement (typically an INSERT statement).
`List`	`executeInsert(GString gstring)`
`protected ResultSet`	`executePreparedQuery(String sql, List params)`
`protected ResultSet`	`executeQuery(String sql)` Hook to allow derived classes to access ResultSet returned from query.
`int`	`executeUpdate(String sql)`
`int`	`executeUpdate(String sql, List params)` Executes the given SQL update with parameters.
`int`	`executeUpdate(String sql, Object[] params)` Executes the given SQL update with parameters.
`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.
`Object`	`firstRow(String sql)` Performs the given SQL query and return the first row of the result set.
`Object`	`firstRow(GString gstring)`
`Object`	`firstRow(String sql, List params)`
`Object`	`firstRow(String sql, Object[] params)`
`Connection`	`getConnection()`
`DataSource`	`getDataSource()`
`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.
`static InParameter`	`in(int type, Object value)`
`static InOutParameter`	`inout(InParameter in)` Create an inout parameter using this in parameter.
`boolean`	`isCacheNamedQueries()`
`boolean`	`isCacheStatements()`
`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
`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(GString gstring, Closure closure)` Performs the given SQL query, which should return a single `ResultSet` object.
`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, Closure metaClosure)`
`List`	`rows(String sql, List params)` Performs the given SQL query and return the rows of the result set.
`List`	`rows(String sql, Object[] params)`
`List`	`rows(String sql, List params, Closure metaClosure)`
`List`	`rows(GString gstring)` Performs the given SQL query and return the rows of the result set.
`List`	`rows(GString gstring, Closure metaClosure)`
`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)`
`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)`
`void`	`withStatement(Closure configureStatement)`
`void`	`withTransaction(Closure closure)` Performs the closure within a transaction using a cached connection.

groovy.sql [Java] Class Sql

Typical usage

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

asSql

cacheConnection

cacheStatements

call

call

call

call

call

call

checkForNamedParams

groovy.sql
[Java] Class Sql