Spring JDBC数据库操作

JDBC Template

Spring 在 JDBC API 上定义了一个抽象层，为不同类型的 JDBC 操作提供模板方法。

# 环境搭建

# 运行架构

        	+------------------------------+
           	| Spring Framework             |
           	|                              |
           	|           +--------------+   |
           	|           |JDBD Template |   |
           	|           +--------------+   |
           	+------------------------------+
           	+------------------------------+
           	|        Druid Datasource      |
           	+------------------------------+
           	+------------------------------+
           	|                              |
           	|            JDBC              |
           	+------------------------------+
           	+-------------+  +-------------+
           	| MySQL Driver|  | Oracle Driver
           	+-------------+  +-------------+
           	+-------------+  +-------------+
           	|             |  |             |
           	|   MySQL     |  |   Oracle    |
           	|             |  |             |
           	+-------------+  +-------------+

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

# 包依赖

For IOC

commons-logging-xxxx.jar
spring-beans-xxxx.jar
spring-context-xxxx.jar
spring-core-xxxx.jar
spring-expression-xxx.jar

For Jdbc Template

spring-jdbc-xxxx.jar
spring-orm-xxx.jar
spring-tx.xxxx.jar

For Driver and Datasource

druid-xxxx.jar
mysql-connector-java-xxxx.jar

# 使用步骤

# 数据库信息配置

配置正确连接到数据库

mysql.properties

user=root
password=root
jdbcUrl=jdbc:mysql:///query_data
driverClass=com.mysql.jdbc.Driver

initialPoolSize=30
minPoolSize=10
maxPoolSize=100
acquireIncrement=5
maxStatements=1000
maxStatementsPerConnection=10

1
2
3
4
5
6
7
8
9
10
11

# 核心 bean 配置

Datasource Bean

<context:property-placeholder location="classpath:jdbc.properties"/>

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
	<property name="user" value="${user}"/>
	<property name="password" value="${password}"/>
	<property name="jdbcUrl" value="${jdbcUrl}"/>
	<property name="driverClass" value="${driverClass}"/>
	<property name="initialPoolSize" value="${initialPoolSize}"/>
	<property name="minPoolSize" value="${minPoolSize}"/>
	<property name="maxPoolSize" value="${maxPoolSize}"/>
	<property name="acquireIncrement" value="${acquireIncrement}"/>
	<property name="maxStatements" value="${maxStatements}"/>
	<property name="maxStatementsPerConnection"
		  value="${maxStatementsPerConnection}"/>
</bean>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

Jdbc Template Bean

<bean id="template"
class="org.springframework.jdbc.core.JdbcTemplate">
	<property name="dataSource" ref="dataSource"/>
</bean>

1
2
3
4

# CRUD

// String 为 SQL 语句
// Object 封装了执行所需要的参数
// RowMapper 封装了查询结果
// ********************************************************
// 增删改单条
jdbcTemplate.update(String, Object...)
// 批量增删改
jdbcTemplate.batchUpdate(String, List<Object>[])
// 查询单条
jdbcTemplate.queryForObject(String, RowMapper<T>, Object)
// 批量查询
jdbcTemplate.query(String, RowMapper<T>, Object...)
// 查询字段值
jdbcTemplate.queryForObject(String, Class, Object...)

1
2
3
4
5
6
7
8
9
10
11
12
13
14

# 封装 Dao

@Repository
public class StudentDao {

	@Autowired
	private JbdcTemplate jdbcTemplate;

	public Student get(Integer id) {}
}

1
2
3
4
5
6
7
8

← Spring AOP 切面编程 Spring Transaction 事务→