首先要獲得DataSource連接池:
要對數(shù)據庫執(zhí)行任何的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行是配置連接數(shù)據庫所需的信息。
二：使用BasicDataSouce創(chuàng)建一個連接池。應為BasicDataSource所有屬性都是通過setter方法暴露在外面的，我們可以像配置其他Srping Bean那樣配置它
我將數(shù)據庫連接信息配置在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參數(shù)，其中<value>中的參數(shù)是在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這個參數(shù)，就是我們上面的到的DataSource.
使用JdbcTemplate插入數(shù)據:
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 //設置傳遞給通配符的參數(shù)
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)方法，方便我們進行數(shù)據的插入.?
進行測試：
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 }
數(shù)據庫中多了一條記錄：
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
批量插入數(shù)據：
批量插入數(shù)據需要用到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 //設置傳遞給通配符的參數(shù)
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()方法是得到需要插入的記錄的個數(shù)，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
查詢一條記錄：
執(zhí)行一條數(shù)據的查詢，需要使用org.springframework.jdbc.core.RowCallbackHandler接口的實現(xiàn)。
修改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 // 設置查詢參數(shù)
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接口的實現(xiàn)。
修改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 }
也可以使用這種方法查詢一條記錄，只要附加查詢參數(shù)即可：
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 // 設置查詢參數(shù)
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 }
?
轉載于:https://www.cnblogs.com/hanyuan/archive/2012/10/05/2712132.html
總結
以上是生活随笔為你收集整理的Spring中使用JDBC的全部內容，希望文章能夠幫你解決所遇到的問題。
如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。
