J2EE系列之Spring4学习笔记（十）--Spring对JDBC的支持

最新推荐文章于 2025-06-22 20:54:01 发布

Java小飞侠

最新推荐文章于 2025-06-22 20:54:01 发布

阅读量1.1k

点赞数

CC 4.0 BY-SA版权

分类专栏： Java程序开发文章标签： Spring使用JdbcDaoSuppo Spring使用NamedParamet

本文链接：https://blog.youkuaiyun.com/feidie436/article/details/72863998

Java程序开发专栏收录该内容

83 篇文章

订阅专栏

本文介绍了Spring框架下两种常见的数据库操作方式：通过JdbcDaoSupport类简化JdbcTemplate的使用及利用NamedParameterJdbcTemplate提高SQL语句的可读性和灵活性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、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{


	@Override
	public 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);
		 
	}

	@Override
	public 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);
	}

	@Override
	public int deleteStudent(int id) {
		String sql = "delete from t_student where id=?";
		Object []params = new Object[]{id};
		return this.getJdbcTemplate().update(sql,params);
	}

	@Override
	public List<Student> findStudents() {
		String sql = "select * from t_student";
		final List<Student> studentList = new ArrayList<Student>();
		this.getJdbcTemplate().query(sql, new RowCallbackHandler(){

			@Override
			public 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) {
				@Override
				protected 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);
	}


	@Override
	public <T> T query(String sql, SqlParameterSource paramSource, ResultSetExtractor<T> rse)
			throws DataAccessException {

		return getJdbcOperations().query(getPreparedStatementCreator(sql, paramSource), rse);
	}

	@Override
	public <T> T query(String sql, Map<String, ?> paramMap, ResultSetExtractor<T> rse)
			throws DataAccessException {

		return query(sql, new MapSqlParameterSource(paramMap), rse);
	}

	@Override
	public <T> T query(String sql, ResultSetExtractor<T> rse) throws DataAccessException {
		return query(sql, EmptySqlParameterSource.INSTANCE, rse);
	}

	@Override
	public void query(String sql, SqlParameterSource paramSource, RowCallbackHandler rch)
			throws DataAccessException {

		getJdbcOperations().query(getPreparedStatementCreator(sql, paramSource), rch);
	}

	@Override
	public void query(String sql, Map<String, ?> paramMap, RowCallbackHandler rch)
			throws DataAccessException {

		query(sql, new MapSqlParameterSource(paramMap), rch);
	}

	@Override
	public void query(String sql, RowCallbackHandler rch) throws DataAccessException {
		query(sql, EmptySqlParameterSource.INSTANCE, rch);
	}


	@Override
	public int update(String sql, SqlParameterSource paramSource) throws DataAccessException {
		return getJdbcOperations().update(getPreparedStatementCreator(sql, paramSource));
	}

	@Override
	public int update(String sql, Map<String, ?> paramMap) throws DataAccessException {
		return update(sql, new MapSqlParameterSource(paramMap));
	}

	@Override
	public 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;
	}

	@Override
	public 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);
	}

	@Override
	public 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);
	}

	@Override
	public 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);
	}

	@Override
	public List<Student> findStudents() {
		String sql="select * from t_student";
		final List<Student> studentList=new ArrayList<Student>();
		namedParameterJdbcTemplate.query(sql, new RowCallbackHandler(){

			@Override
			public 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类相同。