Spring 简明教程

Spring - JDBC Framework Overview

在处理普通老 JDBC 使用数据库时，编写不必要的代码来处理异常、打开和关闭数据库连接会变得繁琐等。然而，Spring JDBC 框架会处理所有低级别详细信息，从打开连接开始，准备并执行 SQL 语句，处理异常，处理事务，最后关闭连接。

While working with the database using plain old JDBC, it becomes cumbersome to write unnecessary code to handle exceptions, opening and closing database connections, etc. However, Spring JDBC Framework takes care of all the low-level details starting from opening the connection, prepare and execute the SQL statement, process exceptions, handle transactions and finally close the connection.

因此，您所要做的就是定义连接参数并指定要执行的 SQL 语句，并在从数据库获取数据时对每次迭代执行所需的工作。

So what you have to do is just define the connection parameters and specify the SQL statement to be executed and do the required work for each iteration while fetching data from the database.

Spring JDBC 提供了多种方法和相应的不同类来与数据库进行交互。我将采用的方法是经典的、最流行的方法，它使用框架的 JdbcTemplate 类。这是管理所有数据库通信和异常处理的中央框架类。

Spring JDBC provides several approaches and correspondingly different classes to interface with the database. I’m going to take classic and the most popular approach which makes use of JdbcTemplate class of the framework. This is the central framework class that manages all the database communication and exception handling.

JdbcTemplate Class

JDBC Template 类执行 SQL 查询、更新语句、存储过程调用、对 ResultSets 执行迭代并提取返回的参数值。它还会捕获 JDBC 异常，并将它们转换到在 org.springframework.dao 包中定义的通用、更具信息性的异常层次结构。

The JDBC Template class executes SQL queries, updates statements, stores procedure calls, performs iteration over ResultSets, and extracts returned parameter values. It also catches JDBC exceptions and translates them to the generic, more informative, exception hierarchy defined in the org.springframework.dao package.

一旦配置，JdbcTemplate 类的实例就是线程安全的。因此，您可以配置 JdbcTemplate 的一个实例，然后将此共享引用安全地注入至多个 DAO。

Instances of the JdbcTemplate class are threadsafe once configured. So you can configure a single instance of a JdbcTemplate and then safely inject this shared reference into multiple DAOs.

在使用 JDBC Template 类时的一个常见做法是在您的 Spring 配置文件中配置一个 DataSource，然后将共享 DataSource bean 依赖注入到 DAO 类中，并且在 DataSource 的 setter 中创建 JdbcTemplate。

A common practice when using the JDBC Template class is to configure a DataSource in your Spring configuration file, and then dependency-inject that shared DataSource bean into your DAO classes, and the JdbcTemplate is created in the setter for the DataSource.

Configuring Data Source

让我们在我们的数据库 TEST 中创建一个数据库表 Student 。我们假设您使用的是 MySQL 数据库，如果您使用的是任何其他数据库，则可相应地更改您的 DDL 和 SQL 查询。

Let us create a database table Student in our database TEST. We assume you are working with MySQL database, if you work with any other database then you can change your DDL and SQL queries accordingly.

CREATE TABLE Student(
   ID   INT NOT NULL AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE  INT NOT NULL,
   PRIMARY KEY (ID)
);

现在，我们需要向 JDBC Template 提供一个 DataSource，以便它可以自行配置以获取数据库访问权。您可以在 XML 文件中配置 DataSource，其代码如下图所示代码片段所示：

Now we need to supply a DataSource to the JDBC Template so it can configure itself to get database access. You can configure the DataSource in the XML file with a piece of code as shown in the following code snippet −

<bean id = "dataSource"
   class = "org.springframework.jdbc.datasource.DriverManagerDataSource">
   <property name = "driverClassName" value = "com.mysql.jdbc.Driver"/>
   <property name = "url" value = "jdbc:mysql://localhost:3306/TEST"/>
   <property name = "username" value = "root"/>
   <property name = "password" value = "password"/>
</bean>

Data Access Object (DAO)

DAO 表示数据访问对象，通常用于数据库交互。DAO 存在于提供读取和向数据库写入数据的方法，并且它们应通过一个接口暴露出此功能，应用程序的其余部分将通过此接口访问它们。

DAO stands for Data Access Object, which is commonly used for database interaction. DAOs exist to provide a means to read and write data to the database and they should expose this functionality through an interface by which the rest of the application will access them.

Spring 中的 DAO 支持可以轻松以一致方式使用像 JDBC、Hibernate、JPA 或 JDO 这样的数据访问技术。

The DAO support in Spring makes it easy to work with data access technologies like JDBC, Hibernate, JPA, or JDO in a consistent way.

Executing SQL statements

让我们了解如何使用 SQL 和 JDBC 模板对象对数据库表执行 CRUD（创建、读取、更新和删除）操作。

Let us see how we can perform CRUD (Create, Read, Update and Delete) operation on database tables using SQL and JDBC Template object.

Querying for an integer

String SQL = "select count(*) from Student";
int rowCount = jdbcTemplateObject.queryForInt( SQL );

Querying for a long

String SQL = "select count(*) from Student";
long rowCount = jdbcTemplateObject.queryForLong( SQL );

A simple query using a bind variable

String SQL = "select age from Student where id = ?";
int age = jdbcTemplateObject.queryForInt(SQL, new Object[]{10});

Querying for a String

String SQL = "select name from Student where id = ?";
String name = jdbcTemplateObject.queryForObject(SQL, new Object[]{10}, String.class);

Querying and returning an object

String SQL = "select * from Student where id = ?";
Student student = jdbcTemplateObject.queryForObject(
   SQL, new Object[]{10}, new StudentMapper());

public class StudentMapper implements RowMapper<Student> {
   public Student mapRow(ResultSet rs, int rowNum) throws SQLException {
      Student student = new Student();
      student.setID(rs.getInt("id"));
      student.setName(rs.getString("name"));
      student.setAge(rs.getInt("age"));

      return student;
   }
}

Querying and returning multiple objects

String SQL = "select * from Student";
List<Student> students = jdbcTemplateObject.query(
   SQL, new StudentMapper());

public class StudentMapper implements RowMapper<Student> {
   public Student mapRow(ResultSet rs, int rowNum) throws SQLException {
      Student student = new Student();
      student.setID(rs.getInt("id"));
      student.setName(rs.getString("name"));
      student.setAge(rs.getInt("age"));

      return student;
   }
}

Inserting a row into the table

String SQL = "insert into Student (name, age) values (?, ?)";
jdbcTemplateObject.update( SQL, new Object[]{"Zara", 11} );

Updating a row into the table

String SQL = "update Student set name = ? where id = ?";
jdbcTemplateObject.update( SQL, new Object[]{"Zara", 10} );

Deleting a row from the table

String SQL = "delete Student where id = ?";
jdbcTemplateObject.update( SQL, new Object[]{20} );

Executing DDL Statements

你可以使用 jdbcTemplate 的 execute(..) 方法来执行任何 SQL 语句或 DDL 语句。以下是使用 CREATE 语句创建表的示例：

You can use the execute(..) method from jdbcTemplate to execute any SQL statements or DDL statements. Following is an example to use CREATE statement to create a table −

String SQL = "CREATE TABLE Student( " +
   "ID   INT NOT NULL AUTO_INCREMENT, " +
   "NAME VARCHAR(20) NOT NULL, " +
   "AGE  INT NOT NULL, " +
   "PRIMARY KEY (ID));"

jdbcTemplateObject.execute( SQL );

Spring JDBC Framework Examples

基于以上概念，让我们查看几个重要的示例，将帮助你理解 Spring 中 JDBC 框架的用法：

Based on the above concepts, let us check few important examples which will help you in understanding usage of JDBC framework in Spring −

Sr.No.

Example & Description

Spring JDBC ExampleThis example will explain how to write a simple JDBC-based Spring application.

SQL Stored Procedure in SpringLearn how to call SQL stored procedure while using JDBC in Spring.