Examples with Connection java.sql.Connection used on opensource projects

Example 6 with Connection

use of java.sql.Connection in project groovy by apache.

the class Sql method callWithRows.

/**
     * Base internal method for call(), callWithRows(), and callWithAllRows() style of methods.
     * <p>
     * Performs a stored procedure call with the given parameters,
     * calling the closure once with all result objects,
     * and also returning the rows of the ResultSet(s) (if processResultSets is set to
     * Sql.FIRST_RESULT_SET, Sql.ALL_RESULT_SETS)
     * <p>
     * Main purpose of processResultSets param is to retain original call() method
     * performance when this is set to Sql.NO_RESULT_SETS
     * <p>
     * Resource handling is performed automatically where appropriate.
     *
     * @param sql     the sql statement
     * @param params  a list of parameters
     * @param processResultsSets the result sets to process, either Sql.NO_RESULT_SETS, Sql.FIRST_RESULT_SET, or Sql.ALL_RESULT_SETS
     * @param closure called once with all out parameter results
     * @return a list of GroovyRowResult objects
     * @throws SQLException if a database access error occurs
     * @see #callWithRows(String, List, Closure)
     */
protected List<List<GroovyRowResult>> callWithRows(String sql, List<Object> params, int processResultsSets, Closure closure) throws SQLException {
    Connection connection = createConnection();
    CallableStatement statement = null;
    List<GroovyResultSet> resultSetResources = new ArrayList<GroovyResultSet>();
    try {
        statement = getCallableStatement(connection, sql, params);
        boolean hasResultSet = 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]));
        List<List<GroovyRowResult>> resultSets = new ArrayList<List<GroovyRowResult>>();
        if (processResultsSets == NO_RESULT_SETS) {
            resultSets.add(new ArrayList<GroovyRowResult>());
            return resultSets;
        }
        //Check both hasResultSet and getMoreResults() because of differences in vendor behavior
        if (!hasResultSet) {
            hasResultSet = statement.getMoreResults();
        }
        while (hasResultSet && (processResultsSets != NO_RESULT_SETS)) {
            resultSets.add(asList(sql, statement.getResultSet()));
            if (processResultsSets == FIRST_RESULT_SET) {
                break;
            } else {
                hasResultSet = statement.getMoreResults();
            }
        }
        return resultSets;
    } catch (SQLException e) {
        LOG.warning("Failed to execute: " + sql + " because: " + e.getMessage());
        throw e;
    } finally {
        for (GroovyResultSet rs : resultSetResources) {
            closeResources(null, null, rs);
        }
        closeResources(connection, statement);
    }
}

Example 7 with Connection

use of java.sql.Connection in project groovy by apache.

the class Sql method eachRow.

/**
     * Performs the given SQL query calling the given <code>rowClosure</code> with each row of the result set starting at
     * the provided <code>offset</code>, and including up to <code>maxRows</code> number of rows.
     * The row will be a <code>GroovyResultSet</code> which is a <code>ResultSet</code>
     * that supports accessing the fields using property style notation and ordinal index values.
     * <p>
     * In addition, the <code>metaClosure</code> will be called once passing in the
     * <code>ResultSetMetaData</code> as argument.
     * The query may contain placeholder question marks which match the given list of parameters.
     * <p>
     * Note that the underlying implementation is based on either invoking <code>ResultSet.absolute()</code>,
     * or if the ResultSet type is <code>ResultSet.TYPE_FORWARD_ONLY</code>, the <code>ResultSet.next()</code> method
     * is invoked equivalently.  The first row of a ResultSet is 1, so passing in an offset of 1 or less has no effect
     * on the initial positioning within the result set.
     * <p>
     * Note that different database and JDBC driver implementations may work differently with respect to this method.
     * Specifically, one should expect that <code>ResultSet.TYPE_FORWARD_ONLY</code> may be less efficient than a
     * "scrollable" type.
     *
     * @param sql         the sql statement
     * @param params      a list of parameters
     * @param offset      the 1-based offset for the first row to be processed
     * @param maxRows     the maximum number of rows to be processed
     * @param metaClosure called for meta data (only once after sql execution)
     * @param rowClosure  called for each row with a GroovyResultSet
     * @throws SQLException if a database access error occurs
     */
public void eachRow(String sql, List<Object> params, Closure metaClosure, int offset, int maxRows, Closure rowClosure) throws SQLException {
    Connection connection = createConnection();
    PreparedStatement statement = null;
    ResultSet results = null;
    try {
        statement = getPreparedStatement(connection, sql, params);
        results = statement.executeQuery();
        if (metaClosure != null)
            metaClosure.call(results.getMetaData());
        boolean cursorAtRow = moveCursor(results, offset);
        if (!cursorAtRow)
            return;
        GroovyResultSet groovyRS = new GroovyResultSetProxy(results).getImpl();
        int i = 0;
        while ((maxRows <= 0 || i++ < maxRows) && groovyRS.next()) {
            rowClosure.call(groovyRS);
        }
    } catch (SQLException e) {
        LOG.warning("Failed to execute: " + sql + " because: " + e.getMessage());
        throw e;
    } finally {
        closeResources(connection, statement, results);
    }
}

Example 8 with Connection

use of java.sql.Connection in project groovy by apache.

the class Sql method execute.

/**
     * Executes the given piece of SQL with parameters.
     * Also saves the updateCount, if any, for subsequent examination.
     * <p>
     * Example usage:
     * <pre>
     * sql.execute """
     *     insert into PERSON (id, firstname, lastname, location_id) values (?, ?, ?, ?)
     * """, [1, "Guillaume", "Laforge", 10]
     * assert sql.updateCount == 1
     * </pre>
     * <p>
     * This method supports named and named ordinal parameters.
     * See the class Javadoc for more details.
     * <p>
     * Resource handling is performed automatically where appropriate.
     *
     * @param sql    the SQL statement
     * @param params a list of parameters
     * @return <code>true</code> if the first result is a <code>ResultSet</code>
     *         object; <code>false</code> if it is an update count or there are
     *         no results
     * @throws SQLException if a database access error occurs
     */
public boolean execute(String sql, List<Object> params) throws SQLException {
    Connection connection = createConnection();
    PreparedStatement statement = null;
    try {
        statement = getPreparedStatement(connection, sql, params);
        boolean isResultSet = statement.execute();
        this.updateCount = statement.getUpdateCount();
        return isResultSet;
    } catch (SQLException e) {
        LOG.warning("Failed to execute: " + sql + " because: " + e.getMessage());
        throw e;
    } finally {
        closeResources(connection, statement);
    }
}

Example 9 with Connection

use of java.sql.Connection in project groovy by apache.

the class Sql method eachRow.

/**
     * Performs the given SQL query calling the given <code>rowClosure</code> with each row of the result set starting at
     * the provided <code>offset</code>, and including up to <code>maxRows</code> number of rows.
     * The row will be a <code>GroovyResultSet</code> which is a <code>ResultSet</code>
     * that supports accessing the fields using property style notation and ordinal index values.
     * <p>
     * In addition, the <code>metaClosure</code> will be called once passing in the
     * <code>ResultSetMetaData</code> as argument.
     * <p>
     * Note that the underlying implementation is based on either invoking <code>ResultSet.absolute()</code>,
     * or if the ResultSet type is <code>ResultSet.TYPE_FORWARD_ONLY</code>, the <code>ResultSet.next()</code> method
     * is invoked equivalently.  The first row of a ResultSet is 1, so passing in an offset of 1 or less has no effect
     * on the initial positioning within the result set.
     * <p>
     * Note that different database and JDBC driver implementations may work differently with respect to this method.
     * Specifically, one should expect that <code>ResultSet.TYPE_FORWARD_ONLY</code> may be less efficient than a
     * "scrollable" type.
     * <p>
     * Resource handling is performed automatically where appropriate.
     *
     * @param sql         the sql statement
     * @param offset      the 1-based offset for the first row to be processed
     * @param maxRows     the maximum number of rows to be processed
     * @param metaClosure called for meta data (only once after sql execution)
     * @param rowClosure  called for each row with a GroovyResultSet
     * @throws SQLException if a database access error occurs
     */
public void eachRow(String sql, Closure metaClosure, int offset, int maxRows, Closure rowClosure) throws SQLException {
    Connection connection = createConnection();
    Statement statement = null;
    ResultSet results = null;
    try {
        statement = getStatement(connection, sql);
        results = statement.executeQuery(sql);
        if (metaClosure != null)
            metaClosure.call(results.getMetaData());
        boolean cursorAtRow = moveCursor(results, offset);
        if (!cursorAtRow)
            return;
        GroovyResultSet groovyRS = new GroovyResultSetProxy(results).getImpl();
        int i = 0;
        while ((maxRows <= 0 || i++ < maxRows) && groovyRS.next()) {
            rowClosure.call(groovyRS);
        }
    } catch (SQLException e) {
        LOG.warning("Failed to execute: " + sql + " because: " + e.getMessage());
        throw e;
    } finally {
        closeResources(connection, statement, results);
    }
}

Example 10 with Connection

use of java.sql.Connection in project groovy by apache.

the class Sql method execute.

/**
     * Executes the given piece of SQL.
     * Also calls the provided processResults Closure to process any ResultSet or UpdateCount results that executing the SQL might produce.
     * <p>
     * Example usages:
     * <pre>
     * boolean first = true
     * sql.execute "{call FindAllByFirst('J')}", { isResultSet, result ->
     *   if (first) {
     *     first = false
     *     assert !isResultSet && result == 0
     *   } else {
     *     assert isResultSet && result == [[ID:1, FIRSTNAME:'James', LASTNAME:'Strachan'], [ID:4, FIRSTNAME:'Jean', LASTNAME:'Gabin']]
     *   }
     * }
     * </pre>
     * <p>
     * Resource handling is performed automatically where appropriate.
     *
     * @param sql the SQL to execute
     * @param processResults a Closure which will be passed two parameters: either {@code true} plus a list of GroovyRowResult values
     *                       derived from {@code statement.getResultSet()} or {@code false} plus the update count from {@code statement.getUpdateCount()}.
     *                       The closure will be called for each result produced from executing the SQL.
     * @throws SQLException if a database access error occurs
     * @since 2.3.2
     */
public void execute(String sql, Closure processResults) throws SQLException {
    Connection connection = createConnection();
    Statement statement = null;
    try {
        statement = getStatement(connection, sql);
        boolean isResultSet = statement.execute(sql);
        int updateCount = statement.getUpdateCount();
        while (isResultSet || updateCount != -1) {
            if (processResults.getMaximumNumberOfParameters() != 2) {
                throw new SQLException("Incorrect number of parameters for processResults Closure");
            }
            if (isResultSet) {
                ResultSet resultSet = statement.getResultSet();
                List<GroovyRowResult> rowResult = resultSet == null ? null : asList(sql, resultSet);
                processResults.call(isResultSet, rowResult);
            } else {
                processResults.call(isResultSet, updateCount);
            }
            isResultSet = statement.getMoreResults();
            updateCount = statement.getUpdateCount();
        }
    } catch (SQLException e) {
        LOG.warning("Failed to execute: " + sql + " because: " + e.getMessage());
        throw e;
    } finally {
        closeResources(connection, statement);
    }
}