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