Using JDBC Data Access in the Sample Application

All the concepts introduced here are used in the sample application. The beans that implement the data access interfaces are defined in applicationContext-jdbc.xml. To make sure SpringBlog uses this implementation, edit the web.xml file and make sure the contextConfigLocation includes the applicationContext-jdbc.xml, as shown in Listing 8-24.

Listing 8-24: web.xml for JDBC Data Access Layer Implementation

<?xml version="1.0" encoding="ISO-8859-1"?> <!DOCTYPE web-app PUBLIC     "-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN"     "http://java.sun.com/dtd/web-app_2_3.dtd"> <web-app>     <context-param>         <param-name>contextConfigLocation</param-name>         <param-value>             /WEB-INF/applicationContext.xml              /WEB-INF/applicationContext-db.xml              /WEB-INF/applicationContext-jdbc.xml          </param-value>     </context-param>          <!-- the rest of the file omitted -->      </web-app>

The implementation classes are all located in the com.apress.prospring.data.jdbc package. We have tried to make the implementation of the JDBC classes as database-independent as possible by using an abstract class that is common for all databases and then providing an implementation of the abstract class for MySQL, as explained in the "Inserting Data" section earlier in this chapter. The UML diagram in Figure 8-1 shows the EntryDao interface, the JdbcAbstractEntryDao that performs the database-independent functions, and the JdbcMysqlEntryDao that provides MySQL-specific functionality to the JdbcAbstractEntryDao.

Figure 8-1: UML diagram of the EntryDao JDBC implementation

The diagram in Figure 8-1 also shows the inner classes used in the JdbcAbstractEntryDao implementation. All classes that return data are subclasses of AbstractSelect, which takes care of mapping the rows retrieved from the database to the Entry domain objects. The concrete implementations of AbstractSelect provide the SQL select statement that will be executed. The Insert, Update, and Delete inner classes all extend the SqlUpdate class, provide the SQL statement for the actual update operation, and define the required parameters.

We have chosen to merge the insert and update operations into the save() method. The motivation is that the data access implementation knows how to tell a domain object that needs to be inserted from a domain object that needs to be updated. In the case of relational databases using an integer primary key, the unsaved object has its primary key value set to 0, whereas the object that references an existing row has the primary key value set greater than 0.

Listing 8-25 shows the implementation of the save() method calling the insert() or update() method appropriately.

Listing 8-25: Implementaion of the save(), insert(), and update() Methods

public abstract class JdbcAbstractEntryDao extends JdbcDaoSupport implements         EntryDao, InitializingBean {          // rest of the code omitted          protected abstract String getIdentitySql();          protected void retrieveIdentity(final Entry entry) {         entry.setEntryId(getJdbcTemplate().queryForInt(getIdentitySql()));     }          public void save(Entry entry) {         if (entry.getEntryId() == 0)             insert(entry);         else             update(entry);     }          private void insert(Entry entry) {         Object[] values = new Object[] { entry.getSubject(), entry.getBody(),                 entry.getPostDate() };         insert.update(values);         retrieveIdentity(entry);          }          private void update(Entry entry) {         Object[] values = new Object[] { entry.getSubject(), entry.getBody(),                 entry.getPostDate(), new Integer(entry.getEntryId()) };         update.update(values);     } }

The code from Listing 8-25 also explains why the JdbcAbstractEntryDao is an abstract class: when a row is inserted into the Entries table, we need to retrieve the value generated for the primary key. To do this, the insert() method calls the retrieveIdentity() method, which gets the integer value by executing a SQL statement returned by the getIdentitySql() method.

The getIdentitySql() method is the only method that must be implemented to support each individual database. We implement this method for MySQL in JdbcMysqlEntryDao, as shown in Listing 8-26.

Listing 8-26: JdbcMysqlEntryDao Implementation

package com.apress.prospring.data.jdbc;      public class JdbcMysqlEntryDao extends JdbcAbstractEntryDao {          protected String getIdentitySql() {         return "select LAST_INSERT_ID()";     } }

The update and delete operations in Listing 8-25 are implemented almost exactly as the same operations were implemented in the "Inserting Data" sections earlier in this chapter.

Selecting the data from the database tables is a trivial task of extending the AbstractSelect query to make sure the correct SQL statements are passed to the database; all this has already been covered in the "Selecting the Data as Java Objects" section earlier in this chapter.

The last thing we have to deal with is selecting data from more than one table. This is best explained by the getById() method, which returns an Entry domain object identified by its primary key, but it also loads all Attachment and Comment objects associated with the Entry. The JdbcAbstractEntryDao class uses CommentDao and AttachmentDao to load and set the attachments and comments properties of the Entry domain object. This means that the getById() method issues three select statements to the database: one to load the Entry object, one to load the List of Attachments, and another one to load the List of Comments. Fortunately, this is not a major problem, because the getById() method is not called very frequently and more importantly, it only loads a single Entry domain object. This is known as N+1 complexity, which we discuss in more detail in 10.