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

Example 71 with PreparedStatement

use of java.sql.PreparedStatement in project groovy-core by groovy.

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 72 with PreparedStatement

use of java.sql.PreparedStatement in project groovy-core by groovy.

the class Sql method getPreparedStatement.

private PreparedStatement getPreparedStatement(Connection connection, String sql, List<Object> params, int returnGeneratedKeys) throws SQLException {
    SqlWithParams updated = checkForNamedParams(sql, params);
    LOG.fine(updated.getSql() + " | " + updated.getParams());
    PreparedStatement statement = (PreparedStatement) getAbstractStatement(new CreatePreparedStatementCommand(returnGeneratedKeys), connection, updated.getSql());
    setParameters(updated.getParams(), statement);
    configure(statement);
    return statement;
}

Example 73 with PreparedStatement

use of java.sql.PreparedStatement in project groovy-core by groovy.

the class Sql method execute.

/**
     * Executes the given piece of SQL with parameters.
     * Also calls the provided processResults Closure to process any ResultSet or UpdateCount results that executing the SQL might produce.
     * <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
     * @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
     * @see #execute(String, Closure)
     * @since 2.3.2
     */
public void execute(String sql, List<Object> params, Closure processResults) throws SQLException {
    Connection connection = createConnection();
    PreparedStatement statement = null;
    try {
        statement = getPreparedStatement(connection, sql, params);
        boolean isResultSet = statement.execute();
        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);
    }
}

Example 74 with PreparedStatement

use of java.sql.PreparedStatement in project groovy-core by groovy.

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 75 with PreparedStatement

use of java.sql.PreparedStatement in project groovy-core by groovy.

the class Sql method executeInsert.

/**
     * 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 (or fields) and you know the column name(s) of the ID field(s).
     * The query may contain placeholder question marks which match the given list of parameters.
     * See {@link #executeInsert(GString)} for more details.
     * <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 to execute
     * @param params         The parameter values that will be substituted
     *                       into the SQL statement's parameter slots
     * @param keyColumnNames a list of column names indicating the columns that should be returned from the
     *                       inserted row or rows (some drivers may be case sensitive, e.g. may require uppercase names)
     * @return A list of the auto-generated row results for each inserted row (typically auto-generated keys)
     * @throws SQLException if a database access error occurs
     * @see Connection#prepareStatement(String, String[])
     * @since 2.3.2
     */
public List<GroovyRowResult> executeInsert(String sql, List<Object> params, List<String> keyColumnNames) throws SQLException {
    Connection connection = createConnection();
    PreparedStatement statement = null;
    try {
        this.keyColumnNames = keyColumnNames;
        statement = getPreparedStatement(connection, sql, params, USE_COLUMN_NAMES);
        this.keyColumnNames = null;
        this.updateCount = statement.executeUpdate();
        ResultSet keys = statement.getGeneratedKeys();
        return asList(sql, keys);
    } catch (SQLException e) {
        LOG.warning("Failed to execute: " + sql + " because: " + e.getMessage());
        throw e;
    } finally {
        closeResources(connection, statement);
    }
}