Building the Data Access Layer for the Sample Application

After this simplistic example we will build parts of the data access layer for the sample application that is discussed in Appendix A. As we move along we will introduce the various features of the framework used in the example and explain how to use them.

Data Model for the Sample Application

Let's start this by looking at the data model for the sample application. We won't cover the entire data model to begin with, but we will gradually include the classes and tables that we will be using for the rest of this chapter. To begin with we have three tables that we will be writing some data access classes for: Show, Genre, and Seating_Plan. The data model in Figure 5-1 shows the relationships between these tables.

image from book
Figure 5-1

We have a Shows associated with both a Genre and a Seating_Plan. Each Genre can have a number of Shows associated with it. Seating plans can vary between shows because of physical setup limitations. Some shows require room for a choir, for example, so some seating would not be available because of this fact.

The DDL (data definition language) needed to create these tables is as follows:

CREATE TABLE Genre (   id INTEGER NOT NULL,   name VARCHAR(50),   PRIMARY KEY(id));     CREATE TABLE Seating_Plan (   id INTEGER NOT NULL,   name VARCHAR(50),   PRIMARY KEY(id));     CREATE TABLE Shows (   id INTEGER NOT NULL,   name VARCHAR(50),   Genre_id INTEGER,   Seating_Plan_id INTEGER,   PRIMARY KEY(id));     ALTER TABLE Shows   ADD CONSTRAINT fk_Genre   FOREIGN KEY (Genre_id)   REFERENCES Genre (id)   ON DELETE CASCADE;     ALTER TABLE Shows   ADD CONSTRAINT fk_Seating_Plan   FOREIGN KEY (Seating_Plan_id)   REFERENCES Seating_Plan (id);

This is a generic script that should work for most SQL databases. Note that the table name for the Show entity is the plural form — Shows. This is because the word "SHOW" is a reserved word in MySQL. If you are using MySQL then you should append TYPE = InnoDB to all the table definitions in the script. This will allow you to use transactions.

The next step is to create these tables using your preferred database. We have used HSQL, MySQL, PostgreSQL, and Oracle to test the sample code and we provide database scripts for the sample application for all of these databases. (See Appendix A for more information on the sample application and how to create the sample tables.) Once we have the tables created, we are ready to move on.

DataSource

You might be used to calling DriverManager.getConnection to get a JDBC Connection object. JDBC2.0 introduced a new interface, javax.sql.DataSource, that acts as a factory for connections. This is now the preferred method for obtaining a connection as it allows the use of connection pooling and avoids hard coding the connection properties. Spring's JDBC framework is built with this in mind, and a DataSource is the only thing Spring uses to get a connection to the database.

Normally you would look up the DataSource using a naming service like JNDI, or you could alternatively configure a basic DataSource locally. In the case of a non-web application or a unit test, you could use a DataSource implementation provided by Jakarta Commons DBCP. This is also the case for a web application that connects to a single database only and that does not need support for distributed transactions (see the "DataSource Declarations" section in Chapter 6 for more in-depth discussion).

The most commonly used DataSources are org.apache.commons.dbcp.BasicDataSource and org.apache.commons.dbcp.PoolingDataSource.

You could also use a DataSource implementation provided by Spring. The DriverManagerDataSource implementation will create a new connection each time the framework calls the getConnection method. This works well for unit testing, but is not a good choice when you have an application that makes multiple calls to the database. For applications where you want to acquire the connection only once and hold on to it, Spring provides the SingleConnectionDataSource. With this implementation, you should make a call to the destroy method when you are done with you database processing. This call will allow Spring to close the physical connection. If you use dependency injection with an ApplicationContext or BeanFactory, then this method will be called automatically when the application is shut down.

It is important to note that while the various DataSource implementations are threadsafe, the SingleConnectionDataSource is not because it holds on to the connection until the destroy method is called. This is not a problem for single-threaded applications, but web applications should allocate a new instance of this class for each request.

Important

For web applications and other applications, we recommend using a DataSource that is threadsafe and one that provides connection pooling. This includes the implementations provided by Apache Commons DBCP and the c3p0 project.

For your data access objects, you normally declare a DataSource variable and have it set using Dependency Injection. Your program does not care whether the DataSource being used is a pooled or a basic one. When it comes to your unit tests, you have a choice whether you want to create the DataSource programmatically or whether you want to use Dependency Injection. The programmatic way would be the following:

BasicDataSource ds = new BasicDataSource(); ds.setDriverClassName("org.hsqldb.jdbcDriver"); ds.setUrl("jdbc:hsqldb:hsql://localhost"); ds.setUsername("sa"); ds.setPassword("");

If you prefer to use an application context, then this is what you would need:

ApplicationContext ac = new ClassPathXmlApplicationContext("ticket-context.xml");  DataSource ds = (DataSource) ac.getBean("dataSource", DataSource.class);

Alternatively, you could extend AbstractTransactionalDataSourceSpringContextTests in the org.springframework.test package, in which case your test will extend a DataSource provided by the super class — which obtains it by Dependency Injection from your application context. This is normally best practice for integration testing.

The application context ticket-context.xml (see Chapter 2 for more detail about application contexts) should be placed on the classpath and has the following content:

<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN"     "http://www.springframework.org/dtd/spring-beans.dtd">     <beans>       <bean             >   <property name="driverClassName">     <value>org.hsqldb.jdbcDriver</value>   </property>   <property name="url">     <value>jdbc:hsqldb:hsql://localhost</value>     </property>   <property name="username">     <value>sa</value>   </property>   <property name="password">     <value></value>   </property>   </bean>     </beans>

Exception Translation

Spring will catch and translate any SQLExceptions that might be thrown during the JDBC processing. The base class for Spring's exception hierarchy is org.springframework.dao.DataAccessException. This is an abstract class that inherits from NestedRuntimeException. The advantage of using NestedRuntimeException is twofold. Firstly, it is a RunTimeException so you don't have to catch it if you don't want to. In practice, data access exceptions are usually unrecoverable. If you don't think that your code can recover from a database failure, then you can let the exception propagate to the surrounding class without catching it. Secondly, it is able to wrap the original SQLException so that you can get to the root cause of the failure.

By using an exception hierarchy, Spring allows you to catch exceptions at the right level. If you think that you can recover from an OptimisticLockingException, then you can easily catch this exception and handle it. Figure 5-2 shows a graph of the entire DataAccessException exception hierarchy.

image from book
Figure 5-2

The concrete exceptions that inherit from the DataAccessException and that are directly related to JDBC usage are the following:

BadSqlGrammarException: This exception indicates a problem with the SQL issued by your application — such as an invalid table or view name as conveyed by ORA-942. However, in this case, code that catches this Spring exception will be self-documenting — there is no risk of a check against a magic number — and it will no longer be Oracle-specific. There is a gain in both maintainability and portability.
DataIntegrityViolationException: There is a data integrity problem. You might have tried to insert a duplicate key or no data was supplied for a column that requires a value to be specified.
DataRetrievalFailureException: A problem was encountered retrieving expected data.
CannotAcquireLockException: When you update the database, someone else might have a lock on the row you are trying to update. If you have configured your database access to not wait for blocking locks, then this would be the appropriate exception to be thrown. This would happen if you used Oracle and executed a SELECT ... FROM mytable FOR UPDATE NOWAIT statement.

DataAccessResourceFailureException: Exception thrown when there is a problem connecting to the database.

Important

Spring's data access exception hierarchy consists of exceptions that are independent not just of a specific database, such as Oracle, but of the use of JDBC altogether. For example, DataAccessResourceFailureException can be thrown by Spring's TopLink integration as well as JdbcTemplate, meaning that code that handles it is less dependent on the specific persistence API in use. JDBC-specific exceptions, such as BadSqlGrammarException, extend the most informative possible generic exception — in this case, InvalidDataAccessResourceUsageException. (Thus it is a sibling of HibernateQueryException, which is thrown on issuing an ill-formed HQL query string.)

Which exception is thrown for a specific SQL error code is controlled by a configuration file named sql- error-codes.xml. This file is loaded when the JdbcTemplate is configured. It contains

<beans>     ...       <bean  >     <property name="badSqlGrammarCodes">       <value>900,903,904,917,936,942,17006</value>     </property>     <property name="dataIntegrityViolationCodes">       <value>1,1400,1722,2291</value>     </property>     <property name="cannotAcquireLockCodes">       <value>54</value>     </property>   </bean>     ...     </beans>

The error code from the SQLException is matched up with the set of codes provided in this file. Which set of codes that is used depends on the name of the database product that is returned from a call to getDatabaseMetaData on the JDBC connection. If you would like to use a different translation, you can supply your own configuration file with the same name. This file must be placed on the classpath so that the SQLErrorCodesFactory can find it.

If the error code from the SQLException can't be found in the list of error codes, then the translation will fall back on using the SQL state as provided in the SQLException. This fallback translation is done by the class SQLStateSQLExceptionTranslator.