groovy.sql
Class Sql

java.lang.Object
  extended by groovy.sql.Sql
Direct Known Subclasses:
DataSet

public class Sql
extends Object

Represents an extent of objects

Version:
$Revision: 4066 $
Author:
Chris Stevenson, James Strachan

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  Logger 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(Connection connection)
          Construts an SQL instance using the given Connection.
Sql(DataSource dataSource)
          Constructs an SQL instance using the given DataSource.
Sql(Sql parent)
           
 
Method Summary
static InParameter ARRAY(Object value)
           
protected  String asSql(GString gstring, List values)
           
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)
           
 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.
 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.
static InParameter CHAR(Object value)
           
static InParameter CLOB(Object value)
           
 void close()
          If this SQL object was created with a Connection then this method closes the connection.
protected  void closeResources(Connection connection, Statement statement)
           
protected  void closeResources(Connection connection, Statement statement, ResultSet results)
           
 void commit()
           
protected  void configure(Statement statement)
          Provides a hook to be able to configure JDBC statements, such as to configure
protected  Connection createConnection()
           
static InParameter DATALINK(Object value)
           
 DataSet dataSet(Class type)
           
 DataSet dataSet(String table)
           
static InParameter DATE(Object value)
           
static InParameter DECIMAL(Object value)
           
static InParameter DISTINCT(Object value)
           
static InParameter DOUBLE(Object value)
           
 void eachRow(GString gstring, Closure closure)
          Performs the given SQL query calling the closure with the result set
 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, List params, Closure closure)
          Performs the given SQL query calling the closure with the result set
 boolean execute(GString gstring)
          Executes the given SQL with embedded expressions inside
 boolean execute(String sql)
          Executes the given piece of SQL
 boolean execute(String sql, List params)
          Executes the given piece of SQL with parameters
 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.
 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.
 int executeUpdate(GString gstring)
          Executes the given SQL update with embedded expressions inside
 int executeUpdate(String sql)
          Executes the given SQL update
 int executeUpdate(String sql, List params)
          Executes the given SQL update with parameters
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)
          Find the first 'where' keyword in the sql.
 Object firstRow(String sql)
          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
static InParameter FLOAT(Object value)
           
 Connection getConnection()
          If this instance was created with a single Connection then the connection is returned.
 DataSource getDataSource()
           
protected  List getParameters(GString gstring)
           
 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.
static InParameter INTEGER(Object value)
           
static InParameter JAVA_OBJECT(Object value)
           
static void loadDriver(String driverClassName)
          Attempts to load the JDBC driver on the thread, current or system class loaders
static InParameter LONGVARBINARY(Object value)
           
static InParameter LONGVARCHAR(Object value)
           
static Sql newInstance(String url)
          A helper method which creates a new Sql instance from a JDBC connection URL
static Sql newInstance(String url, Properties properties)
          A helper method which creates a new Sql instance from a JDBC connection URL
static Sql newInstance(String url, Properties properties, String driverClassName)
          A helper method which creates a new Sql instance from a JDBC connection URL and driver class name
static Sql newInstance(String url, String driverClassName)
          A helper method which creates a new Sql instance from a JDBC connection URL and driver class name
static Sql newInstance(String url, String user, String password)
          A helper method which creates a new Sql instance from a JDBC connection URL, username and password
static Sql newInstance(String url, String user, String password, String driverClassName)
          A helper method which creates a new Sql instance from a JDBC connection URL, username, password and driver class name
static InParameter NULL(Object value)
           
protected  String nullify(String sql)
          replace ?'"? references with NULLish
static InParameter NUMERIC(Object value)
           
static InParameter OTHER(Object value)
           
static OutParameter out(int type)
          Create a new OutParameter
 void query(GString gstring, Closure closure)
          Performs the given SQL query calling the closure with the result set
 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 queryEach(GString gstring, Closure closure)
          Deprecated. please use eachRow instead
 void queryEach(String sql, Closure closure)
          Deprecated. please use eachRow instead
 void queryEach(String sql, List params, Closure closure)
          Deprecated. please use eachRow instead
static InParameter REAL(Object value)
           
static InParameter REF(Object value)
           
static ResultSetOutParameter resultSet(int type)
          Create a new ResultSetOutParameter
 void rollback()
           
 List rows(String sql)
          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
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)
          Appends the parameters to the given statement
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)
           
 void withStatement(Closure configureStatement)
          Allows a closure to be passed in to configure the JDBC statements before they are executed to do things like set the query size etc.
 
Methods inherited from class java.lang.Object
clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait
 

Field Detail

log

protected Logger log

ARRAY

public static final OutParameter ARRAY

BIGINT

public static final OutParameter BIGINT

BINARY

public static final OutParameter BINARY

BIT

public static final OutParameter BIT

BLOB

public static final OutParameter BLOB

BOOLEAN

public static final OutParameter BOOLEAN

CHAR

public static final OutParameter CHAR

CLOB

public static final OutParameter CLOB

DATALINK

public static final OutParameter DATALINK

DATE

public static final OutParameter DATE

DECIMAL

public static final OutParameter DECIMAL

DISTINCT

public static final OutParameter DISTINCT

DOUBLE

public static final OutParameter DOUBLE

FLOAT

public static final OutParameter FLOAT

INTEGER

public static final OutParameter INTEGER

JAVA_OBJECT

public static final OutParameter JAVA_OBJECT

LONGVARBINARY

public static final OutParameter LONGVARBINARY

LONGVARCHAR

public static final OutParameter LONGVARCHAR

NULL

public static final OutParameter NULL

NUMERIC

public static final OutParameter NUMERIC

OTHER

public static final OutParameter OTHER

REAL

public static final OutParameter REAL

REF

public static final OutParameter REF

SMALLINT

public static final OutParameter SMALLINT

STRUCT

public static final OutParameter STRUCT

TIME

public static final OutParameter TIME

TIMESTAMP

public static final OutParameter TIMESTAMP

TINYINT

public static final OutParameter TINYINT

VARBINARY

public static final OutParameter VARBINARY

VARCHAR

public static final OutParameter VARCHAR
Constructor Detail

Sql

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 -

Sql

public Sql(Connection connection)
Construts an SQL instance using the given Connection. It is the callers responsibility to close the Connection after the Sql instance has been used. You can do this on the connection object directly or by calling the Connection.close() method.

Parameters:
connection -

Sql

public Sql(Sql parent)
Method Detail

newInstance

public static Sql newInstance(String url)
                       throws SQLException
A helper method which creates a new Sql instance from a JDBC connection URL

Parameters:
url -
Returns:
a new Sql instance with a connection
Throws:
SQLException

newInstance

public static Sql newInstance(String url,
                              Properties properties)
                       throws SQLException
A helper method which creates a new Sql instance from a JDBC connection URL

Parameters:
url -
Returns:
a new Sql instance with a connection
Throws:
SQLException

newInstance

public static Sql newInstance(String url,
                              Properties properties,
                              String driverClassName)
                       throws SQLException,
                              ClassNotFoundException
A helper method which creates a new Sql instance from a JDBC connection URL and driver class name

Parameters:
url -
Returns:
a new Sql instance with a connection
Throws:
SQLException
ClassNotFoundException

newInstance

public static Sql newInstance(String url,
                              String user,
                              String password)
                       throws SQLException
A helper method which creates a new Sql instance from a JDBC connection URL, username and password

Parameters:
url -
Returns:
a new Sql instance with a connection
Throws:
SQLException

newInstance

public static Sql newInstance(String url,
                              String user,
                              String password,
                              String driverClassName)
                       throws SQLException,
                              ClassNotFoundException
A helper method which creates a new Sql instance from a JDBC connection URL, username, password and driver class name

Parameters:
url -
Returns:
a new Sql instance with a connection
Throws:
SQLException
ClassNotFoundException

newInstance

public static Sql newInstance(String url,
                              String driverClassName)
                       throws SQLException,
                              ClassNotFoundException
A helper method which creates a new Sql instance from a JDBC connection URL and driver class name

Parameters:
url -
driverClassName - the class name of the driver
Returns:
a new Sql instance with a connection
Throws:
SQLException
ClassNotFoundException

loadDriver

public static void loadDriver(String driverClassName)
                       throws ClassNotFoundException
Attempts to load the JDBC driver on the thread, current or system class loaders

Parameters:
driverClassName -
Throws:
ClassNotFoundException

ARRAY

public static InParameter ARRAY(Object value)

BIGINT

public static InParameter BIGINT(Object value)

BINARY

public static InParameter BINARY(Object value)

BIT

public static InParameter BIT(Object value)

BLOB

public static InParameter BLOB(Object value)

BOOLEAN

public static InParameter BOOLEAN(Object value)

CHAR

public static InParameter CHAR(Object value)

CLOB

public static InParameter CLOB(Object value)

DATALINK

public static InParameter DATALINK(Object value)

DATE

public static InParameter DATE(Object value)

DECIMAL

public static InParameter DECIMAL(Object value)

DISTINCT

public static InParameter DISTINCT(Object value)

DOUBLE

public static InParameter DOUBLE(Object value)

FLOAT

public static InParameter FLOAT(Object value)

INTEGER

public static InParameter INTEGER(Object value)

JAVA_OBJECT

public static InParameter JAVA_OBJECT(Object value)

LONGVARBINARY

public static InParameter LONGVARBINARY(Object value)

LONGVARCHAR

public static InParameter LONGVARCHAR(Object value)

NULL

public static InParameter NULL(Object value)

NUMERIC

public static InParameter NUMERIC(Object value)

OTHER

public static InParameter OTHER(Object value)

REAL

public static InParameter REAL(Object value)

REF

public static InParameter REF(Object value)

SMALLINT

public static InParameter SMALLINT(Object value)

STRUCT

public static InParameter STRUCT(Object value)

TIME

public static InParameter TIME(Object value)

TIMESTAMP

public static InParameter TIMESTAMP(Object value)

TINYINT

public static InParameter TINYINT(Object value)

VARBINARY

public static InParameter VARBINARY(Object value)

VARCHAR

public static InParameter VARCHAR(Object value)

in

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

out

public static OutParameter out(int type)
Create a new OutParameter

Parameters:
type - the JDBC data type.
Returns:
an OutParameter

inout

public static InOutParameter inout(InParameter in)
Create an inout parameter using this in parameter.

Parameters:
in -

resultSet

public static ResultSetOutParameter resultSet(int type)
Create a new ResultSetOutParameter

Parameters:
type - the JDBC data type.
Returns:
a ResultSetOutParameter

expand

public static ExpandedVariable expand(Object object)
Creates a variable to be expanded in the Sql string rather than representing an sql parameter.

Parameters:
object -

dataSet

public DataSet dataSet(String table)

dataSet

public DataSet dataSet(Class type)

query

public void query(String sql,
                  Closure closure)
           throws SQLException
Performs the given SQL query calling the closure with the result set

Throws:
SQLException

query

public void query(String sql,
                  List params,
                  Closure closure)
           throws SQLException
Performs the given SQL query with parameters calling the closure with the result set

Throws:
SQLException

query

public void query(GString gstring,
                  Closure closure)
           throws SQLException
Performs the given SQL query calling the closure with the result set

Throws:
SQLException

queryEach

public void queryEach(String sql,
                      Closure closure)
               throws SQLException
Deprecated. please use eachRow instead

Throws:
SQLException

eachRow

public void eachRow(String sql,
                    Closure closure)
             throws SQLException
Performs the given SQL query calling the closure with each row of the result set

Throws:
SQLException

queryEach

public void queryEach(String sql,
                      List params,
                      Closure closure)
               throws SQLException
Deprecated. please use eachRow instead

Throws:
SQLException

eachRow

public void eachRow(String sql,
                    List params,
                    Closure closure)
             throws SQLException
Performs the given SQL query calling the closure with the result set

Throws:
SQLException

eachRow

public void eachRow(GString gstring,
                    Closure closure)
             throws SQLException
Performs the given SQL query calling the closure with the result set

Throws:
SQLException

queryEach

public void queryEach(GString gstring,
                      Closure closure)
               throws SQLException
Deprecated. please use eachRow instead

Throws:
SQLException

rows

public List rows(String sql)
          throws SQLException
Performs the given SQL query and return the rows of the result set

Throws:
SQLException

firstRow

public Object firstRow(String sql)
                throws SQLException
Performs the given SQL query and return the first row of the result set

Throws:
SQLException

rows

public List rows(String sql,
                 List params)
          throws SQLException
Performs the given SQL query with the list of params and return the rows of the result set

Throws:
SQLException

firstRow

public Object firstRow(String sql,
                       List params)
                throws SQLException
Performs the given SQL query with the list of params and return the first row of the result set

Throws:
SQLException

execute

public boolean execute(String sql)
                throws SQLException
Executes the given piece of SQL

Throws:
SQLException

executeUpdate

public int executeUpdate(String sql)
                  throws SQLException
Executes the given SQL update

Returns:
the number of rows updated
Throws:
SQLException

executeInsert

public List executeInsert(String sql)
                   throws SQLException
Executes the given SQL statement. See executeInsert(GString) for more details.

Parameters:
sql - The SQL statement to execute.
Returns:
A list of the auto-generated column values for each inserted row.
Throws:
SQLException

execute

public boolean execute(String sql,
                       List params)
                throws SQLException
Executes the given piece of SQL with parameters

Throws:
SQLException

executeUpdate

public int executeUpdate(String sql,
                         List params)
                  throws SQLException
Executes the given SQL update with parameters

Returns:
the number of rows updated
Throws:
SQLException

executeInsert

public List executeInsert(String sql,
                          List params)
                   throws SQLException
Executes the given SQL statement with a particular list of parameter values. See executeInsert(GString) for more details.

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.
Throws:
SQLException

execute

public boolean execute(GString gstring)
                throws SQLException
Executes the given SQL with embedded expressions inside

Throws:
SQLException

executeUpdate

public int executeUpdate(GString gstring)
                  throws SQLException
Executes the given SQL update with embedded expressions inside

Returns:
the number of rows updated
Throws:
SQLException

executeInsert

public List executeInsert(GString gstring)
                   throws SQLException

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

Returns:
A list of column values representing each row's auto-generated keys.
Throws:
SQLException

call

public int call(String sql)
         throws Exception
Performs a stored procedure call

Throws:
Exception

call

public int call(String sql,
                List params)
         throws Exception
Performs a stored procedure call with the given parameters

Throws:
Exception

call

public void call(String sql,
                 List params,
                 Closure closure)
          throws Exception
Performs a stored procedure call with the given parameters. The closure is called once with all the out parameters.

Throws:
Exception

call

public int call(GString gstring)
         throws Exception
Performs a stored procedure call with the given parameters

Throws:
Exception

call

public void call(GString gstring,
                 Closure closure)
          throws Exception
Performs a stored procedure call with the given parameters, calling the closure once with all result objects.

Throws:
Exception

close

public void close()
           throws SQLException
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 does nothing.

Throws:
SQLException

getDataSource

public DataSource getDataSource()

commit

public void commit()

rollback

public void rollback()

getUpdateCount

public int getUpdateCount()
Returns:
Returns the updateCount.

getConnection

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

withStatement

public void withStatement(Closure configureStatement)
Allows a closure to be passed in to configure the JDBC statements before they are executed to do things like set the query size etc.

Parameters:
configureStatement -

asSql

protected String asSql(GString gstring,
                       List values)
Returns:
the SQL version of the given query using ? instead of any parameter

nullify

protected String nullify(String sql)
replace ?'"? references with NULLish

Parameters:
sql -

findWhereKeyword

protected int findWhereKeyword(String sql)
Find the first 'where' keyword in the sql.

Parameters:
sql -

getParameters

protected List getParameters(GString gstring)
Returns:
extracts the parameters from the expression as a List

setParameters

protected void setParameters(List params,
                             PreparedStatement statement)
                      throws SQLException
Appends the parameters to the given statement

Throws:
SQLException

setObject

protected void setObject(PreparedStatement statement,
                         int i,
                         Object value)
                  throws SQLException
Strategy method allowing derived classes to handle types differently such as for CLOBs etc.

Throws:
SQLException

createConnection

protected Connection createConnection()
                               throws SQLException
Throws:
SQLException

closeResources

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

closeResources

protected void closeResources(Connection connection,
                              Statement statement)

configure

protected void configure(Statement statement)
Provides a hook to be able to configure JDBC statements, such as to configure

Parameters:
statement -


Copyright © 2003-2009 The Codehaus. All Rights Reserved.