1 什么是springJDBC
spring通过抽象JDBC访问并一致的API来简化JDBC编程的工作量。我们只需要声明SQL、调用合适的SpringJDBC框架API、处理结果集即可。事务由Spring管理，并将JDBC受查异常转换为Spring一致的非受查异常，从而简化开发。
利用传统的jdbc操作数据库的步骤：获取连接→创建Statement→执行数据操作→获取结果→关闭Statement→关闭结果集→关闭连接；而Spring JDBC通过一个模板类org.springframework. jdbc.core.JdbcTemplate封装了样板式的代码，用户通过模板类就可以轻松地完成大部分数据访问的操作。
2 前期准备
2.1 jar包
spring-jdbc : springjdbc的包
mysql ： MySQL的驱动包
dbcp ：数据库连接池
spring-webmvc : springmvc框架包
annotation ：@resource需要用到的包，该包在Tomcat中有，如果是web项目而且运行环境是Tomcat的话就不需要导入这个包了
junit ： 单元测试包
2.2 数据库（使用mysql数据库5.4）
1 # 创建用户表相关表
2 CREATE TABLE t_user (
3 user_id INT AUTO_INCREMENT PRIMARY KEY,
4 user_name VARCHAR (30),
5 credits INT,
6 password VARCHAR (32),
7 last_visit DATETIME,
8 last_ip VARCHAR(23)
9 ) ENGINE = InnoDB;
10
11 # 查询t_user表的结构
12 DESC t_user;
13
14 # 创建用户登录日志表
15 CREATE TABLE t_login_log (
16 login_log_id INT AUTO_INCREMENT PRIMARY KEY,
17 user_id INT,
18 ip VARCHAR (23),
19 login_datetime DATETIME
20 ) ENGINE = InnoDB;
21
22 #查询 t_login_log 表的结构
23 DESC t_login_log;
24
25 INSERT INTO t_user
26 (user_name, password)
27 VALUES
28 ("wys", "182838" );
29
30 SELECT * FROM t_user;
3 环境搭建（使用的是eclipse）
3.1 利用maven导入相关jar包
1 <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">pom.xml
2 <modelVersion>4.0.0</modelVersion>
3 <groupId>cn.xiangxu</groupId>
4 <artifactId>baobaotao</artifactId>
5 <version>0.0.1-SNAPSHOT</version>
6 <packaging>war</packaging>
7 <dependencies>
8 <dependency>
9 <groupId>org.springframework</groupId>
10 <artifactId>spring-webmvc</artifactId>
11 <version>3.2.8.RELEASE</version>
12 </dependency>
13 <dependency>
14 <groupId>mysql</groupId>
15 <artifactId>mysql-connector-java</artifactId>
16 <version>5.1.37</version>
17 </dependency>
18 <dependency>
19 <groupId>org.springframework</groupId>
20 <artifactId>spring-jdbc</artifactId>
21 <version>3.2.8.RELEASE</version>
22 </dependency>
23 <dependency>
24 <groupId>commons-dbcp</groupId>
25 <artifactId>commons-dbcp</artifactId>
26 <version>1.4</version>
27 </dependency>
28 <dependency>
29 <groupId>javax.annotation</groupId>
30 <artifactId>javax.annotation-api</artifactId>
31 <version>1.2</version>
32 </dependency>
33 <dependency>
34 <groupId>junit</groupId>
35 <artifactId>junit</artifactId>
36 <version>4.12</version>
37 </dependency>
38 </dependencies>
39 </project>
3.2 创建properties文件，用于存放数据库相关信息
1 driverClassName=com.mysql.jdbc.Drivermysql.properties
2 url=jdbc:mysql://127.0.0.1:3306/sampledb
3 username=root
4 password=182838
5 maxActive=10
6 maxWait=3000
3.3 创建spring配置文件
3.3.1 配置properties文件的bean
3.3.2 配置数据库连接池
3.3.3 配置jdbcTemplate
3.3.4 配置组件扫描
1 <?xml version="1.0" encoding="UTF-8"?>spring_mysql.xml
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:jdbc="http://www.springframework.org/schema/jdbc" xmlns:jee="http://www.springframework.org/schema/jee"
5 xmlns:tx="http://www.springframework.org/schema/tx" xmlns:aop="http://www.springframework.org/schema/aop"
6 xmlns:mvc="http://www.springframework.org/schema/mvc" xmlns:util="http://www.springframework.org/schema/util"
7 xmlns:jpa="http://www.springframework.org/schema/data/jpa"
8 xsi:schemaLocation="
9 http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.2.xsd
10 http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.2.xsd
11 http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.2.xsd
12 http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee-3.2.xsd
13 http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.2.xsd
14 http://www.springframework.org/schema/data/jpa http://www.springframework.org/schema/data/jpa/spring-jpa-1.3.xsd
15 http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.2.xsd
16 http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-3.2.xsd
17 http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-3.2.xsd">
18
19 <!-- 读取mysql.properties文件 -->
20 <util:properties id="mysql" location="classpath:config/mysql.properties"/>
21
22 <!-- 配置连接池 -->
23 <bean id="ds" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
24 <property name="driverClassName" value="#{mysql.driverClassName}" />
25 <property name="url" value="#{mysql.url}" />
26 <property name="username" value="#{mysql.username}" />
27 <property name="password" value="#{mysql.password}" />
28 </bean>
29
30 <!-- 配置jdbcTemplate -->
31 <bean id="jt" class="org.springframework.jdbc.core.JdbcTemplate">
32 <property name="dataSource" ref="ds"></property>
33 </bean>
34
35 <!-- 组件扫描 -->
36 <context:component-scan base-package="com.baobaotao"></context:component-scan>
37
38 </beans>
注意：我们不用配置spirng的主控制器，因为我们只是操作持久层；虽然我们用到了spring容器，但是我们可以通过编写代码来启动容器
3.4 项目结构图
4 代码实现数据库操作
4.1 编写实体类
1 package com.baobaotao.entity;User.java
2
3 import java.io.Serializable;
4 import java.util.Date;
5
6 public class User implements Serializable {
7
8 private static final long serialVersionUID = -3573627859368072117L;
9
10 private Integer userId;
11 private String userName;
12 private Integer credits;
13 private String password;
14 private Date lastVisit;
15 private String lastIp;
16
17 public User() {
18 super();
19 // TODO Auto-generated constructor stub
20 }
21
22 public User(Integer userId, String userName, Integer credits, String password, Date lastVisit, String lastIp) {
23 super();
24 this.userId = userId;
25 this.userName = userName;
26 this.credits = credits;
27 this.password = password;
28 this.lastVisit = lastVisit;
29 this.lastIp = lastIp;
30 }
31
32 @Override
33 public int hashCode() {
34 final int prime = 31;
35 int result = 1;
36 result = prime * result + ((lastIp == null) ? 0 : lastIp.hashCode());
37 return result;
38 }
39 @Override
40 public boolean equals(Object obj) {
41 if (this == obj)
42 return true;
43 if (obj == null)
44 return false;
45 if (getClass() != obj.getClass())
46 return false;
47 User other = (User) obj;
48 if (lastIp == null) {
49 if (other.lastIp != null)
50 return false;
51 } else if (!lastIp.equals(other.lastIp))
52 return false;
53 return true;
54 }
55 public Integer getUserId() {
56 return userId;
57 }
58 public void setUserId(Integer userId) {
59 this.userId = userId;
60 }
61 public String getUserName() {
62 return userName;
63 }
64 public void setUserName(String userName) {
65 this.userName = userName;
66 }
67 public Integer getCredits() {
68 return credits;
69 }
70 public void setCredits(Integer credits) {
71 this.credits = credits;
72 }
73 public String getPassword() {
74 return password;
75 }
76 public void setPassword(String password) {
77 this.password = password;
78 }
79 public Date getLastVisit() {
80 return lastVisit;
81 }
82 public void setLastVisit(Date lastVisit) {
83 this.lastVisit = lastVisit;
84 }
85 public String getLastIp() {
86 return lastIp;
87 }
88 public void setLastIp(String lastIp) {
89 this.lastIp = lastIp;
90 }
91
92 @Override
93 public String toString() {
94 return "User [userId=" + userId + ", userName=" + userName + ", credits=" + credits + ", password=" + password
95 + ", lastVisit=" + lastVisit + ", lastIp=" + lastIp + "]";
96 }
97
98 }
1 package com.baobaotao.entity;LoginLog.java
2
3 import java.io.Serializable;
4 import java.util.Date;
5
6 public class LoginLog implements Serializable {
7
8 private static final long serialVersionUID = 5176708814959439551L;
9
10 private Integer loginLogId;
11 private String userId;
12 private String ip;
13 private Date loginDatetime;
14 @Override
15 public int hashCode() {
16 final int prime = 31;
17 int result = 1;
18 result = prime * result + ((ip == null) ? 0 : ip.hashCode());
19 result = prime * result + ((loginDatetime == null) ? 0 : loginDatetime.hashCode());
20 result = prime * result + ((loginLogId == null) ? 0 : loginLogId.hashCode());
21 result = prime * result + ((userId == null) ? 0 : userId.hashCode());
22 return result;
23 }
24 @Override
25 public boolean equals(Object obj) {
26 if (this == obj)
27 return true;
28 if (obj == null)
29 return false;
30 if (getClass() != obj.getClass())
31 return false;
32 LoginLog other = (LoginLog) obj;
33 if (ip == null) {
34 if (other.ip != null)
35 return false;
36 } else if (!ip.equals(other.ip))
37 return false;
38 if (loginDatetime == null) {
39 if (other.loginDatetime != null)
40 return false;
41 } else if (!loginDatetime.equals(other.loginDatetime))
42 return false;
43 if (loginLogId == null) {
44 if (other.loginLogId != null)
45 return false;
46 } else if (!loginLogId.equals(other.loginLogId))
47 return false;
48 if (userId == null) {
49 if (other.userId != null)
50 return false;
51 } else if (!userId.equals(other.userId))
52 return false;
53 return true;
54 }
55 public Integer getLoginLogId() {
56 return loginLogId;
57 }
58 public void setLoginLogId(Integer loginLogId) {
59 this.loginLogId = loginLogId;
60 }
61 public String getUserId() {
62 return userId;
63 }
64 public void setUserId(String userId) {
65 this.userId = userId;
66 }
67 public String getIp() {
68 return ip;
69 }
70 public void setIp(String ip) {
71 this.ip = ip;
72 }
73 public Date getLoginDatetime() {
74 return loginDatetime;
75 }
76 public void setLoginDatetime(Date loginDatetime) {
77 this.loginDatetime = loginDatetime;
78 }
79 public LoginLog() {
80 super();
81 // TODO Auto-generated constructor stub
82 }
83 public LoginLog(Integer loginLogId, String userId, String ip, Date loginDatetime) {
84 super();
85 this.loginLogId = loginLogId;
86 this.userId = userId;
87 this.ip = ip;
88 this.loginDatetime = loginDatetime;
89 }
90 @Override
91 public String toString() {
92 return "LoginLog [loginLogId=" + loginLogId + ", userId=" + userId + ", ip=" + ip + ", loginDatetime="
93 + loginDatetime + "]";
94 }
95
96 }
4.2 编写UserDao接口
1 package com.baobaotao.dao;UserDao.java
2
3 import java.util.List;
4
5 import com.baobaotao.entity.User;
6
7 public interface UserDao {
8 /**
9 * 向用户表中添加记录
10 * @param user 用户表实体对象
11 */
12 public void insert(User user);
13
14 /**
15 * 查询所有用户数据
16 * @return 由查询到记录组成的集合
17 */
18 public List<User> findAll();
19
20 }
4.3 编写UserDao接口的实现类UserDaoImpl
1 package com.baobaotao.dao;UserDaoImpl
2
3 import java.sql.ResultSet;
4 import java.sql.SQLException;
5 import java.util.List;
6
7 import javax.annotation.Resource;
8
9 import org.springframework.jdbc.core.JdbcTemplate;
10 import org.springframework.jdbc.core.RowMapper;
11 import org.springframework.stereotype.Repository;
12
13 import com.baobaotao.entity.User;
14
15 @Repository("userDao")
16 public class UserDaoImpl implements UserDao {
17
18 @Resource(name="jt")
19 private JdbcTemplate jt;
20
21 public void insert(User user) {
22
23 String sql = "INSERT INTO t_user " +
24 "(user_name, password) " +
25 "VALUES " +
26 "(?, ?) ";
27 Object [] args = {user.getUserName(), user.getPassword()};
28 Integer num = jt.update(sql, args);
29 if(num > 0) {
30 System.out.println("插入数据成功");
31 } else {
32 System.out.println("插入数据失败");
33 }
34
35 }
36
37 public List<User> findAll() {
38 String sql = "SELECT * FROM t_user ";
39 List<User> users = jt.query(sql, new UserRowMapper());
40 return users;
41 }
42
43 /**
44 * 结果集处理：经每一条查询记录转变成一个实体对象
45 * @author 三少
46 *
47 */
48 class UserRowMapper implements RowMapper<User> {
49
50 public User mapRow(ResultSet rs, int rowNum) throws SQLException {
51 User user = new User();
52 user.setUserId(rs.getInt("user_id"));
53 user.setUserName(rs.getString("user_name"));
54 user.setCredits(rs.getInt("credits"));
55 user.setPassword(rs.getString("password"));
56 user.setLastVisit(rs.getDate("last_visit"));
57 user.setLastIp(rs.getString("last_ip"));
58 return user;
59 }
60 }
61
62 }
使用springJDBC操作数据库时，程序员只需要编写相关的sql语句，待定参数可以用？代替，然后调用JdbcTemplate类的相关方法来执行sql语句就行啦。
JdbcTemplate类中的主要方法
execute方法：可以用于执行任何SQL语句，一般用于执行DDL语句；
update方法及batchUpdate方法：update方法用于执行新增、修改、删除等语句;
batchUpdate方法用于执行批处理相关语句；
query方法及queryForXXX方法：用于执行查询相关语句
call方法：用于执行存储过程、函数相关语句。
注意：
使用query方法时有三个参数
参数1 sql语句字符串
参数2 未知参数组
参数3 查询结果处理（就是讲每一条查询记录变成一个实体对象，三少的一般做法是编写一个实现了RowMapper接口的内部类，然后创建一个该内部类对象来作为参数3）
4.4 项目结构图
5 测试类
5.1 测试前需要启动spring容器，因为我们的代码中使用到了spring容器的功能
5.2 编写测试方法
1 package testDao;测试类
2
3 import java.util.List;
4
5 import org.junit.Before;
6 import org.junit.Test;
7 import org.springframework.context.ApplicationContext;
8 import org.springframework.context.support.ClassPathXmlApplicationContext;
9
10 import com.baobaotao.dao.UserDao;
11 import com.baobaotao.entity.User;
12
13 public class TestDao {
14
15 private ApplicationContext ac;
16 private UserDao userDao;
17
18 @Before
19 public void init() {
20 ac = new ClassPathXmlApplicationContext("config/spring_mysql.xml"); // 启动容器
21 System.out.println(ac);
22
23 userDao = ac.getBean("userDao", UserDao.class); // 利用容器创建对象
24 System.out.println(userDao);
25 }
26
27 /**
28 * 测试插入数据
29 */
30 @Test
31 public void test01() {
32 User user = new User();
33 user.setUserName("wym");
34 user.setPassword("111");
35
36 userDao.insert(user);
37 }
38
39 /**
40 * 测试查询所有数据
41 */
42 @Test
43 public void test02() {
44 List<User> users = userDao.findAll();
45 for(User user : users) {
46 System.out.println(user);
47 }
48 }
49
50
51 }