public Sql(DataSource dataSource) 
{
        this.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 - the DataSource to use

 public Sql(Connection connection) 
{
        if (connection == null) {
            throw new NullPointerException("Must specify a non-null Connection");
        }
        this.useConnection = connection;
}

Constructs an SQL instance using the given Connection. It is the caller's 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 - the Connection to use

 public Sql(Sql parent) 
{
        this.dataSource = parent.dataSource;
        this.useConnection = parent.useConnection;
}

 public static InParameter ARRAY(Object value) 
{
 return in(Types.ARRAY, value);
}

 public static InParameter BIGINT(Object value) 
{
 return in(Types.BIGINT, value);
}

 public static InParameter BINARY(Object value) 
{
 return in(Types.BINARY, value);
}

 public static InParameter BIT(Object value) 
{
 return in(Types.BIT, value);
}

 public static InParameter BLOB(Object value) 
{
 return in(Types.BLOB, value);
}

 public static InParameter BOOLEAN(Object value) 
{
 return in(Types.BOOLEAN, value);
}

 public static InParameter CHAR(Object value) 
{
 return in(Types.CHAR, value);
}

 public static InParameter CLOB(Object value) 
{
 return in(Types.CLOB, value);
}

 public static InParameter DATALINK(Object value) 
{
 return in(Types.DATALINK, value);
}

 public static InParameter DATE(Object value) 
{
 return in(Types.DATE, value);
}

 public static InParameter DECIMAL(Object value) 
{
 return in(Types.DECIMAL, value);
}

 public static InParameter DISTINCT(Object value) 
{
 return in(Types.DISTINCT, value);
}

 public static InParameter DOUBLE(Object value) 
{
 return in(Types.DOUBLE, value);
}

 public static InParameter FLOAT(Object value) 
{
 return in(Types.FLOAT, value);
}

 public static InParameter INTEGER(Object value) 
{
 return in(Types.INTEGER, value);
}

 public static InParameter JAVA_OBJECT(Object value) 
{
 return in(Types.JAVA_OBJECT, value);
}

 public static InParameter LONGVARBINARY(Object value) 
{
 return in(Types.LONGVARBINARY, value);
}

 public static InParameter LONGVARCHAR(Object value) 
{
 return in(Types.LONGVARCHAR, value);
}

 public static InParameter NULL(Object value) 
{
 return in(Types.NULL, value);
}

 public static InParameter NUMERIC(Object value) 
{
 return in(Types.NUMERIC, value);
}

 public static InParameter OTHER(Object value) 
{
 return in(Types.OTHER, value);
}

 public static InParameter REAL(Object value) 
{
 return in(Types.REAL, value);
}

 public static InParameter REF(Object value) 
{
 return in(Types.REF, value);
}

 public static InParameter SMALLINT(Object value) 
{
 return in(Types.SMALLINT, value);
}

 public static InParameter STRUCT(Object value) 
{
 return in(Types.STRUCT, value);
}

 public static InParameter TIME(Object value) 
{
 return in(Types.TIME, value);
}

 public static InParameter TIMESTAMP(Object value) 
{
 return in(Types.TIMESTAMP, value);
}

 public static InParameter TINYINT(Object value) 
{
 return in(Types.TINYINT, value);
}

 public static InParameter VARBINARY(Object value) 
{
 return in(Types.VARBINARY, value);
}

 public static InParameter VARCHAR(Object value) 
{
 return in(Types.VARCHAR, value);
}

 protected List<GroovyRowResult> asList(String sql,
    ResultSet rs) throws SQLException 
{
        List< GroovyRowResult > results = new ArrayList< GroovyRowResult >();
        try {
            while (rs.next()) {
                results.add(DefaultGroovyMethods.toRowResult(rs));
            }
            return (results);
        } catch (SQLException e) {
            log.log(Level.INFO, "Failed to retrieve row from ResultSet for: " + sql, e);
            throw e;
        } finally {
            rs.close();
        }
}

Hook to allow derived classes to override list of result collection behavior. The default behavior is to return a list of GroovyRowResult objects corresponding to each row in the ResultSet.

 protected String asSql(GString gstring,
    List<Object> values) 
{
        String[] strings = gstring.getStrings();
        if (strings.length < = 0) {
            throw new IllegalArgumentException("No SQL specified in GString: " + gstring);
        }
        boolean nulls = false;
        StringBuffer buffer = new StringBuffer();
        boolean warned = false;
        Iterator< Object > iter = values.iterator();
        for (int i = 0; i <  strings.length; i++) {
            String text = strings[i];
            if (text != null) {
                buffer.append(text);
            }
            if (iter.hasNext()) {
                Object value = iter.next();
                if (value != null) {
                    if (value instanceof ExpandedVariable) {
                        buffer.append(((ExpandedVariable) value).getObject());
                        iter.remove();
                    } else {
                        boolean validBinding = true;
                        if (i <  strings.length - 1) {
                            String nextText = strings[i + 1];
                            if ((text.endsWith("\"") || text.endsWith("'")) && (nextText.startsWith("'") || nextText.startsWith("\""))) {
                                if (!warned) {
                                    log.warning("In Groovy SQL please do not use quotes around dynamic expressions " +
                                            "(which start with $) as this means we cannot use a JDBC PreparedStatement " +
                                            "and so is a security hole. Groovy has worked around your mistake but the security hole is still there. " +
                                            "The expression so far is: " + buffer.toString() + "?" + nextText);
                                    warned = true;
                                }
                                buffer.append(value);
                                iter.remove();
                                validBinding = false;
                            }
                        }
                        if (validBinding) {
                            buffer.append("?");
                        }
                    }
                } else {
                    nulls = true;
                    iter.remove();
                    buffer.append("?'\"?"); // will replace these with nullish values
                }
            }
        }
        String sql = buffer.toString();
        if (nulls) {
            sql = nullify(sql);
        }
        return sql;
}

Hook to allow derived classes to override sql generation from GStrings.

 public synchronized  void cacheConnection(Closure closure) throws SQLException 
{
        boolean savedCacheConnection = cacheConnection;
        cacheConnection = true;
        Connection connection = null;
        try {
            connection = createConnection();
            callClosurePossiblyWithConnection(closure, connection);
        }
        finally {
            cacheConnection = false;
            closeResources(connection, null);
            cacheConnection = savedCacheConnection;
            if (dataSource != null && !cacheConnection) {
                useConnection = null;
            }
        }
}

Caches the connection used while the closure is active. If the closure takes a single argument, it will be called with the connection, otherwise it will be called with no arguments.

 public synchronized  void cacheStatements(Closure closure) throws SQLException 
{
        boolean savedCacheStatements = cacheStatements;
        cacheStatements = true;
        Connection connection = null;
        try {
            connection = createConnection();
            callClosurePossiblyWithConnection(closure, connection);
        }
        finally {
            cacheStatements = false;
            closeResources(connection, null);
            cacheStatements = savedCacheStatements;
        }
}

Caches every created preparedStatement in Closure closure
Every cached preparedStatement is closed after closure has been called. If the closure takes a single argument, it will be called with the connection, otherwise it will be called with no arguments.

 public int call(String sql) throws Exception 
{
        return call(sql, EMPTY_LIST);
}

Performs a stored procedure call.

Example usage (tested with MySQL) - suppose we have the following stored procedure:

sql.execute """
    CREATE PROCEDURE HouseSwap(_first1 VARCHAR(50), _first2 VARCHAR(50))
    BEGIN
        DECLARE _loc1 INT;
        DECLARE _loc2 INT;
        SELECT location_id into _loc1 FROM PERSON where firstname = _first1;
        SELECT location_id into _loc2 FROM PERSON where firstname = _first2;
        UPDATE PERSON
        set location_id = case firstname
            when _first1 then _loc2
            when _first2 then _loc1
        end
        where (firstname = _first1 OR firstname = _first2);
    END
"""

then you can invoke the procedure as follows:

def rowsChanged = sql.call("{call HouseSwap('Guillaume', 'Paul')}")
assert rowsChanged == 2

 public int call(GString gstring) throws Exception 
{
        List< Object > params = getParameters(gstring);
        String sql = asSql(gstring, params);
        return call(sql, params);
}

Performs a stored procedure call with the given embedded parameters.

Example usage - see #call(String) for more details about creating a HouseSwap(IN name1, IN name2) stored procedure. Once created, it can be called like this:

def p1 = 'Paul'
def p2 = 'Guillaume'
def rowsChanged = sql.call("{call HouseSwap($p1, $p2)}")
assert rowsChanged == 2

 public int call(String sql,
    List<Object> params) throws Exception 
{
        Connection connection = createConnection();
        CallableStatement statement = connection.prepareCall(sql);
        try {
            log.fine(sql);
            setParameters(params, statement);
            configure(statement);
            return statement.executeUpdate();
        }
        catch (SQLException e) {
            log.log(Level.FINE, "Failed to execute: " + sql, e);
            throw e;
        }
        finally {
            closeResources(connection, statement);
        }
}

Performs a stored procedure call with the given parameters.

Example usage - see #call(String) for more details about creating a HouseSwap(IN name1, IN name2) stored procedure. Once created, it can be called like this:

def rowsChanged = sql.call("{call HouseSwap(?, ?)}", ['Guillaume', 'Paul'])
assert rowsChanged == 2

 public  void call(GString gstring,
    Closure closure) throws Exception 
{
        List< Object > params = getParameters(gstring);
        String sql = asSql(gstring, params);
        call(sql, params, closure);
}

Performs a stored procedure call with the given parameters, calling the closure once with all result objects.

See #call(String, List, Closure) for more details about creating a Hemisphere(IN first, IN last, OUT dwells) stored procedure. Once created, it can be called like this:

def first = 'Scott'
def last = 'Davis'
sql.call "{call Hemisphere($first, $last, ${Sql.VARCHAR})}", { dwells ->
    println dwells
}

As another example, see #call(String, List, Closure) for more details about creating a FullName(IN first) stored function. Once created, it can be called like this:

def first = 'Sam'
sql.call("{$Sql.VARCHAR = call FullName($first)}") { name ->
    assert name == 'Sam Pullara'
}

 public  void call(String sql,
    List<Object> params,
    Closure closure) throws Exception 
{
        Connection connection = createConnection();
        CallableStatement statement = connection.prepareCall(sql);
        List< GroovyResultSet > resultSetResources = new ArrayList< GroovyResultSet >();
        try {
            log.fine(sql);
            setParameters(params, statement);
            // TODO handle multiple results and mechanism for retrieving ResultSet if any (GROOVY-3048)
            statement.execute();
            List< Object > results = new ArrayList< Object >();
            int indx = 0;
            int inouts = 0;
            for (Object value : params) {
                if (value instanceof OutParameter) {
                    if (value instanceof ResultSetOutParameter) {
                        GroovyResultSet resultSet = CallResultSet.getImpl(statement, indx);
                        resultSetResources.add(resultSet);
                        results.add(resultSet);
                    } else {
                        Object o = statement.getObject(indx + 1);
                        if (o instanceof ResultSet) {
                            GroovyResultSet resultSet = new GroovyResultSetProxy((ResultSet) o).getImpl();
                            results.add(resultSet);
                            resultSetResources.add(resultSet);
                        } else {
                            results.add(o);
                        }
                    }
                    inouts++;
                }
                indx++;
            }
            closure.call(results.toArray(new Object[inouts]));
        } catch (SQLException e) {
            log.log(Level.WARNING, "Failed to execute: " + sql, e);
            throw e;
        } finally {
            closeResources(connection, statement);
            for (GroovyResultSet rs : resultSetResources) {
                closeResources(null, null, rs);
            }
        }
}

Performs a stored procedure call with the given parameters. The closure is called once with all the out parameters.

Example usage - suppose we create a stored procedure (ignore its simplistic implementation):

// Tested with MySql 5.0.75
sql.execute """
    CREATE PROCEDURE Hemisphere(
        IN p_firstname VARCHAR(50),
        IN p_lastname VARCHAR(50),
        OUT ans VARCHAR(50))
    BEGIN
    DECLARE loc INT;
    SELECT location_id into loc FROM PERSON where firstname = p_firstname and lastname = p_lastname;
    CASE loc
        WHEN 40 THEN
            SET ans = 'Southern Hemisphere';
        ELSE
            SET ans = 'Northern Hemisphere';
    END CASE;
    END;
"""

we can now call the stored procedure as follows:

sql.call '{call Hemisphere(?, ?, ?)}', ['Guillaume', 'Laforge', Sql.VARCHAR], { dwells ->
    println dwells
}

which will output 'Northern Hemisphere'.

We can also access stored functions with scalar return values where the return value will be treated as an OUT parameter. Here are examples for various databases for creating such a procedure:

// Tested with MySql 5.0.75
sql.execute """
    create function FullName(p_firstname VARCHAR(40)) returns VARCHAR(80)
    begin
        declare ans VARCHAR(80);
        SELECT CONCAT(firstname, ' ', lastname) INTO ans FROM PERSON WHERE firstname = p_firstname;
        return ans;
    end
"""

// Tested with MS SQLServer Express 2008
sql.execute """
    {@code create function FullName(@firstname VARCHAR(40)) returns VARCHAR(80)}
    begin
        declare {@code @ans} VARCHAR(80)
        {@code SET @ans = (SELECT firstname + ' ' + lastname FROM PERSON WHERE firstname = @firstname)}
        return {@code @ans}
    end
"""

// Tested with Oracle XE 10g
sql.execute """
    create function FullName(p_firstname VARCHAR) return VARCHAR is
    ans VARCHAR(80);
    begin
        SELECT CONCAT(CONCAT(firstname, ' '), lastname) INTO ans FROM PERSON WHERE firstname = p_firstname;
        return ans;
    end;
"""

and here is how you access the stored function for all databases:

sql.call("{? = call FullName(?)}", [Sql.VARCHAR, 'Sam']) { name ->
    assert name == 'Sam Pullara'
}

 public  void close() throws SQLException 
{
        if (useConnection == null) {
            log.log(Level.FINEST, "Close operation not supported when using datasets - attempt to close ignored");
            return;
        }
        try {
            useConnection.close();
        }
        catch (SQLException e) {
            log.log(Level.SEVERE, "Caught exception closing connection: " + e, e);
            throw e;
        }
}

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.

 protected  void closeResources(Connection connection,
    Statement statement) 
{
        if (cacheStatements) return;
        if (statement != null) {
            try {
                statement.close();
            }
            catch (SQLException e) {
                log.log(Level.INFO, "Caught exception closing statement: " + e.getMessage() + " - continuing", e);
            }
        }
        if (cacheConnection) return;
        if (connection != null && dataSource != null) {
            try {
                connection.close();
            }
            catch (SQLException e) {
                log.log(Level.INFO, "Caught exception closing connection: " + e.getMessage() + " - continuing", e);
            }
        }
}

An extension point allowing the behavior of resource closing to be overridden in derived classes.

 protected  void closeResources(Connection connection,
    Statement statement,
    ResultSet results) 
{
        if (results != null) {
            try {
                results.close();
            }
            catch (SQLException e) {
                log.log(Level.INFO, "Caught exception closing resultSet: " + e.getMessage() + " - continuing", e);
            }
        }
        closeResources(connection, statement);
}

An extension point allowing derived classes to change the behavior of resource closing.

 public  void commit() throws SQLException 
{
        if (useConnection == null) {
            log.log(Level.FINEST, "Commit operation not supported when using datasets unless using withTransaction or cacheConnection - attempt to commit ignored");
            return;
        }
        try {
            useConnection.commit();
        }
        catch (SQLException e) {
            log.log(Level.SEVERE, "Caught exception committing connection: " + e, e);
            throw e;
        }
}

If this SQL object was created with a Connection then this method commits the connection. If this SQL object was created from a DataSource then this method does nothing.

 protected  void configure(Statement statement) 
{
        // for thread safety, grab local copy
        Closure configureStatement = this.configureStatement;
        if (configureStatement != null) {
            configureStatement.call(statement);
        }
}

Provides a hook for derived classes to be able to configure JDBC statements. Default behavior is to call a previously saved closure, if any, using the statement as a parameter.

 protected Connection createConnection() throws SQLException 
{
        if ((cacheStatements || cacheConnection) && useConnection != null) {
            return useConnection;
        }
        if (dataSource != null) {
            // Use a doPrivileged here as many different properties need to be
            // read, and the policy shouldn't have to list them all.
            Connection con;
            try {
                con = AccessController.doPrivileged(new PrivilegedExceptionAction< Connection >() {
                    public Connection run() throws SQLException {
                        return dataSource.getConnection();
                    }
                });
            }
            catch (PrivilegedActionException pae) {
                Exception e = pae.getException();
                if (e instanceof SQLException) {
                    throw (SQLException) e;
                } else {
                    throw (RuntimeException) e;
                }
            }
            if (cacheStatements || cacheConnection) {
                useConnection = con;
            }
            return con;
        }
        return useConnection;
}

An extension point allowing derived classes to change the behavior of connection creation. The default behavior is to either use the supplied connection or obtain it from the supplied datasource.

 protected AbstractQueryCommand createPreparedQueryCommand(String sql,
    List<Object> queryParams) 
{
    	return new PreparedQueryCommand(sql, queryParams);
}

Factory for the PreparedQueryCommand command pattern object allows subclass to supply implementations of the command class.

 protected AbstractQueryCommand createQueryCommand(String sql) 
{
    	return new QueryCommand(sql);
}

Factory for the QueryCommand command pattern object allows subclasses to supply implementations of the command class. The factory will be used in a pattern similar to

AbstractQueryCommand q = createQueryCommand("update TABLE set count = 0) where count is null");
try {
	   ResultSet rs = q.execute();
    return asList(rs);
} finally {
    q.closeResources();
}

 public DataSet dataSet(String table) 
{
        return new DataSet(this, table);
}

 public DataSet dataSet(Class<?> type) 
{
        return new DataSet(this, type);
}

 public  void eachRow(String sql,
    Closure closure) throws SQLException 
{
        eachRow(sql, (Closure) null, closure);
}

Performs the given SQL query calling the given Closure with each row of the result set. The row will be a GroovyRowResult which is a Map that also supports accessing the fields using ordinal index values.

Example usages:

sql.eachRow("select * from PERSON where firstname like 'S%'") { row ->
   println "$row.firstname ${row[2]}}"
}

sql.eachRow "call my_stored_proc_returning_resultset()", {
    println it.firstname
}

 public  void eachRow(GString gstring,
    Closure closure) throws SQLException 
{
        List< Object > params = getParameters(gstring);
        String sql = asSql(gstring, params);
        eachRow(sql, params, closure);
}

Performs the given SQL query calling the given Closure with each row of the result set. The row will be a GroovyRowResult which is a Map that also supports accessing the fields using ordinal index values. The query may contain GString expressions.

Example usage:

def location = 25
sql.eachRow("select * from PERSON where location_id < $location") { row ->
    println row.firstname
}

 public  void eachRow(String sql,
    Closure metaClosure,
    Closure rowClosure) throws SQLException 
{
        Connection connection = createConnection();
        Statement statement = getStatement(connection, sql);
        ResultSet results = null;
        try {
            log.fine(sql);
            results = statement.executeQuery(sql);
            if (metaClosure != null) metaClosure.call(results.getMetaData());
            GroovyResultSet groovyRS = new GroovyResultSetProxy(results).getImpl();
            while (groovyRS.next()) {
                rowClosure.call(groovyRS);
            }
        } catch (SQLException e) {
            log.log(Level.FINE, "Failed to execute: " + sql, e);
            throw e;
        } finally {
            closeResources(connection, statement, results);
        }
}

Performs the given SQL query calling the given rowClosure with each row of the result set. The row will be a GroovyRowResult which is a Map that also supports accessing the fields using ordinal index values. In addition, the metaClosure will be called once passing in the ResultSetMetaData as argument.

Example usage:

def printColNames = { meta ->
    (1..meta.columnCount).each {
        print meta.getColumnLabel(it).padRight(20)
    }
    println()
}
def printRow = { row ->
    row.toRowResult().values().each{ print it.toString().padRight(20) }
    println()
}
sql.eachRow("select * from PERSON", printColNames, printRow)

 public  void eachRow(String sql,
    List<Object> params,
    Closure closure) throws SQLException 
{
        Connection connection = createConnection();
        PreparedStatement statement = null;
        ResultSet results = null;
        try {
            log.fine(sql);
            statement = getPreparedStatement(connection, sql, params);
            results = statement.executeQuery();
            GroovyResultSet groovyRS = new GroovyResultSetProxy(results).getImpl();
            while (groovyRS.next()) {
                closure.call(groovyRS);
            }
        }
        catch (SQLException e) {
            log.log(Level.FINE, "Failed to execute: " + sql, e);
            throw e;
        }
        finally {
            closeResources(connection, statement, results);
        }
}

Performs the given SQL query calling the given Closure with each row of the result set. The row will be a GroovyRowResult which is a Map that also supports accessing the fields using ordinal index values. The query may contain placeholder question marks which match the given list of parameters.

Example usage:

sql.eachRow("select * from PERSON where lastname like ?", ['%a%']) { row ->
    println "${row[1]} $row.lastname"
}

 public boolean execute(String sql) throws SQLException 
{
        Connection connection = createConnection();
        Statement statement = null;
        try {
            log.fine(sql);
            statement = getStatement(connection, sql);
            // TODO handle multiple results
            boolean isResultSet = statement.execute(sql);
            this.updateCount = statement.getUpdateCount();
            return isResultSet;
        }
        catch (SQLException e) {
            log.log(Level.FINE, "Failed to execute: " + sql, e);
            throw e;
        }
        finally {
            closeResources(connection, statement);
        }
}

Executes the given piece of SQL. Also saves the updateCount, if any, for subsequent examination.

Example usages:

sql.execute "drop table if exists PERSON"

sql.execute """
    create table PERSON (
        id integer not null,
        firstname varchar(100),
        lastname varchar(100),
        location_id integer
    )
"""

sql.execute """
    insert into PERSON (id, firstname, lastname, location_id) values (4, 'Paul', 'King', 40)
"""
assert sql.updateCount == 1

 public boolean execute(GString gstring) throws SQLException 
{
        List< Object > params = getParameters(gstring);
        String sql = asSql(gstring, params);
        return execute(sql, params);
}

Executes the given SQL with embedded expressions inside. Also saves the updateCount, if any, for subsequent examination.

Example usage:

def scott = [firstname: "Scott", lastname: "Davis", id: 5, location_id: 50]
sql.execute """
    insert into PERSON (id, firstname, lastname, location_id) values ($scott.id, $scott.firstname, $scott.lastname, $scott.location_id)
"""
assert sql.updateCount == 1

 public boolean execute(String sql,
    List<Object> params) throws SQLException 
{
        Connection connection = createConnection();
        PreparedStatement statement = null;
        try {
            log.fine(sql);
            statement = getPreparedStatement(connection, sql, params);
            // TODO handle multiple results
            boolean isResultSet = statement.execute();
            this.updateCount = statement.getUpdateCount();
            return isResultSet;
        }
        catch (SQLException e) {
            log.log(Level.FINE, "Failed to execute: " + sql, e);
            throw e;
        }
        finally {
            closeResources(connection, statement);
        }
}

Executes the given piece of SQL with parameters. Also saves the updateCount, if any, for subsequent examination.

Example usage:

sql.execute """
    insert into PERSON (id, firstname, lastname, location_id) values (?, ?, ?, ?)
""", [1, "Guillaume", "Laforge", 10]
assert sql.updateCount == 1

 public List<Object> executeInsert(String sql) throws SQLException 
{
        Connection connection = createConnection();
        Statement statement = null;
        try {
            log.fine(sql);
            statement = getStatement(connection, sql);
            this.updateCount = statement.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
            ResultSet keys = statement.getGeneratedKeys();
            return calculateKeys(keys);
        }
        catch (SQLException e) {
            log.log(Level.FINE, "Failed to execute: " + sql, e);
            throw e;
        }
        finally {
            closeResources(connection, statement);
        }
}

Executes the given SQL statement (typically an INSERT statement). Use this variant when you want to receive the values of any auto-generated columns, such as an autoincrement ID field. See #executeInsert(GString) for more details.

 public List<Object> executeInsert(GString gstring) throws SQLException 
{
        List< Object > params = getParameters(gstring);
        String sql = asSql(gstring, params);
        return executeInsert(sql, params);
}

Executes the given SQL statement (typically an INSERT statement). Use this variant when you want to receive the values of any auto-generated columns, such as an autoincrement ID field. The query may contain GString expressions.

Generated key 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.executeInsert("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 List<Object> executeInsert(String sql,
    List<Object> params) throws SQLException 
{
        Connection connection = createConnection();
        PreparedStatement statement = null;
        try {
            log.fine(sql);
            statement = getPreparedStatement(connection, sql, params, Statement.RETURN_GENERATED_KEYS);
            this.updateCount = statement.executeUpdate();
            ResultSet keys = statement.getGeneratedKeys();
            return calculateKeys(keys);
        }
        catch (SQLException e) {
            log.log(Level.FINE, "Failed to execute: " + sql, e);
            throw e;
        }
        finally {
            closeResources(connection, statement);
        }
}

Executes the given SQL statement (typically an INSERT statement). Use this variant when you want to receive the values of any auto-generated columns, such as an autoincrement ID field. The query may contain placeholder question marks which match the given list of parameters. See #executeInsert(GString) for more details.

 protected final ResultSet executePreparedQuery(String sql,
    List<Object> params) throws SQLException 
{
        AbstractQueryCommand command = createPreparedQueryCommand(sql, params);
        ResultSet rs = null;
        try {
        	rs = command.execute();
        } finally {
        	command.closeResources();
        }
        return rs;
}

Hook to allow derived classes to access ResultSet returned from query.

 protected final ResultSet executeQuery(String sql) throws SQLException 
{
        AbstractQueryCommand command = createQueryCommand(sql);
        ResultSet rs = null;
        try {
        	rs = command.execute();
        } finally {
        	command.closeResources();
        }
        return rs;
}

Hook to allow derived classes to access ResultSet returned from query.

 public int executeUpdate(String sql) throws SQLException 
{
        Connection connection = createConnection();
        Statement statement = null;
        try {
            log.fine(sql);
            statement = getStatement(connection, sql);
            this.updateCount = statement.executeUpdate(sql);
            return this.updateCount;
        }
        catch (SQLException e) {
            log.log(Level.FINE, "Failed to execute: " + sql, e);
            throw e;
        }
        finally {
            closeResources(connection, statement);
        }
}

Executes the given SQL update.

 public int executeUpdate(GString gstring) throws SQLException 
{
        List< Object > params = getParameters(gstring);
        String sql = asSql(gstring, params);
        return executeUpdate(sql, params);
}

Executes the given SQL update with embedded expressions inside.

 public int executeUpdate(String sql,
    List<Object> params) throws SQLException 
{
        Connection connection = createConnection();
        PreparedStatement statement = null;
        try {
            log.fine(sql);
            statement = getPreparedStatement(connection, sql, params);
            this.updateCount = statement.executeUpdate();
            return this.updateCount;
        }
        catch (SQLException e) {
            log.log(Level.FINE, "Failed to execute: " + sql, e);
            throw e;
        }
        finally {
            closeResources(connection, statement);
        }
}

Executes the given SQL update with parameters.

 public static ExpandedVariable expand(Object object) 
{
        return new ExpandedVariable() {
            public Object getObject() {
                return object;
            }
        };
}

When using GString SQL queries, allows a variable to be expanded in the Sql string rather than representing an sql parameter.

Example usage:

def fieldName = 'firstname'
def fieldOp = Sql.expand('like')
def fieldVal = '%a%'
sql.query "select * from PERSON where ${Sql.expand(fieldName)} $fieldOp ${fieldVal}", { ResultSet rs ->
    while (rs.next()) println rs.getString('firstname')
}
// query will be 'select * from PERSON where firstname like ?'
// params will be [fieldVal]

 protected int findWhereKeyword(String sql) 
{
        char[] chars = sql.toLowerCase().toCharArray();
        char[] whereChars = "where".toCharArray();
        int i = 0;
        boolean inString = false; //TODO: Cater for comments?
        int inWhere = 0;
        while (i <  chars.length) {
            switch (chars[i]) {
                case '\'':
                    inString = !inString;
                    break;
                default:
                    if (!inString && chars[i] == whereChars[inWhere]) {
                        inWhere++;
                        if (inWhere == whereChars.length) {
                            return i;
                        }
                    }
            }
            i++;
        }
        return -1;
}

Hook to allow derived classes to override where clause sniffing. Default behavior is to find the first 'where' keyword in the sql doing simple avoidance of the word 'where' within quotes.

 public Object firstRow(String sql) throws SQLException 
{
        List< GroovyRowResult > rows = rows(sql);
        if (rows.isEmpty()) return null;
        return (rows.get(0));
}

Performs the given SQL query and return the first row of the result set.

Example usage:

def ans = sql.firstRow("select * from PERSON where firstname like 'S%'")
println ans.firstname

 public Object firstRow(GString gstring) throws SQLException 
{
        List< Object > params = getParameters(gstring);
        String sql = asSql(gstring, params);
        return firstRow(sql, params);
}

Performs the given SQL query and return the first row of the result set. The query may contain GString expressions.

Example usage:

def location = 25
def ans = sql.firstRow("select * from PERSON where location_id < $location")
println ans.firstname

 public Object firstRow(String sql,
    List<Object> params) throws SQLException 
{
        List< GroovyRowResult > rows = rows(sql, params);
        if (rows.isEmpty()) return null;
        return rows.get(0);
}

Performs the given SQL query and return the first row of the result set. The query may contain placeholder question marks which match the given list of parameters.

Example usages:

def ans = sql.firstRow("select * from PERSON where lastname like ?", ['%a%'])
println ans.firstname

If your database returns scalar functions as ResultSets, you can also use firstRow to gain access to stored procedure results, e.g. using hsqldb 1.9 RC4:

sql.execute """
    create function FullName(p_firstname VARCHAR(40)) returns VARCHAR(80)
    BEGIN atomic
    DECLARE ans VARCHAR(80);
    SET ans = (SELECT firstname || ' ' || lastname FROM PERSON WHERE firstname = p_firstname);
    RETURN ans;
    END
"""

assert sql.firstRow("{call FullName(?)}", ['Sam'])[0] == 'Sam Pullara'

 public Connection getConnection() 
{
        return useConnection;
}

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

 public DataSource getDataSource() 
{
        return dataSource;
}

 protected List<Object> getParameters(GString gstring) 
{
        return new ArrayList< Object >(Arrays.asList(gstring.getValues()));
}

Hook to allow derived classes to override behavior associated with extracting params from a GString.

 public int getResultSetConcurrency() 
{
        return resultSetConcurrency;
}

Gets the resultSetConcurrency for statements created using the connection.

 public int getResultSetHoldability() 
{
        return resultSetHoldability;
}

Gets the resultSetHoldability for statements created using the connection.

 public int getResultSetType() 
{
        return resultSetType;
}

Gets the resultSetType for statements created using the connection.

 public int getUpdateCount() 
{
        return updateCount;
}

 public static InParameter in(int type,
    Object value) 
{
        return new InParameter() {
            public int getType() {
                return type;
            }
            public Object getValue() {
                return value;
            }
        };
}

Create a new InParameter

 public static InOutParameter inout(InParameter in) 
{
        return new InOutParameter() {
            public int getType() {
                return in.getType();
            }
            public Object getValue() {
                return in.getValue();
            }
        };
}

Create an inout parameter using this in parameter.

 public boolean isCacheStatements() 
{
        return cacheStatements;
}

 public static  void loadDriver(String driverClassName) throws ClassNotFoundException 
{
        // let's try the thread context class loader first
        // let's try to use the system class loader
        try {
            Class.forName(driverClassName);
        }
        catch (ClassNotFoundException e) {
            try {
                Thread.currentThread().getContextClassLoader().loadClass(driverClassName);
            }
            catch (ClassNotFoundException e2) {
                // now let's try the classloader which loaded us
                try {
                    Sql.class.getClassLoader().loadClass(driverClassName);
                }
                catch (ClassNotFoundException e3) {
                    throw e;
                }
            }
        }
}

Attempts to load the JDBC driver on the thread, current or system class loaders

 public static Sql newInstance(String url) throws SQLException 
{
        Connection connection = DriverManager.getConnection(url);
        return new Sql(connection);
}

Creates a new Sql instance given a JDBC connection URL.

 public static Sql newInstance(String url,
    Properties properties) throws SQLException 
{
        Connection connection = DriverManager.getConnection(url, properties);
        return new Sql(connection);
}

Creates a new Sql instance given a JDBC connection URL and some properties.

 public static Sql newInstance(String url,
    String driverClassName) throws SQLException, ClassNotFoundException 
{
        loadDriver(driverClassName);
        return newInstance(url);
}

Creates a new Sql instance given a JDBC connection URL and a driver class name.

 public static Sql newInstance(String url,
    Properties properties,
    String driverClassName) throws SQLException, ClassNotFoundException 
{
        loadDriver(driverClassName);
        return newInstance(url, properties);
}

Creates a new Sql instance given a JDBC connection URL, some properties and a driver class name.

 public static Sql newInstance(String url,
    String user,
    String password) throws SQLException 
{
        Connection connection = DriverManager.getConnection(url, user, password);
        return new Sql(connection);
}

Creates a new Sql instance given a JDBC connection URL, a username and a password.

 public static Sql newInstance(String url,
    String user,
    String password,
    String driverClassName) throws SQLException, ClassNotFoundException 
{
        loadDriver(driverClassName);
        return newInstance(url, user, password);
}

Creates a new Sql instance given a JDBC connection URL, a username, a password and a driver class name.

 protected String nullify(String sql) 
{
        /*
         * Some drivers (Oracle classes12.zip) have difficulty resolving data
         * type if setObject(null). We will modify the query to pass 'null', 'is
         * null', and 'is not null'
         */
        //could be more efficient by compiling expressions in advance.
        int firstWhere = findWhereKeyword(sql);
        if (firstWhere  >= 0) {
            Pattern[] patterns = {Pattern.compile("(?is)^(.{" + firstWhere + "}.*?)!=\\s{0,1}(\\s*)\\?'\"\\?(.*)"),
                    Pattern.compile("(?is)^(.{" + firstWhere + "}.*?)<  >\\s{0,1}(\\s*)\\?'\"\\?(.*)"),
                    Pattern.compile("(?is)^(.{" + firstWhere + "}.*?[^<  >])=\\s{0,1}(\\s*)\\?'\"\\?(.*)"),};
            String[] replacements = {"$1 is not $2null$3", "$1 is not $2null$3", "$1 is $2null$3",};
            for (int i = 0; i <  patterns.length; i++) {
                Matcher matcher = patterns[i].matcher(sql);
                while (matcher.matches()) {
                    sql = matcher.replaceAll(replacements[i]);
                    matcher = patterns[i].matcher(sql);
                }
            }
        }
        return sql.replaceAll("\\?'\"\\?", "null");
}

Hook to allow derived classes to override null handling. Default behavior is to replace ?'"? references with NULLish

 public static OutParameter out(int type) 
{
        return new OutParameter() {
            public int getType() {
                return type;
            }
        };
}

Create a new OutParameter

 public  void query(String sql,
    Closure closure) throws SQLException 
{
        Connection connection = createConnection();
        Statement statement = getStatement(connection, sql);
        ResultSet results = null;
        try {
            log.fine(sql);
            results = statement.executeQuery(sql);
            closure.call(results);
        }
        catch (SQLException e) {
            log.log(Level.FINE, "Failed to execute: " + sql, e);
            throw e;
        }
        finally {
            closeResources(connection, statement, results);
        }
}

Performs the given SQL query, which should return a single ResultSet object. The given closure is called with the ResultSet as its argument.

Example usages:

sql.query("select * from PERSON where firstname like 'S%'") { ResultSet rs ->
    while (rs.next()) println rs.getString('firstname') + ' ' + rs.getString(3)
}

sql.query("call get_people_places()") { ResultSet rs ->
    while (rs.next()) println rs.toRowResult().firstname
}

All resources including the ResultSet are closed automatically after the closure is called.

 public  void query(GString gstring,
    Closure closure) throws SQLException 
{
        List< Object > params = getParameters(gstring);
        String sql = asSql(gstring, params);
        query(sql, params, closure);
}

Performs the given SQL query, which should return a single ResultSet object. The given closure is called with the ResultSet as its argument. The query may contain GString expressions.

Example usage:

def location = 25
sql.query "select * from PERSON where location_id < $location", { ResultSet rs ->
    while (rs.next()) println rs.getString('firstname')
}

All resources including the ResultSet are closed automatically after the closure is called.

 public  void query(String sql,
    List<Object> params,
    Closure closure) throws SQLException 
{
        Connection connection = createConnection();
        PreparedStatement statement = null;
        ResultSet results = null;
        try {
            log.fine(sql);
            statement = getPreparedStatement(connection, sql, params);
            results = statement.executeQuery();
            closure.call(results);
        }
        catch (SQLException e) {
            log.log(Level.FINE, "Failed to execute: " + sql, e);
            throw e;
        }
        finally {
            closeResources(connection, statement, results);
        }
}

Performs the given SQL query, which should return a single ResultSet object. The given closure is called with the ResultSet as its argument. The query may contain placeholder question marks which match the given list of parameters.

Example usage:

sql.query('select * from PERSON where lastname like ?', ['%a%']) { ResultSet rs ->
    while (rs.next()) println rs.getString('lastname')
}

All resources including the ResultSet are closed automatically after the closure is called.

 public static ResultSetOutParameter resultSet(int type) 
{
        return new ResultSetOutParameter() {
            public int getType() {
                return type;
            }
        };
}

Create a new ResultSetOutParameter

 public  void rollback() throws SQLException 
{
        if (useConnection == null) {
            log.log(Level.FINEST, "Rollback operation not supported when using datasets unless using withTransaction or cacheConnection - attempt to rollback ignored");
            return;
        }
        try {
            useConnection.rollback();
        }
        catch (SQLException e) {
            log.log(Level.SEVERE, "Caught exception rolling back connection: " + e, e);
            throw e;
        }
}

If this SQL object was created with a Connection then this method rolls back the connection. If this SQL object was created from a DataSource then this method does nothing.

 public List<GroovyRowResult> rows(String sql) throws SQLException 
{
        return rows(sql, (Closure) null);
}

Performs the given SQL query and return the rows of the result set.

Example usage:

def ans = sql.rows("select * from PERSON where firstname like 'S%'")
println "Found ${ans.size()} rows"     * 

 public List<GroovyRowResult> rows(GString gstring) throws SQLException 
{
        List< Object > params = getParameters(gstring);
        String sql = asSql(gstring, params);
        return rows(sql, params);
}

Performs the given SQL query and return the rows of the result set. The query may contain GString expressions.

Example usage:

def location = 25
def ans = sql.rows("select * from PERSON where location_id < $location")
println "Found ${ans.size()} rows"

 public List<GroovyRowResult> rows(String sql,
    Closure metaClosure) throws SQLException 
{
        AbstractQueryCommand command = createQueryCommand(sql);
        ResultSet rs = null;
        try {
        	rs = command.execute();
        	if (metaClosure != null) metaClosure.call(rs.getMetaData());
        	List< GroovyRowResult > result = asList(sql, rs);
        	rs = null;
        	return result;
        } finally {
        	command.closeResources(rs);
        }
}

Performs the given SQL query and return the rows of the result set.

Example usage:

def printNumCols = { meta -> println "Found $meta.columnCount columns" }
def ans = sql.rows("select * from PERSON", printNumCols)
println "Found ${ans.size()} rows"

 public List<GroovyRowResult> rows(String sql,
    List<Object> params) throws SQLException 
{
        AbstractQueryCommand command = createPreparedQueryCommand(sql, params);
        try {
        	return asList(sql, command.execute());
        } finally {
        	command.closeResources();
        }
}

Performs the given SQL query and return the rows of the result set. The query may contain placeholder question marks which match the given list of parameters.

Example usage:

def ans = sql.rows("select * from PERSON where lastname like ?", ['%a%'])
println "Found ${ans.size()} rows"

 public synchronized  void setCacheStatements(boolean cacheStatements) 
{
        this.cacheStatements = cacheStatements;
        if (!cacheStatements) {
            clearStatementCache();
        }
}

Enables statement caching.
if b is true, cache is created and all created prepared statements will be cached.
if b is false, all cached statements will be properly closed.

 protected  void setInternalConnection(Connection conn) 
{

}

Stub needed for testing. Called when a connection is opened by one of the command-pattern classes so that a test case can monitor the state of the connection through its subclass.

 protected  void setObject(PreparedStatement statement,
    int i,
    Object value) throws SQLException 
{
        if (value instanceof InParameter || value instanceof OutParameter) {
            if (value instanceof InParameter) {
                InParameter in = (InParameter) value;
                Object val = in.getValue();
                if (null == val) {
                    statement.setNull(i, in.getType());
                } else {
                    statement.setObject(i, val, in.getType());
                }
            }
            if (value instanceof OutParameter) {
                try {
                    OutParameter out = (OutParameter) value;
                    ((CallableStatement) statement).registerOutParameter(i, out.getType());
                } catch (ClassCastException e) {
                    throw new SQLException("Cannot register out parameter.");
                }
            }
        } else {
            statement.setObject(i, value);
        }
}

Strategy method allowing derived classes to handle types differently such as for CLOBs etc.

 protected  void setParameters(List<Object> params,
    PreparedStatement statement) throws SQLException 
{
        int i = 1;
        for (Object value : params) {
            setObject(statement, i++, value);
        }
}

Hook to allow derived classes to override behavior associated with setting params for a prepared statement. Default behavior is to append the parameters to the given statement using setObject.

 public  void setResultSetConcurrency(int resultSetConcurrency) 
{
        this.resultSetConcurrency = resultSetConcurrency;
}

Sets the resultSetConcurrency for statements created using the connection. May cause SQLFeatureNotSupportedException exceptions to occur if the underlying database doesn't support the requested concurrency value.

 public  void setResultSetHoldability(int resultSetHoldability) 
{
        this.resultSetHoldability = resultSetHoldability;
}

Sets the resultSetHoldability for statements created using the connection. May cause SQLFeatureNotSupportedException exceptions to occur if the underlying database doesn't support the requested holdability value.

 public  void setResultSetType(int resultSetType) 
{
        this.resultSetType = resultSetType;
}

Sets the resultSetType for statements created using the connection. May cause SQLFeatureNotSupportedException exceptions to occur if the underlying database doesn't support the requested type value.

 public synchronized int[] withBatch(Closure closure) throws SQLException 
{
        boolean savedCacheConnection = cacheConnection;
        cacheConnection = true;
        Connection connection = null;
        Statement statement = null;
        boolean savedAutoCommit = true;
        try {
            connection = createConnection();
            savedAutoCommit = connection.getAutoCommit();
            connection.setAutoCommit(false);
            statement = createStatement(connection);
            closure.call(statement);
            int[] result = statement.executeBatch();
            connection.commit();
            log.fine("Successfully executed batch with " + result.length + " command(s)");
            return result;
        } catch (SQLException e) {
            handleError(connection, e);
            throw e;
        } catch (RuntimeException e) {
            handleError(connection, e);
            throw e;
        } catch (Error e) {
            handleError(connection, e);
            throw e;
        } finally {
            if (connection != null) connection.setAutoCommit(savedAutoCommit);
            cacheConnection = false;
            closeResources(connection, statement);
            cacheConnection = savedCacheConnection;
            if (dataSource != null && !cacheConnection) {
                useConnection = null;
            }
        }
}

Performs the closure within a batch using a cached connection. The closure will be called with a single argument; the statement associated with this batch. Use it like this:

def updateCounts = sql.withBatch { stmt ->
    stmt.addBatch("insert into TABLENAME ...")
    stmt.addBatch("insert into TABLENAME ...")
    stmt.addBatch("insert into TABLENAME ...")
}

 public  void withStatement(Closure configureStatement) 
{
        this.configureStatement = configureStatement;
}

Allows a closure to be passed in to configure the JDBC statements before they are executed. It can be used to do things like set the query size etc. When this method is invoked, the supplied closure is saved. Statements subsequently created from other methods will then be configured using this closure. The statement being configured is passed into the closure as its single argument, e.g.:

sql.withStatement{ stmt -> stmt.maxRows == 10 }
def firstTenRows = sql.rows("select * from table")

 public synchronized  void withTransaction(Closure closure) throws SQLException 
{
        boolean savedCacheConnection = cacheConnection;
        cacheConnection = true;
        Connection connection = null;
        boolean savedAutoCommit = true;
        try {
            connection = createConnection();
            savedAutoCommit = connection.getAutoCommit();
            connection.setAutoCommit(false);
            callClosurePossiblyWithConnection(closure, connection);
            connection.commit();
        } catch (SQLException e) {
            handleError(connection, e);
            throw e;
        } catch (RuntimeException e) {
            handleError(connection, e);
            throw e;
        } catch (Error e) {
            handleError(connection, e);
            throw e;
        } finally {
            if (connection != null) connection.setAutoCommit(savedAutoCommit);
            cacheConnection = false;
            closeResources(connection, null);
            cacheConnection = savedCacheConnection;
            if (dataSource != null && !cacheConnection) {
                useConnection = null;
            }
        }
}

Performs the closure within a transaction using a cached connection. If the closure takes a single argument, it will be called with the connection, otherwise it will be called with no arguments.