groovy.sql
Class Sql

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

public class Sql
extends java.lang.Object

Represents an extent of objects

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

Field Summary
private  Closure configureStatement
          allows a closure to be used to configure the statement before its use
private  javax.sql.DataSource dataSource
           
protected  java.util.logging.Logger log
           
(package private)  int updateCount
           
private  java.sql.Connection useConnection
           
private  boolean warned
          lets only warn of using deprecated methods once
 
Constructor Summary
Sql(java.sql.Connection connection)
          Construts an SQL instance using the given Connection.
Sql(javax.sql.DataSource dataSource)
          Constructs an SQL instance using the given DataSource.
Sql(Sql parent)
           
 
Method Summary
protected  java.lang.String asSql(GString gstring, java.util.List values)
           
 int call(GString gstring)
          Performs a stored procedure call with the given parameters
 int call(java.lang.String sql)
          Performs a stored procedure call
 int call(java.lang.String sql, java.util.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(java.sql.Connection connection, java.sql.Statement statement)
           
protected  void closeResources(java.sql.Connection connection, java.sql.Statement statement, java.sql.ResultSet results)
           
 void commit()
           
protected  void configure(java.sql.Statement statement)
          Provides a hook to be able to configure JDBC statements, such as to configure
protected  java.sql.Connection createConnection()
           
 DataSet dataSet(java.lang.Class type)
           
 DataSet dataSet(java.lang.String table)
           
 void eachRow(GString gstring, Closure closure)
          Performs the given SQL query calling the closure with the result set
 void eachRow(java.lang.String sql, Closure closure)
          Performs the given SQL query calling the closure with each row of the result set
 void eachRow(java.lang.String sql, java.util.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(java.lang.String sql)
          Executes the given piece of SQL
 boolean execute(java.lang.String sql, java.util.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(java.lang.String sql)
          Executes the given SQL update
 int executeUpdate(java.lang.String sql, java.util.List params)
          Executes the given SQL update with parameters
protected  int findWhereKeyword(java.lang.String sql)
          Find the first 'where' keyword in the sql.
 java.lang.Object firstRow(java.lang.String sql)
          Performs the given SQL query and return the first row of the result set
 java.lang.Object firstRow(java.lang.String sql, java.util.List params)
          Performs the given SQL query with the list of params and return the first row of the result set
 java.sql.Connection getConnection()
          If this instance was created with a single Connection then the connection is returned.
 javax.sql.DataSource getDataSource()
           
protected  java.util.List getParameters(GString gstring)
           
 int getUpdateCount()
           
static void loadDriver(java.lang.String driverClassName)
          Attempts to load the JDBC driver on the thread, current or system class loaders
static Sql newInstance(java.lang.String url)
          A helper method which creates a new Sql instance from a JDBC connection URL
static Sql newInstance(java.lang.String url, java.util.Properties properties)
          A helper method which creates a new Sql instance from a JDBC connection URL
static Sql newInstance(java.lang.String url, java.util.Properties properties, java.lang.String driverClassName)
          A helper method which creates a new Sql instance from a JDBC connection URL and driver class name
static Sql newInstance(java.lang.String url, java.lang.String driverClassName)
          A helper method which creates a new Sql instance from a JDBC connection URL and driver class name
static Sql newInstance(java.lang.String url, java.lang.String user, java.lang.String password)
          A helper method which creates a new Sql instance from a JDBC connection URL, username and password
static Sql newInstance(java.lang.String url, java.lang.String user, java.lang.String password, java.lang.String driverClassName)
          A helper method which creates a new Sql instance from a JDBC connection URL, username, password and driver class name
protected  java.lang.String nullify(java.lang.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(java.lang.String sql, Closure closure)
          Performs the given SQL query calling the closure with the result set
 void query(java.lang.String sql, java.util.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(java.lang.String sql, Closure closure)
          Deprecated. please use eachRow instead
 void queryEach(java.lang.String sql, java.util.List params, Closure closure)
          Deprecated. please use eachRow instead
 void rollback()
           
 java.util.List rows(java.lang.String sql)
          Performs the given SQL query and return the rows of the result set
 java.util.List rows(java.lang.String sql, java.util.List params)
          Performs the given SQL query with the list of params and return the rows of the result set
protected  void setObject(java.sql.PreparedStatement statement, int i, java.lang.Object value)
          Strategy method allowing derived classes to handle types differently such as for CLOBs etc.
protected  void setParameters(java.util.List params, java.sql.PreparedStatement statement)
          Appends the parameters to the given statement
private  void warnDeprecated()
           
 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 java.util.logging.Logger log

dataSource

private javax.sql.DataSource dataSource

useConnection

private java.sql.Connection useConnection

warned

private boolean warned
lets only warn of using deprecated methods once


updateCount

int updateCount

configureStatement

private Closure configureStatement
allows a closure to be used to configure the statement before its use

Constructor Detail

Sql

public Sql(javax.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(java.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(java.lang.String url)
                       throws java.sql.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:
java.sql.SQLException

newInstance

public static Sql newInstance(java.lang.String url,
                              java.util.Properties properties)
                       throws java.sql.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:
java.sql.SQLException

newInstance

public static Sql newInstance(java.lang.String url,
                              java.util.Properties properties,
                              java.lang.String driverClassName)
                       throws java.sql.SQLException,
                              java.lang.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:
java.sql.SQLException
java.lang.ClassNotFoundException

newInstance

public static Sql newInstance(java.lang.String url,
                              java.lang.String user,
                              java.lang.String password)
                       throws java.sql.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:
java.sql.SQLException

newInstance

public static Sql newInstance(java.lang.String url,
                              java.lang.String user,
                              java.lang.String password,
                              java.lang.String driverClassName)
                       throws java.sql.SQLException,
                              java.lang.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:
java.sql.SQLException
java.lang.ClassNotFoundException

newInstance

public static Sql newInstance(java.lang.String url,
                              java.lang.String driverClassName)
                       throws java.sql.SQLException,
                              java.lang.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:
java.sql.SQLException
java.lang.ClassNotFoundException

loadDriver

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

Parameters:
driverClassName -
Throws:
java.lang.ClassNotFoundException

dataSet

public DataSet dataSet(java.lang.String table)

dataSet

public DataSet dataSet(java.lang.Class type)

query

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

Throws:
java.sql.SQLException

query

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

Throws:
java.sql.SQLException

query

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

Throws:
java.sql.SQLException

queryEach

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

Throws:
java.sql.SQLException

eachRow

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

Throws:
java.sql.SQLException

queryEach

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

Throws:
java.sql.SQLException

eachRow

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

Throws:
java.sql.SQLException

eachRow

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

Throws:
java.sql.SQLException

queryEach

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

Throws:
java.sql.SQLException

rows

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

Throws:
java.sql.SQLException

firstRow

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

Throws:
java.sql.SQLException

rows

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

Throws:
java.sql.SQLException

firstRow

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

Throws:
java.sql.SQLException

execute

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

Throws:
java.sql.SQLException

executeUpdate

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

Returns:
the number of rows updated
Throws:
java.sql.SQLException

execute

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

Throws:
java.sql.SQLException

executeUpdate

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

Returns:
the number of rows updated
Throws:
java.sql.SQLException

execute

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

Throws:
java.sql.SQLException

executeUpdate

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

Returns:
the number of rows updated
Throws:
java.sql.SQLException

call

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

Throws:
java.lang.Exception

call

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

Throws:
java.lang.Exception

call

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

Throws:
java.lang.Exception

close

public void close()
           throws java.sql.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:
java.sql.SQLException

getDataSource

public javax.sql.DataSource getDataSource()

commit

public void commit()

rollback

public void rollback()

getUpdateCount

public int getUpdateCount()
Returns:
Returns the updateCount.

getConnection

public java.sql.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 java.lang.String asSql(GString gstring,
                                 java.util.List values)
Returns:
the SQL version of the given query using ? instead of any parameter

nullify

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

Parameters:
sql -
Returns:

findWhereKeyword

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

Parameters:
sql -
Returns:

getParameters

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

setParameters

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

Throws:
java.sql.SQLException

setObject

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

Throws:
java.sql.SQLException

createConnection

protected java.sql.Connection createConnection()
                                        throws java.sql.SQLException
Throws:
java.sql.SQLException

closeResources

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

closeResources

protected void closeResources(java.sql.Connection connection,
                              java.sql.Statement statement)

warnDeprecated

private void warnDeprecated()

configure

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

Parameters:
statement -


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