java操作数据库的基本步骤如下:
- 建立数据库连接
- 创建Statement或者PreparedStatement
- 执行sql,返回ResultSet
- 关闭ResultSet
- 关闭Statement
- 关闭Connection
Spring JDBC
在Spring JDBC中,JdbcTemplate是一个主要的模板类,继承关系如图
- JdbcAccessor对DataSource数据源进行管理和配置
- JdbcOperations接口定义了通过JDBC操作数据库的基本操作方法
- JdbcTemplate实现了的数据库的操作方法
JdbcAccessor的源码:
public abstract class JdbcAccessor implements InitializingBean {
/** Logger available to subclasses */
protected final Log logger = LogFactory.getLog(getClass());
//数据源 在配置文件中指定
private DataSource dataSource;
private SQLExceptionTranslator exceptionTranslator;
private boolean lazyInit = true;
public void setDataSource(DataSource dataSource) {
this.dataSource = dataSource;
}
public DataSource getDataSource() {
return this.dataSource;
}
public void setDatabaseProductName(String dbName) {
this.exceptionTranslator = new SQLErrorCodeSQLExceptionTranslator(dbName);
}
public void setExceptionTranslator(SQLExceptionTranslator exceptionTranslator) {
this.exceptionTranslator = exceptionTranslator;
}
public synchronized 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;
}
public void setLazyInit(boolean lazyInit) {
this.lazyInit = lazyInit;
}
public boolean isLazyInit() {
return this.lazyInit;
}
/**
*当前类实现 InitializingBean接口,该方法在JdbcTemplate初始化被调用
*/
@Override
public void afterPropertiesSet() {
if (getDataSource() == null) {
throw new IllegalArgumentException("Property 'dataSource' is required");
}
if (!isLazyInit()) {
getExceptionTranslator();
}
}
}
下面是数据源的配置内容
<bean id="dataSource" class="app.datasource.DataSource"
destroy-method="close">
<!--省略数据库配置-->
</bean>
<!-- 配置jdbcTemplate -->
<bean id="jdbcTemplate"
class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"></property>
</bean>
JdbcTemplate
JdbcTemplate的execute实现
数据源配置好之后,我们开始对据库进行操作,下面以JdbcTemplate中execute()方法为例:
//执行输入的sql语句
public void execute(final String sql) throws DataAccessException {
if (logger.isDebugEnabled()) {
logger.debug("Executing SQL statement [" + sql + "]");
}
class ExecuteStatementCallback implements StatementCallback<Object>, SqlProvider {
@Override
public Object doInStatement(Statement stmt) throws SQLException {
stmt.execute(sql);
return null;
}
@Override
public String getSql() {
return sql;
}
}
execute(new ExecuteStatementCallback());
}
//java.sql.Statement执行静态sql
public <T> T execute(StatementCallback<T> action) throws DataAccessException {
Assert.notNull(action, "Callback object must not be null");
//获取对数据的连接
Connection con = DataSourceUtils.getConnection(getDataSource());
Statement stmt = null;
try {
Connection conToUse = con;
if (this.nativeJdbcExtractor != null &&
this.nativeJdbcExtractor.isNativeConnectionNecessaryForNativeStatements()) {
conToUse = this.nativeJdbcExtractor.getNativeConnection(con);
}
//创建Statement
stmt = conToUse.createStatement();
applyStatementSettings(stmt);
Statement stmtToUse = stmt;
if (this.nativeJdbcExtractor != null) {
stmtToUse = this.nativeJdbcExtractor.getNativeStatement(stmt);
}
//执行回调,也就是Statement执行静态sql
T result = action.doInStatement(stmtToUse);
handleWarnings(stmt);
return result;
}
catch (SQLException ex) {
//捕获异常,释放数据库的connection,抛出转换的异常信息
JdbcUtils.closeStatement(stmt);
stmt = null;
DataSourceUtils.releaseConnection(con, getDataSource());
con = null;
throw getExceptionTranslator().translate("StatementCallback", getSql(action), ex);
}
finally {
JdbcUtils.closeStatement(stmt);
放连接,以避免潜在的死锁连接池
DataSourceUtils.releaseConnection(con, getDataSource());
}
}
JdbcTemplate的query实现
query方法依赖上面的execute方法,通过QueryStatementCallback的回调doInStatement来实现,回调中是Statement的执行以及查询结果的返回等处理.
public <T> T query(final String sql, final ResultSetExtractor<T> rse) throws DataAccessException {
Assert.notNull(sql, "SQL must not be null");
Assert.notNull(rse, "ResultSetExtractor must not be null");
if (logger.isDebugEnabled()) {
logger.debug("Executing SQL query [" + sql + "]");
}
class QueryStatementCallback implements StatementCallback<T>, SqlProvider {
@Override
public T doInStatement(Statement stmt) throws SQLException {
ResultSet rs = null;
try {
rs = stmt.executeQuery(sql);
ResultSet rsToUse = rs;
if (nativeJdbcExtractor != null) {
rsToUse = nativeJdbcExtractor.getNativeResultSet(rs);
}
//返回需要的记录集合
return rse.extractData(rsToUse);
}
finally {
//关闭查询的记录集合,数据库的connection在execute()中释放
JdbcUtils.closeResultSet(rs);
}
}
@Override
public String getSql() {
return sql;
}
}
//调用execute()方法
return execute(new QueryStatementCallback());
}
JdbcTemplate的update实现
update方法依赖上面的execute方法,通过UpdateStatementCallback的回调doInStatement来实现,回调中是Statement的执行.
@Override
public int update(final String sql) throws DataAccessException {
Assert.notNull(sql, "SQL must not be null");
if (logger.isDebugEnabled()) {
logger.debug("Executing SQL update [" + sql + "]");
}
class UpdateStatementCallback implements StatementCallback<Integer>, SqlProvider {
@Override
public Integer doInStatement(Statement stmt) throws SQLException {
int rows = stmt.executeUpdate(sql);
if (logger.isDebugEnabled()) {
logger.debug("SQL update affected " + rows + " rows");
}
return rows;
}
@Override
public String getSql() {
return sql;
}
}
return execute(new UpdateStatementCallback());
}
数据库的Connection
我们在以上的操作中使用DataSourceUtils对数据库的Connection进行管理.
//获取数据库的连接,这里进行了异常的转换,抛出CannotGetJdbcConnectionException异常
public static Connection getConnection(DataSource dataSource) throws CannotGetJdbcConnectionException {
try {
return doGetConnection(dataSource);
}
catch (SQLException ex) {
throw new CannotGetJdbcConnectionException("Could not get JDBC Connection", ex);
}
}
public static Connection doGetConnection(DataSource dataSource) throws SQLException {
Assert.notNull(dataSource, "No DataSource specified");
//从TransactionSynchronizationManager中的线程取connection
ConnectionHolder conHolder = (ConnectionHolder) TransactionSynchronizationManager.getResource(dataSource);
if (conHolder != null && (conHolder.hasConnection() || conHolder.isSynchronizedWithTransaction())) {
conHolder.requested();
if (!conHolder.hasConnection()) {
logger.debug("Fetching resumed JDBC Connection from DataSource");
conHolder.setConnection(dataSource.getConnection());
}
return conHolder.getConnection();
}
// Else we either got no holder or an empty thread-bound holder here.
//获取数据库的Connection
logger.debug("Fetching JDBC Connection from DataSource");
Connection con = dataSource.getConnection();
if (TransactionSynchronizationManager.isSynchronizationActive()) {
logger.debug("Registering transaction synchronization for JDBC Connection");
// Use same Connection for further JDBC actions within the transaction.
// Thread-bound object will get removed by synchronization at transaction completion.
ConnectionHolder holderToUse = conHolder;
if (holderToUse == null) {
holderToUse = new ConnectionHolder(con);
}
else {
holderToUse.setConnection(con);
}
holderToUse.requested();
//TransactionSynchronizationManager把connection与当前线程绑定
TransactionSynchronizationManager.registerSynchronization(
new ConnectionSynchronization(holderToUse, dataSource));
holderToUse.setSynchronizedWithTransaction(true);
if (holderToUse != conHolder) {
TransactionSynchronizationManager.bindResource(dataSource, holderToUse);
}
}
return con;
}
结果集处理回调
- RowMapper 结果集转换接口,用户需实现方法mapRow(ResultSet rs, int rowNum)来完成将每行数据转换为相应的类型。
- ColumnMapRowMapper 结果集转换成Map,每一行数据转换成一个Map
- BeanPropertyRowMapper 结果集转换成指定的实例对象(一行数据一个对象)
- ResultSetExtractor 用于结果集数据提取,用户需实现方法extractData(ResultSet rs)来处理结果集,用户必须处理整个结果集;
下面看一下ColumnMapRowMapper的源码,结果返回Map对象
@FunctionalInterface
public interface RowMapper<T> {
T mapRow(ResultSet rs, int rowNum) throws SQLException;
}
public class ColumnMapRowMapper implements RowMapper<Map<String, Object>> {
@Override
public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
ResultSetMetaData rsmd = rs.getMetaData();
//ResultSet中列数
int columnCount = rsmd.getColumnCount();
//创建Map对象,经过包装的LinkedHashMap
Map<String, Object> mapOfColValues = createColumnMap(columnCount);
for (int i = 1; i <= columnCount; i++) {
//获取列名(sql中as指定的,若没有as则直接表中的列名)
String key = getColumnKey(JdbcUtils.lookupColumnName(rsmd, i));
//获取对应的值
Object obj = getColumnValue(rs, i);
mapOfColValues.put(key, obj);
}
return mapOfColValues;
}
protected Map<String, Object> createColumnMap(int columnCount) {
return new LinkedCaseInsensitiveMap<>(columnCount);
}
protected String getColumnKey(String columnName) {
return columnName;
}
protected Object getColumnValue(ResultSet rs, int index) throws SQLException {
return JdbcUtils.getResultSetValue(rs, index);
}
}
BeanPropertyRowMapper中mapRow的实现
public T mapRow(ResultSet rs, int rowNumber) throws SQLException {
Assert.state(this.mappedClass != null, "Mapped class was not specified");
//实例化一个对象
T mappedObject = BeanUtils.instantiateClass(this.mappedClass);
//BeanWrapper中的Object持有mappedObject对象
BeanWrapper bw = PropertyAccessorFactory.forBeanPropertyAccess(mappedObject);
initBeanWrapper(bw);
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
//是否检验对象的属性与数据库中查出的属性一致,默认不需要
Set<String> populatedProperties = (isCheckFullyPopulated() ? new HashSet<>() : null);
for (int index = 1; index <= columnCount; index++) {
String column = JdbcUtils.lookupColumnName(rsmd, index);
String field = lowerCaseName(column.replaceAll(" ", ""));
PropertyDescriptor pd = this.mappedFields.get(field);
if (pd != null) {
try {
Object value = getColumnValue(rs, index, pd);
if (rowNumber == 0 && logger.isDebugEnabled()) {
logger.debug("Mapping column '" + column + "' to property '" + pd.getName() +
"' of type [" + ClassUtils.getQualifiedName(pd.getPropertyType()) + "]");
}
try {
bw.setPropertyValue(pd.getName(), value);
}
catch (TypeMismatchException ex) {
if (value == null && this.primitivesDefaultedForNullValue) {
if (logger.isDebugEnabled()) {
logger.debug("Intercepted TypeMismatchException for row " + rowNumber +
" and column '" + column + "' with null value when setting property '" +
pd.getName() + "' of type [" +
ClassUtils.getQualifiedName(pd.getPropertyType()) +
"] on object: " + mappedObject, ex);
}
}
else {
throw ex;
}
}
if (populatedProperties != null) {
populatedProperties.add(pd.getName());
}
}
catch (NotWritablePropertyException ex) {
throw new DataRetrievalFailureException(
"Unable to map column '" + column + "' to property '" + pd.getName() + "'", ex);
}
}
else {
// No PropertyDescriptor found
if (rowNumber == 0 && logger.isDebugEnabled()) {
logger.debug("No property found for column '" + column + "' mapped to field '" + field + "'");
}
}
}
if (populatedProperties != null && !populatedProperties.equals(this.mappedProperties)) {
throw new InvalidDataAccessApiUsageException("Given ResultSet does not contain all fields " +
"necessary to populate object of class [" + this.mappedClass.getName() + "]: " +
this.mappedProperties);
}
return mappedObject;
}
结果集的转换时通过ResultSetExtractor来完成的,ResultSetExtractor在这里是于适配器的作用
public interface ResultSetExtractor<T> {
T extractData(ResultSet var1) throws SQLException, DataAccessException;
}
public class RowMapperResultSetExtractor<T> implements ResultSetExtractor<List<T>> {
private final RowMapper<T> rowMapper;
private final int rowsExpected;
public RowMapperResultSetExtractor(RowMapper<T> rowMapper) {
this(rowMapper, 0);
}
public RowMapperResultSetExtractor(RowMapper<T> rowMapper, int rowsExpected) {
Assert.notNull(rowMapper, "RowMapper is required");
this.rowMapper = rowMapper;
this.rowsExpected = rowsExpected;
}
public List<T> extractData(ResultSet rs) throws SQLException {
ArrayList results = this.rowsExpected > 0?new ArrayList(this.rowsExpected):new ArrayList();
int rowNum = 0;
while(rs.next()) {
results.add(this.rowMapper.mapRow(rs, rowNum++));
}
return results;
}
}