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();
		
	}
}