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

Example 1 with PreparedStatement

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

the class ElsqlProducer method process.

@Override
public void process(final Exchange exchange) throws Exception {
    final Object data = exchange.getIn().getBody();
    final SqlParameterSource param = new ElsqlSqlMapSource(exchange, data);
    final String sql = elSql.getSql(elSqlName, new SpringSqlParams(param));
    LOG.debug("ElsqlProducer @{} using sql: {}", elSqlName, sql);
    // special for processing stream list (batch not supported)
    final SqlOutputType outputType = getEndpoint().getOutputType();
    if (outputType == SqlOutputType.StreamList) {
        processStreamList(exchange, sql, param);
        return;
    }
    log.trace("jdbcTemplate.execute: {}", sql);
    jdbcTemplate.execute(sql, param, new PreparedStatementCallback<Object>() {

        @Override
        public Object doInPreparedStatement(final PreparedStatement ps) throws SQLException, DataAccessException {
            ResultSet rs = null;
            try {
                boolean isResultSet = false;
                final int expected = ps.getParameterMetaData().getParameterCount();
                if (expected > 0 && batch) {
                    final String sqlForDefaultPreparedStamentStrategy = sql.replaceAll(":", ":\\?");
                    final String preparedQuery = sqlPrepareStatementStrategy.prepareQuery(sqlForDefaultPreparedStamentStrategy, getEndpoint().isAllowNamedParameters(), exchange);
                    final Iterator<?> iterator = exchange.getIn().getBody(Iterator.class);
                    while (iterator != null && iterator.hasNext()) {
                        final Object value = iterator.next();
                        final Iterator<?> i = sqlPrepareStatementStrategy.createPopulateIterator(sqlForDefaultPreparedStamentStrategy, preparedQuery, expected, exchange, value);
                        sqlPrepareStatementStrategy.populateStatement(ps, i, expected);
                        ps.addBatch();
                    }
                }
                // execute the prepared statement and populate the outgoing message
                if (batch) {
                    final int[] updateCounts = ps.executeBatch();
                    int total = 0;
                    for (final int count : updateCounts) {
                        total += count;
                    }
                    exchange.getIn().setHeader(SqlConstants.SQL_UPDATE_COUNT, total);
                } else {
                    isResultSet = ps.execute();
                    if (isResultSet) {
                        rs = ps.getResultSet();
                        // preserve headers first, so we can override the SQL_ROW_COUNT header
                        exchange.getOut().getHeaders().putAll(exchange.getIn().getHeaders());
                        final SqlOutputType outputType = getEndpoint().getOutputType();
                        log.trace("Got result list from query: {}, outputType={}", rs, outputType);
                        if (outputType == SqlOutputType.SelectList) {
                            final List<?> data = getEndpoint().queryForList(rs, true);
                            // for noop=true we still want to enrich with the row count header
                            if (getEndpoint().isNoop()) {
                                exchange.getOut().setBody(exchange.getIn().getBody());
                            } else if (getEndpoint().getOutputHeader() != null) {
                                exchange.getOut().setBody(exchange.getIn().getBody());
                                exchange.getOut().setHeader(getEndpoint().getOutputHeader(), data);
                            } else {
                                exchange.getOut().setBody(data);
                            }
                            exchange.getOut().setHeader(SqlConstants.SQL_ROW_COUNT, data.size());
                        } else if (outputType == SqlOutputType.SelectOne) {
                            final Object data = getEndpoint().queryForObject(rs);
                            if (data != null) {
                                // for noop=true we still want to enrich with the row count header
                                if (getEndpoint().isNoop()) {
                                    exchange.getOut().setBody(exchange.getIn().getBody());
                                } else if (getEndpoint().getOutputHeader() != null) {
                                    exchange.getOut().setBody(exchange.getIn().getBody());
                                    exchange.getOut().setHeader(getEndpoint().getOutputHeader(), data);
                                } else {
                                    exchange.getOut().setBody(data);
                                }
                                exchange.getOut().setHeader(SqlConstants.SQL_ROW_COUNT, 1);
                            } else {
                                if (getEndpoint().isNoop()) {
                                    exchange.getOut().setBody(exchange.getIn().getBody());
                                } else if (getEndpoint().getOutputHeader() != null) {
                                    exchange.getOut().setBody(exchange.getIn().getBody());
                                }
                                exchange.getOut().setHeader(SqlConstants.SQL_ROW_COUNT, 0);
                            }
                        } else {
                            throw new IllegalArgumentException("Invalid outputType=" + outputType);
                        }
                    } else {
                        // if we are here, there isResultSet is false. This can happen only if we are doing an update operation or there is no result.
                        // we can simply add the updateCount in this case.
                        exchange.getOut().setHeader(SqlConstants.SQL_UPDATE_COUNT, ps.getUpdateCount());
                    }
                }
            } finally {
                closeResultSet(rs);
            }
            return null;
        }
    });
}

Example 2 with PreparedStatement

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

the class ElsqlProducer method processStreamList.

protected void processStreamList(final Exchange exchange, final PreparedStatementCreator statementCreator, final String preparedQuery) throws Exception {
    log.trace("processStreamList: {}", preparedQuery);
    // do not use the jdbcTemplate as it will auto-close connection/ps/rs when exiting the execute method
    // and we need to keep the connection alive while routing and close it when the Exchange is done being routed
    Connection con = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
        con = dataSource.getConnection();
        ps = statementCreator.createPreparedStatement(con);
        final boolean isResultSet = ps.execute();
        if (isResultSet) {
            rs = ps.getResultSet();
            final ResultSetIterator iterator = getEndpoint().queryForStreamList(con, ps, rs);
            if (getEndpoint().isNoop()) {
                exchange.getOut().setBody(exchange.getIn().getBody());
            } else if (getEndpoint().getOutputHeader() != null) {
                exchange.getOut().setBody(exchange.getIn().getBody());
                exchange.getOut().setHeader(getEndpoint().getOutputHeader(), iterator);
            } else {
                exchange.getOut().setBody(iterator);
            }
            // we do not know the row count so we cannot set a ROW_COUNT header
            // defer closing the iterator when the exchange is complete
            exchange.addOnCompletion(new ResultSetIteratorCompletion(iterator));
        }
    } catch (final Exception e) {
        // in case of exception then close all this before rethrow
        closeConnection(con);
        closeStatement(ps);
        closeResultSet(rs);
        throw e;
    }
}

Example 3 with PreparedStatement

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

the class ElsqlSqlProcessingStrategy method commit.

@Override
public int commit(DefaultSqlEndpoint defaultSqlEndpoint, Exchange exchange, Object data, NamedParameterJdbcTemplate jdbcTemplate, SqlParameterSource parameterSource, String query) throws Exception {
    final SqlParameterSource param = new ElsqlSqlMapSource(exchange, data);
    final String sql = elSql.getSql(query, new SpringSqlParams(param));
    LOG.debug("commit @{} using sql: {}", query, sql);
    return jdbcTemplate.execute(sql, param, new PreparedStatementCallback<Integer>() {

        @Override
        public Integer doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
            ps.execute();
            int updateCount = ps.getUpdateCount();
            if (LOG.isTraceEnabled()) {
                LOG.trace("Update count {}", updateCount);
            }
            return updateCount;
        }
    });
}

Example 4 with PreparedStatement

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

the class JdbcProducer method doCreateAndExecuteSqlStatementWithHeaders.

private boolean doCreateAndExecuteSqlStatementWithHeaders(Exchange exchange, String sql, Connection conn) throws Exception {
    PreparedStatement ps = null;
    ResultSet rs = null;
    boolean shouldCloseResources = true;
    try {
        final String preparedQuery = getEndpoint().getPrepareStatementStrategy().prepareQuery(sql, getEndpoint().isAllowNamedParameters());
        Boolean shouldRetrieveGeneratedKeys = exchange.getIn().getHeader(JdbcConstants.JDBC_RETRIEVE_GENERATED_KEYS, false, Boolean.class);
        if (shouldRetrieveGeneratedKeys) {
            Object expectedGeneratedColumns = exchange.getIn().getHeader(JdbcConstants.JDBC_GENERATED_COLUMNS);
            if (expectedGeneratedColumns == null) {
                ps = conn.prepareStatement(preparedQuery, Statement.RETURN_GENERATED_KEYS);
            } else if (expectedGeneratedColumns instanceof String[]) {
                ps = conn.prepareStatement(preparedQuery, (String[]) expectedGeneratedColumns);
            } else if (expectedGeneratedColumns instanceof int[]) {
                ps = conn.prepareStatement(preparedQuery, (int[]) expectedGeneratedColumns);
            } else {
                throw new IllegalArgumentException("Header specifying expected returning columns isn't an instance of String[] or int[] but " + expectedGeneratedColumns.getClass());
            }
        } else {
            ps = conn.prepareStatement(preparedQuery);
        }
        int expectedCount = ps.getParameterMetaData().getParameterCount();
        if (expectedCount > 0) {
            Iterator<?> it = getEndpoint().getPrepareStatementStrategy().createPopulateIterator(sql, preparedQuery, expectedCount, exchange, exchange.getIn().getBody());
            getEndpoint().getPrepareStatementStrategy().populateStatement(ps, it, expectedCount);
        }
        LOG.debug("Executing JDBC PreparedStatement: {}", sql);
        boolean stmtExecutionResult = ps.execute();
        if (stmtExecutionResult) {
            rs = ps.getResultSet();
            shouldCloseResources = setResultSet(exchange, conn, rs);
        } else {
            int updateCount = ps.getUpdateCount();
            // preserve headers
            exchange.getOut().getHeaders().putAll(exchange.getIn().getHeaders());
            // and then set the new header
            exchange.getOut().setHeader(JdbcConstants.JDBC_UPDATE_COUNT, updateCount);
        }
        if (shouldRetrieveGeneratedKeys) {
            setGeneratedKeys(exchange, conn, ps.getGeneratedKeys());
        }
    } finally {
        if (shouldCloseResources) {
            closeQuietly(rs);
            closeQuietly(ps);
        }
    }
    return shouldCloseResources;
}

Example 5 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.
     * 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
     * @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, List<Object> params) throws SQLException {
    Connection connection = createConnection();
    PreparedStatement statement = null;
    try {
        statement = getPreparedStatement(connection, sql, params, Statement.RETURN_GENERATED_KEYS);
        this.updateCount = statement.executeUpdate();
        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);
    }
}