1. Overview
1.概述
In this tutorial, we’ll go through practical use cases of the Spring JDBC module.
在本教程中，我们将了解Spring JDBC模块的实际使用案例。
All the classes in Spring JDBC are divided into four separate packages:
Spring JDBC中的所有类都被划分为四个独立的包。
- core — the core functionality of JDBC. Some of the important classes under this package include JdbcTemplate, SimpleJdbcInsert, SimpleJdbcCall and NamedParameterJdbcTemplate.
- datasource — utility classes to access a data source. It also has various data source implementations for testing JDBC code outside the Jakarta EE container.
- object — DB access in an object-oriented manner. It allows running queries and returning the results as a business object. It also maps the query results between the columns and properties of business objects.
- support — support classes for classes under core and object packages, e.g., provides the SQLException translation functionality
2. Configuration
2.配置
Let’s start with some simple configuration of the data source.
让我们从数据源的一些简单配置开始。
We’ll use a MySQL database:
我们将使用一个MySQL数据库。
@Configuration
@ComponentScan("com.baeldung.jdbc")
public class SpringJdbcConfig {
@Bean
public DataSource mysqlDataSource() {
DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setUrl("jdbc:mysql://localhost:3306/springjdbc");
dataSource.setUsername("guest_user");
dataSource.setPassword("guest_password");
return dataSource;
}
}Alternatively, we can also make good use of an embedded database for development or testing.
另外，我们也可以很好地利用嵌入式数据库进行开发或测试。
Here is a quick configuration that creates an instance of H2 embedded database and pre-populates it with simple SQL scripts:
下面是一个快速配置，它创建了一个H2嵌入式数据库的实例，并预先用简单的SQL脚本填充它。
@Bean
public DataSource dataSource() {
return new EmbeddedDatabaseBuilder()
.setType(EmbeddedDatabaseType.H2)
.addScript("classpath:jdbc/schema.sql")
.addScript("classpath:jdbc/test-data.sql").build();
}
Finally, the same can be done using XML configuring for the datasource:
最后，同样可以使用XML为datasource进行配置。
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
destroy-method="close">
<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/springjdbc"/>
<property name="username" value="guest_user"/>
<property name="password" value="guest_password"/>
</bean>3. The JdbcTemplate and Running Queries
3、JdbcTemplate和运行查询
3.1. Basic Queries
3.1.基本查询
The JDBC template is the main API through which we’ll access most of the functionality that we’re interested in:
JDBC模板是主要的API，我们将通过它访问我们感兴趣的大部分功能。
- creation and closing of connections
- running statements and stored procedure calls
- iterating over the ResultSet and returning results
First, let’s start with a simple example to see what the JdbcTemplate can do:
首先，让我们从一个简单的例子开始，看看 JdbcTemplate能做什么。
int result = jdbcTemplate.queryForObject(
"SELECT COUNT(*) FROM EMPLOYEE", Integer.class);
And here’s a simple INSERT:
这里是一个简单的INSERT。
public int addEmplyee(int id) {
return jdbcTemplate.update(
"INSERT INTO EMPLOYEE VALUES (?, ?, ?, ?)", id, "Bill", "Gates", "USA");
}Notice the standard syntax of providing parameters using the ? character.
注意使用?字符提供参数的标准语法。
Next, let’s look at an alternative to this syntax.
接下来，让我们看看这种语法的替代方法。
3.2. Queries With Named Parameters
3.2.带有命名参数的查询
To get support for named parameters, we’ll use the other JDBC template provided by the framework — the NamedParameterJdbcTemplate.
为了获得对命名参数的支持，我们将使用框架提供的另一个JDBC模板 – NamedParameterJdbcTemplate。
Additionally, this wraps the JbdcTemplate and provides an alternative to the traditional syntax using ? to specify parameters.
此外，它还包装了JbdcTemplate，并提供了一种替代传统语法的方法，即使用? 来指定参数。
Under the hood, it substitutes the named parameters to JDBC ? placeholder and delegates to the wrapped JDCTemplate to run the queries:
在引擎盖下，它将命名的参数替换为JDBC ? 占位符，并委托包装好的JDCTemplate 来运行查询。
SqlParameterSource namedParameters = new MapSqlParameterSource().addValue("id", 1);
return namedParameterJdbcTemplate.queryForObject(
"SELECT FIRST_NAME FROM EMPLOYEE WHERE ID = :id", namedParameters, String.class);Notice how we are using the MapSqlParameterSource to provide the values for the named parameters.
注意我们是如何使用MapSqlParameterSource来提供命名参数的值的。
Let’s look at using properties from a bean to determine the named parameters:
让我们来看看使用Bean的属性来确定命名参数。
Employee employee = new Employee();
employee.setFirstName("James");
String SELECT_BY_ID = "SELECT COUNT(*) FROM EMPLOYEE WHERE FIRST_NAME = :firstName";
SqlParameterSource namedParameters = new BeanPropertySqlParameterSource(employee);
return namedParameterJdbcTemplate.queryForObject(
SELECT_BY_ID, namedParameters, Integer.class);Note how we’re now using the BeanPropertySqlParameterSource implementations instead of specifying the named parameters manually like before.
注意我们现在是如何使用BeanPropertySqlParameterSource实现的，而不是像以前那样手动指定命名参数。
3.3. Mapping Query Results to Java Object
3.3.将查询结果映射到Java对象
Another very useful feature is the ability to map query results to Java objects by implementing the RowMapper interface.
另一个非常有用的功能是通过实现 RowMapper接口，将查询结果映射到Java对象。
For example, for every row returned by the query, Spring uses the row mapper to populate the java bean:
例如，对于查询返回的每一条记录，Spring使用行映射器来填充java bean。
public class EmployeeRowMapper implements RowMapper<Employee> {
@Override
public Employee mapRow(ResultSet rs, int rowNum) throws SQLException {
Employee employee = new Employee();
employee.setId(rs.getInt("ID"));
employee.setFirstName(rs.getString("FIRST_NAME"));
employee.setLastName(rs.getString("LAST_NAME"));
employee.setAddress(rs.getString("ADDRESS"));
return employee;
}
}Subsequently, we can now pass the row mapper to the query API and get fully populated Java objects:
随后，我们现在可以将行映射器传递给查询API，并获得完全填充的Java对象。
String query = "SELECT * FROM EMPLOYEE WHERE ID = ?";
Employee employee = jdbcTemplate.queryForObject(
query, new Object[] { id }, new EmployeeRowMapper());4. Exception Translation
4.例外情况的翻译
Spring comes with its own data exception hierarchy out of the box — with DataAccessException as the root exception — and it translates all underlying raw exceptions to it.
Spring开箱即有自己的数据异常层次结构–以DataAccessException为根异常–并将所有底层的原始异常转换为它。
So, we keep our sanity by not handling low-level persistence exceptions. We also benefit from the fact that Spring wraps the low-level exceptions in DataAccessException or one of its sub-classes.
因此，我们通过不处理低级的持久化异常来保持我们的理智。我们还得益于Spring将低级别的异常封装在DataAccessException或其子类中。
This also keeps the exception handling mechanism independent of the underlying database we are using.
这也使异常处理机制独立于我们所使用的底层数据库。
Besides the default SQLErrorCodeSQLExceptionTranslator, we can also provide our own implementation of SQLExceptionTranslator.
除了默认的SQLErrorCodeSQLExceptionTranslator，我们还可以提供我们自己的SQLExceptionTranslator的实现。
Here’s a quick example of a custom implementation — customizing the error message when there is a duplicate key violation, which results in error code 23505 when using H2:
这里有一个自定义实现的快速例子–当出现重复键违规时，自定义错误信息，在使用H2时导致错误代码23505>。
public class CustomSQLErrorCodeTranslator extends SQLErrorCodeSQLExceptionTranslator {
@Override
protected DataAccessException
customTranslate(String task, String sql, SQLException sqlException) {
if (sqlException.getErrorCode() == 23505) {
return new DuplicateKeyException(
"Custom Exception translator - Integrity constraint violation.", sqlException);
}
return null;
}
}To use this custom exception translator, we need to pass it to the JdbcTemplate by calling setExceptionTranslator() method:
为了使用这个自定义的异常翻译器，我们需要通过调用setExceptionTranslator()方法将其传递给JdbcTemplate。
CustomSQLErrorCodeTranslator customSQLErrorCodeTranslator =
new CustomSQLErrorCodeTranslator();
jdbcTemplate.setExceptionTranslator(customSQLErrorCodeTranslator);5. JDBC Operations Using SimpleJdbc Classes
5.使用SimpleJdbc类的JDBC操作
SimpleJdbc classes provide an easy way to configure and run SQL statements. These classes use database metadata to build basic queries. So, SimpleJdbcInsert and SimpleJdbcCall classes provide an easier way to run insert and stored procedure calls.
SimpleJdbc 类提供了一种配置和运行SQL语句的简单方法。这些类使用数据库元数据来构建基本查询。因此，SimpleJdbcInsert和SimpleJdbcCall类提供了一种更简单的方式来运行插入和存储过程调用。
5.1. SimpleJdbcInsert
5.1.SimpleJdbcInsert
Let’s take a look at running simple insert statements with minimal configuration.
让我们来看看以最小的配置运行简单的插入语句。
The INSERT statement is generated based on the configuration of SimpleJdbcInsert. All we need is to provide the Table name, Column names and values.
INSERT语句是根据SimpleJdbcInsert的配置生成的。我们所需要的是提供表名、列名和值。
First, let’s create a SimpleJdbcInsert:
首先，让我们创建一个SimpleJdbcInsert。
SimpleJdbcInsert simpleJdbcInsert =
new SimpleJdbcInsert(dataSource).withTableName("EMPLOYEE");Next, let’s provide the Column names and values, and run the operation:
接下来，让我们提供列名和值，并运行操作。
public int addEmplyee(Employee emp) {
Map<String, Object> parameters = new HashMap<String, Object>();
parameters.put("ID", emp.getId());
parameters.put("FIRST_NAME", emp.getFirstName());
parameters.put("LAST_NAME", emp.getLastName());
parameters.put("ADDRESS", emp.getAddress());
return simpleJdbcInsert.execute(parameters);
}Further, we can use the executeAndReturnKey() API to allow the database to generate the primary key. We’ll also need to configure the actual auto-generated column:
此外，我们可以使用executeAndReturnKey() API来让数据库生成主键。我们还需要配置实际自动生成的列。
SimpleJdbcInsert simpleJdbcInsert = new SimpleJdbcInsert(dataSource)
.withTableName("EMPLOYEE")
.usingGeneratedKeyColumns("ID");
Number id = simpleJdbcInsert.executeAndReturnKey(parameters);
System.out.println("Generated id - " + id.longValue());Finally, we can also pass in this data by using the BeanPropertySqlParameterSource and MapSqlParameterSource.
最后，我们还可以通过使用BeanPropertySqlParameterSource和MapSqlParameterSource来传递这些数据。
5.2. Stored Procedures With SimpleJdbcCall
5.2.使用SimpleJdbcCall的存储程序
Let’s also take a look at running stored procedures.
我们也来看看运行存储过程的情况。
We’ll make use of the SimpleJdbcCall abstraction:
我们将利用SimpleJdbcCall的抽象。
SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(dataSource)
.withProcedureName("READ_EMPLOYEE");
public Employee getEmployeeUsingSimpleJdbcCall(int id) {
SqlParameterSource in = new MapSqlParameterSource().addValue("in_id", id);
Map<String, Object> out = simpleJdbcCall.execute(in);
Employee emp = new Employee();
emp.setFirstName((String) out.get("FIRST_NAME"));
emp.setLastName((String) out.get("LAST_NAME"));
return emp;
}6. Batch Operations
6.批量操作
Another simple use case is batching multiple operations together.
另一个简单的用例是将多个操作批在一起。
6.1. Basic Batch Operations Using JdbcTemplate
6.1.使用JdbcTemplate的基本批处理操作
Using JdbcTemplate, Batch Operations can be run via the batchUpdate() API.
使用JdbcTemplate，批量操作可以通过batchUpdate() API运行。
The interesting part here is the concise but highly useful BatchPreparedStatementSetter implementation:
这里有趣的部分是简明但非常有用的BatchPreparedStatementSetter实现。
public int[] batchUpdateUsingJdbcTemplate(List<Employee> employees) {
return jdbcTemplate.batchUpdate("INSERT INTO EMPLOYEE VALUES (?, ?, ?, ?)",
new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
ps.setInt(1, employees.get(i).getId());
ps.setString(2, employees.get(i).getFirstName());
ps.setString(3, employees.get(i).getLastName());
ps.setString(4, employees.get(i).getAddress();
}
@Override
public int getBatchSize() {
return 50;
}
});
}6.2. Batch Operations Using NamedParameterJdbcTemplate
6.2.使用NamedParameterJdbcTemplate的批处理操作
We also have the option of batching operations with the NamedParameterJdbcTemplate – batchUpdate() API.
我们还可以选择用NamedParameterJdbcTemplate–batchUpdate() API进行批量操作。
This API is simpler than the previous one. So, there’s no need to implement any extra interfaces to set the parameters, as it has an internal prepared statement setter to set the parameter values.
这个API比之前的API更简单。所以，不需要实现任何额外的接口来设置参数，因为它有一个内部的准备好的语句设置器来设置参数值。
Instead, the parameter values can be passed to the batchUpdate() method as an array of SqlParameterSource.
相反，参数值可以作为SqlParameterSource的一个数组传递给batchUpdate()方法。
SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(employees.toArray());
int[] updateCounts = namedParameterJdbcTemplate.batchUpdate(
"INSERT INTO EMPLOYEE VALUES (:id, :firstName, :lastName, :address)", batch);
return updateCounts;7. Spring JDBC With Spring Boot
7.Spring JDBC与Spring Boot
Spring Boot provides a starter spring-boot-starter-jdbc for using JDBC with relational databases.
Spring Boot提供了一个启动器spring-boot-starter-jdbc，用于使用关系型数据库的JDBC。
As with every Spring Boot starter, this one helps us get our application up and running quickly.
与每一个Spring Boot启动器一样，这个启动器可以帮助我们快速启动和运行我们的应用程序。
7.1. Maven Dependency
7.1.Maven的依赖性
We’ll need the spring-boot-starter-jdbc dependency as the primary one. We’ll also need a dependency for the database that we’ll be using. In our case, this is MySQL:
我们需要spring-boot-starter-jdbc依赖作为主要依赖。我们还需要一个我们将要使用的数据库的依赖关系。在我们的例子中，这就是MySQL。
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>7.2. Configuration
7.2.配置
Spring Boot configures the data source automatically for us. We just need to provide the properties in a properties file:
Spring Boot会自动为我们配置数据源。我们只需要在properties文件中提供这些属性。
spring.datasource.url=jdbc:mysql://localhost:3306/springjdbc
spring.datasource.username=guest_user
spring.datasource.password=guest_passwordAnd that’s it. Our application is up and running just by doing these configurations only. We can now use it for other database operations.
就这样了。仅仅通过这些配置，我们的应用程序就已经启动并运行了。我们现在可以用它来进行其他的数据库操作。
The explicit configuration we saw in the previous section for a standard Spring application is now included as part of Spring Boot auto-configuration.
我们在上一节看到的对标准Spring应用程序的显式配置，现在已经作为Spring Boot自动配置的一部分。
8. Conclusion
8.结论
In this article, we looked at the JDBC abstraction in the Spring Framework. We covered the various capabilities provided by Spring JDBC with practical examples.
在这篇文章中，我们研究了Spring框架中的JDBC抽象。我们用实际例子介绍了Spring JDBC提供的各种功能。
We also looked into how we can quickly get started with Spring JDBC using a Spring Boot JDBC starter.
我们还研究了如何使用Spring Boot JDBC启动器快速开始使用Spring JDBC。
The source code for the examples is available over on GitHub.
示例的源代码可在GitHub上获得。