J2EE系列之Spring4學習筆記（十）--Spring對JDBC的支援__Spring使用JdbcDaoSuppo

一、JdbcDaoSupport類的引入

上一節的工程裡面資料庫操作實作類別StudentDaoImpl類中要想使用Spring操作資料庫需要首先定義JdbcTemplate類的對象，這一節我們引入Spring對JDBC支援的JdbcDaoSupport類，首先看一下這個類中的部分內容：

/* * Copyright 2002-2012 the original author or authors. * * Licensed under the Apache License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. * You may obtain a copy of the License at * *      http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. */package org.springframework.jdbc.core.support;import java.sql.Connection;import javax.sql.DataSource;import org.springframework.dao.support.DaoSupport;import org.springframework.jdbc.CannotGetJdbcConnectionException;import org.springframework.jdbc.core.JdbcTemplate;import org.springframework.jdbc.datasource.DataSourceUtils;import org.springframework.jdbc.support.SQLExceptionTranslator;/** * Convenient super class for JDBC-based data access objects. * * <p>Requires a {@link javax.sql.DataSource} to be set, providing a * {@link org.springframework.jdbc.core.JdbcTemplate} based on it to * subclasses through the {@link #getJdbcTemplate()} method. * * <p>This base class is mainly intended for JdbcTemplate usage but can * also be used when working with a Connection directly or when using * {@code org.springframework.jdbc.object} operation objects. * * @author Juergen Hoeller * @since 28.07.2003 * @see #setDataSource * @see #getJdbcTemplate * @see org.springframework.jdbc.core.JdbcTemplate */public abstract class JdbcDaoSupport extends DaoSupport {private JdbcTemplate jdbcTemplate;/** * Set the JDBC DataSource to be used by this DAO. */public final void setDataSource(DataSource dataSource) {if (this.jdbcTemplate == null || dataSource != this.jdbcTemplate.getDataSource()) {this.jdbcTemplate = createJdbcTemplate(dataSource);initTemplateConfig();}}/** * Create a JdbcTemplate for the given DataSource. * Only invoked if populating the DAO with a DataSource reference! * <p>Can be overridden in subclasses to provide a JdbcTemplate instance * with different configuration, or a custom JdbcTemplate subclass. * @param dataSource the JDBC DataSource to create a JdbcTemplate for * @return the new JdbcTemplate instance * @see #setDataSource */protected JdbcTemplate createJdbcTemplate(DataSource dataSource) {return new JdbcTemplate(dataSource);}/** * Return the JDBC DataSource used by this DAO. */public final DataSource getDataSource() {return (this.jdbcTemplate != null ? this.jdbcTemplate.getDataSource() : null);}/** * Set the JdbcTemplate for this DAO explicitly, * as an alternative to specifying a DataSource. */public final void setJdbcTemplate(JdbcTemplate jdbcTemplate) {this.jdbcTemplate = jdbcTemplate;initTemplateConfig();}/** * Return the JdbcTemplate for this DAO, * pre-initialized with the DataSource or set explicitly. */public final JdbcTemplate getJdbcTemplate() {  return this.jdbcTemplate;}}

可以看到這個類中定義了JdbcTemplate類的對象，並且裡面有這個對象的get和set方法，裡面還有DataSource類的set方法。

我們修改StudentDaoImpl類，讓這個類繼承JdbcDaoSupport類：

package com.test.dao.impl;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;import org.springframework.jdbc.core.JdbcTemplate;import org.springframework.jdbc.core.RowCallbackHandler;import org.springframework.jdbc.core.support.JdbcDaoSupport;import com.test.dao.StudentDao;import com.test.model.Student;public class StudentDaoImpl extends JdbcDaoSupport implements StudentDao{@Overridepublic int addStudent(Student student) {String sql = "insert into t_student values(null,?,?)";Object []params = new Object[]{student.getName(),student.getAge()};return this.getJdbcTemplate().update(sql,params); }@Overridepublic int updateStudent(Student student) {String sql = "update t_student set name=?,age=? where id=?";Object []params = new Object[]{student.getName(),student.getAge(),student.getId()};return this.getJdbcTemplate().update(sql,params);}@Overridepublic int deleteStudent(int id) {String sql = "delete from t_student where id=?";Object []params = new Object[]{id};return this.getJdbcTemplate().update(sql,params);}@Overridepublic List<Student> findStudents() {String sql = "select * from t_student";final List<Student> studentList = new ArrayList<Student>();this.getJdbcTemplate().query(sql, new RowCallbackHandler(){@Overridepublic void processRow(ResultSet rs) throws SQLException {Student student = new Student();student.setId(rs.getInt("id"));student.setName(rs.getString("name"));student.setAge(rs.getInt("age"));studentList.add(student);}});return studentList;}}

這裡把之前定義的JdbcTemplate類的對象給去掉了，並調用了get方法來擷取父類JdbcDaoSupport類中的JdbcTemplate類對象。

這裡不需要定義JdbcTemplate類的對象了，修改Spring設定檔：

<?xml version="1.0" encoding="UTF-8"?><beans xmlns="http://www.springframework.org/schema/beans"    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"    xmlns:aop="http://www.springframework.org/schema/aop"    xmlns:context="http://www.springframework.org/schema/context"    xsi:schemaLocation="http://www.springframework.org/schema/beans        http://www.springframework.org/schema/beans/spring-beans.xsd        http://www.springframework.org/schema/aop        http://www.springframework.org/schema/aop/spring-aop.xsd        http://www.springframework.org/schema/context        http://www.springframework.org/schema/context/spring-context.xsd">            <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">        <property name="driverClassName" value="${jdbc.driverClassName}"/>        <property name="url" value="${jdbc.url}"/>        <property name="username" value="${jdbc.username}"/>        <property name="password" value="${jdbc.password}"/>    </bean><context:property-placeholder location="jdbc.properties"/><bean id="studentDao" class="com.test.dao.impl.StudentDaoImpl"><property name="dataSource" ref="dataSource"></property></bean><bean id="studentService" class="com.test.service.impl.StudentServiceImpl"><property name="studentDao" ref="studentDao"></property></bean></beans>

這裡去掉了定義JdbcTemplate類的執行個體，把dataSource對象值注入到studentDao執行個體中。

運行各個測試方法，效果和上一節相同。

二、NamedParameterJdbcTemplate 類的使用

還是在上一節部落格的工程基礎上進行修改。上一節中使用Spring操作資料庫使用的是JdbcTemplate類，使用這個類時要先寫好sql語句然後調用JdbcTemplate類中的方法即可。這裡在寫的sql語句格式如下：

String sql="insert into t_student values(null,?,?)";Object []params=new Object[]{student.getName(),student.getAge()};return jdbcTemplate.update(sql,params);

要傳入的參數用問號。表示，這樣的話代碼的可讀性不好。

這裡我們不使用JdbcTemplate類了，而是使用Spring對JDBC操作支援的另一個類NamedParameterJdbcTemplate類，這個類的使用方法與JdbcTemplate相差不多，但是它支援具名引數變數。

截取一部分這個類的代碼為：

/* * Copyright 2002-2014 the original author or authors. * * Licensed under the Apache License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. * You may obtain a copy of the License at * *      http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. */package org.springframework.jdbc.core.namedparam;import java.util.LinkedHashMap;import java.util.List;import java.util.Map;import javax.sql.DataSource;import org.springframework.dao.DataAccessException;import org.springframework.dao.support.DataAccessUtils;import org.springframework.jdbc.core.ColumnMapRowMapper;import org.springframework.jdbc.core.JdbcOperations;import org.springframework.jdbc.core.JdbcTemplate;import org.springframework.jdbc.core.PreparedStatementCallback;import org.springframework.jdbc.core.PreparedStatementCreator;import org.springframework.jdbc.core.PreparedStatementCreatorFactory;import org.springframework.jdbc.core.ResultSetExtractor;import org.springframework.jdbc.core.RowCallbackHandler;import org.springframework.jdbc.core.RowMapper;import org.springframework.jdbc.core.SingleColumnRowMapper;import org.springframework.jdbc.core.SqlParameter;import org.springframework.jdbc.core.SqlRowSetResultSetExtractor;import org.springframework.jdbc.support.KeyHolder;import org.springframework.jdbc.support.rowset.SqlRowSet;import org.springframework.util.Assert;/** * Template class with a basic set of JDBC operations, allowing the use * of named parameters rather than traditional '?' placeholders. * * <p>This class delegates to a wrapped {@link #getJdbcOperations() JdbcTemplate} * once the substitution from named parameters to JDBC style '?' placeholders is * done at execution time. It also allows for expanding a {@link java.util.List} * of values to the appropriate number of placeholders. * * <p>The underlying {@link org.springframework.jdbc.core.JdbcTemplate} is * exposed to allow for convenient access to the traditional * {@link org.springframework.jdbc.core.JdbcTemplate} methods. * * <p><b>NOTE: An instance of this class is thread-safe once configured.</b> * * @author Thomas Risberg * @author Juergen Hoeller * @since 2.0 * @see NamedParameterJdbcOperations * @see org.springframework.jdbc.core.JdbcTemplate */public class NamedParameterJdbcTemplate implements NamedParameterJdbcOperations {/** Default maximum number of entries for this template's SQL cache: 256 */public static final int DEFAULT_CACHE_LIMIT = 256;/** The JdbcTemplate we are wrapping */private final JdbcOperations classicJdbcTemplate;private volatile int cacheLimit = DEFAULT_CACHE_LIMIT;/** Cache of original SQL String to ParsedSql representation */@SuppressWarnings("serial")private final Map<String, ParsedSql> parsedSqlCache =new LinkedHashMap<String, ParsedSql>(DEFAULT_CACHE_LIMIT, 0.75f, true) {@Overrideprotected boolean removeEldestEntry(Map.Entry<String, ParsedSql> eldest) {return size() > getCacheLimit();}};/** * Create a new NamedParameterJdbcTemplate for the given {@link DataSource}. * <p>Creates a classic Spring {@link org.springframework.jdbc.core.JdbcTemplate} and wraps it. * @param dataSource the JDBC DataSource to access */public NamedParameterJdbcTemplate(DataSource dataSource) {Assert.notNull(dataSource, "DataSource must not be null");this.classicJdbcTemplate = new JdbcTemplate(dataSource);}@Overridepublic <T> T query(String sql, SqlParameterSource paramSource, ResultSetExtractor<T> rse)throws DataAccessException {return getJdbcOperations().query(getPreparedStatementCreator(sql, paramSource), rse);}@Overridepublic <T> T query(String sql, Map<String, ?> paramMap, ResultSetExtractor<T> rse)throws DataAccessException {return query(sql, new MapSqlParameterSource(paramMap), rse);}@Overridepublic <T> T query(String sql, ResultSetExtractor<T> rse) throws DataAccessException {return query(sql, EmptySqlParameterSource.INSTANCE, rse);}@Overridepublic void query(String sql, SqlParameterSource paramSource, RowCallbackHandler rch)throws DataAccessException {getJdbcOperations().query(getPreparedStatementCreator(sql, paramSource), rch);}@Overridepublic void query(String sql, Map<String, ?> paramMap, RowCallbackHandler rch)throws DataAccessException {query(sql, new MapSqlParameterSource(paramMap), rch);}@Overridepublic void query(String sql, RowCallbackHandler rch) throws DataAccessException {query(sql, EmptySqlParameterSource.INSTANCE, rch);}@Overridepublic int update(String sql, SqlParameterSource paramSource) throws DataAccessException {return getJdbcOperations().update(getPreparedStatementCreator(sql, paramSource));}@Overridepublic int update(String sql, Map<String, ?> paramMap) throws DataAccessException {return update(sql, new MapSqlParameterSource(paramMap));}@Overridepublic int update(String sql, SqlParameterSource paramSource, KeyHolder generatedKeyHolder)throws DataAccessException {return update(sql, paramSource, generatedKeyHolder, null);}}

可以看到這個類中需要賦值的屬性沒有，但是構造這個類的對象的時候需要傳入DataSource類的對象。

1.修改StudentDaoImpl類代碼：

package com.test.dao.impl;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;import org.springframework.jdbc.core.JdbcTemplate;import org.springframework.jdbc.core.RowCallbackHandler;import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;import com.test.dao.StudentDao;import com.test.model.Student;public class StudentDaoImpl implements StudentDao{private NamedParameterJdbcTemplate namedParameterJdbcTemplate;public void setNamedParameterJdbcTemplate(NamedParameterJdbcTemplate namedParameterJdbcTemplate) {this.namedParameterJdbcTemplate = namedParameterJdbcTemplate;}@Overridepublic int addStudent(Student student) {String sql="insert into t_student values(null,:name,:age)";MapSqlParameterSource sps = new MapSqlParameterSource();sps.addValue("name", student.getName());sps.addValue("age", student.getAge());return namedParameterJdbcTemplate.update(sql,sps);}@Overridepublic int updateStudent(Student student) {String sql="update t_student set name=:name,age=:age where id=:id";MapSqlParameterSource sps = new MapSqlParameterSource();sps.addValue("name", student.getName());sps.addValue("age", student.getAge());sps.addValue("id", student.getId());return namedParameterJdbcTemplate.update(sql,sps);}@Overridepublic int deleteStudent(int id) {String sql="delete from t_student where id=:id";MapSqlParameterSource sps = new MapSqlParameterSource();sps.addValue("id", id);return namedParameterJdbcTemplate.update(sql,sps);}@Overridepublic List<Student> findStudents() {String sql="select * from t_student";final List<Student> studentList=new ArrayList<Student>();namedParameterJdbcTemplate.query(sql, new RowCallbackHandler(){@Overridepublic void processRow(ResultSet rs) throws SQLException {Student student=new Student();student.setId(rs.getInt("id"));student.setName(rs.getString("name"));student.setAge(rs.getInt("age"));studentList.add(student);}});return studentList;}}

這裡使用了NamedParameterJdbcTemplate這個類。在寫sql語句的時候需要傳入的參數使用“:參數名”表示，這樣的話增加了程式的可讀性。傳入的參數使用索引值對的方式儲存在MapSqlParameterSource類的對象中。

2.修改Spring設定檔為：

<?xml version="1.0" encoding="UTF-8"?><beans xmlns="http://www.springframework.org/schema/beans"    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"    xmlns:aop="http://www.springframework.org/schema/aop"    xmlns:context="http://www.springframework.org/schema/context"    xsi:schemaLocation="http://www.springframework.org/schema/beans        http://www.springframework.org/schema/beans/spring-beans.xsd        http://www.springframework.org/schema/aop        http://www.springframework.org/schema/aop/spring-aop.xsd        http://www.springframework.org/schema/context        http://www.springframework.org/schema/context/spring-context.xsd">        <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">        <property name="driverClassName" value="${jdbc.driverClassName}"/>        <property name="url" value="${jdbc.url}"/>        <property name="username" value="${jdbc.username}"/>        <property name="password" value="${jdbc.password}"/>    </bean>    <context:property-placeholder location="jdbc.properties"/>        <bean id="namedParameterJdbcTemplate" class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">    <constructor-arg ref="dataSource"></constructor-arg>    </bean><bean id="studentDao" class="com.test.dao.impl.StudentDaoImpl"><property name="namedParameterJdbcTemplate" ref="namedParameterJdbcTemplate"></property></bean> <bean id="studentService" class="com.test.service.impl.StudentServiceImpl"><property name="studentDao" ref="studentDao"></property></bean> </beans>

這裡定義了NamedParameterJdbcTemplate類的執行個體，使用構造方法注入DataSource類對象。把定義的NamedParameterJdbcTemplate對象執行個體注入到StudentDaoImpl執行個體中。

3.運行各個測試方法，效果與使用JdbcTemplate類相同。