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

Example 6 with PreparedStatement

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

the class Sql method withBatch.

/**
     * Performs the closure (containing batch operations specific to an associated prepared statement)
     * within a batch using a given batch size.
     * <p>
     * After every <code>batchSize</code> <code>addBatch(params)</code>
     * operations, automatically calls an <code>executeBatch()</code> operation to "chunk" up the database operations
     * into partitions. Though not normally needed, you can also explicitly call <code>executeBatch()</code> which
     * after executing the current batch, resets the batch count back to zero.
     * <p>
     * The closure will be called with a single argument; the prepared
     * statement (actually a <code>BatchingPreparedStatementWrapper</code> helper object)
     * associated with this batch.
     * <p>
     * Below is an example using a batchSize of 20:
     * <pre>
     * def updateCounts = sql.withBatch(20, 'insert into TABLENAME(a, b, c) values (?, ?, ?)') { ps ->
     *     ps.addBatch(10, 12, 5)      // varargs style
     *     ps.addBatch([7, 3, 98])     // list
     *     ps.addBatch([22, 67, 11])
     *     ...
     * }
     * </pre>
     * Named parameters (into maps or domain objects) are also supported:
     * <pre>
     * def updateCounts = sql.withBatch(20, 'insert into TABLENAME(a, b, c) values (:foo, :bar, :baz)') { ps ->
     *     ps.addBatch([foo:10, bar:12, baz:5])  // map
     *     ps.addBatch(foo:7, bar:3, baz:98)     // Groovy named args allow outer brackets to be dropped
     *     ...
     * }
     * </pre>
     * Named ordinal parameters (into maps or domain objects) are also supported:
     * <pre>
     * def updateCounts = sql.withBatch(20, 'insert into TABLENAME(a, b, c) values (?1.foo, ?2.bar, ?2.baz)') { ps ->
     *     ps.addBatch([[foo:22], [bar:67, baz:11]])  // list of maps or domain objects
     *     ps.addBatch([foo:10], [bar:12, baz:5])     // varargs allows outer brackets to be dropped
     *     ps.addBatch([foo:7], [bar:3, baz:98])
     *     ...
     * }
     * // swap to batch size of 5 and illustrate simple and domain object cases ...
     * class Person { String first, last }
     * def updateCounts2 = sql.withBatch(5, 'insert into PERSON(id, first, last) values (?1, ?2.first, ?2.last)') { ps ->
     *     ps.addBatch(1, new Person(first:'Peter', last:'Pan'))
     *     ps.addBatch(2, new Person(first:'Snow', last:'White'))
     *     ...
     * }
     * </pre>
     * For integrity and performance reasons, you may wish to consider executing your batch command(s) within a transaction:
     * <pre>
     * sql.withTransaction {
     *     def result1 = sql.withBatch { ... }
     *     ...
     * }
     * </pre>
     *
     * @param batchSize partition the batch into batchSize pieces, i.e. after batchSize
     *                  <code>addBatch()</code> invocations, call <code>executeBatch()</code> automatically;
     *                  0 means manual calls to executeBatch are required if additional partitioning of the batch is required
     * @param sql       batch update statement
     * @param closure   the closure containing batch statements (to bind parameters) and optionally other statements
     * @return an array of update counts containing one element for each
     *         binding in the batch.  The elements of the array are ordered according
     *         to the order in which commands were executed.
     * @throws SQLException if a database access error occurs,
     *                      or this method is called on a closed <code>Statement</code>, or the
     *                      driver does not support batch statements. Throws {@link java.sql.BatchUpdateException}
     *                      (a subclass of <code>SQLException</code>) if one of the commands sent to the
     *                      database fails to execute properly or attempts to return a result set.
     * @see BatchingPreparedStatementWrapper
     * @see PreparedStatement
     */
public int[] withBatch(int batchSize, String sql, Closure closure) throws SQLException {
    Connection connection = createConnection();
    List<Tuple> indexPropList = null;
    SqlWithParams preCheck = buildSqlWithIndexedProps(sql);
    boolean savedWithinBatch = withinBatch;
    BatchingPreparedStatementWrapper psWrapper = null;
    if (preCheck != null) {
        indexPropList = new ArrayList<Tuple>();
        for (Object next : preCheck.getParams()) {
            indexPropList.add((Tuple) next);
        }
        sql = preCheck.getSql();
    }
    try {
        withinBatch = true;
        PreparedStatement statement = (PreparedStatement) getAbstractStatement(new CreatePreparedStatementCommand(0), connection, sql);
        configure(statement);
        psWrapper = new BatchingPreparedStatementWrapper(statement, indexPropList, batchSize, LOG, this);
        closure.call(psWrapper);
        return psWrapper.executeBatch();
    } catch (SQLException e) {
        LOG.warning("Error during batch execution of '" + sql + "' with message: " + e.getMessage());
        throw e;
    } finally {
        closeResources(psWrapper);
        closeResources(connection);
        withinBatch = savedWithinBatch;
    }
}

Example 7 with PreparedStatement

use of java.sql.PreparedStatement 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 PreparedStatement

use of java.sql.PreparedStatement 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 PreparedStatement

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

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);
    }
}

Example 10 with PreparedStatement

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

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);
    }
}