Spring JDBCTemplate是一款轻量级ORM框架,使用它替换原生的JDBC操作数据库会轻松许多。
本文将将介绍Spring中关于JDBC的一个辅助类(JDBCTemplate),它封装了JDBC的操作,使用起来非常方便。
maven依赖
<properties>
<spring.version>4.2.4.RELEASE</spring.version>
<aspectj.version>1.8.8</aspectj.version>
<java.version>1.7</java.version>
<maven.compiler.version>3.1</maven.compiler.version>
<junit.version>4.12</junit.version>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.26</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.0.21</version>
</dependency>
</dependencies>
Model
employee表:
CREATE TABLE `tb_employee` (
`ID` bigint(10) NOT NULL AUTO_INCREMENT,
`NAME` VARCHAR(100) NOT NULL,
`AGE` smallint(4) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Employee.java
package com.ricky.codelab.domain;
public class Employee {
private int id;
private String name;
private int age;
public Employee(){
}
public Employee(int id, String name, int age) {
this.id = id;
this.name = name;
this.age = age;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
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;
}
@Override
public String toString() {
return Employee ["id= "+ id + ", name= "+ name + ", age= "+ age
+"]";
}
}
Spring配置
1、applicationContext.xml
<?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:util="http://www.springframework.org/schema/util"
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/util
http://www.springframework.org/schema/util/spring-util.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.0.xsd"
default-lazy-init="false">
<context:annotation-config/>
<context:component-scan base-package="com.ricky.codelab.spring"/>
<!-- 引入配置文件 -->
<util:properties id="jdbc" location="classpath:jdbc.properties"/>
<import resource="spring-dao.xml"/>
<bean id="threadPool" class="java.util.concurrent.ThreadPoolExecutor">
<constructor-arg index="0" value="10"/>
<constructor-arg index="1" value="20"/>
<constructor-arg index="2" value="1"/>
<constructor-arg index="3" value="MINUTES"/>
<constructor-arg index="4">
<bean class="java.util.concurrent.LinkedBlockingQueue"/>
</constructor-arg>
</bean>
</beans>
其中,jdbc.properties
test1.jdbc.driver=com.mysql.jdbc.Driver
test1.jdbc.url=jdbc:mysql://localhost:3306/process?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull
test1.jdbc.username=root
test1.jdbc.password=root
test2.jdbc.driver=com.mysql.jdbc.Driver
test2.jdbc.url=jdbc:mysql://localhost:3306/process?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull
test2.jdbc.username=root
test2.jdbc.password=root
2、spring-dao.xml
<?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:tx="http://www.springframework.org/schema/tx"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:util="http://www.springframework.org/schema/util"
xsi:schemaLocation="
http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx.xsd
http://www.springframework.org/schema/util
http://www.springframework.org/schema/util/spring-util.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context.xsd"
default-lazy-init="false">
<context:annotation-config />
<context:component-scan base-package="com.ricky.codelab.spring" />
<bean id="dataSource_Abstract" class="com.alibaba.druid.pool.DruidDataSource"
destroy-method="close" abstract="true" init-method="init" >
<!-- 初始化连接大小 -->
<property name="initialSize" value="2" />
<!-- 连接池最大使用连接数量 -->
<property name="maxActive" value="10" />
<!-- 连接池最小空闲 -->
<property name="minIdle" value="5" />
<!-- 获取连接最大等待时间 -->
<property name="maxWait" value="30000" />
<!-- <property name="poolPreparedStatements" value="true" /> -->
<!-- <property name="maxPoolPreparedStatementPerConnectionSize" value="33" /> -->
<property name="validationQuery" value="SELECT 1" />
<property name="testOnBorrow" value="false" />
<property name="testOnReturn" value="false" />
<property name="testWhileIdle" value="true" />
<!-- 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒 -->
<property name="timeBetweenEvictionRunsMillis" value="60000" />
<!-- 配置一个连接在池中最小生存的时间,单位是毫秒 -->
<property name="minEvictableIdleTimeMillis" value="25200000" />
<!-- 打开removeAbandoned功能 -->
<property name="removeAbandoned" value="true" />
<!-- 1800秒,也就是30分钟 -->
<property name="removeAbandonedTimeout" value="1800" />
<!-- 关闭abanded连接时输出错误日志 -->
<property name="logAbandoned" value="true" />
<!-- 监控数据库 -->
<!-- <property name="filters" value="stat" /> -->
<property name="filters" value="mergeStat" />
</bean>
<!-- 配置来源数据 -->
<bean id="Test1DataSource" parent="dataSource_Abstract">
<property name="url" value="#{jdbc['test1.jdbc.url']}" />
<property name="username" value="#{jdbc['test1.jdbc.username']}" />
<property name="password" value="#{jdbc['test1.jdbc.password']}" />
<property name="driverClassName" value="#{jdbc['test1.jdbc.driver']}" />
<!-- 连接池最大使用连接数量 -->
<property name="maxActive" value="50" />
</bean>
<bean id="Test2DataSource" parent="dataSource_Abstract">
<property name="url" value="#{jdbc['test2.jdbc.url']}" />
<property name="username" value="#{jdbc['test2.jdbc.username']}" />
<property name="password" value="#{jdbc['test2.jdbc.password']}" />
<property name="driverClassName" value="#{jdbc['test2.jdbc.driver']}" />
</bean>
<bean id="test1JdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="Test1DataSource"/>
</bean>
<bean id="test2JdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="Test2DataSource"/>
</bean>
</beans>
3、EmployeeDao
package com.ricky.codelab.spring.jdbc.dao;
import com.ricky.codelab.spring.domain.Employee;
import java.util.List;
/**
* ${DESCRIPTION}
*
* @author Ricky Fung
* @create 2016-07-19 17:43
*/
public interface EmployeeDao {
public int insert(Employee employee);
public Employee findById(long id);
public List<Employee> queryAllEmployees();
}
EmployeeDaoImpl.java
package com.ricky.codelab.spring.jdbc.dao.impl;
import com.ricky.codelab.spring.domain.Employee;
import com.ricky.codelab.spring.jdbc.dao.EmployeeDao;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import javax.annotation.Resource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
/**
* ${DESCRIPTION}
*
* @author Ricky Fung
* @create 2016-07-19 17:44
*/
@Repository("employeeDao")
public class EmployeeDaoImpl implements EmployeeDao {
@Resource(name = "test1JdbcTemplate")
private JdbcTemplate jdbcTemplate;
@Override
public int insert(Employee employee) {
String sql = "INSERT INTO tb_employee(NAME, AGE) VALUES (?, ?)";
return jdbcTemplate.update(sql, new Object[] {employee.getName(), employee.getAge()});
}
@Override
public Employee findById(long id) {
String sql = "SELECT * FROM tb_employee WHERE ID = ?";
return jdbcTemplate.queryForObject(sql, new Object[]{ id }, new RowMapper<Employee>() {
public Employee mapRow(ResultSet rs, int rowNum) throws SQLException {
if(rs!=null){
Employee employee = new Employee();
employee.setId(rs.getInt("ID"));
employee.setName(rs.getString("NAME"));
employee.setAge(rs.getInt("AGE"));
return employee;
}
return null;
}
});
}
@Override
public List<Employee> queryAllEmployees() {
String sql = "SELECT * FROM tb_employee";
return jdbcTemplate.query(sql, new RowMapper<Employee>() {
public Employee mapRow(ResultSet rs, int rowNum) throws SQLException {
if(rs!=null){
Employee employee = new Employee();
employee.setId(rs.getInt("ID"));
employee.setName(rs.getString("NAME"));
employee.setAge(rs.getInt("AGE"));
return employee;
}
return null;
}
});
}
}
主要使用JdbcTemplate的update、queryForObject和query方法。
参考资料
http://docs.spring.io/spring/docs/current/spring-framework-reference/html/jdbc.html
代码下载
所有代码均已上传到Github,点此下载