jdbctemplate.java

Java/J2EE application framework based on [Expert One-on-One J2EE Design and Development] by Rod John

	}

	/**
	 * Query using a prepared statement, allowing for a PreparedStatementCreator
	 * and a PreparedStatementSetter. Most other query methods use this method,
	 * but application code will always work with either a creator or a setter.
	 * @param psc Callback handler that can create a PreparedStatement given a
	 * Connection
	 * @param pss object that knows how to set values on the prepared statement.
	 * If this is null, the SQL will be assumed to contain no bind parameters.
	 * @param rse object that will extract results.
	 * @return an arbitrary result object, as returned by the ResultSetExtractor
	 * @throws DataAccessException if there is any problem
	 */
	protected Object query(
			PreparedStatementCreator psc, final PreparedStatementSetter pss, final ResultSetExtractor rse)
			throws DataAccessException {
		if (logger.isDebugEnabled()) {
			String sql = getSql(psc);
			logger.debug("Executing SQL query" + (sql != null ? " [" + sql  + "]" : ""));
		}
		return execute(psc, new PreparedStatementCallback() {
			public Object doInPreparedStatement(PreparedStatement ps) throws SQLException {
				ResultSet rs = null;
				try {
					if (pss != null) {
						pss.setValues(ps);
					}
					if (getFetchSize() > 0) {
						ps.setFetchSize(getFetchSize());
					}
					rs = ps.executeQuery();
					ResultSet rsToUse = rs;
					if (nativeJdbcExtractor != null) {
						rsToUse = nativeJdbcExtractor.getNativeResultSet(rs);
					}
					return rse.extractData(rsToUse);
				}
				finally {
					JdbcUtils.closeResultSet(rs);
					if (pss instanceof ParameterDisposer) {
						((ParameterDisposer) pss).cleanupParameters();
					}
				}
			}
		});
	}

	public Object query(PreparedStatementCreator psc, ResultSetExtractor rse) {
		return query(psc, null, rse);
	}

	public Object query(final String sql, final PreparedStatementSetter pss, final ResultSetExtractor rse)
			throws DataAccessException {
		if (sql == null) {
			throw new InvalidDataAccessApiUsageException("SQL may not be null");
		}
		return query(new SimplePreparedStatementCreator(sql), pss, rse);
	}

	public Object query(String sql, Object[] args, int[] argTypes, ResultSetExtractor rse) {
		return query(sql, new ArgTypePreparedStatementSetter(args, argTypes), rse);
	}

	public Object query(String sql, Object[] args, ResultSetExtractor rse) {
		return query(sql, new ArgPreparedStatementSetter(args), rse);
	}

	public List query(PreparedStatementCreator psc, RowCallbackHandler rch)
			throws DataAccessException {
		return (List) query(psc, new RowCallbackHandlerResultSetExtractor(rch));
	}

	public List query(String sql, final PreparedStatementSetter pss, final RowCallbackHandler rch)
			throws DataAccessException {
		return (List) query(sql, pss, new RowCallbackHandlerResultSetExtractor(rch));
	}

	public List query(String sql, final Object[] args, final int[] argTypes, RowCallbackHandler rch)
			throws DataAccessException {
		return query(sql, new ArgTypePreparedStatementSetter(args, argTypes), rch);
	}

	public List query(String sql, final Object[] args, RowCallbackHandler rch)
			throws DataAccessException {
		return query(sql, new ArgPreparedStatementSetter(args), rch);
	}

	public List query(PreparedStatementCreator psc, RowMapper rowMapper)
			throws DataAccessException {
		return query(psc, new RowMapperResultReader(rowMapper));
	}

	public List query(String sql, PreparedStatementSetter pss, RowMapper rowMapper)
			throws DataAccessException {
		return query(sql, pss, new RowMapperResultReader(rowMapper));
	}

	public List query(String sql, Object[] args, int[] argTypes, RowMapper rowMapper)
			throws DataAccessException {
		return query(sql, args, argTypes, new RowMapperResultReader(rowMapper));
	}

	public List query(String sql, Object[] args, RowMapper rowMapper)
			throws DataAccessException {
		return query(sql, args, new RowMapperResultReader(rowMapper));
	}

	public List queryForList(String sql, final Object[] args) throws DataAccessException {
		return (List) query(sql,
				new ArgPreparedStatementSetter(args),
				new ListResultSetExtractor());
	}

	public Object queryForObject(String sql, Object[] args, Class requiredType)
			throws DataAccessException {
		return query(sql,
				new ArgPreparedStatementSetter(args),
				new ObjectResultSetExtractor(requiredType));
	}

	public long queryForLong(String sql, final Object[] args) throws DataAccessException {
		Number number = (Number) queryForObject(sql, args, Number.class);
		return (number != null ? number.longValue() : 0);
	}

	public int queryForInt(String sql, final Object[] args) throws DataAccessException {
		Number number = (Number) queryForObject(sql, args, Number.class);
		return (number != null ? number.intValue() : 0);
	}

	protected int update(final PreparedStatementCreator psc, final PreparedStatementSetter pss)
			throws DataAccessException {
		if (logger.isDebugEnabled()) {
			String sql = getSql(psc);
			logger.debug("Executing SQL update" + (sql != null ? " [" + sql  + "]" : ""));
		}
		Integer result = (Integer) execute(psc, new PreparedStatementCallback() {
			public Object doInPreparedStatement(PreparedStatement ps) throws SQLException {
				try {
					if (pss != null) {
						pss.setValues(ps);
					}
					int rows = ps.executeUpdate();
					if (logger.isDebugEnabled()) {
						logger.debug("SQL update affected " + rows + " rows");
					}
					return new Integer(rows);
				}
				finally {
					if (pss instanceof ParameterDisposer) {
						((ParameterDisposer) pss).cleanupParameters();
					}
				}
			}
		});
		return result.intValue();
	}

	public int update(PreparedStatementCreator psc) throws DataAccessException {
		return update(psc, (PreparedStatementSetter)null);
	}

	public int update(final PreparedStatementCreator psc, final KeyHolder generatedKeyHolder)
			throws DataAccessException {
		if (logger.isDebugEnabled()) {
			String sql = getSql(psc);
			logger.debug("Executing SQL update and returning generated keys" + (sql != null ? " [" + sql  + "]" : ""));
		}
		Integer result = (Integer) execute(psc, new PreparedStatementCallback() {
			public Object doInPreparedStatement(PreparedStatement ps) throws SQLException {
				int rows = ps.executeUpdate();
				List generatedKeys = generatedKeyHolder.getKeyList();
				generatedKeys.clear();
				ResultSet keys = ps.getGeneratedKeys();
				if (keys != null) {
					ListResultSetExtractor lrse = new ListResultSetExtractor();
					generatedKeys.addAll((List) lrse.extractData(keys));
				}
				if (logger.isDebugEnabled()) {
					logger.debug("SQL update affected " + rows + " rows and returned " + generatedKeys.size() + " keys");
				}
				return new Integer(rows);
			}
		});
		return result.intValue();
	}

	public int update(String sql, final PreparedStatementSetter pss) throws DataAccessException {
		return update(new SimplePreparedStatementCreator(sql), pss);
	}

	public int update(String sql, final Object[] args, final int[] argTypes) throws DataAccessException {
		return update(sql, new ArgTypePreparedStatementSetter(args, argTypes));
	}

	public int update(String sql, final Object[] args) throws DataAccessException {
		return update(sql, new ArgPreparedStatementSetter(args));
	}

	public int[] batchUpdate(String sql, final BatchPreparedStatementSetter pss) throws DataAccessException {
		if (logger.isDebugEnabled()) {
			logger.debug("Executing SQL batch update [" + sql + "]");
		}
		return (int[]) execute(sql, new PreparedStatementCallback() {
			public Object doInPreparedStatement(PreparedStatement ps) throws SQLException {
				int batchSize = pss.getBatchSize();
				DatabaseMetaData dbmd = ps.getConnection().getMetaData();
				try {
					boolean supportsBatchUpdates = false;
					try {
						if (dbmd != null) {
							if (dbmd.supportsBatchUpdates()) {
								if (logger.isDebugEnabled()) {
									logger.debug("Batch Updates supported for [" + dbmd.getDriverName() + " " + dbmd.getDriverVersion() + "]");
								}
								supportsBatchUpdates = true;
							}
							else {
								if (logger.isDebugEnabled()) {
									logger.debug("Batch Updates are not supported for [" + dbmd.getDriverName() + " " + dbmd.getDriverVersion() + "]");
								}
							}
						}
					}
					catch (AbstractMethodError ame) {
						logger.warn("Driver does not support JDBC 2.0 method supportsBatchUpdatres [" + dbmd.getDriverName() + " " + dbmd.getDriverVersion() + "]");
					}
					if (supportsBatchUpdates) {
						for (int i = 0; i < batchSize; i++) {
							pss.setValues(ps, i);
							ps.addBatch();
						}
						return ps.executeBatch();
					}
					else {
						int[] rowsAffected = new int[batchSize];
						for (int i = 0; i < batchSize; i++) {
							pss.setValues(ps, i);
							rowsAffected[i] = ps.executeUpdate();
						}
						return rowsAffected;
					}
				}
				finally {
					if (pss instanceof ParameterDisposer) {
						((ParameterDisposer) pss).cleanupParameters();
					}
				}
			}
		});
	}


	//-------------------------------------------------------------------------
	// Methods dealing with callable statements
	//-------------------------------------------------------------------------

	public Object execute(CallableStatementCreator csc, CallableStatementCallback action) {
		if (logger.isDebugEnabled()) {
			String sql = getSql(csc);
			logger.debug("Calling stored procedure" + (sql != null ? " [" + sql  + "]" : ""));
		}
		Connection con = DataSourceUtils.getConnection(getDataSource());
		CallableStatement cs = null;
		try {
			Connection conToUse = con;
			if (this.nativeJdbcExtractor != null &&
					this.nativeJdbcExtractor.isNativeConnectionNecessaryForNativeCallableStatements()) {
				conToUse = this.nativeJdbcExtractor.getNativeConnection(con);
			}
			cs = csc.createCallableStatement(conToUse);
			DataSourceUtils.applyTransactionTimeout(cs, getDataSource());
			CallableStatement csToUse = cs;
			if (this.nativeJdbcExtractor != null) {
				csToUse = this.nativeJdbcExtractor.getNativeCallableStatement(cs);
			}
			Object result = action.doInCallableStatement(csToUse);
			SQLWarning warning = cs.getWarnings();
			throwExceptionOnWarningIfNotIgnoringWarnings(warning);
			return result;
		}
		catch (SQLException ex) {
			throw getExceptionTranslator().translate("executing CallableStatementCallback [" + csc + "]",
																							 getSql(csc), ex);
		}
		finally {
			if (csc instanceof ParameterDisposer) {
				((ParameterDisposer) csc).cleanupParameters();
			}
			JdbcUtils.closeStatement(cs);
			DataSourceUtils.closeConnectionIfNecessary(con, getDataSource());
		}
	}

	public Object execute(final String callString, CallableStatementCallback action) {
		return execute(new SimpleCallableStatementCreator(callString), action);
	}

	public Map call(CallableStatementCreator csc, final List declaredParameters) throws DataAccessException {
		return (Map) execute(csc, new CallableStatementCallback() {
			public Object doInCallableStatement(CallableStatement cs) throws SQLException {
				boolean retVal = cs.execute();
				int updateCount = cs.getUpdateCount();
				if (logger.isDebugEnabled()) {
					logger.debug("CallableStatement.execute returned [" + retVal + "]");
					logger.debug("CallableStatement.getUpdateCount returned [" + updateCount + "]");
				}
				Map returnedResults = new HashMap();
				if (retVal || updateCount != -1) {
					returnedResults.putAll(extractReturnedResultSets(cs, declaredParameters, updateCount));
				}
				returnedResults.putAll(extractOutputParameters(cs, declaredParameters));
				return returnedResults;
			}
		});
	}

	/**
	 * Extract returned ResultSets from the completed stored procedure.
	 * @param cs JDBC wrapper for the stored procedure
	 * @param parameters Parameter list for the stored procedure
	 * @return Map that contains returned results
	 */
	protected Map extractReturnedResultSets(CallableStatement cs, List parameters, int updateCount) throws SQLException {
		Map returnedResults = new HashMap();
		int rsIndex = 0;
		boolean moreResults; 
		do {
			if (updateCount == -1) {
				Object param = null;
				if (parameters != null && parameters.size() > rsIndex) {
					param = parameters.get(rsIndex);
				}
				if (param instanceof SqlReturnResultSet) {
					SqlReturnResultSet rsParam = (SqlReturnResultSet) param;
					returnedResults.putAll(processResultSet(cs.getResultSet(), rsParam));
				}
				else {
					logger.warn("ResultSet returned from stored procedure but a corresponding " +
											"SqlReturnResultSet parameter was not declared");
				}
				rsIndex++;
			}
			moreResults = cs.getMoreResults();
			updateCount = cs.getUpdateCount();
			if (logger.isDebugEnabled()) {
				logger.debug("CallableStatement.getUpdateCount returned [" + updateCount + "]");
			}
		}
		while (moreResults || updateCount != -1);
		return returnedResults;