Data Access

Global transactions let you work with multiple transactional resources, typically relational databases and message queues. The application server manages global transactions through the JTA, which is a cumbersome API (partly due to its exception model). Furthermore, a JTA UserTransaction normally needs to be sourced from JNDI, meaning that you also need to use JNDI in order to use JTA. The use of global transactions limits any potential reuse of application code, as JTA is normally only available in an application server environment.

Previously, the preferred way to use global transactions was through EJB CMT (Container Managed Transaction). CMT is a form of declarative transaction management (as distinguished from programmatic transaction management). EJB CMT removes the need for transaction-related JNDI lookups, although the use of EJB itself necessitates the use of JNDI. It removes most but not all of the need to write Java code to control transactions. The significant downside is that CMT is tied to JTA and an application server environment. Also, it is only available if one chooses to implement business logic in EJBs (or at least behind a transactional EJB facade). The negatives of EJB in general are so great that this is not an attractive proposition, especially in the face of compelling alternatives for declarative transaction management.

Local transactions are resource-specific, such as a transaction associated with a JDBC connection. Local transactions may be easier to use but have a significant disadvantage: They cannot work across multiple transactional resources. For example, code that manages transactions by using a JDBC connection cannot run within a global JTA transaction. Because the application server is not involved in transaction management, it cannot help ensure correctness across multiple resources. (It is worth noting that most applications use a single transaction resource.) Another downside is that local transactions are invasive to the programming model.

Spring resolves the disadvantages of global and local transactions. It lets application developers use a consistent programming model in any environment. You write your code once, and it can benefit from different transaction management strategies in different environments. The Spring Framework provides both declarative and programmatic transaction management. Most users prefer declarative transaction management, which we recommend in most cases.

With programmatic transaction management, developers work with the Spring Framework transaction abstraction, which can run over any underlying transaction infrastructure. With the preferred declarative model, developers typically write little or no code related to transaction management and, hence, do not depend on the Spring Framework transaction API or any other transaction API.

The preferred approach is to use Spring’s highest-level template based persistence integration APIs or to use native ORM APIs with transaction-aware factory beans or proxies for managing the native resource factories. These transaction-aware solutions internally handle resource creation and reuse, cleanup, optional transaction synchronization of the resources, and exception mapping. Thus, user data access code does not have to address these tasks but can focus purely on non-boilerplate persistence logic. Generally, you use the native ORM API or take a template approach for JDBC access by using the JdbcTemplate. These solutions are detailed in subsequent sections of this reference documentation.

Classes such as DataSourceUtils (for JDBC), EntityManagerFactoryUtils (for JPA), SessionFactoryUtils (for Hibernate), and so on exist at a lower level. When you want the application code to deal directly with the resource types of the native persistence APIs, you use these classes to ensure that proper Spring Framework-managed instances are obtained, transactions are (optionally) synchronized, and exceptions that occur in the process are properly mapped to a consistent API.

For example, in the case of JDBC, instead of the traditional JDBC approach of calling the getConnection() method on the DataSource, you can instead use Spring’s org.springframework.jdbc.datasource.DataSourceUtils class, as follows:

If an existing transaction already has a connection synchronized (linked) to it, that instance is returned. Otherwise, the method call triggers the creation of a new connection, which is (optionally) synchronized to any existing transaction and made available for subsequent reuse in that same transaction. As mentioned earlier, any SQLException is wrapped in a Spring Framework CannotGetJdbcConnectionException, one of the Spring Framework’s hierarchy of unchecked DataAccessException types. This approach gives you more information than can be obtained easily from the SQLException and ensures portability across databases and even across different persistence technologies.

This approach also works without Spring transaction management (transaction synchronization is optional), so you can use it whether or not you use Spring for transaction management.

Of course, once you have used Spring’s JDBC support, JPA support, or Hibernate support, you generally prefer not to use DataSourceUtils or the other helper classes, because you are much happier working through the Spring abstraction than directly with the relevant APIs. For example, if you use the Spring JdbcTemplate or jdbc.object package to simplify your use of JDBC, correct connection retrieval occurs behind the scenes and you need not write any special code.

At the very lowest level exists the TransactionAwareDataSourceProxy class. This is a proxy for a target DataSource, which wraps the target DataSource to add awareness of Spring-managed transactions. In this respect, it is similar to a transactional JNDI DataSource, as provided by a Java EE server.

You should almost never need or want to use this class, except when existing code must be called and passed a standard JDBC DataSource interface implementation. In that case, it is possible that this code is usable but is participating in Spring-managed transactions. You can write your new code by using the higher-level abstractions mentioned earlier.

It is not sufficient merely to tell you to annotate your classes with the @Transactional annotation, add @EnableTransactionManagement to your configuration, and expect you to understand how it all works. To provide a deeper understanding, this section explains the inner workings of the Spring Framework’s declarative transaction infrastructure in the context of transaction-related issues.

The most important concepts to grasp with regard to the Spring Framework’s declarative transaction support are that this support is enabled via AOP proxies and that the transactional advice is driven by metadata (currently XML- or annotation-based). The combination of AOP with transactional metadata yields an AOP proxy that uses a TransactionInterceptor in conjunction with an appropriate TransactionManager implementation to drive transactions around method invocations.

Spring Frameworks’s TransactionInterceptor provides transaction management for imperative and reactive programming models. The interceptor detects the desired flavor of transaction management by inspecting the method return type. Methods returning a reactive type such as Publisher or Kotlin Flow (or a subtype of those) qualify for reactive transaction management. All other return types including void use the code path for imperative transaction management.

Transaction management flavors impact which transaction manager is required. Imperative transactions require a PlatformTransactionManager, while reactive transactions use ReactiveTransactionManager implementations.

The following image shows a conceptual view of calling a method on a transactional proxy:

Consider the following interface and its attendant implementation. This example uses Foo and Bar classes as placeholders so that you can concentrate on the transaction usage without focusing on a particular domain model. For the purposes of this example, the fact that the DefaultFooService class throws UnsupportedOperationException instances in the body of each implemented method is good. That behavior lets you see transactions being created and then rolled back in response to the UnsupportedOperationException instance. The following listing shows the FooService interface:

The following example shows an implementation of the preceding interface:

Assume that the first two methods of the FooService interface, getFoo(String) and getFoo(String, String), must run in the context of a transaction with read-only semantics and that the other methods, insertFoo(Foo) and updateFoo(Foo), must run in the context of a transaction with read-write semantics. The following configuration is explained in detail in the next few paragraphs:

Examine the preceding configuration. It assumes that you want to make a service object, the fooService bean, transactional. The transaction semantics to apply are encapsulated in the <tx:advice/> definition. The <tx:advice/> definition reads as "all methods starting with get are to run in the context of a read-only transaction, and all other methods are to run with the default transaction semantics". The transaction-manager attribute of the <tx:advice/> tag is set to the name of the TransactionManager bean that is going to drive the transactions (in this case, the txManager bean).

The <aop:config/> definition ensures that the transactional advice defined by the txAdvice bean runs at the appropriate points in the program. First, you define a pointcut that matches the execution of any operation defined in the FooService interface (fooServiceOperation). Then you associate the pointcut with the txAdvice by using an advisor. The result indicates that, at the execution of a fooServiceOperation, the advice defined by txAdvice is run.

The expression defined within the <aop:pointcut/> element is an AspectJ pointcut expression. See the AOP section for more details on pointcut expressions in Spring.

A common requirement is to make an entire service layer transactional. The best way to do this is to change the pointcut expression to match any operation in your service layer. The following example shows how to do so:

Now that we have analyzed the configuration, you may be asking yourself, "What does all this configuration actually do?"

The configuration shown earlier is used to create a transactional proxy around the object that is created from the fooService bean definition. The proxy is configured with the transactional advice so that, when an appropriate method is invoked on the proxy, a transaction is started, suspended, marked as read-only, and so on, depending on the transaction configuration associated with that method. Consider the following program that test drives the configuration shown earlier:

The output from running the preceding program should resemble the following (the Log4J output and the stack trace from the UnsupportedOperationException thrown by the insertFoo(..) method of the DefaultFooService class have been truncated for clarity):

To use reactive transaction management the code has to use reactive types.

The following listing shows a modified version of the previously used FooService, but this time the code uses reactive types:

The following example shows an implementation of the preceding interface:

Imperative and reactive transaction management share the same semantics for transaction boundary and transaction attribute definitions. The main difference between imperative and reactive transactions is the deferred nature of the latter. TransactionInterceptor decorates the returned reactive type with a transactional operator to begin and clean up the transaction. Therefore, calling a transactional reactive method defers the actual transaction management to a subscription type that activates processing of the reactive type.

Another aspect of reactive transaction management relates to data escaping which is a natural consequence of the programming model.

Method return values of imperative transactions are returned from transactional methods upon successful termination of a method so that partially computed results do not escape the method closure.

Reactive transaction methods return a reactive wrapper type which represents a computation sequence along with a promise to begin and complete the computation.

A Publisher can emit data while a transaction is ongoing but not necessarily completed. Therefore, methods that depend upon successful completion of an entire transaction need to ensure completion and buffer results in the calling code.

The previous section outlined the basics of how to specify transactional settings for classes, typically service layer classes, declaratively in your application. This section describes how you can control the rollback of transactions in a simple, declarative fashion.

The recommended way to indicate to the Spring Framework’s transaction infrastructure that a transaction’s work is to be rolled back is to throw an Exception from code that is currently executing in the context of a transaction. The Spring Framework’s transaction infrastructure code catches any unhandled Exception as it bubbles up the call stack and makes a determination whether to mark the transaction for rollback.

In its default configuration, the Spring Framework’s transaction infrastructure code marks a transaction for rollback only in the case of runtime, unchecked exceptions. That is, when the thrown exception is an instance or subclass of RuntimeException. ( Error instances also, by default, result in a rollback). Checked exceptions that are thrown from a transactional method do not result in rollback in the default configuration.

You can configure exactly which Exception types mark a transaction for rollback, including checked exceptions. The following XML snippet demonstrates how you configure rollback for a checked, application-specific Exception type:

If you do not want a transaction rolled back when an exception is thrown, you can also specify 'no rollback rules'. The following example tells the Spring Framework’s transaction infrastructure to commit the attendant transaction even in the face of an unhandled InstrumentNotFoundException:

When the Spring Framework’s transaction infrastructure catches an exception and it consults the configured rollback rules to determine whether to mark the transaction for rollback, the strongest matching rule wins. So, in the case of the following configuration, any exception other than an InstrumentNotFoundException results in a rollback of the attendant transaction:

You can also indicate a required rollback programmatically. Although simple, this process is quite invasive and tightly couples your code to the Spring Framework’s transaction infrastructure. The following example shows how to programmatically indicate a required rollback:

You are strongly encouraged to use the declarative approach to rollback, if at all possible. Programmatic rollback is available should you absolutely need it, but its usage flies in the face of achieving a clean POJO-based architecture.

Consider the scenario where you have a number of service layer objects, and you want to apply a totally different transactional configuration to each of them. You can do so by defining distinct <aop:advisor/> elements with differing pointcut and advice-ref attribute values.

As a point of comparison, first assume that all of your service layer classes are defined in a root x.y.service package. To make all beans that are instances of classes defined in that package (or in subpackages) and that have names ending in Service have the default transactional configuration, you could write the following:

The following example shows how to configure two distinct beans with totally different transactional settings:

This section summarizes the various transactional settings that you can specify by using the <tx:advice/> tag. The default <tx:advice/> settings are:

You can change these default settings. The following table summarizes the various attributes of the <tx:method/> tags that are nested within <tx:advice/> and <tx:attributes/> tags:

In addition to the XML-based declarative approach to transaction configuration, you can use an annotation-based approach. Declaring transaction semantics directly in the Java source code puts the declarations much closer to the affected code. There is not much danger of undue coupling, because code that is meant to be used transactionally is almost always deployed that way anyway.

The ease-of-use afforded by the use of the @Transactional annotation is best illustrated with an example, which is explained in the text that follows. Consider the following class definition:

Used at the class level as above, the annotation indicates a default for all methods of the declaring class (as well as its subclasses). Alternatively, each method can get annotated individually. Note that a class-level annotation does not apply to ancestor classes up the class hierarchy; in such a scenario, methods need to be locally redeclared in order to participate in a subclass-level annotation.

When a POJO class such as the one above is defined as a bean in a Spring context, you can make the bean instance transactional through an @EnableTransactionManagement annotation in a @Configuration class. See the javadoc for full details.

In XML configuration, the <tx:annotation-driven/> tag provides similar convenience:

Reactive transactional methods use reactive return types in contrast to imperative programming arrangements as the following listing shows:

Note that there are special considerations for the returned Publisher with regards to Reactive Streams cancellation signals. See the Cancel Signals section under "Using the TransactionOperator" for more details.

You can apply the @Transactional annotation to an interface definition, a method on an interface, a class definition, or a public method on a class. However, the mere presence of the @Transactional annotation is not enough to activate the transactional behavior. The @Transactional annotation is merely metadata that can be consumed by some runtime infrastructure that is @Transactional-aware and that can use the metadata to configure the appropriate beans with transactional behavior. In the preceding example, the <tx:annotation-driven/> element switches on the transactional behavior.

Consider using of AspectJ mode (see the mode attribute in the following table) if you expect self-invocations to be wrapped with transactions as well. In this case, there no proxy in the first place. Instead, the target class is woven (that is, its byte code is modified) to turn @Transactional into runtime behavior on any kind of method.

The most derived location takes precedence when evaluating the transactional settings for a method. In the case of the following example, the DefaultFooService class is annotated at the class level with the settings for a read-only transaction, but the @Transactional annotation on the updateFoo(Foo) method in the same class takes precedence over the transactional settings defined at the class level.

This section describes some semantics of transaction propagation in Spring. Note that this section is not an introduction to transaction propagation proper. Rather, it details some of the semantics regarding transaction propagation in Spring.

In Spring-managed transactions, be aware of the difference between physical and logical transactions, and how the propagation setting applies to this difference.

Suppose you want to run both transactional operations and some basic profiling advice. How do you effect this in the context of <tx:annotation-driven/>?

When you invoke the updateFoo(Foo) method, you want to see the following actions:

The following code shows the simple profiling aspect discussed earlier:

The ordering of advice is controlled through the Ordered interface. For full details on advice ordering, see Advice ordering.

The following configuration creates a fooService bean that has profiling and transactional aspects applied to it in the desired order:

You can configure any number of additional aspects in similar fashion.

The following example creates the same setup as the previous two examples but uses the purely XML declarative approach:

The result of the preceding configuration is a fooService bean that has profiling and transactional aspects applied to it in that order. If you want the profiling advice to run after the transactional advice on the way in and before the transactional advice on the way out, you can swap the value of the profiling aspect bean’s order property so that it is higher than the transactional advice’s order value.

You can configure additional aspects in similar fashion.

You can also use the Spring Framework’s @Transactional support outside of a Spring container by means of an AspectJ aspect. To do so, first annotate your classes (and optionally your classes' methods) with the @Transactional annotation, and then link (weave) your application with the org.springframework.transaction.aspectj.AnnotationTransactionAspect defined in the spring-aspects.jar file. You must also configure The aspect with a transaction manager. You can use the Spring Framework’s IoC container to take care of dependency-injecting the aspect. The simplest way to configure the transaction management aspect is to use the <tx:annotation-driven/> element and specify the mode attribute to aspectj as described in Using @Transactional. Because we focus here on applications that run outside of a Spring container, we show you how to do it programmatically.

The following example shows how to create a transaction manager and configure the AnnotationTransactionAspect to use it:

The @Transactional annotation on a class specifies the default transaction semantics for the execution of any public method in the class.

The @Transactional annotation on a method within the class overrides the default transaction semantics given by the class annotation (if present). You can annotate any method, regardless of visibility.

To weave your applications with the AnnotationTransactionAspect, you must either build your application with AspectJ (see the AspectJ Development Guide) or use load-time weaving. See Load-time weaving with AspectJ in the Spring Framework for a discussion of load-time weaving with AspectJ.

The TransactionTemplate adopts the same approach as other Spring templates, such as the JdbcTemplate. It uses a callback approach (to free application code from having to do the boilerplate acquisition and release transactional resources) and results in code that is intention driven, in that your code focuses solely on what you want to do.

Application code that must run in a transactional context and that explicitly uses the TransactionTemplate resembles the next example. You, as an application developer, can write a TransactionCallback implementation (typically expressed as an anonymous inner class) that contains the code that you need to run in the context of a transaction. You can then pass an instance of your custom TransactionCallback to the execute(..) method exposed on the TransactionTemplate. The following example shows how to do so:

If there is no return value, you can use the convenient TransactionCallbackWithoutResult class with an anonymous class, as follows:

Code within the callback can roll the transaction back by calling the setRollbackOnly() method on the supplied TransactionStatus object, as follows:

The TransactionOperator follows an operator design that is similar to other reactive operators. It uses a callback approach (to free application code from having to do the boilerplate acquisition and release transactional resources) and results in code that is intention driven, in that your code focuses solely on what you want to do.

Application code that must run in a transactional context and that explicitly uses the TransactionOperator resembles the next example:

TransactionalOperator can be used in two ways:

Code within the callback can roll the transaction back by calling the setRollbackOnly() method on the supplied ReactiveTransaction object, as follows:

The following sections explain programmatic usage of imperative and reactive transaction managers.

On WebSphere 6.1.0.9 and above, the recommended Spring JTA transaction manager to use is WebSphereUowTransactionManager. This special adapter uses IBM’s UOWManager API, which is available in WebSphere Application Server 6.1.0.9 and later. With this adapter, Spring-driven transaction suspension (suspend and resume as initiated by PROPAGATION_REQUIRES_NEW) is officially supported by IBM.

On WebLogic Server 9.0 or above, you would typically use the WebLogicJtaTransactionManager instead of the stock JtaTransactionManager class. This special WebLogic-specific subclass of the normal JtaTransactionManager supports the full power of Spring’s transaction definitions in a WebLogic-managed transaction environment, beyond standard JTA semantics. Features include transaction names, per-transaction isolation levels, and proper resuming of transactions in all cases.

Use the correct PlatformTransactionManager implementation based on your choice of transactional technologies and requirements. Used properly, the Spring Framework merely provides a straightforward and portable abstraction. If you use global transactions, you must use the org.springframework.transaction.jta.JtaTransactionManager class (or an application server-specific subclass of it) for all your transactional operations. Otherwise, the transaction infrastructure tries to perform local transactions on such resources as container DataSource instances. Such local transactions do not make sense, and a good application server treats them as errors.

JdbcTemplate is the central class in the JDBC core package. It handles the creation and release of resources, which helps you avoid common errors, such as forgetting to close the connection. It performs the basic tasks of the core JDBC workflow (such as statement creation and execution), leaving application code to provide SQL and extract results. The JdbcTemplate class:

When you use the JdbcTemplate for your code, you need only to implement callback interfaces, giving them a clearly defined contract. Given a Connection provided by the JdbcTemplate class, the PreparedStatementCreator callback interface creates a prepared statement, providing SQL and any necessary parameters. The same is true for the CallableStatementCreator interface, which creates callable statements. The RowCallbackHandler interface extracts values from each row of a ResultSet.

You can use JdbcTemplate within a DAO implementation through direct instantiation with a DataSource reference, or you can configure it in a Spring IoC container and give it to DAOs as a bean reference.

All SQL issued by this class is logged at the DEBUG level under the category corresponding to the fully qualified class name of the template instance (typically JdbcTemplate, but it may be different if you use a custom subclass of the JdbcTemplate class).

The following sections provide some examples of JdbcTemplate usage. These examples are not an exhaustive list of all of the functionality exposed by the JdbcTemplate. See the attendant javadoc for that.

The NamedParameterJdbcTemplate class adds support for programming JDBC statements by using named parameters, as opposed to programming JDBC statements using only classic placeholder ( '?') arguments. The NamedParameterJdbcTemplate class wraps a JdbcTemplate and delegates to the wrapped JdbcTemplate to do much of its work. This section describes only those areas of the NamedParameterJdbcTemplate class that differ from the JdbcTemplate itself — namely, programming JDBC statements by using named parameters. The following example shows how to use NamedParameterJdbcTemplate:

Notice the use of the named parameter notation in the value assigned to the sql variable and the corresponding value that is plugged into the namedParameters variable (of type MapSqlParameterSource).

Alternatively, you can pass along named parameters and their corresponding values to a NamedParameterJdbcTemplate instance by using the Map-based style.The remaining methods exposed by the NamedParameterJdbcOperations and implemented by the NamedParameterJdbcTemplate class follow a similar pattern and are not covered here.

The following example shows the use of the Map-based style:

One nice feature related to the NamedParameterJdbcTemplate (and existing in the same Java package) is the SqlParameterSource interface. You have already seen an example of an implementation of this interface in one of the previous code snippets (the MapSqlParameterSource class). An SqlParameterSource is a source of named parameter values to a NamedParameterJdbcTemplate. The MapSqlParameterSource class is a simple implementation that is an adapter around a java.util.Map, where the keys are the parameter names and the values are the parameter values.

Another SqlParameterSource implementation is the BeanPropertySqlParameterSource class. This class wraps an arbitrary JavaBean (that is, an instance of a class that adheres to the JavaBean conventions) and uses the properties of the wrapped JavaBean as the source of named parameter values.

The following example shows a typical JavaBean:

The following example uses a NamedParameterJdbcTemplate to return the count of the members of the class shown in the preceding example:

Remember that the NamedParameterJdbcTemplate class wraps a classic JdbcTemplate template. If you need access to the wrapped JdbcTemplate instance to access functionality that is present only in the JdbcTemplate class, you can use the getJdbcOperations() method to access the wrapped JdbcTemplate through the JdbcOperations interface.

See also JdbcTemplate Best Practices for guidelines on using the NamedParameterJdbcTemplate class in the context of an application.

SQLExceptionTranslator is an interface to be implemented by classes that can translate between SQLExceptions and Spring’s own org.springframework.dao.DataAccessException, which is agnostic in regard to data access strategy. Implementations can be generic (for example, using SQLState codes for JDBC) or proprietary (for example, using Oracle error codes) for greater precision.

SQLErrorCodeSQLExceptionTranslator is the implementation of SQLExceptionTranslator that is used by default. This implementation uses specific vendor codes. It is more precise than the SQLState implementation. The error code translations are based on codes held in a JavaBean type class called SQLErrorCodes. This class is created and populated by an SQLErrorCodesFactory, which (as the name suggests) is a factory for creating SQLErrorCodes based on the contents of a configuration file named sql-error-codes.xml. This file is populated with vendor codes and based on the DatabaseProductName taken from DatabaseMetaData. The codes for the actual database you are using are used.

The SQLErrorCodeSQLExceptionTranslator applies matching rules in the following sequence:

You can extend SQLErrorCodeSQLExceptionTranslator, as the following example shows:

In the preceding example, the specific error code (-12345) is translated, while other errors are left to be translated by the default translator implementation. To use this custom translator, you must pass it to the JdbcTemplate through the method setExceptionTranslator, and you must use this JdbcTemplate for all of the data access processing where this translator is needed. The following example shows how you can use this custom translator:

The custom translator is passed a data source in order to look up the error codes in sql-error-codes.xml.

Running an SQL statement requires very little code. You need a DataSource and a JdbcTemplate, including the convenience methods that are provided with the JdbcTemplate. The following example shows what you need to include for a minimal but fully functional class that creates a new table:

Some query methods return a single value. To retrieve a count or a specific value from one row, use queryForObject(..). The latter converts the returned JDBC Type to the Java class that is passed in as an argument. If the type conversion is invalid, an InvalidDataAccessApiUsageException is thrown. The following example contains two query methods, one for an int and one that queries for a String:

In addition to the single result query methods, several methods return a list with an entry for each row that the query returned. The most generic method is queryForList(..), which returns a List where each element is a Map containing one entry for each column, using the column name as the key. If you add a method to the preceding example to retrieve a list of all the rows, it might be as follows:

The returned list would resemble the following:

The following example updates a column for a certain primary key:

In the preceding example, an SQL statement has placeholders for row parameters. You can pass the parameter values in as varargs or ,alternatively, as an array of objects. Thus, you should explicitly wrap primitives in the primitive wrapper classes, or you should use auto-boxing.

An update() convenience method supports the retrieval of primary keys generated by the database. This support is part of the JDBC 3.0 standard. See Chapter 13.6 of the specification for details. The method takes a PreparedStatementCreator as its first argument, and this is the way the required insert statement is specified. The other argument is a KeyHolder, which contains the generated key on successful return from the update. There is no standard single way to create an appropriate PreparedStatement (which explains why the method signature is the way it is). The following example works on Oracle but may not work on other platforms:

Spring obtains a connection to the database through a DataSource. A DataSource is part of the JDBC specification and is a generalized connection factory. It lets a container or a framework hide connection pooling and transaction management issues from the application code. As a developer, you need not know details about how to connect to the database. That is the responsibility of the administrator who sets up the datasource. You most likely fill both roles as you develop and test code, but you do not necessarily have to know how the production data source is configured.

When you use Spring’s JDBC layer, you can obtain a data source from JNDI, or you can configure your own with a connection pool implementation provided by a third party. Traditional choices are Apache Commons DBCP and C3P0 with bean-style DataSource classes; for a modern JDBC connection pool, consider HikariCP with its builder-style API instead.

The following section uses Spring’s DriverManagerDataSource implementation. Several other DataSource variants are covered later.

To configure a DriverManagerDataSource:

The following example shows how to configure a DriverManagerDataSource in Java:

The following example shows the corresponding XML configuration:

The next two examples show the basic connectivity and configuration for DBCP and C3P0. To learn about more options that help control the pooling features, see the product documentation for the respective connection pooling implementations.

The following example shows DBCP configuration:

The following example shows C3P0 configuration:

The DataSourceUtils class is a convenient and powerful helper class that provides static methods to obtain connections from JNDI and close connections if necessary. It supports thread-bound connections with, for example, DataSourceTransactionManager.

The SmartDataSource interface should be implemented by classes that can provide a connection to a relational database. It extends the DataSource interface to let classes that use it query whether the connection should be closed after a given operation. This usage is efficient when you know that you need to reuse a connection.

AbstractDataSource is an abstract base class for Spring’s DataSource implementations. It implements code that is common to all DataSource implementations. You should extend the AbstractDataSource class if you write your own DataSource implementation.

The SingleConnectionDataSource class is an implementation of the SmartDataSource interface that wraps a single Connection that is not closed after each use. This is not multi-threading capable.

If any client code calls close on the assumption of a pooled connection (as when using persistence tools), you should set the suppressClose property to true. This setting returns a close-suppressing proxy that wraps the physical connection. Note that you can no longer cast this to a native Oracle Connection or a similar object.

SingleConnectionDataSource is primarily a test class. For example, it enables easy testing of code outside an application server, in conjunction with a simple JNDI environment. In contrast to DriverManagerDataSource, it reuses the same connection all the time, avoiding excessive creation of physical connections.

The DriverManagerDataSource class is an implementation of the standard DataSource interface that configures a plain JDBC driver through bean properties and returns a new Connection every time.

This implementation is useful for test and stand-alone environments outside of a Java EE container, either as a DataSource bean in a Spring IoC container or in conjunction with a simple JNDI environment. Pool-assuming Connection.close() calls close the connection, so any DataSource-aware persistence code should work. However, using JavaBean-style connection pools (such as commons-dbcp) is so easy, even in a test environment, that it is almost always preferable to use such a connection pool over DriverManagerDataSource.

TransactionAwareDataSourceProxy is a proxy for a target DataSource. The proxy wraps that target DataSource to add awareness of Spring-managed transactions. In this respect, it is similar to a transactional JNDI DataSource, as provided by a Java EE server.

See the TransactionAwareDataSourceProxy javadoc for more details.

The DataSourceTransactionManager class is a PlatformTransactionManager implementation for single JDBC datasources. It binds a JDBC connection from the specified data source to the currently executing thread, potentially allowing for one thread connection per data source.

Application code is required to retrieve the JDBC connection through DataSourceUtils.getConnection(DataSource) instead of Java EE’s standard DataSource.getConnection. It throws unchecked org.springframework.dao exceptions instead of checked SQLExceptions. All framework classes (such as JdbcTemplate) use this strategy implicitly. If not used with this transaction manager, the lookup strategy behaves exactly like the common one. Thus, it can be used in any case.

The DataSourceTransactionManager class supports custom isolation levels and timeouts that get applied as appropriate JDBC statement query timeouts. To support the latter, application code must either use JdbcTemplate or call the DataSourceUtils.applyTransactionTimeout(..) method for each created statement.

You can use this implementation instead of JtaTransactionManager in the single-resource case, as it does not require the container to support JTA. Switching between both is just a matter of configuration, provided you stick to the required connection lookup pattern. JTA does not support custom isolation levels.

You accomplish JdbcTemplate batch processing by implementing two methods of a special interface, BatchPreparedStatementSetter, and passing that implementation in as the second parameter in your batchUpdate method call. You can use the getBatchSize method to provide the size of the current batch. You can use the setValues method to set the values for the parameters of the prepared statement. This method is called the number of times that you specified in the getBatchSize call. The following example updates the t_actor table based on entries in a list, and the entire list is used as the batch:

If you process a stream of updates or reading from a file, you might have a preferred batch size, but the last batch might not have that number of entries. In this case, you can use the InterruptibleBatchPreparedStatementSetter interface, which lets you interrupt a batch once the input source is exhausted. The isBatchExhausted method lets you signal the end of the batch.

Both the JdbcTemplate and the NamedParameterJdbcTemplate provides an alternate way of providing the batch update. Instead of implementing a special batch interface, you provide all parameter values in the call as a list. The framework loops over these values and uses an internal prepared statement setter. The API varies, depending on whether you use named parameters. For the named parameters, you provide an array of SqlParameterSource, one entry for each member of the batch. You can use the SqlParameterSourceUtils.createBatch convenience methods to create this array, passing in an array of bean-style objects (with getter methods corresponding to parameters), String-keyed Map instances (containing the corresponding parameters as values), or a mix of both.

The following example shows a batch update using named parameters:

For an SQL statement that uses the classic ? placeholders, you pass in a list containing an object array with the update values. This object array must have one entry for each placeholder in the SQL statement, and they must be in the same order as they are defined in the SQL statement.

The following example is the same as the preceding example, except that it uses classic JDBC ? placeholders:

All of the batch update methods that we described earlier return an int array containing the number of affected rows for each batch entry. This count is reported by the JDBC driver. If the count is not available, the JDBC driver returns a value of -2.

The preceding example of a batch update deals with batches that are so large that you want to break them up into several smaller batches. You can do this with the methods mentioned earlier by making multiple calls to the batchUpdate method, but there is now a more convenient method. This method takes, in addition to the SQL statement, a Collection of objects that contain the parameters, the number of updates to make for each batch, and a ParameterizedPreparedStatementSetter to set the values for the parameters of the prepared statement. The framework loops over the provided values and breaks the update calls into batches of the size specified.

The following example shows a batch update that uses a batch size of 100:

The batch update methods for this call returns an array of int arrays that contains an array entry for each batch with an array of the number of affected rows for each update. The top-level array’s length indicates the number of batches run, and the second level array’s length indicates the number of updates in that batch. The number of updates in each batch should be the batch size provided for all batches (except that the last one that might be less), depending on the total number of update objects provided. The update count for each update statement is the one reported by the JDBC driver. If the count is not available, the JDBC driver returns a value of -2.

We start by looking at the SimpleJdbcInsert class with the minimal amount of configuration options. You should instantiate the SimpleJdbcInsert in the data access layer’s initialization method. For this example, the initializing method is the setDataSource method. You do not need to subclass the SimpleJdbcInsert class. Instead, you can create a new instance and set the table name by using the withTableName method. Configuration methods for this class follow the fluid style that returns the instance of the SimpleJdbcInsert, which lets you chain all configuration methods. The following example uses only one configuration method (we show examples of multiple methods later):

The execute method used here takes a plain java.util.Map as its only parameter. The important thing to note here is that the keys used for the Map must match the column names of the table, as defined in the database. This is because we read the metadata to construct the actual insert statement.

The next example uses the same insert as the preceding example, but, instead of passing in the id, it retrieves the auto-generated key and sets it on the new Actor object. When it creates the SimpleJdbcInsert, in addition to specifying the table name, it specifies the name of the generated key column with the usingGeneratedKeyColumns method. The following listing shows how it works:

The main difference when you run the insert by using this second approach is that you do not add the id to the Map, and you call the executeAndReturnKey method. This returns a java.lang.Number object with which you can create an instance of the numerical type that is used in your domain class. You cannot rely on all databases to return a specific Java class here. java.lang.Number is the base class that you can rely on. If you have multiple auto-generated columns or the generated values are non-numeric, you can use a KeyHolder that is returned from the executeAndReturnKeyHolder method.

You can limit the columns for an insert by specifying a list of column names with the usingColumns method, as the following example shows:

The execution of the insert is the same as if you had relied on the metadata to determine which columns to use.

Using a Map to provide parameter values works fine, but it is not the most convenient class to use. Spring provides a couple of implementations of the SqlParameterSource interface that you can use instead. The first one is BeanPropertySqlParameterSource, which is a very convenient class if you have a JavaBean-compliant class that contains your values. It uses the corresponding getter method to extract the parameter values. The following example shows how to use BeanPropertySqlParameterSource:

Another option is the MapSqlParameterSource that resembles a Map but provides a more convenient addValue method that can be chained. The following example shows how to use it:

As you can see, the configuration is the same. Only the executing code has to change to use these alternative input classes.

The SimpleJdbcCall class uses metadata in the database to look up names of in and out parameters so that you do not have to explicitly declare them. You can declare parameters if you prefer to do that or if you have parameters (such as ARRAY or STRUCT) that do not have an automatic mapping to a Java class. The first example shows a simple procedure that returns only scalar values in VARCHAR and DATE format from a MySQL database. The example procedure reads a specified actor entry and returns first_name, last_name, and birth_date columns in the form of out parameters. The following listing shows the first example:

The in_id parameter contains the id of the actor that you are looking up. The out parameters return the data read from the table.

You can declare SimpleJdbcCall in a manner similar to declaring SimpleJdbcInsert. You should instantiate and configure the class in the initialization method of your data-access layer. Compared to the StoredProcedure class, you need not create a subclass and you need not to declare parameters that can be looked up in the database metadata. The following example of a SimpleJdbcCall configuration uses the preceding stored procedure (the only configuration option, in addition to the DataSource, is the name of the stored procedure):

The code you write for the execution of the call involves creating an SqlParameterSource containing the IN parameter. You must match the name provided for the input value with that of the parameter name declared in the stored procedure. The case does not have to match because you use metadata to determine how database objects should be referred to in a stored procedure. What is specified in the source for the stored procedure is not necessarily the way it is stored in the database. Some databases transform names to all upper case, while others use lower case or use the case as specified.

The execute method takes the IN parameters and returns a Map that contains any out parameters keyed by the name, as specified in the stored procedure. In this case, they are out_first_name, out_last_name, and out_birth_date.

The last part of the execute method creates an Actor instance to use to return the data retrieved. Again, it is important to use the names of the out parameters as they are declared in the stored procedure. Also, the case in the names of the out parameters stored in the results map matches that of the out parameter names in the database, which could vary between databases. To make your code more portable, you should do a case-insensitive lookup or instruct Spring to use a LinkedCaseInsensitiveMap. To do the latter, you can create your own JdbcTemplate and set the setResultsMapCaseInsensitive property to true. Then you can pass this customized JdbcTemplate instance into the constructor of your SimpleJdbcCall. The following example shows this configuration:

By taking this action, you avoid conflicts in the case used for the names of your returned out parameters.

Earlier in this chapter, we described how parameters are deduced from metadata, but you can declare them explicitly if you wish. You can do so by creating and configuring SimpleJdbcCall with the declareParameters method, which takes a variable number of SqlParameter objects as input. See the next section for details on how to define an SqlParameter.

You can opt to explicitly declare one, some, or all of the parameters. The parameter metadata is still used where you do not explicitly declare parameters. To bypass all processing of metadata lookups for potential parameters and use only the declared parameters, you can call the method withoutProcedureColumnMetaDataAccess as part of the declaration. Suppose that you have two or more different call signatures declared for a database function. In this case, you call useInParameterNames to specify the list of IN parameter names to include for a given signature.

The following example shows a fully declared procedure call and uses the information from the preceding example:

The execution and end results of the two examples are the same. The second example specifies all details explicitly rather than relying on metadata.

To define a parameter for the SimpleJdbc classes and also for the RDBMS operations classes (covered in Modeling JDBC Operations as Java Objects) you can use SqlParameter or one of its subclasses. To do so, you typically specify the parameter name and SQL type in the constructor. The SQL type is specified by using the java.sql.Types constants. Earlier in this chapter, we saw declarations similar to the following:

The first line with the SqlParameter declares an IN parameter. You can use IN parameters for both stored procedure calls and for queries by using the SqlQuery and its subclasses (covered in Understanding SqlQuery).

The second line (with the SqlOutParameter) declares an out parameter to be used in a stored procedure call. There is also an SqlInOutParameter for InOut parameters (parameters that provide an IN value to the procedure and that also return a value).

For IN parameters, in addition to the name and the SQL type, you can specify a scale for numeric data or a type name for custom database types. For out parameters, you can provide a RowMapper to handle mapping of rows returned from a REF cursor. Another option is to specify an SqlReturnType that provides an opportunity to define customized handling of the return values.

You can call a stored function in almost the same way as you call a stored procedure, except that you provide a function name rather than a procedure name. You use the withFunctionName method as part of the configuration to indicate that you want to make a call to a function, and the corresponding string for a function call is generated. A specialized call (executeFunction) is used to run the function, and it returns the function return value as an object of a specified type, which means you do not have to retrieve the return value from the results map. A similar convenience method (named executeObject) is also available for stored procedures that have only one out parameter. The following example (for MySQL) is based on a stored function named get_actor_name that returns an actor’s full name:

To call this function, we again create a SimpleJdbcCall in the initialization method, as the following example shows:

The executeFunction method used returns a String that contains the return value from the function call.

Calling a stored procedure or function that returns a result set is a bit tricky. Some databases return result sets during the JDBC results processing, while others require an explicitly registered out parameter of a specific type. Both approaches need additional processing to loop over the result set and process the returned rows. With the SimpleJdbcCall, you can use the returningResultSet method and declare a RowMapper implementation to be used for a specific parameter. If the result set is returned during the results processing, there are no names defined, so the returned results must match the order in which you declare the RowMapper implementations. The name specified is still used to store the processed list of results in the results map that is returned from the execute statement.

The next example (for MySQL) uses a stored procedure that takes no IN parameters and returns all rows from the t_actor table:

To call this procedure, you can declare the RowMapper. Because the class to which you want to map follows the JavaBean rules, you can use a BeanPropertyRowMapper that is created by passing in the required class to map to in the newInstance method. The following example shows how to do so:

The execute call passes in an empty Map, because this call does not take any parameters. The list of actors is then retrieved from the results map and returned to the caller.

SqlQuery is a reusable, thread-safe class that encapsulates an SQL query. Subclasses must implement the newRowMapper(..) method to provide a RowMapper instance that can create one object per row obtained from iterating over the ResultSet that is created during the execution of the query. The SqlQuery class is rarely used directly, because the MappingSqlQuery subclass provides a much more convenient implementation for mapping rows to Java classes. Other implementations that extend SqlQuery are MappingSqlQueryWithParameters and UpdatableSqlQuery.

MappingSqlQuery is a reusable query in which concrete subclasses must implement the abstract mapRow(..) method to convert each row of the supplied ResultSet into an object of the type specified. The following example shows a custom query that maps the data from the t_actor relation to an instance of the Actor class:

The class extends MappingSqlQuery parameterized with the Actor type. The constructor for this customer query takes a DataSource as the only parameter. In this constructor, you can call the constructor on the superclass with the DataSource and the SQL that should be run to retrieve the rows for this query. This SQL is used to create a PreparedStatement, so it may contain placeholders for any parameters to be passed in during execution. You must declare each parameter by using the declareParameter method passing in an SqlParameter. The SqlParameter takes a name, and the JDBC type as defined in java.sql.Types. After you define all parameters, you can call the compile() method so that the statement can be prepared and later run. This class is thread-safe after it is compiled, so, as long as these instances are created when the DAO is initialized, they can be kept as instance variables and be reused. The following example shows how to define such a class:

The method in the preceding example retrieves the customer with the id that is passed in as the only parameter. Since we want only one object to be returned, we call the findObject convenience method with the id as the parameter. If we had instead a query that returned a list of objects and took additional parameters, we would use one of the execute methods that takes an array of parameter values passed in as varargs. The following example shows such a method:

The SqlUpdate class encapsulates an SQL update. As with a query, an update object is reusable, and, as with all RdbmsOperation classes, an update can have parameters and is defined in SQL. This class provides a number of update(..) methods analogous to the execute(..) methods of query objects. The SQLUpdate class is concrete. It can be subclassed — for example, to add a custom update method. However, you do not have to subclass the SqlUpdate class, since it can easily be parameterized by setting SQL and declaring parameters. The following example creates a custom update method named execute:

The StoredProcedure class is a superclass for object abstractions of RDBMS stored procedures. This class is abstract, and its various execute(..) methods have protected access, preventing use other than through a subclass that offers tighter typing.

The inherited sql property is the name of the stored procedure in the RDBMS.

To define a parameter for the StoredProcedure class, you can use an SqlParameter or one of its subclasses. You must specify the parameter name and SQL type in the constructor, as the following code snippet shows:

The SQL type is specified using the java.sql.Types constants.

The first line (with the SqlParameter) declares an IN parameter. You can use IN parameters both for stored procedure calls and for queries using the SqlQuery and its subclasses (covered in Understanding SqlQuery).

The second line (with the SqlOutParameter) declares an out parameter to be used in the stored procedure call. There is also an SqlInOutParameter for InOut parameters (parameters that provide an in value to the procedure and that also return a value).

For in parameters, in addition to the name and the SQL type, you can specify a scale for numeric data or a type name for custom database types. For out parameters, you can provide a RowMapper to handle mapping of rows returned from a REF cursor. Another option is to specify an SqlReturnType that lets you define customized handling of the return values.

The next example of a simple DAO uses a StoredProcedure to call a function (sysdate()), which comes with any Oracle database. To use the stored procedure functionality, you have to create a class that extends StoredProcedure. In this example, the StoredProcedure class is an inner class. However, if you need to reuse the StoredProcedure, you can declare it as a top-level class. This example has no input parameters, but an output parameter is declared as a date type by using the SqlOutParameter class. The execute() method runs the procedure and extracts the returned date from the results Map. The results Map has an entry for each declared output parameter (in this case, only one) by using the parameter name as the key. The following listing shows our custom StoredProcedure class:

The following example of a StoredProcedure has two output parameters (in this case, Oracle REF cursors):

Notice how the overloaded variants of the declareParameter(..) method that have been used in the TitlesAndGenresStoredProcedure constructor are passed RowMapper implementation instances. This is a very convenient and powerful way to reuse existing functionality. The next two examples provide code for the two RowMapper implementations.

The TitleMapper class maps a ResultSet to a Title domain object for each row in the supplied ResultSet, as follows:

The GenreMapper class maps a ResultSet to a Genre domain object for each row in the supplied ResultSet, as follows:

To pass parameters to a stored procedure that has one or more input parameters in its definition in the RDBMS, you can code a strongly typed execute(..) method that would delegate to the untyped execute(Map) method in the superclass, as the following example shows:

Usually, Spring determines the SQL type of the parameters based on the type of parameter passed in. It is possible to explicitly provide the SQL type to be used when setting parameter values. This is sometimes necessary to correctly set NULL values.

You can provide SQL type information in several ways:

You can store images, other binary data, and large chunks of text in the database. These large objects are called BLOBs (Binary Large OBject) for binary data and CLOBs (Character Large OBject) for character data. In Spring, you can handle these large objects by using the JdbcTemplate directly and also when using the higher abstractions provided by RDBMS Objects and the SimpleJdbc classes. All of these approaches use an implementation of the LobHandler interface for the actual management of the LOB (Large OBject) data. LobHandler provides access to a LobCreator class, through the getLobCreator method, that is used for creating new LOB objects to be inserted.

LobCreator and LobHandler provide the following support for LOB input and output:

The next example shows how to create and insert a BLOB. Later we show how to read it back from the database.

This example uses a JdbcTemplate and an implementation of the AbstractLobCreatingPreparedStatementCallback. It implements one method, setValues. This method provides a LobCreator that we use to set the values for the LOB columns in your SQL insert statement.

For this example, we assume that there is a variable, lobHandler, that is already set to an instance of a DefaultLobHandler. You typically set this value through dependency injection.

The following example shows how to create and insert a BLOB:

Now it is time to read the LOB data from the database. Again, you use a JdbcTemplate with the same instance variable lobHandler and a reference to a DefaultLobHandler. The following example shows how to do so:

The SQL standard allows for selecting rows based on an expression that includes a variable list of values. A typical example would be select * from T_ACTOR where id in (1, 2, 3). This variable list is not directly supported for prepared statements by the JDBC standard. You cannot declare a variable number of placeholders. You need a number of variations with the desired number of placeholders prepared, or you need to generate the SQL string dynamically once you know how many placeholders are required. The named parameter support provided in the NamedParameterJdbcTemplate and JdbcTemplate takes the latter approach. You can pass in the values as a java.util.List of primitive objects. This list is used to insert the required placeholders and pass in the values during statement execution.

In addition to the primitive values in the value list, you can create a java.util.List of object arrays. This list can support multiple expressions being defined for the in clause, such as select * from T_ACTOR where (id, last_name) in ((1, 'Johnson'), (2, 'Harrop'\)). This, of course, requires that your database supports this syntax.

When you call stored procedures, you can sometimes use complex types specific to the database. To accommodate these types, Spring provides a SqlReturnType for handling them when they are returned from the stored procedure call and SqlTypeValue when they are passed in as a parameter to the stored procedure.

The SqlReturnType interface has a single method (named getTypeValue) that must be implemented. This interface is used as part of the declaration of an SqlOutParameter. The following example shows returning the value of an Oracle STRUCT object of the user declared type ITEM_TYPE:

You can use SqlTypeValue to pass the value of a Java object (such as TestItem) to a stored procedure. The SqlTypeValue interface has a single method (named createTypeValue) that you must implement. The active connection is passed in, and you can use it to create database-specific objects, such as StructDescriptor instances or ArrayDescriptor instances. The following example creates a StructDescriptor instance:

You can now add this SqlTypeValue to the Map that contains the input parameters for the execute call of the stored procedure.

Another use for the SqlTypeValue is passing in an array of values to an Oracle stored procedure. Oracle has its own internal ARRAY class that must be used in this case, and you can use the SqlTypeValue to create an instance of the Oracle ARRAY and populate it with values from the Java ARRAY, as the following example shows:

An embedded database can be useful during the development phase of a project because of its lightweight nature. Benefits include ease of configuration, quick startup time, testability, and the ability to rapidly evolve your SQL during development.

If you want to expose an embedded database instance as a bean in a Spring ApplicationContext, you can use the embedded-database tag in the spring-jdbc namespace:

The preceding configuration creates an embedded HSQL database that is populated with SQL from the schema.sql and test-data.sql resources in the root of the classpath. In addition, as a best practice, the embedded database is assigned a uniquely generated name. The embedded database is made available to the Spring container as a bean of type javax.sql.DataSource that can then be injected into data access objects as needed.

The EmbeddedDatabaseBuilder class provides a fluent API for constructing an embedded database programmatically. You can use this when you need to create an embedded database in a stand-alone environment or in a stand-alone integration test, as in the following example:

See the javadoc for EmbeddedDatabaseBuilder for further details on all supported options.

You can also use the EmbeddedDatabaseBuilder to create an embedded database by using Java configuration, as the following example shows:

This section covers how to select one of the three embedded databases that Spring supports. It includes the following topics:

Embedded databases provide a lightweight way to test data access code. The next example is a data access integration test template that uses an embedded database. Using such a template can be useful for one-offs when the embedded database does not need to be reused across test classes. However, if you wish to create an embedded database that is shared within a test suite, consider using the Spring TestContext Framework and configuring the embedded database as a bean in the Spring ApplicationContext as described in Creating an Embedded Database by Using Spring XML and Creating an Embedded Database Programmatically. The following listing shows the test template:

Development teams often encounter errors with embedded databases if their test suite inadvertently attempts to recreate additional instances of the same database. This can happen quite easily if an XML configuration file or @Configuration class is responsible for creating an embedded database and the corresponding configuration is then reused across multiple testing scenarios within the same test suite (that is, within the same JVM process) — for example, integration tests against embedded databases whose ApplicationContext configuration differs only with regard to which bean definition profiles are active.

The root cause of such errors is the fact that Spring’s EmbeddedDatabaseFactory (used internally by both the <jdbc:embedded-database> XML namespace element and the EmbeddedDatabaseBuilder for Java configuration) sets the name of the embedded database to testdb if not otherwise specified. For the case of <jdbc:embedded-database>, the embedded database is typically assigned a name equal to the bean’s id (often, something like dataSource). Thus, subsequent attempts to create an embedded database do not result in a new database. Instead, the same JDBC connection URL is reused, and attempts to create a new embedded database actually point to an existing embedded database created from the same configuration.

To address this common issue, Spring Framework 4.2 provides support for generating unique names for embedded databases. To enable the use of generated names, use one of the following options.

You can extend Spring JDBC embedded database support in two ways:

We encourage you to contribute extensions to the Spring community at GitHub Issues.

If you want to initialize a database and you can provide a reference to a DataSource bean, you can use the initialize-database tag in the spring-jdbc namespace:

The preceding example runs the two specified scripts against the database. The first script creates a schema, and the second populates tables with a test data set. The script locations can also be patterns with wildcards in the usual Ant style used for resources in Spring (for example, classpath*:/com/foo/**/sql/*-data.sql). If you use a pattern, the scripts are run in the lexical order of their URL or filename.

The default behavior of the database initializer is to unconditionally run the provided scripts. This may not always be what you want — for instance, if you run the scripts against a database that already has test data in it. The likelihood of accidentally deleting data is reduced by following the common pattern (shown earlier) of creating the tables first and then inserting the data. The first step fails if the tables already exist.

However, to gain more control over the creation and deletion of existing data, the XML namespace provides a few additional options. The first is a flag to switch the initialization on and off. You can set this according to the environment (such as pulling a boolean value from system properties or from an environment bean). The following example gets a value from a system property:

The second option to control what happens with existing data is to be more tolerant of failures. To this end, you can control the ability of the initializer to ignore certain errors in the SQL it runs from the scripts, as the following example shows:

In the preceding example, we are saying that we expect that, sometimes, the scripts are run against an empty database, and there are some DROP statements in the scripts that would, therefore, fail. So failed SQL DROP statements will be ignored, but other failures will cause an exception. This is useful if your SQL dialect doesn’t support DROP …​ IF EXISTS (or similar) but you want to unconditionally remove all test data before re-creating it. In that case the first script is usually a set of DROP statements, followed by a set of CREATE statements.

The ignore-failures option can be set to NONE (the default), DROPS (ignore failed drops), or ALL (ignore all failures).

Each statement should be separated by ; or a new line if the ; character is not present at all in the script. You can control that globally or script by script, as the following example shows:

In this example, the two test-data scripts use @@ as statement separator and only the db-schema.sql uses ;. This configuration specifies that the default separator is @@ and overrides that default for the db-schema script.

If you need more control than you get from the XML namespace, you can use the DataSourceInitializer directly and define it as a component in your application.

Typical business applications are cluttered with repetitive resource management code. Many projects try to invent their own solutions, sometimes sacrificing proper handling of failures for programming convenience. Spring advocates simple solutions for proper resource handling, namely IoC through templating in the case of JDBC and applying AOP interceptors for the ORM technologies.

The infrastructure provides proper resource handling and appropriate conversion of specific API exceptions to an unchecked infrastructure exception hierarchy. Spring introduces a DAO exception hierarchy, applicable to any data access strategy. For direct JDBC, the JdbcTemplate class mentioned in a previous section provides connection handling and proper conversion of SQLException to the DataAccessException hierarchy, including translation of database-specific SQL error codes to meaningful exception classes. For ORM technologies, see the next section for how to get the same exception translation benefits.

When it comes to transaction management, the JdbcTemplate class hooks in to the Spring transaction support and supports both JTA and JDBC transactions, through respective Spring transaction managers. For the supported ORM technologies, Spring offers Hibernate and JPA support through the Hibernate and JPA transaction managers as well as JTA support. For details on transaction support, see the Transaction Management chapter.

When you use Hibernate or JPA in a DAO, you must decide how to handle the persistence technology’s native exception classes. The DAO throws a subclass of a HibernateException or PersistenceException, depending on the technology. These exceptions are all runtime exceptions and do not have to be declared or caught. You may also have to deal with IllegalArgumentException and IllegalStateException. This means that callers can only treat exceptions as being generally fatal, unless they want to depend on the persistence technology’s own exception structure. Catching specific causes (such as an optimistic locking failure) is not possible without tying the caller to the implementation strategy. This trade-off might be acceptable to applications that are strongly ORM-based or do not need any special exception treatment (or both). However, Spring lets exception translation be applied transparently through the @Repository annotation. The following examples (one for Java configuration and one for XML configuration) show how to do so:

The postprocessor automatically looks for all exception translators (implementations of the PersistenceExceptionTranslator interface) and advises all beans marked with the @Repository annotation so that the discovered translators can intercept and apply the appropriate translation on the thrown exceptions.

In summary, you can implement DAOs based on the plain persistence technology’s API and annotations while still benefiting from Spring-managed transactions, dependency injection, and transparent exception conversion (if desired) to Spring’s custom exception hierarchies.

To avoid tying application objects to hard-coded resource lookups, you can define resources (such as a JDBC DataSource or a Hibernate SessionFactory) as beans in the Spring container. Application objects that need to access resources receive references to such predefined instances through bean references, as illustrated in the DAO definition in the next section.

The following excerpt from an XML application context definition shows how to set up a JDBC DataSource and a Hibernate SessionFactory on top of it:

Switching from a local Jakarta Commons DBCP BasicDataSource to a JNDI-located DataSource (usually managed by an application server) is only a matter of configuration, as the following example shows:

You can also access a JNDI-located SessionFactory, using Spring’s JndiObjectFactoryBean / <jee:jndi-lookup> to retrieve and expose it. However, that is typically not common outside of an EJB context.

Hibernate has a feature called contextual sessions, wherein Hibernate itself manages one current Session per transaction. This is roughly equivalent to Spring’s synchronization of one Hibernate Session per transaction. A corresponding DAO implementation resembles the following example, based on the plain Hibernate API:

This style is similar to that of the Hibernate reference documentation and examples, except for holding the SessionFactory in an instance variable. We strongly recommend such an instance-based setup over the old-school static HibernateUtil class from Hibernate’s CaveatEmptor sample application. (In general, do not keep any resources in static variables unless absolutely necessary.)

The preceding DAO example follows the dependency injection pattern. It fits nicely into a Spring IoC container, as it would if coded against Spring’s HibernateTemplate. You can also set up such a DAO in plain Java (for example, in unit tests). To do so, instantiate it and call setSessionFactory(..) with the desired factory reference. As a Spring bean definition, the DAO would resemble the following:

The main advantage of this DAO style is that it depends on Hibernate API only. No import of any Spring class is required. This is appealing from a non-invasiveness perspective and may feel more natural to Hibernate developers.

However, the DAO throws plain HibernateException (which is unchecked, so it does not have to be declared or caught), which means that callers can treat exceptions only as being generally fatal — unless they want to depend on Hibernate’s own exception hierarchy. Catching specific causes (such as an optimistic locking failure) is not possible without tying the caller to the implementation strategy. This trade off might be acceptable to applications that are strongly Hibernate-based, do not need any special exception treatment, or both.

Fortunately, Spring’s LocalSessionFactoryBean supports Hibernate’s SessionFactory.getCurrentSession() method for any Spring transaction strategy, returning the current Spring-managed transactional Session, even with HibernateTransactionManager. The standard behavior of that method remains to return the current Session associated with the ongoing JTA transaction, if any. This behavior applies regardless of whether you use Spring’s JtaTransactionManager, EJB container managed transactions (CMTs), or JTA.

In summary, you can implement DAOs based on the plain Hibernate API, while still being able to participate in Spring-managed transactions.

We recommend that you use Spring’s declarative transaction support, which lets you replace explicit transaction demarcation API calls in your Java code with an AOP transaction interceptor. You can configure this transaction interceptor in a Spring container by using either Java annotations or XML. This declarative transaction capability lets you keep business services free of repetitive transaction demarcation code and focus on adding business logic, which is the real value of your application.

You can annotate the service layer with @Transactional annotations and instruct the Spring container to find these annotations and provide transactional semantics for these annotated methods. The following example shows how to do so:

In the container, you need to set up the PlatformTransactionManager implementation (as a bean) and a <tx:annotation-driven/> entry, opting into @Transactional processing at runtime. The following example shows how to do so:

You can demarcate transactions in a higher level of the application, on top of lower-level data access services that span any number of operations. Nor do restrictions exist on the implementation of the surrounding business service. It needs only a Spring PlatformTransactionManager. Again, the latter can come from anywhere, but preferably as a bean reference through a setTransactionManager(..) method. Also, the productDAO should be set by a setProductDao(..) method. The following pair of snippets show a transaction manager and a business service definition in a Spring application context and an example for a business method implementation:

Spring’s TransactionInterceptor lets any checked application exception be thrown with the callback code, while TransactionTemplate is restricted to unchecked exceptions within the callback. TransactionTemplate triggers a rollback in case of an unchecked application exception or if the transaction is marked rollback-only by the application (by setting TransactionStatus). By default, TransactionInterceptor behaves the same way but allows configurable rollback policies per method.

Both TransactionTemplate and TransactionInterceptor delegate the actual transaction handling to a PlatformTransactionManager instance (which can be a HibernateTransactionManager (for a single Hibernate SessionFactory) by using a ThreadLocal Session under the hood) or a JtaTransactionManager (delegating to the JTA subsystem of the container) for Hibernate applications. You can even use a custom PlatformTransactionManager implementation. Switching from native Hibernate transaction management to JTA (such as when facing distributed transaction requirements for certain deployments of your application) is only a matter of configuration. You can replace the Hibernate transaction manager with Spring’s JTA transaction implementation. Both transaction demarcation and data access code work without changes, because they use the generic transaction management APIs.

For distributed transactions across multiple Hibernate session factories, you can combine JtaTransactionManager as a transaction strategy with multiple LocalSessionFactoryBean definitions. Each DAO then gets one specific SessionFactory reference passed into its corresponding bean property. If all underlying JDBC data sources are transactional container ones, a business service can demarcate transactions across any number of DAOs and any number of session factories without special regard, as long as it uses JtaTransactionManager as the strategy.

Both HibernateTransactionManager and JtaTransactionManager allow for proper JVM-level cache handling with Hibernate, without container-specific transaction manager lookup or a JCA connector (if you do not use EJB to initiate transactions).

HibernateTransactionManager can export the Hibernate JDBC Connection to plain JDBC access code for a specific DataSource. This ability allows for high-level transaction demarcation with mixed Hibernate and JDBC data access completely without JTA, provided you access only one database. HibernateTransactionManager automatically exposes the Hibernate transaction as a JDBC transaction if you have set up the passed-in SessionFactory with a DataSource through the dataSource property of the LocalSessionFactoryBean class. Alternatively, you can specify explicitly the DataSource for which the transactions are supposed to be exposed through the dataSource property of the HibernateTransactionManager class.

You can switch between a container-managed JNDI SessionFactory and a locally defined one without having to change a single line of application code. Whether to keep resource definitions in the container or locally within the application is mainly a matter of the transaction strategy that you use. Compared to a Spring-defined local SessionFactory, a manually registered JNDI SessionFactory does not provide any benefits. Deploying a SessionFactory through Hibernate’s JCA connector provides the added value of participating in the Java EE server’s management infrastructure, but does not add actual value beyond that.

Spring’s transaction support is not bound to a container. When configured with any strategy other than JTA, transaction support also works in a stand-alone or test environment. Especially in the typical case of single-database transactions, Spring’s single-resource local transaction support is a lightweight and powerful alternative to JTA. When you use local EJB stateless session beans to drive transactions, you depend both on an EJB container and on JTA, even if you access only a single database and use only stateless session beans to provide declarative transactions through container-managed transactions. Direct use of JTA programmatically also requires a Java EE environment. JTA does not involve only container dependencies in terms of JTA itself and of JNDI DataSource instances. For non-Spring, JTA-driven Hibernate transactions, you have to use the Hibernate JCA connector or extra Hibernate transaction code with the TransactionManagerLookup configured for proper JVM-level caching.

Spring-driven transactions can work as well with a locally defined Hibernate SessionFactory as they do with a local JDBC DataSource, provided they access a single database. Thus, you need only use Spring’s JTA transaction strategy when you have distributed transaction requirements. A JCA connector requires container-specific deployment steps, and (obviously) JCA support in the first place. This configuration requires more work than deploying a simple web application with local resource definitions and Spring-driven transactions. Also, you often need the Enterprise Edition of your container if you use, for example, WebLogic Express, which does not provide JCA. A Spring application with local resources and transactions that span one single database works in any Java EE web container (without JTA, JCA, or EJB), such as Tomcat, Resin, or even plain Jetty. Additionally, you can easily reuse such a middle tier in desktop applications or test suites.

All things considered, if you do not use EJBs, stick with local SessionFactory setup and Spring’s HibernateTransactionManager or JtaTransactionManager. You get all of the benefits, including proper transactional JVM-level caching and distributed transactions, without the inconvenience of container deployment. JNDI registration of a Hibernate SessionFactory through the JCA connector adds value only when used in conjunction with EJBs.

In some JTA environments with very strict XADataSource implementations (currently only some WebLogic Server and WebSphere versions), when Hibernate is configured without regard to the JTA PlatformTransactionManager object for that environment, spurious warning or exceptions can show up in the application server log. These warnings or exceptions indicate that the connection being accessed is no longer valid or JDBC access is no longer valid, possibly because the transaction is no longer active. As an example, here is an actual exception from WebLogic:

You can resolve this warning by making Hibernate aware of the JTA PlatformTransactionManager instance, to which it synchronizes (along with Spring). You have two options for doing this:

The remainder of this section describes the sequence of events that occur with and without Hibernate’s awareness of the JTA PlatformTransactionManager.

When Hibernate is not configured with any awareness of the JTA PlatformTransactionManager, the following events occur when a JTA transaction commits:

When Hibernate is configured with awareness of the JTA PlatformTransactionManager, the following events occur when a JTA transaction commits:

The Spring JPA support offers three ways of setting up the JPA EntityManagerFactory that is used by the application to obtain an entity manager.

It is possible to write code against the plain JPA without any Spring dependencies, by using an injected EntityManagerFactory or EntityManager. Spring can understand the @PersistenceUnit and @PersistenceContext annotations both at the field and the method level if a PersistenceAnnotationBeanPostProcessor is enabled. The following example shows a plain JPA DAO implementation that uses the @PersistenceUnit annotation:

The preceding DAO has no dependency on Spring and still fits nicely into a Spring application context. Moreover, the DAO takes advantage of annotations to require the injection of the default EntityManagerFactory, as the following example bean definition shows:

As an alternative to explicitly defining a PersistenceAnnotationBeanPostProcessor, consider using the Spring context:annotation-config XML element in your application context configuration. Doing so automatically registers all Spring standard post-processors for annotation-based configuration, including CommonAnnotationBeanPostProcessor and so on.

Consider the following example:

The main problem with such a DAO is that it always creates a new EntityManager through the factory. You can avoid this by requesting a transactional EntityManager (also called a “shared EntityManager” because it is a shared, thread-safe proxy for the actual transactional EntityManager) to be injected instead of the factory. The following example shows how to do so:

The @PersistenceContext annotation has an optional attribute called type, which defaults to PersistenceContextType.TRANSACTION. You can use this default to receive a shared EntityManager proxy. The alternative, PersistenceContextType.EXTENDED, is a completely different affair. This results in a so-called extended EntityManager, which is not thread-safe and, hence, must not be used in a concurrently accessed component, such as a Spring-managed singleton bean. Extended EntityManager instances are only supposed to be used in stateful components that, for example, reside in a session, with the lifecycle of the EntityManager not tied to a current transaction but rather being completely up to the application.

The injected EntityManager is Spring-managed (aware of the ongoing transaction). Even though the new DAO implementation uses method-level injection of an EntityManager instead of an EntityManagerFactory, no change is required in the application context XML, due to annotation usage.

The main advantage of this DAO style is that it depends only on the Java Persistence API. No import of any Spring class is required. Moreover, as the JPA annotations are understood, the injections are applied automatically by the Spring container. This is appealing from a non-invasiveness perspective and can feel more natural to JPA developers.

The recommended strategy for JPA is local transactions through JPA’s native transaction support. Spring’s JpaTransactionManager provides many capabilities known from local JDBC transactions (such as transaction-specific isolation levels and resource-level read-only optimizations) against any regular JDBC connection pool (no XA requirement).

Spring JPA also lets a configured JpaTransactionManager expose a JPA transaction to JDBC access code that accesses the same DataSource, provided that the registered JpaDialect supports retrieval of the underlying JDBC Connection. Spring provides dialects for the EclipseLink and Hibernate JPA implementations. See the next section for details on the JpaDialect mechanism.

As an advanced feature, JpaTransactionManager and subclasses of AbstractEntityManagerFactoryBean allow a custom JpaDialect to be passed into the jpaDialect bean property. A JpaDialect implementation can enable the following advanced features supported by Spring, usually in a vendor-specific manner:

This is particularly valuable for special transaction semantics and for advanced translation of exception. The default implementation (DefaultJpaDialect) does not provide any special abilities and, if the features listed earlier are required, you have to specify the appropriate dialect.

See the JpaDialect and JpaVendorAdapter javadoc for more details of its operations and how they are used within Spring’s JPA support.

As an alternative to JpaTransactionManager, Spring also allows for multi-resource transaction coordination through JTA, either in a Java EE environment or with a stand-alone transaction coordinator, such as Atomikos. Aside from choosing Spring’s JtaTransactionManager instead of JpaTransactionManager, you need to take few further steps:

As of Spring Framework 5.1 and Hibernate 5.2/5.3, a native LocalSessionFactoryBean setup in combination with HibernateTransactionManager allows for interaction with @PersistenceContext and other JPA access code. A Hibernate SessionFactory natively implements JPA’s EntityManagerFactory interface now and a Hibernate Session handle natively is a JPA EntityManager. Spring’s JPA support facilities automatically detect native Hibernate sessions.

Such native Hibernate setup can, therefore, serve as a replacement for a standard JPA LocalContainerEntityManagerFactoryBean and JpaTransactionManager combination in many scenarios, allowing for interaction with SessionFactory.getCurrentSession() (and also HibernateTemplate) next to @PersistenceContext EntityManager within the same local transaction. Such a setup also provides stronger Hibernate integration and more configuration flexibility, because it is not constrained by JPA bootstrap contracts.

You do not need HibernateJpaVendorAdapter configuration in such a scenario, since Spring’s native Hibernate setup provides even more features (for example, custom Hibernate Integrator setup, Hibernate 5.3 bean container integration, and stronger optimizations for read-only transactions). Last but not least, you can also express native Hibernate setup through LocalSessionFactoryBuilder, seamlessly integrating with @Bean style configuration (no FactoryBean involved).

Spring’s bean factory makes it easy to configure marshallers, without needing to construct JAXB context, JiBX binding factories, and so on. You can configure the marshallers as you would any other bean in your application context. Additionally, XML namespace-based configuration is available for a number of marshallers, making the configuration even simpler.

Spring’s O-X mapping operates through two global interfaces: Marshaller and Unmarshaller. These abstractions let you switch O-X mapping frameworks with relative ease, with little or no change required on the classes that do the marshalling. This approach has the additional benefit of making it possible to do XML marshalling with a mix-and-match approach (for example, some marshalling performed using JAXB and some by XStream) in a non-intrusive fashion, letting you use the strength of each technology.

Spring provides a conversion from exceptions from the underlying O-X mapping tool to its own exception hierarchy with the XmlMappingException as the root exception. These runtime exceptions wrap the original exception so that no information is lost.

Spring abstracts all marshalling operations behind the org.springframework.oxm.Marshaller interface, the main method of which follows:

The Marshaller interface has one main method, which marshals the given object to a given javax.xml.transform.Result. The result is a tagging interface that basically represents an XML output abstraction. Concrete implementations wrap various XML representations, as the following table indicates:

Similar to the Marshaller, we have the org.springframework.oxm.Unmarshaller interface, which the following listing shows:

This interface also has one method, which reads from the given javax.xml.transform.Source (an XML input abstraction) and returns the object read. As with Result, Source is a tagging interface that has three concrete implementations. Each wraps a different XML representation, as the following table indicates:

Even though there are two separate marshalling interfaces (Marshaller and Unmarshaller), all implementations in Spring-WS implement both in one class. This means that you can wire up one marshaller class and refer to it both as a marshaller and as an unmarshaller in your applicationContext.xml.

Spring converts exceptions from the underlying O-X mapping tool to its own exception hierarchy with the XmlMappingException as the root exception. These runtime exceptions wrap the original exception so that no information will be lost.

Additionally, the MarshallingFailureException and UnmarshallingFailureException provide a distinction between marshalling and unmarshalling operations, even though the underlying O-X mapping tool does not do so.

The O-X Mapping exception hierarchy is shown in the following figure:

The Jaxb2Marshaller class implements both of Spring’s Marshaller and Unmarshaller interfaces. It requires a context path to operate. You can set the context path by setting the contextPath property. The context path is a list of colon-separated Java package names that contain schema derived classes. It also offers a classesToBeBound property, which allows you to set an array of classes to be supported by the marshaller. Schema validation is performed by specifying one or more schema resources to the bean, as the following example shows:

The JibxMarshaller class implements both the Marshaller and Unmarshaller interface. To operate, it requires the name of the class to marshal in, which you can set using the targetClass property. Optionally, you can set the binding name by setting the bindingName property. In the following example, we bind the Flights class:

A JibxMarshaller is configured for a single class. If you want to marshal multiple classes, you have to configure multiple JibxMarshaller instances with different targetClass property values.

The XStreamMarshaller does not require any configuration and can be configured in an application context directly. To further customize the XML, you can set an alias map, which consists of string aliases mapped to classes, as the following example shows:

The tx tags deal with configuring all of those beans in Spring’s comprehensive support for transactions. These tags are covered in the chapter entitled Transaction Management.

In the interest of completeness, to use the elements in the tx schema, you need to have the following preamble at the top of your Spring XML configuration file. The text in the following snippet references the correct schema so that the tags in the tx namespace are available to you:

The jdbc elements let you quickly configure an embedded database or initialize an existing data source. These elements are documented in Embedded Database Support and Initializing a DataSource, respectively.

To use the elements in the jdbc schema, you need to have the following preamble at the top of your Spring XML configuration file. The text in the following snippet references the correct schema so that the elements in the jdbc namespace are available to you: