Spring之对JDBC的支持
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(); } }