首先要获得DataSource连接池:
要对数据库执行任何的JDBC操作，需要有一个Connection.在Spring中，Connection对象是通过DataSource获得的。
有几种方法可以得到DataSource, 其中一种方法是使用Spring提供的轻量级org.springframework.jdbc.datasource.DriverManagerDataSource，第二种方法是使用org.apache.commons.dbcp.BasicDataSource类。
一：使用DriverMangerDataSource，这种方法是轻量级的，方便测试
1 public class DataSoureProvider {
2 public static DriverManagerDataSource dataSource = new DriverManagerDataSource();
3
4 public static DriverManagerDataSource getInstance() {
5 dataSource.setDriverClassName("com.mysql.jdbc.Driver");
6 dataSource.setUrl("jdbc:mysql://localhost:3306/book");
7 dataSource.setUsername("y****");
8 dataSource.setPassword("h*******");
9 return dataSource;
10 }
11
12 @Test
13 public void test() {
14 DataSoureProvider.getInstance();
15 try {
16 dataSource.getConnection();
17 }
18 catch (SQLException e) {
19 e.printStackTrace();
20 }
21 }
22 }
第5~8行是配置连接数据库所需的信息。
二：使用BasicDataSouce创建一个连接池。应为BasicDataSource所有属性都是通过setter方法暴露在外面的，我们可以像配置其他Srping Bean那样配置它我将数据库连接信息配置在properties文件中，利用spring的org.springframeword.beans.factory.config.PropertyPlaceholderConfigurer类进行读取装载，可以查看spring_装配Bean一文。
书写配置文件applicationContext.xml:
1 <?xml version="1.0" encoding="UTF-8"?>
2 <beans
3 xmlns="http://www.springframework.org/schema/beans"
4 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
5 xmlns:p="http://www.springframework.org/schema/p"
6 xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd">
7
8 <bean id="dbproperty" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
9 <property name="location">
10 <value>connect.properties</value>
11 </property>
12 </bean>
13
14 <bean id="myDataSource" class="org.apache.commons.dbcp.BasicDataSource">
15 <property name="driverClassName">
16 <value>${db.driver}</value>
17 </property>
18 <property name="url">
19 <value>${db.url}</value>
20 </property>
21 <property name="username">
22 <value>${db.username}</value>
23 </property>
24 <property name="password">
25 <value>${db.password}</value>
26 </property>
27 </bean>
28 </beans>
第14~27行配置BasicDataSource参数，其中<value>中的参数是在connect.propertices配置文件中拿到的。
进行测试：
1 public class DataSourceProvider2 {
2 @Test
3 public void connectTest() {
4 ApplicationContext context = new ClassPathXmlApplicationContext(
5 "applicationContext.xml");
6 BasicDataSource dataSource = (BasicDataSource) context
7 .getBean("myDataSource");
8 try {
9 dataSource.getConnection();
10 System.out.println("connect successful");
11 }
12 catch (SQLException e) {
13 e.printStackTrace();
14 }
15 }
16
17 }
使用org.apache.commons.dbcp.BasicDataSource需要引入额外的jar包，分别是commons-collections-2.1.1.jar,commons-dbcp-1.4.jar,commons-pool-1.2.jar.为了方便大家，这里有这三个jar包的下载地址：http://pan.baidu.com/share/link?shareid=68214&uk=2198762756#dir/path=%2Flib%2Fdatasource
Spring把JDBC中重复的操作建立成了一个模板类org.springframework.jdbc.core.JdbcTemplate。
使用JdbcTemplate:
要使用JdbcTemplate,需要为每一个DAO配置一个JdbcTemplate实例
1 public class StudentDaoImp implements StudentDao {
2 private JdbcTemplate jdbcTemplate;
3
4 @Override
5 public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
6 this.jdbcTemplate = jdbcTemplate;
7 }
8 }
如上，StudentDaoImp内配置了一个JdbcTemplate对象和它对应的setter方法。这样就可以在Spring配置文件中对其进行赋值。
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:p="http://www.springframework.org/schema/p"
4 xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd">
5
6 <bean id="dbproperty"
7 class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
8 <property name="location">
9 <value>connect.properties</value>
10 </property>
11 </bean>
12
13 <bean id="myDataSource" class="org.apache.commons.dbcp.BasicDataSource">
14 <property name="driverClassName">
15 <value>${db.driver}</value>
16 </property>
17 <property name="url">
18 <value>${db.url}</value>
19 </property>
20 <property name="username">
21 <value>${db.username}</value>
22 </property>
23 <property name="password">
24 <value>${db.password}</value>
25 </property>
26 </bean>
27
28 <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
29 <property name="dataSource">
30 <ref bean="myDataSource"/>
31 </property>
32 </bean>
33
34 <bean id="studentDao" class="com.sunflower.dao.StudentDaoImp">
35 <property name="jdbcTemplate">
36 <ref bean="jdbcTemplate"/>
37 </property>
38 </bean>
39 </beans>
第28~32行是装配JdbcTemplate这个Bean,其中需要为其设置dataSource这个参数，就是我们上面的到的DataSource.
使用JdbcTemplate插入数据:
1 public class StudentDaoImp implements StudentDao {
2 private JdbcTemplate jdbcTemplate;
3
4 @Override
5 public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
6 this.jdbcTemplate = jdbcTemplate;
7 }
8
9 public void insert(Student student)
10 {
11 String sql = "insert into student (cno,name,score) values(?,?,?)";
12 //设置传递给通配符的参数
13 Object[] params = new Object[]{student.getCno(), student.getName(), student.getScore()};
14 jdbcTemplate.update(sql, params);
15 }
16 }
第9~15行为插入一条学生记录的方法，第14行中，JdbcTemplate为我们提供了update(String sql,Object... args)方法，方便我们进行数据的插入.
进行测试：
1 public class InsertTest {
2 @Test
3 public void insertTest() {
4 Student student = new Student();
5 student.setCno(1);
6 student.setName("张飞");
7 student.setScore(50);
8
9 ApplicationContext context = new ClassPathXmlApplicationContext(
10 "applicationContext.xml");
11 StudentDaoImp studentDao = (StudentDaoImp) context.getBean("studentDao");
12 studentDao.insert(student);
13 }
14 }
数据库中多了一条记录：
mysql> select * from student;
+-----+-----+------+-------+
| sno | cno | name | score |
+-----+-----+------+-------+
| 1 | 1 | 地心 | 50 |
| 2 | 2 | 华雄 | 88 |
| 3 | 1 | 孝慈 | 90 |
| 4 | 3 | 必须 | 42 |
| 5 | 1 | 华雄 | 74 |
| 6 | 2 | 地心 | 75 |
| 7 | 2 | 横切 | 85 |
| 11 | 2 | 横切 | 85 |
| 12 | 2 | 横切 | 85 |
| 14 | 1 | 张飞 | 50 |
+-----+-----+------+-------+
10 rows in set
批量插入数据：
批量插入数据需要用到org.springframework.jdbc.core.BatchPreparedStatementSetter接口。
修改StudentDaoImp:
1 public class StudentDaoImp implements StudentDao {
2 private JdbcTemplate jdbcTemplate;
3
4 @Override
5 public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
6 this.jdbcTemplate = jdbcTemplate;
7 }
8
9 public int insert(Student student)
10 {
11 String sql = "insert into student (cno,name,score) values(?,?,?)";
12 //设置传递给通配符的参数
13 Object[] params = new Object[]{student.getCno(), student.getName(), student.getScore()};
14 return jdbcTemplate.update(sql, params);
15 }
16
17 public int[] batchInsert(final List<Student> list)
18 {
19 String sql = "insert into student (cno,name,score) values(?,?,?)";
20
21 BatchPreparedStatementSetter setter = new BatchPreparedStatementSetter() {
22
23 @Override
24 public void setValues(PreparedStatement ps, int index) throws SQLException {
25 Student student = (Student) list.get(index);
26 ps.setInt(1, student.getCno());
27 ps.setString(2, student.getName());
28 ps.setDouble(3, student.getScore());
29 }
30
31 //有多少条记录要处理
32 @Override
33 public int getBatchSize() {
34 return list.size();
35 }
36 };
37
38 return jdbcTemplate.batchUpdate(sql, setter);
39 }
40 }
第17~39行为批量插入的方法，BatchPreparedStatementSetter接口的两个方法，其中getBatchSize()方法是得到需要插入的记录的个数，setValues(PreparedStatement ps, int index)方法是实际进行插入的方法。
进行测试：
1 @Test
2 public void batchInsert() {
3 Student student1 = null, student2 = null, student3 = null;
4 student1 = new Student();
5 student2 = new Student();
6 student3 = new Student();
7
8 student1.setCno(1);
9 student1.setName("刘备");
10 student1.setScore(70);
11
12 student2.setCno(2);
13 student2.setName("关羽");
14 student2.setScore(90);
15
16 student3.setCno(2);
17 student3.setName("张飞");
18 student3.setScore(40);
19
20 List<Student> list = new ArrayList<Student>();
21 list.add(student1);
22 list.add(student2);
23 list.add(student3);
24
25 ApplicationContext context = new ClassPathXmlApplicationContext(
26 "applicationContext.xml");
27 StudentDaoImp studentDao = (StudentDaoImp) context
28 .getBean("studentDao");
29 studentDao.batchInsert(list);
30 }
插入结果：
mysql> select * from student;
+-----+-----+------+-------+
| sno | cno | name | score |
+-----+-----+------+-------+
| 15 | 1 | 刘备 | 70 |
| 16 | 2 | 关羽 | 90 |
| 17 | 2 | 张飞 | 40 |
+-----+-----+------+-------+
3 rows in set
查询一条记录：
执行一条数据的查询，需要使用org.springframework.jdbc.core.RowCallbackHandler接口的实现。
修改StudentDaoImp:
1 /**
2 * 查询一条记录
3 */
4 public Student getStudent(final int id) {
5 // 装载查询结果
6 final Student student = new Student();
7
8 String sql = "select s.cno,s.name,s.score from student s where sno = ?";
9 // 设置查询参数
10 final Object[] params = new Object[] { new Integer(id) };
11 // 进行查询
12 jdbcTemplate.query(sql, params, new RowCallbackHandler() {
13 @Override
14 public void processRow(ResultSet rs) throws SQLException {
15 student.setCno(rs.getInt("cno"));
16 student.setName(rs.getString("name"));
17 student.setScore(rs.getDouble("score"));
18 }
19 });
20
21 return student;
22 }
进行测试：
1 @Test
2 public void selectTest() {
3 ApplicationContext context = new ClassPathXmlApplicationContext(
4 "applicationContext.xml");
5 StudentDaoImp studentDao = (StudentDaoImp) context
6 .getBean("studentDao");
7
8 Student student = studentDao.getStudent(15);
9 System.out.println("cno:" + student.getCno() + " name:"+ student.getName() + " score:" + student.getScore());
10 }
查询多条记录：这里需要用到org.springframework.jdbc.core.RowMapper接口的实现。
修改StudentDaoImp:
1 /**
2 * 查询多条记录
3 */
4 public List<Student> getAllStudent() {
5 String sql = "select s.cno,s.name,s.score from student s";
6
7 return jdbcTemplate.query(sql, new RowMapper<Student>() {
8 @Override
9 public Student mapRow(ResultSet rs, int index) throws SQLException {
10 Student student = new Student();
11 student.setCno(rs.getInt("cno"));
12 student.setName(rs.getString("name"));
13 student.setScore(rs.getDouble("score"));
14
15 return student;
16 }
17 });
18 }
RowMapper接口负责把Result中的一条记录映射成一个对象。
进行测试：
1 @Test
2 public void getAllStudent() {
3 ApplicationContext context = new ClassPathXmlApplicationContext(
4 "applicationContext.xml");
5 StudentDaoImp studentDao = (StudentDaoImp) context
6 .getBean("studentDao");
7
8 List<Student> list = new ArrayList<Student>();
9 list = studentDao.getAllStudent();
10
11 for (int i = 0; i < list.size(); i++) {
12 System.out.println("name is:" + list.get(i).getName());
13 }
14 }
也可以使用这种方法查询一条记录，只要附加查询参数即可：
1 /**
2 * 查询一条记录
3 */
4 public Student getStudent(final int id) {
5 // 装载查询结果
6 final Student student = new Student();
7
8 String sql = "select s.cno,s.name,s.score from student s where sno = ?";
9 // 设置查询参数
10 final Object[] params = new Object[] { new Integer(id) };
11
12 List<Student> list = jdbcTemplate.query(sql, params,
13 new RowMapper<Student>() {
14 @Override
15 public Student mapRow(ResultSet rs, int index)
16 throws SQLException {
17 Student student = new Student();
18 student.setCno(rs.getInt("cno"));
19 student.setName(rs.getString("name"));
20 student.setScore(rs.getDouble("score"));
21
22 return student;
23 }
24 });
25
26 return list.get(0);
27 }