spring中jdbcTemplate归纳1

jdbcTemplate整理

为了实现基本的CRUD操作，spring给我们提供了jdbcTemplate这个模板类.实现最常用的CRUD操作。
先看jdbcTemplate的定义

 

public class JdbcTemplate extends JdbcAccessor implements JdbcOperations {

	/** Custom NativeJdbcExtractor */
	private NativeJdbcExtractor nativeJdbcExtractor;

	/** If this variable is false, we will throw exceptions on SQL warnings */
	private boolean ignoreWarnings = true;

	/**
	 * If this variable is set to a non-zero value, it will be used for setting the
	 * fetchSize property on statements used for query processing.
	 */
	private int fetchSize = 0;

	/**
	 * If this variable is set to a non-zero value, it will be used for setting the
	 * maxRows property on statements used for query processing.
	 */
	private int maxRows = 0;

	/**
	 * If this variable is set to true then all results checking will be bypassed for any
	 * callable statement processing.  This can be used to avoid a bug in some older Oracle
	 * JDBC drivers like 10.1.0.2.
	 */
	private boolean skipResultsProcessing = false;
	
}

 

 

JdbcTemplate继承自JdbcAccessor,同时实现了JdbcOperations接口，在整个框架中这个JdbcOperations接口只让

JdbcTemplate实现了。JdbcOperations定义了CRUD的操作。

public interface JdbcOperations {

	//-------------------------------------------------------------------------
	// Methods dealing with a plain java.sql.Connection
	//-------------------------------------------------------------------------

	/**
	 * Execute a JDBC data access operation, implemented as callback action
	 * working on a JDBC Connection. This allows for implementing arbitrary
	 * data access operations, within Spring's managed JDBC environment:
	 * that is, participating in Spring-managed transactions and converting
	 * JDBC SQLExceptions into Spring's DataAccessException hierarchy.
	 * <p>The callback action can return a result object, for example a
	 * domain object or a collection of domain objects.
	 * @param action the callback object that specifies the action
	 * @return a result object returned by the action, or <code>null</code>
	 * @throws DataAccessException if there is any problem
	 */
	Object execute(ConnectionCallback action) throws DataAccessException;


	//-------------------------------------------------------------------------
	// Methods dealing with static SQL (java.sql.Statement)
	//-------------------------------------------------------------------------

	/**
	 * Execute a JDBC data access operation, implemented as callback action
	 * working on a JDBC Statement. This allows for implementing arbitrary data
	 * access operations on a single Statement, within Spring's managed JDBC
	 * environment: that is, participating in Spring-managed transactions and
	 * converting JDBC SQLExceptions into Spring's DataAccessException hierarchy.
	 * <p>The callback action can return a result object, for example a
	 * domain object or a collection of domain objects.
	 * @param action callback object that specifies the action
	 * @return a result object returned by the action, or <code>null</code>
	 * @throws DataAccessException if there is any problem
	 */
	Object execute(StatementCallback action) throws DataAccessException;

	/**
	 * Issue a single SQL execute, typically a DDL statement.
	 * @param sql static SQL to execute
	 * @throws DataAccessException if there is any problem
	 */
	void execute(String sql) throws DataAccessException;

	/**
	 * Execute a query given static SQL, reading the ResultSet with a
	 * ResultSetExtractor.
	 * <p>Uses a JDBC Statement, not a PreparedStatement. If you want to
	 * execute a static query with a PreparedStatement, use the overloaded
	 * <code>query</code> method with <code>null</code> as argument array.
	 * @param sql SQL query to execute
	 * @param rse object that will extract all rows of results
	 * @return an arbitrary result object, as returned by the ResultSetExtractor
	 * @throws DataAccessException if there is any problem executing the query
	 * @see #query(String, Object[], ResultSetExtractor)
	 */
	Object query(String sql, ResultSetExtractor rse) throws DataAccessException;

	/**
	 * Execute a query given static SQL, reading the ResultSet on a per-row
	 * basis with a RowCallbackHandler.
	 * <p>Uses a JDBC Statement, not a PreparedStatement. If you want to
	 * execute a static query with a PreparedStatement, use the overloaded
	 * <code>query</code> method with <code>null</code> as argument array.
	 * @param sql SQL query to execute
	 * @param rch object that will extract results, one row at a time
	 * @throws DataAccessException if there is any problem executing the query
	 * @see #query(String, Object[], RowCallbackHandler)
	 */
	void query(String sql, RowCallbackHandler rch) throws DataAccessException;

	/**
	 * Execute a query given static SQL, mapping each row to a Java object
	 * via a RowMapper.
	 * <p>Uses a JDBC Statement, not a PreparedStatement. If you want to
	 * execute a static query with a PreparedStatement, use the overloaded
	 * <code>query</code> method with <code>null</code> as argument array.
	 * @param sql SQL query to execute
	 * @param rowMapper object that will map one object per row
	 * @return the result List, containing mapped objects
	 * @throws DataAccessException if there is any problem executing the query
	 * @see #query(String, Object[], RowMapper)
	 */
	List query(String sql, RowMapper rowMapper) throws DataAccessException;

	/**
	 * Execute a query given static SQL, mapping a single result row to a Java
	 * object via a RowMapper.
	 * <p>Uses a JDBC Statement, not a PreparedStatement. If you want to
	 * execute a static query with a PreparedStatement, use the overloaded
	 * <code>queryForObject</code> method with <code>null</code> as argument array.
	 * @param sql SQL query to execute
	 * @param rowMapper object that will map one object per row
	 * @return the single mapped object
	 * @throws IncorrectResultSizeDataAccessException if the query does not
	 * return exactly one row
	 * @throws DataAccessException if there is any problem executing the query
	 * @see #queryForObject(String, Object[], RowMapper)
	 */
	Object queryForObject(String sql, RowMapper rowMapper) throws DataAccessException;

	/**
	 * Execute a query for a result object, given static SQL.
	 * <p>Uses a JDBC Statement, not a PreparedStatement. If you want to
	 * execute a static query with a PreparedStatement, use the overloaded
	 * <code>queryForObject</code> method with <code>null</code> as argument array.
	 * <p>This method is useful for running static SQL with a known outcome.
	 * The query is expected to be a single row/single column query; the returned
	 * result will be directly mapped to the corresponding object type.
	 * @param sql SQL query to execute
	 * @param requiredType the type that the result object is expected to match
	 * @return the result object of the required type, or <code>null</code> in case of SQL NULL
	 * @throws IncorrectResultSizeDataAccessException if the query does not return
	 * exactly one row, or does not return exactly one column in that row
	 * @throws DataAccessException if there is any problem executing the query
	 * @see #queryForObject(String, Object[], Class)
	 */
	Object queryForObject(String sql, Class requiredType) throws DataAccessException;

	/**
	 * Execute a query for a result Map, given static SQL.
	 * <p>Uses a JDBC Statement, not a PreparedStatement. If you want to
	 * execute a static query with a PreparedStatement, use the overloaded
	 * <code>queryForMap</code> method with <code>null</code> as argument array.
	 * <p>The query is expected to be a single row query; the result row will be
	 * mapped to a Map (one entry for each column, using the column name as the key).
	 * @param sql SQL query to execute
	 * @return the result Map (one entry for each column, using the
	 * column name as the key)
	 * @throws IncorrectResultSizeDataAccessException if the query does not
	 * return exactly one row
	 * @throws DataAccessException if there is any problem executing the query
	 * @see #queryForMap(String, Object[])
	 * @see ColumnMapRowMapper
	 */
	Map queryForMap(String sql) throws DataAccessException;
}

 

 

中间的省略了。
定义的都是jdbc几方法。
JdbcAccessor是一个抽象类，本身继承了InitializingBean接口，在InitializingBean接口中只定义了一个方法

 

public interface InitializingBean {
	
	/**
	 * Invoked by a BeanFactory after it has set all bean properties supplied
	 * (and satisfied BeanFactoryAware and ApplicationContextAware).
	 * <p>This method allows the bean instance to perform initialization only
	 * possible when all bean properties have been set and to throw an
	 * exception in the event of misconfiguration.
	 * @throws Exception in the event of misconfiguration (such
	 * as failure to set an essential property) or if initialization fails.
	 */
	void afterPropertiesSet() throws Exception;

}

 

关于InitializingBean 这个接口以后介绍。继续返回到JdbcAccessor这个抽象类中，这个类做了什么呢？按照源代码

的注解说这个类是JdbcTemplate的一个基本类，同时也是其他Jdbc操作类的一个DAO工具类。定义了通用的属性，比如
DataSource和异常等信息。
看源代码

 

public abstract class JdbcAccessor implements InitializingBean {

	protected final Log logger = LogFactory.getLog(getClass());

	/** Used to obtain connections throughout the lifecycle of this object */
	private DataSource dataSource;

	/** Helper to translate SQL exceptions to DataAccessExceptions */
	private SQLExceptionTranslator exceptionTranslator;

	private boolean lazyInit = true;


	/**
	 * Set the JDBC DataSource to obtain connections from.
	 */
	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}

	/**
	 * Return the DataSource used by this template.
	 */
	public DataSource getDataSource() {
		return dataSource;
	}

	/**
	 * Specify the database product name for the DataSource that this accessor uses.
	 * This allows to initialize a SQLErrorCodeSQLExceptionTranslator without
	 * obtaining a Connection from the DataSource to get the metadata.
	 * @param dbName the database product name that identifies the error codes entry
	 * @see SQLErrorCodeSQLExceptionTranslator#setDatabaseProductName
	 * @see java.sql.DatabaseMetaData#getDatabaseProductName()
	 */
	public void setDatabaseProductName(String dbName) {
		this.exceptionTranslator = new SQLErrorCodeSQLExceptionTranslator(dbName);
	}

	/**
	 * Set the exception translator for this instance.
	 * <p>If no custom translator is provided, a default SQLErrorCodeSQLExceptionTranslator
	 * is used which examines the SQLException's vendor-specific error code.
	 * @param exceptionTranslator exception translator
	 * @see org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator
	 * @see org.springframework.jdbc.support.SQLStateSQLExceptionTranslator
	 */
	public void setExceptionTranslator(SQLExceptionTranslator exceptionTranslator) {
		this.exceptionTranslator = exceptionTranslator;
	}

	/**
	 * Return the exception translator for this instance.
	 * <p>Creates a default SQLErrorCodeSQLExceptionTranslator for the specified
	 * DataSource if none set.
	 */
	public SQLExceptionTranslator getExceptionTranslator() {
		if (this.exceptionTranslator == null) {
			DataSource dataSource = getDataSource();
			if (dataSource != null) {
				this.exceptionTranslator = new SQLErrorCodeSQLExceptionTranslator

(dataSource);
			}
			else {
				this.exceptionTranslator = new SQLStateSQLExceptionTranslator();
			}
		}
		return this.exceptionTranslator;
	}

	/**
	 * Set whether to lazily initialize the SQLExceptionTranslator for this accessor,
	 * on first encounter of a SQLException. Default is "true"; can be switched to
	 * "false" for initialization on startup.
	 * <p>Early initialization only applies if <code>afterPropertiesSet</code> is called.
	 * @see #getExceptionTranslator
	 * @see #afterPropertiesSet
	 */
	public void setLazyInit(boolean lazyInit) {
		this.lazyInit = lazyInit;
	}

	/**
	 * Return whether to lazily initialize the SQLExceptionTranslator for this accessor.
	 */
	public boolean isLazyInit() {
		return lazyInit;
	}

	/**
	 * Eagerly initialize the exception translator,
	 * creating a default one for the specified DataSource if none set.
	 */
	public void afterPropertiesSet() {
		if (getDataSource() == null) {
			throw new IllegalArgumentException("dataSource is required");
		}
		if (!isLazyInit()) {
			getExceptionTranslator();
		}
	}
	
}

 

这里只定义了三个属性， DataSource dataSource;SQLExceptionTranslator exceptionTranslator;boolean

lazyInit = true;DataSource 不用再说了，已经介绍过了，下面说说这个SQLExceptionTranslator，这到底是什么，

做什么用的，其实从名字就不能猜测出来，是一个sql异常翻译者。就是把sql底层的异常翻译成spring的异常

public interface SQLExceptionTranslator {

	/** 
	 * Translate the given {@link SQLException} into a generic {@link DataAccessException}.
	 * @param task readable text describing the task being attempted
	 * @param sql SQL query or update that caused the problem. May be <code>null</code>.
	 * @param ex the offending <code>SQLException</code> 
	 * @return the DataAccessException to throw 
	 */
	DataAccessException translate(String task, String sql, SQLException ex);

}

 

 

定义的一个接口，里面只有一个方法translate，意思是翻译的意思，
将一个sql异常转化为DataAccessException异常。
这个异常接口SQLExceptionTranslator 有俩个实现类，SQLErrorCodeSQLExceptionTranslator,

SQLStateSQLExceptionTranslator
SQLErrorCodeSQLExceptionTranslator的核心方法:

public class SQLErrorCodeSQLExceptionTranslator implements SQLExceptionTranslator {

	public DataAccessException translate(String task, String sql, SQLException sqlEx) {
		if (task == null) {
			task = "";
		}
		if (sql == null) {
			sql = "";
		}
		
		// First, try custom translation from overridden method.
		DataAccessException dex = customTranslate(task, sql, sqlEx);
		if (dex != null) {
			return dex;
		}

		// Check SQLErrorCodes with corresponding error code, if available.
		if (this.sqlErrorCodes != null) {
			String errorCode = null;
			if (this.sqlErrorCodes.isUseSqlStateForTranslation()) {
				errorCode = sqlEx.getSQLState();
			}
			else {
				errorCode = Integer.toString(sqlEx.getErrorCode());
			}

			if (errorCode != null) {

				// Look for defined custom translations first.
				CustomSQLErrorCodesTranslation[] customTranslations = 

this.sqlErrorCodes.getCustomTranslations();
				if (customTranslations != null) {
					for (int i = 0; i < customTranslations.length; i++) {
						CustomSQLErrorCodesTranslation customTranslation = 

customTranslations[i];
						if (Arrays.binarySearch

(customTranslation.getErrorCodes(), errorCode) >= 0) {
							if (customTranslation.getExceptionClass() != 

null) {
								DataAccessException customException = 

createCustomException(
										task, sql, sqlEx, 

customTranslation.getExceptionClass());
								if (customException != null) {
									logTranslation(task, sql, 

sqlEx, true);
									return customException;
								}
							}
						}
					}
				}

				// Next, look for grouped error codes.
				if (Arrays.binarySearch(this.sqlErrorCodes.getBadSqlGrammarCodes(), 

errorCode) >= 0) {
					logTranslation(task, sql, sqlEx, false);
					return new BadSqlGrammarException(task, sql, sqlEx);
				}
				else if (Arrays.binarySearch

(this.sqlErrorCodes.getInvalidResultSetAccessCodes(), errorCode) >= 0) {
					logTranslation(task, sql, sqlEx, false);
					return new InvalidResultSetAccessException(task, sql, sqlEx);
				}
				else if (Arrays.binarySearch

(this.sqlErrorCodes.getDataAccessResourceFailureCodes(), errorCode) >= 0) {
					logTranslation(task, sql, sqlEx, false);
					return new DataAccessResourceFailureException(buildMessage

(task, sql, sqlEx), sqlEx);
				}
				else if (Arrays.binarySearch

(this.sqlErrorCodes.getPermissionDeniedCodes(), errorCode) >= 0) {
					logTranslation(task, sql, sqlEx, false);
					return new PermissionDeniedDataAccessException(buildMessage

(task, sql, sqlEx), sqlEx);
				}
				else if (Arrays.binarySearch

(this.sqlErrorCodes.getDataIntegrityViolationCodes(), errorCode) >= 0) {
					logTranslation(task, sql, sqlEx, false);
					return new DataIntegrityViolationException(buildMessage(task, 

sql, sqlEx), sqlEx);
				}
				else if (Arrays.binarySearch

(this.sqlErrorCodes.getCannotAcquireLockCodes(), errorCode) >= 0) {
					logTranslation(task, sql, sqlEx, false);
					return new CannotAcquireLockException(buildMessage(task, sql, 

sqlEx), sqlEx);
				}
				else if (Arrays.binarySearch(this.sqlErrorCodes.getDeadlockLoserCodes

(), errorCode) >= 0) {
					logTranslation(task, sql, sqlEx, false);
					return new DeadlockLoserDataAccessException(buildMessage(task, 

sql, sqlEx), sqlEx);
				}
				else if (Arrays.binarySearch

(this.sqlErrorCodes.getCannotSerializeTransactionCodes(), errorCode) >= 0) {
					logTranslation(task, sql, sqlEx, false);
					return new CannotSerializeTransactionException(buildMessage

(task, sql, sqlEx), sqlEx);
				}
			}
		}

		// We couldn't identify it more precisely - let's hand it over to the SQLState fallback 

translator.
		if (logger.isDebugEnabled()) {
			String codes = null;
			if (this.sqlErrorCodes.isUseSqlStateForTranslation()) {
				codes = "SQL state '" + sqlEx.getSQLState() +
					"', error code '" + sqlEx.getErrorCode();
			}
			else {
				codes = "Error code '" + sqlEx.getErrorCode() + "'";
			}
			logger.debug("Unable to translate SQLException with " + codes +
					", will now try the fallback translator");
		}
		return this.fallbackTranslator.translate(task, sql, sqlEx);
	}
}

 

 
根据 errorCode = sqlEx.getSQLState()，返回不同的值，返回不同的异常信息。

public class SQLStateSQLExceptionTranslator implements SQLExceptionTranslator {

	/**
	 * Set of well-known String 2-digit codes that indicate bad SQL
	 */
	private static final Set BAD_SQL_CODES = new HashSet(6);

	/**
	 * Set of well-known String 2-digit codes that indicate RDBMS integrity violation
	 */
	private static final Set INTEGRITY_VIOLATION_CODES = new HashSet(4);

	/**
	 * Set of String 2-digit codes that indicate communication errors
	 */
	private static final Set RESOURCE_FAILURE_CODES = new HashSet(3);

	/**
	 * Set of String 2-digit codes that indicate concurrency errors
	 */
	private static final Set CONCURRENCY_CODES = new HashSet(1);


	// Populate reference data.
	static {
		BAD_SQL_CODES.add("07");
		BAD_SQL_CODES.add("37");
		BAD_SQL_CODES.add("42");
		BAD_SQL_CODES.add("2A");
		BAD_SQL_CODES.add("65");	// Oracle throws this on unknown identifier
		BAD_SQL_CODES.add("S0");	// MySQL uses this - from ODBC error codes?

		INTEGRITY_VIOLATION_CODES.add("22");	// Integrity constraint violation
		INTEGRITY_VIOLATION_CODES.add("23");	// Integrity constraint violation
		INTEGRITY_VIOLATION_CODES.add("27");	// Triggered data change violation
		INTEGRITY_VIOLATION_CODES.add("44");	// With check violation

		CONCURRENCY_CODES.add("40");	// Transaction rollback

		RESOURCE_FAILURE_CODES.add("08");	// Connection exception
		RESOURCE_FAILURE_CODES.add("53");	// PostgreSQL uses this - insufficient 

resources (e.g. disk full)
		RESOURCE_FAILURE_CODES.add("54");	// PostgreSQL uses this - program limit 

exceeded (e.g. statement too complex)
	}


	/** Logger available to subclasses */
	protected final Log logger = LogFactory.getLog(getClass());


	public DataAccessException translate(String task, String sql, SQLException ex) {
		Assert.notNull(ex, "Cannot translate a null SQLException.");
		if (task == null) {
			task = "";
		}
		if (sql == null) {
			sql = "";
		}
		String sqlState = getSqlState(ex);
		if (sqlState != null && sqlState.length() >= 2) {
			String classCode = sqlState.substring(0, 2);
			if (BAD_SQL_CODES.contains(classCode)) {
				return new BadSqlGrammarException(task, sql, ex);
			}
			else if (INTEGRITY_VIOLATION_CODES.contains(classCode)) {
				return new DataIntegrityViolationException(buildMessage(task, sql, ex), 

ex);
			}
			else if (RESOURCE_FAILURE_CODES.contains(classCode)) {
				return new DataAccessResourceFailureException(buildMessage(task, sql, 

ex), ex);
			}
			else if (CONCURRENCY_CODES.contains(classCode)) {
				return new ConcurrencyFailureException(buildMessage(task, sql, ex), 

ex);
			}
		}
		// We couldn't identify it more precisely.
		return new UncategorizedSQLException(task, sql, ex);
	}


	/**
	 * Build a message <code>String</code> for the given {@link SQLException}.
	 * <p>Called when creating an instance of a generic
	 * {@link DataAccessException} class.
	 * @param task readable text describing the task being attempted
	 * @param sql  the SQL statement that caused the problem. May be <code>null</code>.
	 * @param ex   the offending <code>SQLException</code>
	 * @return the message <code>String</code> to use
	 */
	protected String buildMessage(String task, String sql, SQLException ex) {
		return task + "; SQL [" + sql + "]; " + ex.getMessage();
	}


	/**
	 * Gets the SQL state code from the supplied {@link SQLException exception}.
	 * <p>Some JDBC drivers nest the actual exception from a batched update, so we
	 * might need to dig down into the nested exception.
	 * @param ex the exception from which the {@link SQLException#getSQLState() SQL state} is to be 

extracted
	 * @return the SQL state code
	 */
	private String getSqlState(SQLException ex) {
		String sqlState = ex.getSQLState();
		if (sqlState == null) {
			SQLException nestedEx = ex.getNextException();
			if (nestedEx != null) {
				sqlState = nestedEx.getSQLState();
			}
		}
		return sqlState;
	}

}

 

 

关于异常这部分以后详细介绍，在JdbcTemplate中碰到就稍微说说，以后再详细介绍，下面在返回来看JdbcTemplate.

这个类。