15.2 使用JDBC核心类控制基础的JDBC处理过程和异常处理机制

15.2.1 JdbcTemplate

JdbcTemplate是JDBC core包里面的核心类。它封装了对资源的创建和释放,可以帮你避免忘记关闭连接等常见错误。它也包含了核心JDBC工作流的一些基础工作、例如执行和声明语句,而把SQL语句的生成以及查询结果的提取工作留给应用代码。JdbcTemplate执行查询、更新SQL语句和调用存储过程,运行结果集迭代和抽取返回参数值。它也可以捕获JDBC异常并把它们转换成更加通用、解释性更强的异常层次结构、这些异常都定义在org.springframework.dao包里面。

当你在代码中使用了JdbcTemplate类,你只需要实现回调接口。PreparedStatementCreator回调接口通过传入的Connection类(该类包含SQL和任何必要的参数)创建已声明的语句。CallableStatementCreator也提供类似的方式、该接口用于创建回调语句。RowCallbackHandler用于获取结果集每一行的值。

可以在DAO实现类中通过传入DataSource引用来完成JdbcTemplate的初始化;也可以在Spring IOC容器里面配置、作为DAO bean的依赖Bean配置。

备注:DataSource最好在Spring IOC容器里作为Bean配置起来。在上面第一种情况下,DataSource bean直接传给相关的服务;第二种情况下DataSource bean传递给JdbcTemplate bean。

JdbcTemplate中使用的所有SQL以“DEBUG”级别记入日志(一般情况下日志的归类是JdbcTemplate对应的全限定类名,不过如果需要对JdbcTemplate进行定制的话,可能是它的子类名)

JdbcTemplate 使用示例

这一节提供了JdbcTemplate类的一些使用例子。这些例子没有囊括JdbcTemplate可提供的所有功能;全部功能和用法请详见相关的javadocs.

查询 (SELECT)

下面是一个简单的例子、用于获取关系表里面的行数

  1. int rowCount = this.jdbcTemplate.queryForObject("select count(*) from t_actor", Integer.class);

使用绑定变量的简单查询:

  1. int countOfActorsNamedJoe = this.jdbcTemplate.queryForObject(
  2. "select count(*) from t_actor where first_name = ?", Integer.class, "Joe");

String查询:

  1. String lastName = this.jdbcTemplate.queryForObject(
  2. "select last_name from t_actor where id = ?",
  3. new Object[]{1212L}, String.class);

查询和填充领域模型:

  1. Actor actor = this.jdbcTemplate.queryForObject(
  2. "select first_name, last_name from t_actor where id = ?",
  3. new Object[]{1212L},
  4. new RowMapper<Actor>() {
  5. public Actor mapRow(ResultSet rs, int rowNum) throws SQLException {
  6. Actor actor = new Actor();
  7. actor.setFirstName(rs.getString("first_name"));
  8. actor.setLastName(rs.getString("last_name"));
  9. return actor;
  10. }
  11. });

查询和填充多个领域对象:

  1. List<Actor> actors = this.jdbcTemplate.query(
  2. "select first_name, last_name from t_actor",
  3. new RowMapper<Actor>() {
  4. public Actor mapRow(ResultSet rs, int rowNum) throws SQLException {
  5. Actor actor = new Actor();
  6. actor.setFirstName(rs.getString("first_name"));
  7. actor.setLastName(rs.getString("last_name"));
  8. return actor;
  9. }
  10. });

如果上面的两段代码实际存在于相同的应用中,建议把RowMapper匿名类中重复的代码抽取到单独的类中(通常是一个静态类),方便被DAO方法引用。例如,上面的代码例子更好的写法如下:

  1. public List<Actor> findAllActors() {
  2. return this.jdbcTemplate.query( "select first_name, last_name from t_actor", new ActorMapper());
  3. }
  4. private static final class ActorMapper implements RowMapper<Actor> {
  5. public Actor mapRow(ResultSet rs, int rowNum) throws SQLException {
  6. Actor actor = new Actor();
  7. actor.setFirstName(rs.getString("first_name"));
  8. actor.setLastName(rs.getString("last_name"));
  9. return actor;
  10. }
  11. }

使用jdbcTemplate实现增删改

你可以使用update(..)方法实现插入,更新和删除操作。参数值可以通过可变参数或者封装在对象内传入。

  1. this.jdbcTemplate.update(
  2. "insert into t_actor (first_name, last_name) values (?, ?)",
  3. "Leonor", "Watling");
  1. this.jdbcTemplate.update(
  2. "update t_actor set last_name = ? where id = ?",
  3. "Banjo", 5276L);
  1. this.jdbcTemplate.update(
  2. "delete from actor where id = ?",
  3. Long.valueOf(actorId));

其他jdbcTemplate操作

你可以使用execute(..)方法执行任何SQL,甚至是DDL语句。这个方法可以传入回调接口、绑定可变参数数组等。

  1. this.jdbcTemplate.execute("create table mytable (id integer, name varchar(100))");

下面的例子调用一段简单的存储过程。更复杂的存储过程支持文档后面会有描述。

  1. this.jdbcTemplate.update(
  2. "call SUPPORT.REFRESH_ACTORS_SUMMARY(?)",
  3. Long.valueOf(unionId));

JdbcTemplate 最佳实践
JdbcTemplate实例一旦配置之后是线程安全的。这点很重要因为这样你就能够配置JdbcTemplate的单例,然后安全的将其注入到多个DAO中(或者repositories)。JdbcTemplate是有状态的,内部存在对DataSource的引用,但是这种状态不是会话状态。

使用JdbcTemplate类的常用做法是在你的Spring配置文件里配置好一个DataSource,然后将其依赖注入进你的DAO类中(NamedParameterJdbcTemplate也是如此)。JdbcTemplate在DataSource的Setter方法中被创建。就像如下DAO类的写法一样:

  1. public class JdbcCorporateEventDao implements CorporateEventDao {
  2. private JdbcTemplate jdbcTemplate;
  3. public void setDataSource(DataSource dataSource) {
  4. this.jdbcTemplate = new JdbcTemplate(dataSource);
  5. }
  6. // JDBC-backed implementations of the methods on the CorporateEventDao follow...
  7. }

相关的配置是这样的:

  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <beans xmlns="http://www.springframework.org/schema/beans"
  3. xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  4. xmlns:context="http://www.springframework.org/schema/context"
  5. xsi:schemaLocation="
  6. http://www.springframework.org/schema/beans
  7. http://www.springframework.org/schema/beans/spring-beans.xsd
  8. http://www.springframework.org/schema/context
  9. http://www.springframework.org/schema/context/spring-context.xsd">
  10. <bean id="corporateEventDao" class="com.example.JdbcCorporateEventDao">
  11. <property name="dataSource" ref="dataSource"/>
  12. </bean>
  13. <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
  14. <property name="driverClassName" value="${jdbc.driverClassName}"/>
  15. <property name="url" value="${jdbc.url}"/>
  16. <property name="username" value="${jdbc.username}"/>
  17. <property name="password" value="${jdbc.password}"/>
  18. </bean>
  19. <context:property-placeholder location="jdbc.properties"/>
  20. </beans>

另一种替代显式配置的方式是使用component-scanning和注解注入。在这个场景下需要添加@Repository注解(添加这个注解可以被component-scanning扫描到),同时在DataSource的Setter方法上添加@Autowired注解:

  1. @Repository
  2. public class JdbcCorporateEventDao implements CorporateEventDao {
  3. private JdbcTemplate jdbcTemplate;
  4. @Autowired
  5. public void setDataSource(DataSource dataSource) {
  6. this.jdbcTemplate = new JdbcTemplate(dataSource);
  7. }
  8. // JDBC-backed implementations of the methods on the CorporateEventDao follow...
  9. }

相关的XML配置如下:

  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <beans xmlns="http://www.springframework.org/schema/beans"
  3. xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  4. xmlns:context="http://www.springframework.org/schema/context"
  5. xsi:schemaLocation="
  6. http://www.springframework.org/schema/beans
  7. http://www.springframework.org/schema/beans/spring-beans.xsd
  8. http://www.springframework.org/schema/context
  9. http://www.springframework.org/schema/context/spring-context.xsd">
  10. <!-- Scans within the base package of the application for @Component classes to configure as beans -->
  11. <context:component-scan base-package="org.springframework.docs.test" />
  12. <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
  13. <property name="driverClassName" value="${jdbc.driverClassName}"/>
  14. <property name="url" value="${jdbc.url}"/>
  15. <property name="username" value="${jdbc.username}"/>
  16. <property name="password" value="${jdbc.password}"/>
  17. </bean>
  18. <context:property-placeholder location="jdbc.properties"/>
  19. </beans>

如果你使用Spring的JdbcDaoSupport类,许多JDBC相关的DAO类都从该类继承过来,这个时候相关子类需要继承JdbcDaoSupport类的setDataSource方法。当然你也可以选择不从这个类继承,JdbcDaoSupport本身只是提供一些便利性。

无论你选择上面提到的哪种初始方式,当你在执行SQL语句时一般都不需要重新创建JdbcTemplate 实例。JdbcTemplate一旦被配置后其实例都是线程安全的。当你的应用需要访问多个数据库时你可能也需要多个JdbcTemplate实例,相应的也需要多个DataSources,同时对应多个JdbcTemplates配置。

15.2.2 NamedParameterJdbcTemplate

NamedParameterJdbcTemplate 提供对JDBC语句命名参数的支持,而普通的JDBC语句只能使用经典的 ‘?’参数。NamedParameterJdbcTemplate内部包装了JdbcTemplate,很多功能是直接通过JdbcTemplate来实现的。本节主要描述NamedParameterJdbcTemplate不同于JdbcTemplate 的点;即通过使用命名参数来操作JDBC

  1. // some JDBC-backed DAO class...
  2. private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
  3. public void setDataSource(DataSource dataSource) {
  4. this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
  5. }
  6. public int countOfActorsByFirstName(String firstName) {
  7. String sql = "select count(*) from T_ACTOR where first_name = :first_name";
  8. SqlParameterSource namedParameters = new MapSqlParameterSource("first_name", firstName);
  9. return this.namedParameterJdbcTemplate.queryForObject(sql, namedParameters, Integer.class);
  10. }

上面代码块可以看到SQL变量中命名参数的标记用法,以及namedParameters变量的相关赋值(类型为MapSqlParameterSource)

除此以外,你还可以在NamedParameterJdbcTemplate中传入Map风格的命名参数及相关的值。NamedParameterJdbcTemplate类从NamedParameterJdbcOperations接口实现的其他方法用法是类似的,这里就不一一叙述了。

下面是一个Map风格的例子:

  1. // some JDBC-backed DAO class...
  2. private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
  3. public void setDataSource(DataSource dataSource) {
  4. this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
  5. }
  6. public int countOfActorsByFirstName(String firstName) {
  7. String sql = "select count(*) from T_ACTOR where first_name = :first_name";
  8. Map<String, String> namedParameters = Collections.singletonMap("first_name", firstName);
  9. return this.namedParameterJdbcTemplate.queryForObject(sql, namedParameters, Integer.class);
  10. }

与NamedParameterJdbcTemplate相关联的SqlParameterSource接口提供了很有用的功能(两者在同一个包里面)。在上面的代码片段中你已经看到了这个接口的一个实现例子(就是MapSqlParameterSource类)。SqlParameterSource类是NamedParameterJdbcTemplate
类的数值值来源。MapSqlParameterSource实现非常简单、只是适配了java.util.Map,其中Key就是参数名字,Value就是参数值。

另外一个SqlParameterSource 的实现是BeanPropertySqlParameterSource类。这个类封装了任意一个JavaBean(也就是任意符合JavaBen规范的实例),在这个实现中,使用了JavaBean的属性作为命名参数的来源。

  1. public class Actor {
  2. private Long id;
  3. private String firstName;
  4. private String lastName;
  5. public String getFirstName() {
  6. return this.firstName;
  7. }
  8. public String getLastName() {
  9. return this.lastName;
  10. }
  11. public Long getId() {
  12. return this.id;
  13. }
  14. // setters omitted...
  15. }
  1. // some JDBC-backed DAO class...
  2. private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
  3. public void setDataSource(DataSource dataSource) {
  4. this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
  5. }
  6. public int countOfActors(Actor exampleActor) {
  7. // notice how the named parameters match the properties of the above 'Actor' class
  8. String sql = "select count(*) from T_ACTOR where first_name = :firstName and last_name = :lastName";
  9. SqlParameterSource namedParameters = new BeanPropertySqlParameterSource(exampleActor);
  10. return this.namedParameterJdbcTemplate.queryForObject(sql, namedParameters, Integer.class);
  11. }

之前提到过NamedParameterJdbcTemplate本身包装了经典的JdbcTemplate模板。如果你想调用只存在于JdbcTemplate类中的方法,你可以使用getJdbcOperations()方法、该方法返回JdbcOperations接口,通过这个接口你可以调用内部JdbcTemplate的方法。

NamedParameterJdbcTemplate 类在应用上下文的使用方式也可见:“JdbcTemplate最佳实践

15.2.3 SQLExceptionTranslator

SQLExceptionTranslator接口用于在SQLExceptions和spring自己的org.springframework.dao.DataAccessException之间做转换,要处理批量更新或者从文件中这是为了屏蔽底层的数据访问策略。其实现可以是比较通用的(例如,使用JDBC的SQLState编码),或者是更精确专有的(例如,使用Oracle的错误类型编码)

SQLExceptionTranslator 接口的默认实现是SQLErrorCodeSQLExceptionTranslator,该实现使用的是指定数据库厂商的错误编码,因为要比SQLState的实现更加精确。错误码转换过程基于JavaBean类型的SQLErrorCodes。这个类通过SQLErrorCodesFactory创建和返回,SQLErrorCodesFactory是一个基于sql-error-codes.xml配置内容来创建SQLErrorCodes的工厂类。该配置中的数据库厂商代码基于Database MetaData信息中返回的数据库产品名(DatabaseProductName),最终使用的就是你正在使用的实际数据库中错误码。

SQLErrorCodeSQLExceptionTranslator按以下的顺序来匹配规则:

备注:SQLErrorCodesFactory是用于定义错误码和自定义异常转换的缺省工厂类。错误码参照Classpath下配置的sql-error-codes.xml文件内容,相匹配的SQLErrorCodes实例基于正在使用的底层数据库的元数据名称

  • 是否存在自定义转换的子类。通常直接使用SQLErrorCodeSQLExceptionTranslator就可以了,因此此规则一般不会生效。只有你真正自己实现了一个子类才会生效。

  • 是否存在SQLExceptionTranslator接口的自定义实现,通过SQLErrorCodes类的customSqlExceptionTranslator属性指定

  • SQLErrorCodes的customTranslations属性数组、类型为CustomSQLErrorCodesTranslation类实例列表、能否被匹配到

  • 错误码被匹配到

  • 使用兜底的转换器。SQLExceptionSubclassTranslator是缺省的兜底转换器。如果此转换器也不存在的话只能使用SQLStateSQLExceptionTranslator

你可以继承SQLErrorCodeSQLExceptionTranslator:

  1. public class CustomSQLErrorCodesTranslator extends SQLErrorCodeSQLExceptionTranslator {
  2. protected DataAccessException customTranslate(String task, String sql, SQLException sqlex) {
  3. if (sqlex.getErrorCode() == -12345) {
  4. return new DeadlockLoserDataAccessException(task, sqlex);
  5. }
  6. return null;
  7. }
  8. }

这个例子中,特定的错误码-12345被识别后单独转换,而其他的错误码则通过默认的转换器实现来处理。在使用自定义转换器时,有必要通过setExceptionTranslator方法传入JdbcTemplate ,并且使用JdbcTemplate来做所有的数据访问处理。下面是一个如何使用自定义转换器的例子

  1. private JdbcTemplate jdbcTemplate;
  2. public void setDataSource(DataSource dataSource) {
  3. // create a JdbcTemplate and set data source
  4. this.jdbcTemplate = new JdbcTemplate();
  5. this.jdbcTemplate.setDataSource(dataSource);
  6. // create a custom translator and set the DataSource for the default translation lookup
  7. CustomSQLErrorCodesTranslator tr = new CustomSQLErrorCodesTranslator();
  8. tr.setDataSource(dataSource);
  9. this.jdbcTemplate.setExceptionTranslator(tr);
  10. }
  11. public void updateShippingCharge(long orderId, long pct) {
  12. // use the prepared JdbcTemplate for this update
  13. this.jdbcTemplate.update("update orders" +
  14. " set shipping_charge = shipping_charge * ? / 100" +
  15. " where id = ?", pct, orderId);
  16. }

自定义转换器需要传入dataSource对象为了能够获取sql-error-codes.xml定义的错误码

15.2.4 执行SQL语句

执行一条SQL语句非常方便。你只需要依赖DataSource和JdbcTemplate,包括JdbcTemplate提供的工具方法。
下面的例子展示了如何创建一个新的数据表,虽然只有几行代码、但已经完全可用了:

  1. import javax.sql.DataSource;
  2. import org.springframework.jdbc.core.JdbcTemplate;
  3. public class ExecuteAStatement {
  4. private JdbcTemplate jdbcTemplate;
  5. public void setDataSource(DataSource dataSource) {
  6. this.jdbcTemplate = new JdbcTemplate(dataSource);
  7. }
  8. public void doExecute() {
  9. this.jdbcTemplate.execute("create table mytable (id integer, name varchar(100))");
  10. }
  11. }

15.2.5 查询

一些查询方法会返回一个单一的结果。使用queryForObject(..)返回结果计数或特定值。当返回特定值类型时,将Java类型作为方法参数传入、最终返回的JDBC类型会被转换成相应的Java类型。如果这个过程中间出现类型转换错误,则会抛出InvalidDataAccessApiUsageException的异常。下面的例子包含两个查询方法,一个返回int类型、另一个返回了String类型。

  1. import javax.sql.DataSource;
  2. import org.springframework.jdbc.core.JdbcTemplate;
  3. public class RunAQuery {
  4. private JdbcTemplate jdbcTemplate;
  5. public void setDataSource(DataSource dataSource) {
  6. this.jdbcTemplate = new JdbcTemplate(dataSource);
  7. }
  8. public int getCount() {
  9. return this.jdbcTemplate.queryForObject("select count(*) from mytable", Integer.class);
  10. }
  11. public String getName() {
  12. return this.jdbcTemplate.queryForObject("select name from mytable", String.class);
  13. }
  14. }

除了返回单一查询结果的方法外,其他方法返回一个列表、列表中每一项代表查询返回的行记录。其中最通用的方式是queryForList(..),返回一个列表,列表每一项是一个Map类型,包含数据库对应行每一列的具体值。下面的代码块给上面的例子添加一个返回所有行的方法:

  1. private JdbcTemplate jdbcTemplate;
  2. public void setDataSource(DataSource dataSource) {
  3. this.jdbcTemplate = new JdbcTemplate(dataSource);
  4. }
  5. public List<Map<String, Object>> getList() {
  6. return this.jdbcTemplate.queryForList("select * from mytable");
  7. }

返回的列表结果数据格式是这样的:

  1. [{name=Bob, id=1}, {name=Mary, id=2}]

15.2.6 更新数据库

下面的例子根据主键更新其中一列值。在这个例子中,一条SQL语句包含行参数的占位符。参数值可以通过可变参数或者对象数组传入。元数据类型需要显式或者自动装箱成对应的包装类型

  1. import javax.sql.DataSource;
  2. import org.springframework.jdbc.core.JdbcTemplate;
  3. public class ExecuteAnUpdate {
  4. private JdbcTemplate jdbcTemplate;
  5. public void setDataSource(DataSource dataSource) {
  6. this.jdbcTemplate = new JdbcTemplate(dataSource);
  7. }
  8. public void setName(int id, String name) {
  9. this.jdbcTemplate.update("update mytable set name = ? where id = ?", name, id);
  10. }
  11. }

15.2.7 获取自增Key

update()方法支持获取数据库自增Key。这个支持已成为JDBC3.0标准之一、更多细节详见13.6章。这个方法使用PreparedStatementCreator作为其第一个入参,该类可以指定所需的insert语句。另外一个参数是KeyHolder,包含了更新操作成功之后产生的自增Key。这不是标准的创建PreparedStatement 的方式。下面的例子可以在Oracle上面运行,但在其他平台上可能就不行了。

  1. final String INSERT_SQL = "insert into my_test (name) values(?)";
  2. final String name = "Rob";
  3. KeyHolder keyHolder = new GeneratedKeyHolder();
  4. jdbcTemplate.update(
  5. new PreparedStatementCreator() {
  6. public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
  7. PreparedStatement ps = connection.prepareStatement(INSERT_SQL, new String[] {"id"});
  8. ps.setString(1, name);
  9. return ps;
  10. }
  11. },
  12. keyHolder);
  13. // keyHolder.getKey() now contains the generated key