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

Example 11 with Connection

use of java.sql.Connection 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.
     * See {@link #executeInsert(GString)} for more details.
     * <p>
     * Resource handling is performed automatically where appropriate.
     *
     * @param sql The SQL statement to execute
     * @return A list of the auto-generated column values for each
     *         inserted row (typically auto-generated keys)
     * @throws SQLException if a database access error occurs
     */
public List<List<Object>> executeInsert(String sql) throws SQLException {
    Connection connection = createConnection();
    Statement statement = null;
    try {
        statement = getStatement(connection, sql);
        this.updateCount = statement.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
        ResultSet keys = statement.getGeneratedKeys();
        return calculateKeys(keys);
    } catch (SQLException e) {
        LOG.warning("Failed to execute: " + sql + " because: " + e.getMessage());
        throw e;
    } finally {
        closeResources(connection, statement);
    }
}

Example 12 with Connection

use of java.sql.Connection 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 13 with Connection

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

the class Sql method execute.

/**
     * Executes the given piece of SQL.
     * Also saves the updateCount, if any, for subsequent examination.
     * <p>
     * Example usages:
     * <pre>
     * 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
     * </pre>
     * <p>
     * Resource handling is performed automatically where appropriate.
     *
     * @param sql the SQL to execute
     * @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) throws SQLException {
    Connection connection = createConnection();
    Statement statement = null;
    try {
        statement = getStatement(connection, sql);
        boolean isResultSet = statement.execute(sql);
        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 14 with Connection

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

the class Sql method cacheStatements.

/**
     * Caches every created preparedStatement in Closure <i>closure</i>
     * 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.
     *
     * @param closure the given closure
     * @throws SQLException if a database error occurs
     * @see #setCacheStatements(boolean)
     */
public 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;
    }
}

Example 15 with Connection

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

the class Sql method withTransaction.

/**
     * 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.
     *
     * @param closure the given closure
     * @throws SQLException if a database error occurs
     */
public 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;
    } catch (Exception e) {
        handleError(connection, e);
        throw new SQLException("Unexpected exception during transaction", e);
    } finally {
        if (connection != null) {
            try {
                connection.setAutoCommit(savedAutoCommit);
            } catch (SQLException e) {
                LOG.finest("Caught exception resetting auto commit: " + e.getMessage() + " - continuing");
            }
        }
        cacheConnection = false;
        closeResources(connection, null);
        cacheConnection = savedCacheConnection;
        if (dataSource != null && !cacheConnection) {
            useConnection = null;
        }
    }
}