spring整合JDBC的詳細內(nèi)容需要參閱github上的spring相關(guān)文檔，傳送門：
http://docs.spring.io/spring-framework/docs/，這里記錄一個spring和JDBC整合的常用案例
搭建測試環(huán)境前，首先添加相關(guān)的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驅(qū)動依賴 -->
33 <dependency>
34 <groupId>mysql</groupId>
35 <artifactId>mysql-connector-java</artifactId>
36 <version>5.1.6</version>
37 </dependency>
38
39 <!-- 數(shù)據(jù)庫連接池 -->
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測試相關(guān)包 -->
54 <dependency>
55 <groupId>org.springframework</groupId>
56 <artifactId>spring-test</artifactId>
57 <version>3.0.5.RELEASE</version>
58 </dependency>
添加之后，順著使用JDBC的思路，創(chuàng)建數(shù)據(jù)源，獲取連接，創(chuàng)建preparedstatement，resultset等，然后依次關(guān)閉。如今，這些通用的操作全都交由spring去做。在beans.xml中創(chuàng)建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 <!-- 數(shù)據(jù)源配置 -->
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 <!-- 數(shù)據(jù)源配置文件 -->
25 <context:property-placeholder location="jdbc.properties"/>
26
27 </beans>
jdbc.properties內(nèi)容
1 jdbc.driverClassName=com.mysql.jdbc.Driver
2 jdbc.url=jdbc:mysql://localhost:3306/shop
3 jdbc.username=root
4 jdbc.password=root
數(shù)據(jù)源至此就配置完畢了，spring對JDBC進行了簡易的封裝，通過使用Spring提供的JdbcTemplate來對數(shù)據(jù)庫進行操作，創(chuàng)建DAO的接口和實現(xiàn)類，在DAO的接口中，通過注入datasource來獲取JdbcTemplate的實例，通過JdbcTemplate實例實現(xiàn)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創(chuàng)建jdbcTemplate
26 @Resource
27 public void setDataSource(DataSource dataSource) {
28 this.jdbcTemplate = new JdbcTemplate(dataSource);
29 }
30
31 /**
32 * 添加方法一般需要返回新增的對象，或者ID，在一個事務(wù)中經(jīng)常會有一次性添加多個有關(guān)聯(lián)的對象 的情況，這里寫出一個新增對象之后同時返回生成的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 * 查詢并返回一條記錄，返回一個數(shù)值
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復(fù)用內(nèi)部類
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 //查詢記錄數(shù)，返回一個數(shù)值
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在傳遞參數(shù)的時候是順序設(shè)置的，spring還提供了一個基于參數(shù)名的傳參方式NamedParameterJdbcTemplate，和JdbcTemplate的使用方式基本一致
在DAO實現(xiàn)類中同樣注入datasource，創(chuàng)建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 ......