spring整合JDBC的详细内容需要参阅github上的spring相关文档，传送门：
http://docs.spring.io/spring-framework/docs/，这里记录一个spring和JDBC整合的常用案例
搭建测试环境前，首先添加相关的maven依赖
1 <dependency>
2 <groupId>org.springframework</groupId>
3 <artifactId>spring-core</artifactId>
4 <version>3.0.5.RELEASE</version>
5 </dependency>
6
7 <dependency>
8 <groupId>org.springframework</groupId>
9 <artifactId>spring-context</artifactId>
10 <version>3.0.5.RELEASE</version>
11 </dependency>
12
13 <dependency>
14 <groupId>javax.annotation</groupId>
15 <artifactId>javax.annotation-api</artifactId>
16 <version>1.2</version>
17 </dependency>
18
19 <dependency>
20 <groupId>org.springframework</groupId>
21 <artifactId>spring-aop</artifactId>
22 <version>3.0.5.RELEASE</version>
23 </dependency>
24
25 <!-- spring AOP -->
26 <dependency>
27 <groupId>org.aspectj</groupId>
28 <artifactId>aspectjweaver</artifactId>
29 <version>1.5.4</version>
30 </dependency>
31
32 <!-- Mysql驱动依赖 -->
33 <dependency>
34 <groupId>mysql</groupId>
35 <artifactId>mysql-connector-java</artifactId>
36 <version>5.1.6</version>
37 </dependency>
38
39 <!-- 数据库连接池 -->
40 <dependency>
41 <groupId>commons-dbcp</groupId>
42 <artifactId>commons-dbcp</artifactId>
43 <version>1.3</version>
44 </dependency>
45
46 <!-- Spring JDBC -->
47 <dependency>
48 <groupId>org.springframework</groupId>
49 <artifactId>spring-jdbc</artifactId>
50 <version>3.2.3.RELEASE</version>
51 </dependency>
52
53 <!-- spring测试相关包 -->
54 <dependency>
55 <groupId>org.springframework</groupId>
56 <artifactId>spring-test</artifactId>
57 <version>3.0.5.RELEASE</version>
58 </dependency>
添加之后，顺着使用JDBC的思路，创建数据源，获取连接，创建preparedstatement，resultset等，然后依次关闭。如今，这些通用的操作全都交由spring去做。在beans.xml中创建datasource
1 <?xml version="1.0" encoding="UTF-8"?>
2 <beans xmlns="http://www.springframework.org/schema/beans"
3 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context"
4 xmlns:aop="http://www.springframework.org/schema/aop"
5 xsi:schemaLocation="http://www.springframework.org/schema/beans
6 http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
7 http://www.springframework.org/schema/context
8 http://www.springframework.org/schema/context/spring-context-3.0.xsd
9 http://www.springframework.org/schema/aop
10 http://www.springframework.org/schema/aop/spring-aop-3.0.xsd">
11
12 <!-- 开启注解 -->
13 <context:annotation-config/>
14 <!-- 指定需要扫描的包路径 -->
15 <context:component-scan base-package="org.duyt"/>
16
17 <!-- 数据源配置 -->
18 <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
19 <property name="driverClassName" value="${jdbc.driverClassName}"/>
20 <property name="url" value="${jdbc.url}"/>
21 <property name="username" value="${jdbc.username}"/>
22 <property name="password" value="${jdbc.password}"/>
23 </bean>
24 <!-- 数据源配置文件 -->
25 <context:property-placeholder location="jdbc.properties"/>
26
27 </beans>
jdbc.properties内容
1 jdbc.driverClassName=com.mysql.jdbc.Driver
2 jdbc.url=jdbc:mysql://localhost:3306/shop
3 jdbc.username=root
4 jdbc.password=root
数据源至此就配置完毕了，spring对JDBC进行了简易的封装，通过使用Spring提供的JdbcTemplate来对数据库进行操作，创建DAO的接口和实现类，在DAO的接口中，通过注入datasource来获取JdbcTemplate的实例，通过JdbcTemplate实例实现CURD操作。
实体类
1 package org.duyt.domain;
2
3 public class User {
4 private int id;
5 private String name;
6 public User() {
7 super();
8 }
9 public User(int id, String name) {
10 super();
11 this.id = id;
12 this.name = name;
13 }
14 //get/set略
15 }
16
IUserDao
1 package org.duyt.dao;
2
3 import java.util.List;
4
5 import org.duyt.domain.User;
6
7 public interface IUserDao {
8
9 public int add(User user);
10 public void delete(int id);
11 public void update(User user);
12 public User load(int id);
13 public int count();
14 public List<User> list();
15 public List<String> listAllUserName();
16
17 }
18
UserDao
1 package org.duyt.dao.impl;
2
3 import java.sql.Connection;
4 import java.sql.PreparedStatement;
5 import java.sql.ResultSet;
6 import java.sql.SQLException;
7 import java.util.List;
8
9 import javax.annotation.Resource;
10 import javax.sql.DataSource;
11
12 import org.duyt.dao.IUserDao;
13 import org.duyt.domain.User;
14 import org.springframework.jdbc.core.JdbcTemplate;
15 import org.springframework.jdbc.core.PreparedStatementCreator;
16 import org.springframework.jdbc.core.RowMapper;
17 import org.springframework.jdbc.support.GeneratedKeyHolder;
18 import org.springframework.stereotype.Repository;
19
20 @Repository("userJdbcDao")
21 public class UserDao implements IUserDao {
22
23 private JdbcTemplate jdbcTemplate;
24
25 //在此注入datasource创建jdbcTemplate
26 @Resource
27 public void setDataSource(DataSource dataSource) {
28 this.jdbcTemplate = new JdbcTemplate(dataSource);
29 }
30
31 /**
32 * 添加方法一般需要返回新增的对象，或者ID，在一个事务中经常会有一次性添加多个有关联的对象 的情况，这里写出一个新增对象之后同时返回生成的ID的方法
33 */
34 public int add(final User user) {
35 GeneratedKeyHolder generatedKeyHolder = new GeneratedKeyHolder();
36 // 返回自动生成的id
37 jdbcTemplate.update(new PreparedStatementCreator() {
38 public PreparedStatement createPreparedStatement(Connection con)
39 throws SQLException {
40 // 可以指定返回哪些想返回的字段值
41 PreparedStatement ps = con.prepareStatement("insert into"
42 + " t_user(username) values(?)", new String[] { "id" });
43 ps.setString(1, user.getName());
44 return ps;
45 }
46 }, generatedKeyHolder);
47 return generatedKeyHolder.getKey().intValue();
48 }
49
50 /**
51 * 删除
52 */
53 public void delete(int id) {
54 jdbcTemplate.update("delete from t_user where id = ?", id);
55 }
56
57 /**
58 * 更新
59 */
60 public void update(User user) {
61 jdbcTemplate.update("update t_user set name = ? where id = ?",
62 user.getName(), user.getId());
63 }
64
65 /**
66 * 查询并返回一条记录，返回一个对象
67 */
68 public User load(int id) {
69 String selAllSql = "select * from t_user where id = ?";
70 return jdbcTemplate.queryForObject(selAllSql, new Object[] { id },
71 new UserMapper());
72 }
73
74 /**
75 * 查询并返回一条记录，返回一个数值
76 */
77 public int count() {
78 String selCoutSql = "select count(*) from t_user";
79 return jdbcTemplate.queryForObject(selCoutSql, Integer.class);
80 }
81
82 /**
83 * 查询并返回一组记录
84 * *倘若selAllSql = "select id,username from t_user";
85 * 选取一个以上的字段返回的时候，不会返回List<Object[]>，仍需要自己定义RowMapper去完成映射
86 *
87 * @return
88 */
89 public List<String> listAllUserName(){
90 String selAllSql = "select username from t_user";
91 return jdbcTemplate.queryForList(selAllSql, String.class);
92 }
93
94 /**
95 * 查询用户列表
96 * @return
97 */
98 public List<User> list() {
99 String selAllSql = "select * from t_user";
100 return jdbcTemplate.query(selAllSql, new UserMapper());
101 }
102
103 /**
104 * UserMapper复用内部类
105 * @author Administrator
106 *
107 */
108 private class UserMapper implements RowMapper<User> {
109 public User mapRow(ResultSet rs, int rowNum) throws SQLException {
110 User user = new User();
111 user.setName(rs.getString("username"));
112 return user;
113 }
114 }
115 }
116
测试类
1 package org.duyt.test;
2
3
4 import java.util.List;
5
6 import org.duyt.dao.IUserDao;
7 import org.duyt.domain.User;
8 import org.junit.Test;
9 import org.springframework.beans.factory.BeanFactory;
10 import org.springframework.context.support.ClassPathXmlApplicationContext;
11
12 public class TestDemo {
13
14 private BeanFactory factory = new ClassPathXmlApplicationContext("beans.xml");
15
16 @Test
17 public void testSimpleJDBC(){
18 //新增一个用户
19 IUserDao dao = (IUserDao) factory.getBean("userJdbcDao");
20 User u = new User();
21 u.setName("duyt");
22 dao.add(u);
23
24 //指定对象查询，但会单个对象
25 u = dao.load(23);
26 System.out.println(u.getName());
27
28 //查询记录数，返回一个数值
29 System.out.println(dao.count());
30
31 //返回一个对象集合
32 List<User> users = dao.list();
33 for (User user : users) {
34 System.out.println("username：" + user.getName());
35 }
36
37 //返回用户名列表
38 List<String> names = dao.listAllUserName();
39 for (String str: names) {
40 System.out.println("username：" + str);
41 }
42 }
43
44 }
45
可以看出JdbcTemplate在传递参数的时候是顺序设置的，spring还提供了一个基于参数名的传参方式NamedParameterJdbcTemplate，和JdbcTemplate的使用方式基本一致
在DAO实现类中同样注入datasource，创建NamedParameterJdbcTemplate，其他操作大致相同，不再赘述
1 ......
2 private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
3 @Resource
4 public void setDataSource(DataSource dataSource) {
5 this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(
6 dataSource);
7 }
8
9 public int add(User user) {
10 String insertSql = "insert into t_user(username) value(:username)";
11 Map<String, Object> params = new HashMap<String, Object>();
12 params.put("username", user.getName());
13 return namedParameterJdbcTemplate.update(insertSql,params);
14 }
15 ......