jdbctemplate.java

一个关于Spring框架的示例应用程序,简单使用,可以参考.

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

	/**
	 * Extract output parameters from the completed stored procedure.
	 * @param cs JDBC wrapper for the stored procedure
	 * @param parameters parameter list for the stored procedure
	 * @return parameters to the stored procedure
	 * @return Map that contains returned results
	 */
	protected Map extractOutputParameters(CallableStatement cs, List parameters) throws SQLException {
		Map returnedResults = new HashMap();
		int sqlColIndex = 1;
		for (int i = 0; i < parameters.size(); i++) {
			Object param = parameters.get(i);
			if (param instanceof SqlOutParameter) {
				SqlOutParameter outParam = (SqlOutParameter) param;
				if (outParam.isReturnTypeSupported()) {
					Object out = outParam.getSqlReturnType().getTypeValue(
							cs, sqlColIndex, outParam.getSqlType(), outParam.getTypeName());
					returnedResults.put(outParam.getName(), out);
				}
				else {
					Object out = cs.getObject(sqlColIndex);
					if (out instanceof ResultSet) {
						if (outParam.isResultSetSupported()) {
							returnedResults.putAll(processResultSet((ResultSet) out, outParam));
						}
						else {
							logger.warn("ResultSet returned from stored procedure but a corresponding " +
									"SqlOutParameter with a RowCallbackHandler was not declared");
							returnedResults.put(outParam.getName(), "ResultSet was returned but not processed");
						}
					}
					else {
						returnedResults.put(outParam.getName(), out);
					}
				}
			}
			if (!(param instanceof SqlReturnResultSet)) {
				sqlColIndex++;
			}
		}
		return returnedResults;
	}

	/**
	 * Process the given ResultSet from a stored procedure.
	 * @param rs the ResultSet to process
	 * @param param the corresponding stored procedure parameter
	 * @return Map that contains returned results
	 */
	protected Map processResultSet(ResultSet rs, ResultSetSupportingSqlParameter param) throws SQLException {
		Map returnedResults = new HashMap();
		try {
			ResultSet rsToUse = rs;
			if (this.nativeJdbcExtractor != null) {
				rsToUse = this.nativeJdbcExtractor.getNativeResultSet(rs);
			}
			if (param.isRowCallbackHandlerSupported()) {
				// It's a RowCallbackHandler or RowMapper.
				// We'll get a RowCallbackHandler to use in both cases.
				RowCallbackHandler rch = param.getRowCallbackHandler();
				(new RowCallbackHandlerResultSetExtractor(rch)).extractData(rsToUse);
				if (rch instanceof ResultReader) {
					returnedResults.put(param.getName(), ((ResultReader) rch).getResults());
				}
				else {
					returnedResults.put(param.getName(), "ResultSet returned from stored procedure was processed.");
				}
			}
			else {
				// It's a ResultSetExtractor - simply apply it.
				Object result = param.getResultSetExtractor().extractData(rsToUse);
				returnedResults.put(param.getName(), result);
			}
		}
		finally {
			JdbcUtils.closeResultSet(rs);
		}
		return returnedResults;
	}


	/**
	 * Throw an SQLWarningException if we're not ignoring warnings.
	 * @param warning warning from current statement. May be null,
	 * in which case this method does nothing.
	 */
	private void throwExceptionOnWarningIfNotIgnoringWarnings(SQLWarning warning) throws SQLWarningException {
		if (warning != null) {
			if (this.ignoreWarnings) {
				logger.warn("SQLWarning ignored: " + warning);
			}
			else {
				throw new SQLWarningException("Warning not ignored", warning);
			}
		}
	}

	/**
	 * Determine SQL from potential provider object.
	 * @param sqlProvider object that's potentially a SqlProvider
	 * @return the SQL string, or null
	 * @see SqlProvider
	 */
	private String getSql(Object sqlProvider) {
		if (sqlProvider instanceof SqlProvider) {
			return ((SqlProvider) sqlProvider).getSql();
		}
		else {
			return null;
		}
	}

	/**
	 * Retrieve a standard JDBC object from a ResultSet using the getObject method.
	 * This method includes a "hack" to get around Oracle returning a non standard
	 * object for their TIMESTAMP datatype.
	 * @param rs is the ResultSet holding the data
	 * @param index is the column index
	 * @return the Object returned
	 */
	private static Object getJdbcObject(ResultSet rs, int index) throws SQLException {
		Object obj = rs.getObject(index);
		if (obj != null && obj.getClass().getName().startsWith("oracle.sql.TIMESTAMP")) {
			obj = rs.getTimestamp(index);
		}
		return obj;
	}


	/**
	 * Simple adapter for PreparedStatementCreator, allowing to use a plain SQL statement.
	 */
	private static class SimplePreparedStatementCreator
			implements PreparedStatementCreator, SqlProvider {

		private final String sql;

		public SimplePreparedStatementCreator(String sql) {
			this.sql = sql;
		}

		public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
			return con.prepareStatement(this.sql);
		}

		public String getSql() {
			return sql;
		}
	}


	/**
	 * Simple adapter for CallableStatementCreator, allowing to use a plain SQL statement.
	 */
	private static class SimpleCallableStatementCreator
			implements CallableStatementCreator, SqlProvider {

		private final String callString;

		public SimpleCallableStatementCreator(String callString) {
			this.callString = callString;
		}

		public CallableStatement createCallableStatement(Connection con) throws SQLException {
			return con.prepareCall(this.callString);
		}

		public String getSql() {
			return callString;
		}

	}


	/**
	 * Simple adapter for PreparedStatementSetter that applies
	 * a given array of arguments.
	 */
	private static class ArgPreparedStatementSetter implements PreparedStatementSetter, ParameterDisposer {

		private final Object[] args;

		public ArgPreparedStatementSetter(Object[] args) {
			this.args = args;
		}

		public void setValues(PreparedStatement ps) throws SQLException {
			if (this.args != null) {
				for (int i = 0; i < this.args.length; i++) {
					StatementCreatorUtils.setParameterValue(ps, i + 1, SqlTypeValue.TYPE_UNKNOWN, null, this.args[i]);
				}
			}
		}

		public void cleanupParameters() {
			StatementCreatorUtils.cleanupParameters(this.args);
		}
	}


	/**
	 * Simple adapter for PreparedStatementSetter that applies
	 * given arrays of arguments and JDBC argument types.
	 */
	private static class ArgTypePreparedStatementSetter implements PreparedStatementSetter, ParameterDisposer {

		private final Object[] args;

		private final int[] argTypes;

		public ArgTypePreparedStatementSetter(Object[] args, int[] argTypes) {
			if ((args != null && argTypes == null) || (args == null && argTypes != null) ||
					(args != null && args.length != argTypes.length)) {
				throw new InvalidDataAccessApiUsageException("args and argTypes parameters must match");
			}
			this.args = args;
			this.argTypes = argTypes;
		}

		public void setValues(PreparedStatement ps) throws SQLException {
			if (this.args != null) {
				for (int i = 0; i < this.args.length; i++) {
					StatementCreatorUtils.setParameterValue(ps, i + 1, this.argTypes[i], null, this.args[i]);
				}
			}
		}

		public void cleanupParameters() {
			StatementCreatorUtils.cleanupParameters(this.args);
		}
	}


	/**
	 * Adapter to enable use of a RowCallbackHandler inside a ResultSetExtractor.
	 * <p>Uses a regular ResultSet, so we have to be careful when using it:
	 * We don't use it for navigating since this could lead to unpredictable consequences.
	 */
	private static class RowCallbackHandlerResultSetExtractor implements ResultSetExtractor {

		private final RowCallbackHandler rch;

		public RowCallbackHandlerResultSetExtractor(RowCallbackHandler rch) {
			this.rch = rch;
		}

		public Object extractData(ResultSet rs) throws SQLException {
			while (rs.next()) {
				this.rch.processRow(rs);
			}
			if (this.rch instanceof ResultReader) {
				return ((ResultReader) this.rch).getResults();
			}
			else {
				return null;
			}
		}
	}


	/**
	 * ResultSetExtractor implementation that returns an ArrayList of HashMaps.
	 */
	private static class ListResultSetExtractor implements ResultSetExtractor {

		public Object extractData(ResultSet rs) throws SQLException {
			List listOfRows = new ArrayList();
			ResultSetMetaData rsmd = null;
			int numberOfColumns = -1;
			while (rs.next()) {
				if (rsmd == null) {
					// Lazily initialize meta data, to avoid unnecessary fetching
					// in case of an empty result set.
					rsmd = rs.getMetaData();
					numberOfColumns = rsmd.getColumnCount();
				}
				Map mapOfColValues = CollectionFactory.createLinkedMapIfPossible(numberOfColumns);
				for (int i = 1; i <= numberOfColumns; i++) {
					Object o = getJdbcObject(rs, i);
					mapOfColValues.put(rsmd.getColumnName(i), o);
				}
				listOfRows.add(mapOfColValues);
			}
			return listOfRows;
		}
	}


	/**
	 * ResultSetExtractor implementation that returns single result object.
	 */
	private static class ObjectResultSetExtractor implements ResultSetExtractor {

		private final Class requiredType;

		public ObjectResultSetExtractor(Class requiredType) {
			this.requiredType = requiredType;
		}

		public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
			if (!rs.next()) {
				throw new IncorrectResultSizeDataAccessException("Expected single row but found none", 1, 0);
			}
			ResultSetMetaData rsmd = rs.getMetaData();
			int nrOfColumns = rsmd.getColumnCount();
			if (nrOfColumns != 1) {
				throw new IncorrectResultSizeDataAccessException(
						"Expected single column but found " + nrOfColumns, 1, nrOfColumns);
			}
			Object result = getJdbcObject(rs, 1);
			if (rs.next()) {
				throw new IncorrectResultSizeDataAccessException("Expected single row but found more than one", 1, -1);
			}
			if (result != null && this.requiredType != null && !this.requiredType.isInstance(result)) {
				if (String.class.equals(this.requiredType)) {
					result = result.toString();
				}
				else if (Number.class.isAssignableFrom(this.requiredType) && Number.class.isInstance(result)) {
					try {
						result = NumberUtils.convertNumberToTargetClass(((Number) result), this.requiredType);
					}
					catch (IllegalArgumentException ex) {
						throw new TypeMismatchDataAccessException(ex.getMessage());
					}
				}
				else {
					throw new TypeMismatchDataAccessException(
							"Result object with column type '" + rsmd.getColumnTypeName(1) +
							"' and value [" + result + "] is of type [" + rsmd.getColumnClassName(1) +
							"] and could not be converted to required type [" + this.requiredType.getName() + "]");
				}
			}
			return result;
		}
	}

}