Spring對JDBC的模板支援:JdbcTemplate
Spring的JdbcTemplate是一個對JDBC的模板封裝,它提供了一套JDBC的模板,能讓我們寫持久層代碼時減少多餘的代碼,簡化JDBC代碼,使代碼看起來更簡潔。在介紹Spring的JdbcTemplate使用方法之前我們先來討論一個問題,以下這是一段常見的往資料庫寫入資料的JDBC代碼:
public int jdbcInsert(Student student) throws SQLException {
Connection connection = null;
try {
connection = dataSource.getConnection();
String sql = "INSERT INTO student(sname,age,sex,address) VALUES (?,?,?,?)";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, student.getName());
preparedStatement.setInt(2, student.getAge());
preparedStatement.setString(3, student.getSex());
preparedStatement.setString(4, student.getAddress());
return preparedStatement.executeUpdate();
} finally {
connection.close();
}
}
public int jdbcUpdate(Student student) throws SQLException {
Connection connection = null;
try {
connection = dataSource.getConnection();
String sql = "UPDATE student SET sname=?,age=?,sex=?,address=?";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, student.getName());
preparedStatement.setInt(2, student.getAge());
preparedStatement.setString(3, student.getSex());
preparedStatement.setString(4, student.getAddress());
return preparedStatement.executeUpdate();
} finally {
connection.close();
}
}
從如上的代碼中,可以看到兩個方法中基本99%的代碼都是重複的,除了sql語句之外,都是重複的代碼,重複的代碼就是壞味道,會讓我們的產生大量的冗餘代碼,不易於維護和修改,而且寫起來還累。
所以Spring提供的JdbcTemplate正是用來解決這個問題的,其實Spring的JDBCTemplate有點像DBUtils,但是有時候還沒有DBUitls好用。這裡來學習一下使用Spring的JdbcTemplate來玩一下CRUD,畢竟JdbcTemplate在實際開發中一般不會使用,通常都是使用Mybatis、Hibernate等成熟、優秀的資料持久層架構,不過還是得知道Spring有一個這樣的jdbc模板類。
Spring對不同的持久化支援:
Spring可不單止支援JDBC,Spring為各種支援的持久化技術,都提供了簡單操作的模板和回調:
ORM持久化技術 |
模板類 |
JDBC |
org.springframework.jdbc.core.JdbcTemplate |
Hibernate5.0 |
org.springframework.orm.hibernate5.HibernateTemplate |
IBatis(MyBatis) |
org.springframework.orm.ibatis.SqlMapClientTemplate |
JPA |
org.springfrmaework.orm.jpa.JpaTemplate |
使用JdbcTemplate需要配置的依賴:
<dependencies>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>4.3.14.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>4.3.14.RELEASE</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.39</version>
</dependency>
<dependency>
<groupId>com.mchange</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.5.2</version>
</dependency>
</dependencies>
使用JdbcTemplate的基本步驟:
- 配置Spring的設定檔,內容如下:
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:p="http://www.springframework.org/schema/p"
xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context.xsd
">
<context:annotation-config/>
<context:component-scan base-package="org.zero01"/>
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"
p:driverClass="com.mysql.jdbc.Driver"
p:jdbcUrl="jdbc:mysql:///school"
p:user="root"
p:password="Zero-One1."
p:loginTimeout="2000"
p:maxPoolSize="10"
p:minPoolSize="1"
/>
</beans>
- 建立資料庫表格欄位封裝類:
package org.zero01.pojo;
import org.springframework.stereotype.Component;
@(JavaWeb)Component("stu")
public class Student {
private int sid;
private String name;
private int age;
private String sex;
private String address;
public int getSid() {
return sid;
}
public void setSid(int sid) {
this.sid = sid;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
}
- 編寫dao類:
package org.zero01.dao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import org.zero01.pojo.Student;
import javax.sql.DataSource;
@Component("stuDAO")
public class StudentDAO {
@Autowired
private DataSource dataSource;
public int springInsert(Student student) {
// 執行個體化jdbc模板對象,並傳入資料來源
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
String sql = "INSERT INTO student(sname,age,sex,address) VALUES (?,?,?,?)";
// 調用update方法執行insert
int row = jdbcTemplate.update(sql, student.getName(), student.getAge(), student.getSex(), student.getAddress());
return row;
}
}
可以看到,使用了JdbcTemplate之後,只需要寫sql語句再調用相應的執行方法即可,不需要去關心資料庫連接對象的獲得、關閉以及減少了大量設定值的代碼。
而且以上只是其中一種寫法,我們還可以直接繼承JdbcTemplate,這樣就可以直接調用父類的方法了:
package org.zero01.dao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import org.zero01.pojo.Student;
import javax.sql.DataSource;
@Component("stuDAO")
public class StudentDAO extends JdbcTemplate {
@Autowired
// 重寫父類的setDataSource來設定資料來源對象
public void setDataSource(DataSource dataSource) {
super.setDataSource(dataSource);
}
public int springInsert(Student student) {
String sql = "INSERT INTO student(sname,age,sex,address) VALUES (?,?,?,?)";
// 直接調用父類的方法即可
int row = update(sql, student.getName(), student.getAge(), student.getSex(), student.getAddress());
return row;
}
}
以下通過JdbcTemplate來編寫一個簡單的增刪查改小例題:
介面:
package org.zero01.dao;
import org.zero01.pojo.Student;
import java.util.List;
public interface DAO {
public int insert(Student student);
public int delete(int sid);
public List<Student> selectAll();
public List<Student> selectByLimit(int start, int end);
public Student selectById(int sid);
public long countAll();
public int update(Student student);
}
因為JdbcTemplate不提供表格欄位自動對應到對象的屬性上的功能,所以我們需要自己實現它的一個介面來進行手動設定映射:
package org.zero01.dao;
import org.springframework.jdbc.core.RowMapper;
import org.zero01.pojo.Student;
import java.sql.ResultSet;
import java.sql.SQLException;
// Student對象的屬性對應類
public class StudentMapper implements RowMapper<Student> {
public Student mapRow(ResultSet resultSet, int i) throws SQLException {
Student student = new Student();
student.setSid(resultSet.getInt("sid"));
student.setName(resultSet.getString("sname"));
student.setAge(resultSet.getInt("age"));
student.setSex(resultSet.getString("sex"));
student.setAddress(resultSet.getString("address"));
return student;
}
}
StudentDAO類:
package org.zero01.dao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import org.zero01.pojo.Student;
import javax.sql.DataSource;
import java.util.List;
@Component("stuDAO")
public class StudentDAO extends JdbcTemplate implements DAO {
@Autowired
// 重寫父類的setDataSource來設定資料來源對象
public void setDataSource(DataSource dataSource) {
super.setDataSource(dataSource);
}
// 插入單行資料
public int insert(Student student) {
String sql = "INSERT INTO student(sname,age,sex,address) VALUES (?,?,?,?)";
int row = update(sql, student.getName(), student.getAge(), student.getSex(), student.getAddress());
return row;
}
// 根據id進行刪除
public int delete(int sid) {
return update("DELETE FROM student WHERE sid=?", sid);
}
// 查詢多行資料
public List<Student> selectAll() {
// 查詢多個對象就需要自己傳遞映射類進行映射
List<Student> studentList = query("SELECT * FROM student", new StudentMapper());
return studentList;
}
// 分頁查詢資料
public List<Student> selectByLimit(int start, int end) {
// 查詢多個對象就需要自己傳遞映射類進行映射
List<Student> studentList = query("SELECT * FROM student LIMIT " + start + "," + end, new StudentMapper());
return studentList;
}
// 根據id查詢單行資料
public Student selectById(int sid) {
// 儲存參數
Object[] objects = {sid};
Student student = queryForObject("SELECT * FROM student where sid=?", objects, new StudentMapper());
return student;
}
// 查詢表的總行數
public long countAll() {
Long countNumber = queryForObject("SELECT count(*) FROM student", Long.class);
return countNumber;
}
// 更新單行資料
public int update(Student student) {
String sql = "UPDATE student SET sname=?,age=?,sex=?,address=? WHERE sid=?";
return update(sql, student.getName(), student.getAge(), student.getSex(), student.getAddress(), student.getSid());
}
}
如上,可以看到,通過使用Spring提供的JdbcTemplate,我們只需要編寫具體的sql語句即可,比起編寫普通的JDBC代碼要簡潔許多,也沒有出現多餘的代碼。當項目不需要使用到Mybatis、Hibernate等架構時,使用JdbcTemplate也不錯。
本文永久更新連結地址:https://www.bkjia.com/Linux/2018-03/151182.htm