|
Groovy 1.6.4 | |||||||
FRAMES NO FRAMES | ||||||||
SUMMARY: NESTED | FIELD | CONSTR | METHOD | DETAIL: FIELD | CONSTR | METHOD |
java.lang.Objectgroovy.sql.Sql
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. The class provides numerous extension points for overriding the facade behavior associated with the various aspects of managing the connection with the underlying database.
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
|
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
|
protected Logger |
log
Hook to allow derived classes to access the log |
Constructor Summary | |
Sql(DataSource dataSource)
Constructs an SQL instance using the given DataSource. |
|
Sql(Connection connection)
Constructs an SQL instance using the given 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 String
|
asSql(GString gstring, List values)
Hook to allow derived classes to override sql generation from Gstrings. |
void
|
cacheConnection(Closure closure)
Caches the connection used while the closure is active. |
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)
Performs a stored procedure call. |
int
|
call(String sql, List params)
Performs a stored procedure call with the given parameters. |
void
|
call(String sql, List params, Closure closure)
Performs a stored procedure call with the given parameters. |
int
|
call(GString gstring)
Performs a stored procedure call with the given parameters. |
void
|
call(GString gstring, Closure closure)
Performs a stored procedure call with the given parameters, calling the closure once with all result objects. |
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)
An extension point allowing the behavior of resource closing to be overriden in derived classes. |
void
|
commit()
If this SQL object was created with a Connection then this method commits the connection. |
protected void
|
configure(Statement statement)
Provides a hook for derived classes to be able to configure JDBC statements. |
protected Connection
|
createConnection()
An extension point allowing derived classes to change the behavior of connection creation. |
DataSet
|
dataSet(String table)
|
DataSet
|
dataSet(Class type)
|
void
|
eachRow(String sql, Closure closure)
Performs the given SQL query calling the closure with each row of the result set. |
void
|
eachRow(String sql, Closure metaClosure, Closure rowClosure)
Performs the given SQL query calling closures for metadata and each row |
void
|
eachRow(String sql, List params, Closure closure)
Performs the given SQL query calling the closure with the result set. |
void
|
eachRow(GString gstring, Closure closure)
Performs the given SQL query calling the closure with the result set. |
boolean
|
execute(String sql)
Executes the given piece of SQL. |
boolean
|
execute(String sql, List params)
Executes the given piece of SQL with parameters. |
boolean
|
execute(GString gstring)
Executes the given SQL with embedded expressions inside. |
List
|
executeInsert(String sql)
Executes the given SQL statement. |
List
|
executeInsert(String sql, List params)
Executes the given SQL statement with a particular list of parameter values. |
List
|
executeInsert(GString gstring)
Executes the given SQL with embedded expressions inside, and returns the values of any auto-generated colums, such as an autoincrement ID field. |
int
|
executeUpdate(String sql)
Executes the given SQL update. |
int
|
executeUpdate(String sql, List params)
Executes the given SQL update with parameters. |
int
|
executeUpdate(GString gstring)
Executes the given SQL update with embedded expressions inside. |
static ExpandedVariable
|
expand(Object object)
Creates 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)
Performs the given SQL query and return the first row of the result set. |
Object
|
firstRow(String sql, List params)
Performs the given SQL query with the list of params and return the first row of the result set. |
Connection
|
getConnection()
If this instance was created with a single Connection then the connection is returned. |
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()
|
static InParameter
|
in(int type, Object value)
Create a new InParameter |
static InOutParameter
|
inout(InParameter in)
Create an inout parameter using this in parameter. |
boolean
|
isCacheStatements()
|
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. |
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)
Performs the given SQL query calling the closure with the result set. |
void
|
query(String sql, List params, Closure closure)
Performs the given SQL query with parameters calling the closure with the result set. |
void
|
query(GString gstring, Closure closure)
Performs the given SQL query calling the closure with the result set. |
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)
Performs the given SQL query and return the rows of the result set. |
List
|
rows(GString gstring)
Performs the given SQL query and return the rows of the result set. |
List
|
rows(String sql, Closure metaClosure)
Performs the given SQL query and return the rows of the result set. |
List
|
rows(String sql, List params)
Performs the given SQL query with the list of params and return the rows of the result set. |
void
|
setCacheStatements(boolean cacheStatements)
Enables statement caching. |
protected void
|
setObject(PreparedStatement statement, int i, Object value)
Strategy method allowing derived classes to handle types differently such as for CLOBs etc. |
protected void
|
setParameters(List params, PreparedStatement statement)
Hook to allow derived classes to override behavior associated with setting params for a prepared 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)
Performs the closure within a batch using a cached connection. |
void
|
withStatement(Closure configureStatement)
Allows a closure to be passed in to configure the JDBC statements before they are executed. |
void
|
withTransaction(Closure closure)
Performs the closure within a transaction using a cached connection. |
Methods inherited from class Object | |
---|---|
wait, wait, wait, hashCode, getClass, equals, toString, notify, notifyAll |
Field Detail |
---|
static final OutParameter ARRAY
static final OutParameter BIGINT
static final OutParameter BINARY
static final OutParameter BIT
static final OutParameter BLOB
static final OutParameter BOOLEAN
static final OutParameter CHAR
static final OutParameter CLOB
static final OutParameter DATALINK
static final OutParameter DATE
static final OutParameter DECIMAL
static final OutParameter DISTINCT
static final OutParameter DOUBLE
static final OutParameter FLOAT
static final OutParameter INTEGER
static final OutParameter JAVA_OBJECT
static final OutParameter LONGVARBINARY
static final OutParameter LONGVARCHAR
static final OutParameter NULL
static final OutParameter NUMERIC
static final OutParameter OTHER
static final OutParameter REAL
static final OutParameter REF
static final OutParameter SMALLINT
static final OutParameter STRUCT
static final OutParameter TIME
static final OutParameter TIMESTAMP
static final OutParameter TINYINT
static final OutParameter VARBINARY
static final OutParameter VARCHAR
protected Logger log
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 String asSql(GString gstring, List values)
public void cacheConnection(Closure closure)
public void cacheStatements(Closure closure)
public int call(String sql)
public int call(String sql, List params)
public void call(String sql, List params, Closure closure)
public int call(GString gstring)
public void call(GString gstring, Closure closure)
public void close()
protected void closeResources(Connection connection, Statement statement, ResultSet results)
protected void closeResources(Connection connection, Statement statement)
public void commit()
protected void configure(Statement statement)
protected Connection createConnection()
public DataSet dataSet(String table)
public DataSet dataSet(Class type)
public void eachRow(String sql, Closure closure)
public void eachRow(String sql, Closure metaClosure, Closure rowClosure)
public void eachRow(String sql, List params, Closure closure)
public void eachRow(GString gstring, Closure closure)
public boolean execute(String sql)
true
if the first result is a ResultSet
object; false
if it is an update count or there are
no results
public boolean execute(String sql, List params)
true
if the first result is a ResultSet
object; false
if it is an update count or there are
no results
public boolean execute(GString gstring)
true
if the first result is a ResultSet
object; false
if it is an update count or there are
no results
public List executeInsert(String sql)
public List executeInsert(String sql, List params)
public List executeInsert(GString gstring)
Executes the given SQL with embedded expressions inside, and
returns the values of any auto-generated colums, such as an
autoincrement ID field. These 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.insert("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. ...
public int executeUpdate(String sql)
public int executeUpdate(String sql, List params)
public int executeUpdate(GString gstring)
public static ExpandedVariable expand(Object object)
protected int findWhereKeyword(String sql)
public Object firstRow(String sql)
null
if no row is found
public Object firstRow(GString gstring)
null
if no row is found
public Object firstRow(String sql, List params)
null
if no row is found
public Connection getConnection()
public DataSource getDataSource()
protected List getParameters(GString gstring)
public int getResultSetConcurrency()
public int getResultSetHoldability()
public int getResultSetType()
public int getUpdateCount()
public static InParameter in(int type, Object value)
public static InOutParameter inout(InParameter in)
public boolean isCacheStatements()
public static void loadDriver(String driverClassName)
public static Sql newInstance(String url)
jdbc:subprotocol:subname
public static Sql newInstance(String url, Properties properties)
jdbc:subprotocol:subname
public static Sql newInstance(String url, Properties properties, String driverClassName)
jdbc:subprotocol:subname
public static Sql newInstance(String url, String user, String password)
jdbc:subprotocol:subname
public static Sql newInstance(String url, String user, String password, String driverClassName)
jdbc:subprotocol:subname
public static Sql newInstance(String url, String driverClassName)
jdbc:subprotocol:subname
protected String nullify(String sql)
public static OutParameter out(int type)
public void query(String sql, Closure closure)
public void query(String sql, List params, Closure closure)
public void query(GString gstring, Closure closure)
public static ResultSetOutParameter resultSet(int type)
public void rollback()
public List rows(String sql)
public List rows(GString gstring)
public List rows(String sql, Closure metaClosure)
public List rows(String sql, List params)
public void setCacheStatements(boolean cacheStatements)
protected void setObject(PreparedStatement statement, int i, Object value)
protected void setParameters(List params, PreparedStatement statement)
setObject
.
public void setResultSetConcurrency(int resultSetConcurrency)
ResultSet
constants:
ResultSet.CONCUR_READ_ONLY
or
ResultSet.CONCUR_UPDATABLE
public void setResultSetHoldability(int resultSetHoldability)
ResultSet
constants:
ResultSet.HOLD_CURSORS_OVER_COMMIT
or
ResultSet.CLOSE_CURSORS_AT_COMMIT
public void setResultSetType(int resultSetType)
ResultSet
constants:
ResultSet.TYPE_FORWARD_ONLY
,
ResultSet.TYPE_SCROLL_INSENSITIVE
, or
ResultSet.TYPE_SCROLL_SENSITIVE
public int[] withBatch(Closure closure)
def updateCounts = sql.withBatch { stmt -> stmt.addBatch("insert into TABLENAME ...") stmt.addBatch("insert into TABLENAME ...") stmt.addBatch("insert into TABLENAME ...") }
Statement
, or the
driver does not support batch statements. Throws BatchUpdateException
(a subclass of SQLException
) if one of the commands sent to the
database fails to execute properly or attempts to return a result set.
public void withStatement(Closure configureStatement)
sql.withStatement{ stmt -> stmt.maxRows == 10 } def firstTenRows = sql.rows("select * from table")
public void withTransaction(Closure closure)
Copyright © 2003-2009 The Codehaus. All rights reserved.