JdbcTemplate
为了使 JDBC 更加易于使用,Spring 在 JDBC API 上定义了一个抽象层,以此建立一个 JDBC 存取框架。作为 Spring JDBC 框架的核心,JDBC 模板的设计目的是为不同类型的 JDBC 操作提供模板方法。每个模板方法都能控制整个过程, 并允许覆盖过程中的特定任务。通过这种方式,可以在尽可能保留灵活性的情况下,将数据库存取的工作量降到最低。
使用JdbcTemplate可以对数据库执行增删改查操作,如下示例:
1. 添加依赖jar包
1.1 核心包
spring-core-4.0.0.RELEASE.jar
spring-expression-4.0.0.RELEASE.jar
spring-context-4.0.0.RELEASE.jar
spring-beans-4.0.0.RELEASE.jar
spring-aop-4.0.0.RELEASE.jar
commons-logging-1.1.1.jar
1.2 jdbc支持包
spring-jdbc-4.0.0.RELEASE.jar
spring-tx-4.0.0.RELEASE.jar
1.3 数据源和驱动包
c3p0-0.9.1.2.jar
mysql-connector-java-5.1.30-bin.jar
2. 在MySQL中建表
create table user(id int(10),username varchar(100),password varchar(100));
3. 添加模型类
package xyz.huning.spring4.jdbc;
public class School {
private int id;
private String name;
private String level;
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 String getLevel() {
return level;
}
public void setLevel(String level) {
this.level = level;
}
@Override
public String toString() {
return "School [id=" + id + ", name=" + name + ", level=" + level + "]";
}
}
package xyz.huning.spring4.jdbc;
public class User {
private int id;
private String name;
private String password;
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 String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
@Override
public String toString() {
return "User [id=" + id + ", name=" + name + ", password=" + password
+ "]";
}
}
4. 配置数据库properties文件
################################################### ## database configuration ## ################################################### jdbc.driverClass=com.mysql.jdbc.Driver jdbc.jdbcUrl=jdbc:mysql://localhost:3306/spring4jdbc jdbc.user=root jdbc.password=root ################################################### ## c3p0 configuration ## ################################################### c3p0.initialPoolSize=3 c3p0.minPoolSize=2 c3p0.maxPoolSize=10 c3p0.maxIdleTime=28000 c3p0.idleConnectionTestPeriod=3600
5. 配置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:context="http://www.springframework.org/schema/context" xmlns:tx="http://www.springframework.org/schema/tx" 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-4.0.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.0.xsd"> <!-- 导入资源文件 --> <context:property-placeholder location="classpath:db.properties"/> <!-- 配置 C3P0 数据源 --> <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"> <property name="user" value="${jdbc.user}"></property> <property name="password" value="${jdbc.password}"></property> <property name="jdbcUrl" value="${jdbc.jdbcUrl}"></property> <property name="driverClass" value="${jdbc.driverClass}"></property> <property name="initialPoolSize" value="${c3p0.initialPoolSize}"></property> <property name="minPoolSize" value="${c3p0.minPoolSize}"></property> <property name="maxPoolSize" value="${c3p0.maxPoolSize}"></property> <property name="maxIdleTime" value="${c3p0.maxIdleTime}"></property> <property name="idleConnectionTestPeriod" value="${c3p0.idleConnectionTestPeriod}"></property> </bean> <!--配置Spring的JdbcTemplate--> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <property name="dataSource" ref="dataSource"></property> </bean> </beans>
6. 添加测试类
package xyz.huning.spring4.jdbc;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class Main {
public static void main(String[] args) throws SQLException {
ApplicationContext ctx = new ClassPathXmlApplicationContext("db.xml");
//获取数据源以及连接
ComboPooledDataSource dataSource = ctx.getBean("dataSource",ComboPooledDataSource.class);
System.out.println(dataSource);
JdbcTemplate jdbcTemplate = ctx.getBean("jdbcTemplate",JdbcTemplate.class);
/**
* ****更新数据库****
* update方法可以执行insert,udpate,delete操作
*/
jdbcTemplate.update("insert into user values (?,?,?)", 3,"PinyCui","123456");
jdbcTemplate.update("update user set password = ? where id = ?","888888",3);
jdbcTemplate.update("delete from user where id = ?", 3);
List<Object[]> batchArgs = new ArrayList<Object[]>();
batchArgs.add(new Object[]{4,"TinyWang","123456"});
batchArgs.add(new Object[]{5,"SaraZhang","123456"});
batchArgs.add(new Object[]{6,"PikiShen","123456"});
/**
* ****批量更新数据库****
*/
jdbcTemplate.batchUpdate("insert into user values (?,?,?)", batchArgs);
/**
* ****查询单行****
* 从数据库中获取一条记录,实际得到对应的一个对象
* 注意不是调用queryForObject(String sql,Class<?> requiredType,Object...args)方法。
* 而需要调用queryForObject(String sql, RowMapper<User> rowMapper, Object... args)方法
* 1. 其中RowMapper指定如何去映射结果级的行,常用的实现类为BeanPropertyRowMapper
* 2. 使用SQL中列的别名完成列明和类的属性的映射,例如username as name /username name
* 3. 不支持接连属性。JdbcTemplate只是一个JDBC小工具,而不是ORM框架
*
*/
String sql = "select id,username as name,password from user where id = ?";
RowMapper<User> rowMapper = new BeanPropertyRowMapper<User>(User.class);
User user = jdbcTemplate.queryForObject(sql, rowMapper, 1);
System.out.println(user);
/**
* ****查询多行****
* 查询实体类的集合
* 调用的方法不是queryForList
*/
sql = "select id,username as name,password from user where id > ?";
rowMapper = new BeanPropertyRowMapper<User>(User.class);
List<User> users = jdbcTemplate.query(sql, rowMapper, 2);
System.out.println(users);
/**
* ****单值查询****
* 获取单个列的值,或做统计查询
*/
sql = "select count(id) from user";
long countId = jdbcTemplate.queryForObject(sql, Long.class);
System.out.println(countId);
((ClassPathXmlApplicationContext)ctx).close();
}
}