背景

在JDBC開發中，充斥這大量重復的代碼，可能只是換了個SQL語句，其他代碼是完全不用變的。Spring的jar包里，提供了一個叫JDBCTemplate的模板，在保持操作靈活方便的情況下，將代碼量降到最低。

---

配置文件

首先修改Spring的xml文件(src目錄下)，我這里采用的是c3p0數據源，如下圖所示：

<?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:context="http://www.springframework.org/schema/context"xmlns:mvc="http://www.springframework.org/schema/mvc"xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsdhttp://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsdhttp://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc.xsd"> <!-- 定義要掃描 controller的包 --><context:component-scan base-package="cn.com.restapi.controller" /><context:component-scan base-package="cn.com.restapi.daoimpl"/><context:component-scan base-package="cn.com.restapi.serviceimpl"/><!-- 引用配置文件 --><context:property-placeholder location="classpath:jdbc.properties"/><mvc:default-servlet-handler /> <!-- 啟動注解驅動 SpringMVC 功能 --><mvc:annotation-driven /><!-- 配置視圖解析器解析路徑 --><bean class="org.springframework.web.servlet.view.InternalResourceViewResolver" id="internalResourceViewResolver"><!-- 定義視圖存放路徑 --><property name="prefix" value="/WEB-INF/jsp/" /><!-- 定義視圖后綴 --><property name="suffix" value=".jsp" /></bean><!-- 1、聲明數據源對象：C3P0連接池 --><bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"><!-- 加載jdbc驅動 --><property name="driverClass" value="${driverClass}"></property><!-- jdbc連接地址 --><property name="jdbcUrl" value="${jdbcUrl}"></property><!-- 連接數據庫的用戶名 --><property name="user" value="${user}"></property><!-- 連接數據庫的密碼 --><property name="password" value="${password}"></property><!-- 數據庫的初始化連接數 --><property name="initialPoolSize" value="3"></property><!-- 數據庫的最大連接數 --><property name="maxPoolSize" value="10"></property><!-- 數據庫最多執行的事務 --><property name="maxStatements" value="100"></property><!-- 連接數量不夠時每次的增量 --><property name="acquireIncrement" value="2"></property> </bean><!-- 創建jdbcTemplate對象 --><bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"><property name="dataSource" ref="dataSource"></property></bean></beans>

properties文件(src目錄下)如下圖內容如下圖所示：

? ? ? ? ? ? ? ? ? ? ???

?

然后寫一個JDBCTemplate的類，里面的方法用于對數據進行增刪改查操作。這里的JdbcTemplate屬性，Spring容器會根據XML文件里的配置進行自動的注入。

package cn.com.restapi.daoimpl;import java.sql.ResultSet; import java.sql.SQLException; import java.util.List; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.RowMapper; import org.springframework.stereotype.Repository; import cn.com.restapi.dao.EmployeeDao; import cn.com.restapi.model.Employee;@Repository public class EmployeeDaoImpl implements EmployeeDao{@Autowiredprivate JdbcTemplate jdbcTemplate;private Log log = LogFactory.getLog(EmployeeDaoImpl.class.getName());@Overridepublic List<Employee> getEmployees() {// TODO Auto-generated method stubString sql = "SELECT EMPLOYEE_ID,EMPLOYEE_NAME,AGE,ADDRESS,SEX FROM EMPLOYEE";log.info(sql);return jdbcTemplate.query(sql, new EmployeeRowMapper());}@Overridepublic Employee getEmployeeByID(int id) {// TODO Auto-generated method stubString sql = "SELECT EMPLOYEE_ID,EMPLOYEE_NAME,AGE,ADDRESS,SEX FROM EMPLOYEE WHERE EMPLOYEE_ID = ?";log.info(sql);List<Employee> employees = this.jdbcTemplate.query(sql, new EmployeeRowMapper(),id);if (employees.isEmpty()) {return null;}return employees.get(0);}@Overridepublic int createEmployee(Employee employee) {// TODO Auto-generated method stubString sql = "INSERT INTO EMPLOYEE(EMPLOYEE_ID,EMPLOYEE_NAME,AGE,ADDRESS,SEX) VALUE(?,?,?,?,?)";log.info(sql);int rowNum = this.jdbcTemplate.update(sql,employee.getEmployeeID(),employee.getEmployeeName(),employee.getAge(),employee.getAddress(),employee.getSex());return rowNum;}@Overridepublic int updateEmployee(Employee employee) {// TODO Auto-generated method stubString sql = "UPDATE EMPLOYEE SET EMPLOYEE_NAME = ?,AGE = ?,ADDRESS = ?,SEX = ? WHERE EMPLOYEE_ID = ?";log.info(sql);int rowNum = this.jdbcTemplate.update(sql,employee.getEmployeeName(),employee.getAge(),employee.getAddress(),employee.getSex(),employee.getEmployeeID());return rowNum;}@Overridepublic int deleteEmployee(Employee employee) {// TODO Auto-generated method stubString sql = "DELETE EMPLOYEE WHERE EMPLOYEE_ID = ?";log.info(sql);int rowNum = this.jdbcTemplate.update(sql,employee.getEmployeeID());return rowNum;}public class EmployeeRowMapper implements RowMapper<Employee>{@Overridepublic Employee mapRow(ResultSet rSet, int rowNum) throws SQLException {// TODO Auto-generated method stubEmployee employee = new Employee();employee.setEmployeeID(rSet.getInt("EMPLOYEE_ID"));employee.setEmployeeName(rSet.getString("EMPLOYEE_NAME"));employee.setAddress(rSet.getString("ADDRESS"));employee.setAge(rSet.getInt("AGE"));employee.setSex(rSet.getString("SEX"));return employee;}}@Overridepublic boolean ifEmployeeExist(Employee employee) {// TODO Auto-generated method stubEmployee employee2 = this.getEmployeeByID(employee.getEmployeeID());if (employee2 == null) {return true;}return false;}}

?只要對JdbcCompany進行依賴注入，就可以調用使用了。以下是spring mvc?rest服務中實現注入：

@RestController public class CompanyController {@Autowiredprivate JdbcCompany jdbcCompany;@RequestMapping(value="/company",method = RequestMethod.GET)public ResponseEntity<List<Company>> getAllCompany(){List<Company> companies = new ArrayList<Company>();companies = jdbcCompany.queryAllCompany();if (companies.isEmpty()) {return new ResponseEntity<List<Company>>(companies,HttpStatus.NO_CONTENT);}return new ResponseEntity<List<Company>>(companies,HttpStatus.OK);}@RequestMapping(value = "/company/{id}",method = RequestMethod.GET)public ResponseEntity<List<Company>> getCompany(@PathVariable("id") String id){List<Company> companies = null;companies = jdbcCompany.queryCompany(id);HttpStatus httpStatus = companies != null? HttpStatus.OK:HttpStatus.NOT_FOUND;return new ResponseEntity<List<Company>>(companies,httpStatus);} }

?

總結

以上是生活随笔為你收集整理的Spring整合JDBC开发的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。