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 String asSql(GString gstring,
    List 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 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, Collections.EMPTY_LIST);
}

Performs a stored procedure call.

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

Performs a stored procedure call with the given parameters.

 public int call(String sql,
    List 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.

 public  void call(GString gstring,
    Closure closure) throws Exception 
{
        List 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.

 public  void call(String sql,
    List params,
    Closure closure) throws Exception 
{
        Connection connection = createConnection();
        CallableStatement statement = connection.prepareCall(sql);
        try {
            log.fine(sql);
            setParameters(params, statement);
            statement.execute();
            List results = new ArrayList();
            int indx = 0;
            int inouts = 0;
            for (Object value : params) {
                if (value instanceof OutParameter) {
                    if (value instanceof ResultSetOutParameter) {
                        results.add(CallResultSet.getImpl(statement, indx));
                    } else {
                        Object o = statement.getObject(indx + 1);
                        if (o instanceof ResultSet) {
                            results.add(new GroovyResultSetProxy((ResultSet) o).getImpl());
                        } 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);
        }
}

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

 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 (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 overriden 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, 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) 
{
        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.

 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 closure with each row of the result set.

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

Performs the given SQL query calling the closure with the result set.

 public  void eachRow(String sql,
    Closure metaClosure,
    Closure rowClosure) throws SQLException 
{
        Connection connection = createConnection();
        Statement statement = getStatement(connection, sql);
        configure(statement);
        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 closures for metadata and each row

 public  void eachRow(String sql,
    List params,
    Closure closure) throws SQLException 
{
        Connection connection = createConnection();
        PreparedStatement statement = null;
        ResultSet results = null;
        try {
            log.fine(sql);
            statement = getPreparedStatement(connection, sql);
            setParameters(params, statement);
            configure(statement);
            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 closure with the result set.

 public boolean execute(String sql) throws SQLException 
{
        Connection connection = createConnection();
        Statement statement = null;
        try {
            log.fine(sql);
            statement = getStatement(connection, sql);
            configure(statement);
            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.

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

Executes the given SQL with embedded expressions inside.

 public boolean execute(String sql,
    List params) throws SQLException 
{
        Connection connection = createConnection();
        PreparedStatement statement = null;
        try {
            log.fine(sql);
            statement = getPreparedStatement(connection, sql);
            setParameters(params, statement);
            configure(statement);
            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.

 public List executeInsert(String sql) throws SQLException 
{
        Connection connection = createConnection();
        Statement statement = null;
        try {
            log.fine(sql);
            statement = getStatement(connection, sql);
            configure(statement);
            boolean hasResultSet = statement.execute(sql, Statement.RETURN_GENERATED_KEYS);
            // Prepare a list to contain the auto-generated column
            // values, and then fetch them from the statement.
            List< List > autoKeys = new ArrayList< List >();
            ResultSet keys = statement.getGeneratedKeys();
            int count = keys.getMetaData().getColumnCount();
            // Copy the column values into a list of a list.
            while (keys.next()) {
                List rowKeys = new ArrayList(count);
                for (int i = 1; i < = count; i++) {
                    rowKeys.add(keys.getObject(i));
                }
                autoKeys.add(rowKeys);
            }
            // Store the update count so that it can be retrieved by
            // clients, and then return the list of auto-generated
            // values.
            this.updateCount = statement.getUpdateCount();
            return autoKeys;
        }
        catch (SQLException e) {
            log.log(Level.FINE, "Failed to execute: " + sql, e);
            throw e;
        }
        finally {
            closeResources(connection, statement);
        }
}

Executes the given SQL statement. See #executeInsert(GString) for more details.

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

Executes the given SQL with embedded expressions inside, and returns the values of any auto-generated colums, such as an autoincrement ID field. These values can be accessed using array notation. For example, to return the second auto-generated column value of the third row, use keys[3][1]. The method is designed to be used with SQL INSERT statements, but is not limited to them.

The standard use for this method is when a table has an autoincrement ID column and you want to know what the ID is for a newly inserted row. In this example, we insert a single row into a table in which the first column contains the autoincrement ID:

def sql = Sql.newInstance("jdbc:mysql://localhost:3306/groovy",
"user",
"password",
"com.mysql.jdbc.Driver")

def keys = sql.insert("insert into test_table (INT_DATA, STRING_DATA) "
+ "VALUES (1, 'Key Largo')")

def id = keys[0][0]

// 'id' now contains the value of the new row's ID column.
// It can be used to update an object representation's
// id attribute for example.
...

 public List executeInsert(String sql,
    List params) throws SQLException 
{
        // Now send the SQL to the database.
        Connection connection = createConnection();
        PreparedStatement statement = null;
        try {
            log.fine(sql);
            // Prepare a statement for the SQL and then execute it.
            statement = getPreparedStatement(connection, sql, Statement.RETURN_GENERATED_KEYS);
            setParameters(params, statement);
            configure(statement);
            this.updateCount = statement.executeUpdate();
            // Prepare a list to contain the auto-generated column
            // values, and then fetch them from the statement.
            List< List > autoKeys = new ArrayList< List >();
            ResultSet keys = statement.getGeneratedKeys();
            int count = keys.getMetaData().getColumnCount();
            // Copy the column values into a list of a list.
            while (keys.next()) {
                List rowKeys = new ArrayList(count);
                for (int i = 1; i < = count; i++) {
                    rowKeys.add(keys.getObject(i));
                }
                autoKeys.add(rowKeys);
            }
            return autoKeys;
        }
        catch (SQLException e) {
            log.log(Level.FINE, "Failed to execute: " + sql, e);
            throw e;
        }
        finally {
            closeResources(connection, statement);
        }
}

Executes the given SQL statement with a particular list of parameter values. See #executeInsert(GString) for more details.

 public int executeUpdate(String sql) throws SQLException 
{
        Connection connection = createConnection();
        Statement statement = null;
        try {
            log.fine(sql);
            statement = getStatement(connection, sql);
            configure(statement);
            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 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 params) throws SQLException 
{
        Connection connection = createConnection();
        PreparedStatement statement = null;
        try {
            log.fine(sql);
            statement = getPreparedStatement(connection, sql);
            setParameters(params, statement);
            configure(statement);
            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;
            }
        };
}

Creates a variable to be expanded in the Sql string rather than representing an sql parameter.

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

 public Object firstRow(GString gstring) throws SQLException 
{
        List 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.

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

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

 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 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 ClassNotFoundException, SQLException 
{
        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 ClassNotFoundException, SQLException 
{
        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 ClassNotFoundException, SQLException 
{
        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);
        configure(statement);
        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 calling the closure with the result set.

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

Performs the given SQL query calling the closure with the result set.

 public  void query(String sql,
    List params,
    Closure closure) throws SQLException 
{
        Connection connection = createConnection();
        PreparedStatement statement = null;
        ResultSet results = null;
        try {
            log.fine(sql);
            statement = getPreparedStatement(connection, sql);
            setParameters(params, statement);
            configure(statement);
            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 with parameters calling the closure with the result set.

 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 rows(String sql) throws SQLException 
{
        return rows(sql, (Closure) null);
}

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

 public List rows(GString gstring) throws SQLException 
{
        List 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.

 public List rows(String sql,
    Closure metaClosure) throws SQLException 
{
        List< GroovyRowResult > results = new ArrayList< GroovyRowResult >();
        Connection connection = createConnection();
        Statement statement = getStatement(connection, sql);
        configure(statement);
        ResultSet rs = null;
        try {
            log.fine(sql);
            rs = statement.executeQuery(sql);
            if (metaClosure != null) metaClosure.call(rs.getMetaData());
            while (rs.next()) {
                results.add(DefaultGroovyMethods.toRowResult(rs));
            }
            return (results);
        } catch (SQLException e) {
            log.log(Level.FINE, "Failed to execute: " + sql, e);
            throw e;
        } finally {
            closeResources(connection, statement, rs);
        }
}

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

 public List rows(String sql,
    List params) throws SQLException 
{
        List< GroovyRowResult > results = new ArrayList< GroovyRowResult >();
        Connection connection = createConnection();
        PreparedStatement statement = null;
        ResultSet rs = null;
        try {
            log.fine(sql);
            statement = getPreparedStatement(connection, sql);
            setParameters(params, statement);
            configure(statement);
            rs = statement.executeQuery();
            while (rs.next()) {
                results.add(DefaultGroovyMethods.toRowResult(rs));
            }
            return (results);
        }
        catch (SQLException e) {
            log.log(Level.FINE, "Failed to execute: " + sql, e);
            throw e;
        }
        finally {
            closeResources(connection, statement, rs);
        }
}

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

 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 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 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  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 subsequent 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;
        try {
            connection = createConnection();
            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(true);
            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.