1. 概述
本文我们将介绍Spring JDBC。Spring JDBC被分为四个package：
- core — JDBC核心，包含一些重要的class如JdbcTemplate、SimpleJdbcInsert、SimpleJdbcCall和NamedParameterJdbcTemplate。
- datasource — 访问数据源的Utils类。它还提供了多种数据源实现，以便在Jakarta EE容器外测试JDBC代码。
- object — 以面向对象的方式访问DB。它允许运行查询，并将结果返回为业务对象。它还映射了查询结果与业务对象的列和属性之间的关系。
- support — 对core和object包下的类的支持类，例如，提供了SQLException转换功能
2.数据源配置
我们从配置数据源开始，配置一个简单 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;
}
}
在开发或测试阶段，我们经常会使用嵌入式内存数据库。
以下创建了一个H2嵌入式数据库实例，并使用SQL脚本对数据库进行预初始化：
@Bean
public DataSource dataSource() {
return new EmbeddedDatabaseBuilder()
.setType(EmbeddedDatabaseType.H2)
.addScript("classpath:jdbc/schema.sql")
.addScript("classpath:jdbc/test-data.sql").build();
}
以XML方式配置数据源：
<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. JdbcTemplate 用法
3.1. 基础查询
JdbcTemplate 提供了我们所需的主要接口：
- 创建和关闭连接
- 运行SQL和存储过程调用
- 迭代ResultSet并返回结果
JdbcTemplate 简单查询示例：
int result = jdbcTemplate.queryForObject(
"SELECT COUNT(*) FROM EMPLOYEE", Integer.class);
数据插入：
public int addEmplyee(int id) {
return jdbcTemplate.update(
"INSERT INTO EMPLOYEE VALUES (?, ?, ?, ?)", id, "Bill", "Gates", "USA");
}
其中使用标准语法 ? 传入参数。接下来，我们看看其它替代方式。
3.2. 命名参数
要支持命名参数，我们需要使用 NamedParameterJdbcTemplate。
它包装了JbdcTemplate，通过指定参数名的方式替代使用 "?"。底层实现原理是将命名参数替换为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.
Let’s look at using properties from a bean to determine the named parameters:
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.
3.3. 将查询结果映射到Java对象
Another very useful feature is the ability to map query results to Java objects by implementing the RowMapper interface.
For example, for every row returned by the query, Spring uses the row mapper to populate the 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:
String query = "SELECT * FROM EMPLOYEE WHERE ID = ?";
Employee employee = jdbcTemplate.queryForObject(query, new EmployeeRowMapper(), id);
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.
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.
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.
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:
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:
CustomSQLErrorCodeTranslator customSQLErrorCodeTranslator =
new CustomSQLErrorCodeTranslator();
jdbcTemplate.setExceptionTranslator(customSQLErrorCodeTranslator);
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.
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.
First, let’s create a 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:
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.
5.2. 使用 SimpleJdbcCall 调用存储过程
Let’s also take a look at running stored procedures.
We’ll make use of the SimpleJdbcCall abstraction:
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. 批量操作
Another simple use case is batching multiple operations together.
6.1. 使用 JdbcTemplate 进行批处理
Using JdbcTemplate, Batch Operations can be run via the batchUpdate() API.
The interesting part here is the concise but highly useful BatchPreparedStatementSetter implementation:
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. 使用 NamedParameterJdbcTemplate 进行批处理
We also have the option of batching operations with the 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.
Instead, the parameter values can be passed to the batchUpdate() method as an array of SqlParameterSource.
SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(employees.toArray());
int[] updateCounts = namedParameterJdbcTemplate.batchUpdate(
"INSERT INTO EMPLOYEE VALUES (:id, :firstName, :lastName, :address)", batch);
return updateCounts;
7. Spring Boot 集成
Spring Boot provides a starter spring-boot-starter-jdbc for using JDBC with relational databases.
As with every Spring Boot starter, this one helps us get our application up and running quickly.
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:
<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. 数据源配置
Spring Boot configures the data source automatically for us. We just need to provide the properties in a properties file:
spring.datasource.url=jdbc:mysql://localhost:3306/springjdbc
spring.datasource.username=guest_user
spring.datasource.password=guest_password
And 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.
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.
We also looked into how we can quickly get started with Spring JDBC using a Spring Boot JDBC starter.
The source code for the examples is available over on GitHub.