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: 1.19 $
Author:
Chris Stevenson, James Strachan

Field Summary
protected  Logger log
           
 
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
protected  String asSql(GString gstring, List values)
           
 int call(GString gstring)
          Performs a stored procedure call with the given parameters
 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 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()
           
 DataSet dataSet(Class type)
           
 DataSet dataSet(String table)
           
 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
 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
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
 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 void loadDriver(String driverClassName)
          Attempts to load the JDBC driver on the thread, current or system class loaders
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
protected  String nullify(String sql)
          replace ?'"? references with NULLish
 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
 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
 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
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

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

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

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

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 int call(GString gstring)
         throws Exception
Performs a stored procedure call with the given parameters

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 -
Returns:

findWhereKeyword

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

Parameters:
sql -
Returns:

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-2010 The Codehaus. All Rights Reserved.