Selecting the Data as Java Objects

You have learned how to use JdbcTemplate to perform raw data operations, but there is too much code to write, and in most cases, writing code for various select statements results in massive code duplication. Also, the queryForList() method returns a List of Map instance because it is not aware of the columns being selected. Unfortunately you still need to write lots of code to retrieve the data using ResultSets and create appropriate domain objects.

In Listing 8-14, we show the use of Spring JDBC support classes. We are going to start the discussion with the use of the MappingSqlQuery class that will process the result set and create appropriate domain objects. Subclasses of MappingSqlQuery must implement its mapRow method. This method gets the values from the underlying ResultSet and returns the appropriate domain object.

Listing 8-14: MappingSqlQuery Usage

package com.apress.prospring.ch10.spring;      import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Types; import java.util.List;      import javax.sql.DataSource;      import org.springframework.jdbc.core.SqlParameter; import org.springframework.jdbc.core.support.JdbcDaoSupport; import org.springframework.jdbc.object.MappingSqlQuery;      import com.apress.prospring.ch10.TestDao; import com.apress.prospring.ch10.domain.Test;      public class JdbcTestDao implements TestDao, InitializingBean {     private DataSource dataSource;          private static final String SELECT_BY_NAME_SQL =          "select * from Test where Name=?";          abstract class AbstractSelect extends MappingSqlQuery {              public AbstractSelect(DataSource dataSource, String sql) {             super(dataSource, sql);         }              protected Object mapRow(ResultSet rs, int rowNum) throws SQLException {             Test test = new Test();                          test.setName(rs.getString("Name"));             test.setTestId(rs.getInt("TestId"));                          return test;         }     }          class SelectByName extends AbstractSelect {         public SelectByName (DataSource dataSource) {             super(dataSource, SELECT_BY_NAME_SQL);             declareParameter(new SqlParameter(Types.VARCHAR));         }     }          public void afterPropertiesSet() throws Exception {         if (dataSource == null) {             throw new BeanCreationException("Must set dataSource on UserDao");         }         // more initialization code     }          public void setDataSource(DataSource dataSource) {         this.dataSource = dataSource;     } }

The code in Listing 8-14 looks like it can actually do some database work! Let's review what we have implemented.

First, we have the AbstractSelect class that extends MappingSqlQuery. This class maps the ResultSet columns and returns the appropriate Test domain object. All query classes that select users inevitably return User objects, so we can use the AbstractSelect class as a super- class of all select classes. The SelectByName class exemplifies this approach. The constructor takes only a single DataSource parameter, because this class performs one specific SQL query. This particular query requires two VARCHAR parameters, username and password. We declare the parameters using a call to declareParameter().

AbstractSelect is a superclass for all concrete Select implementations that no longer have to implement the mapRow() method to create an instance of the domain object. This implies that all select statements need to return the same set of columns in order for AbstractSelect.mapRow() to be able to create the domain object.

You may be wondering why AbstractSelect is still marked as abstract; after all, it already implements the mapRow() abstract method. There is a simple explanation for this: we should not use the AbstractSelect class to perform any SQL query, because different queries may use different parameters, and by marking this class abstract, we are forcing ourselves to subclass AbstractSelect for every SQL query.

A more important benefit, however, is that by implementing each individual query in its own class, we can take advantage of the fact that most databases precompile the queries and cache the compiled query plan. This may not be very critical in simple select * from Table queries similar to those that result in a full table scan, but it becomes more important as the queries get more complex.

Note 

Some databases do not use the precompiled plans. The rationale behind this is that the statistics used to create the query plan may become out of date very quickly as records are inserted into the database. If a table has 100 rows, for example, it is not worth the effort to use index operations, but if a table has

1,000,000 rows, then the additional work of an Index Scan operation is well justified. It is not impossible to imagine that a query plan was created for a table of 100 rows and, in the meantime, the number of rows increased to a million. If this was the case, the database would be using a very inefficient query plan, and the query time saved by using the precompiled query plan would be insignificant.

Listing 8-15 shows how to use the concept of the AbstractSelect class in a TestDao implementation.

Listing 8-15: TestDao Interface

package com.apress.prospring.ch10;      import java.util.List;      public interface TestDao {          public List getByName(String name);      }

To implement the method declared in the TestDao interface, we are going to use the SelectByName inner class (see Listing 8-16). To use this class, we need to instantiate, and we will do so in the afterPropertiesSet() method declared in InitializingBean.

Listing 8-16: Implementation of getByName

package com.apress.prospring.data.jdbc;      public class JdbcTestDao implements TestDao, InitializingBean {          private DataSource dataSource;          private static final String SELECT_BY_NAME_SQL =          "select * from Test where Name=?";          abstract class AbstractSelect extends MappingSqlQuery {              public AbstractSelect(DataSource dataSource, String sql) {             super(dataSource, sql);         }              protected Object mapRow(ResultSet rs, int rowNum) throws SQLException {             Test test = new Test();                  test.setName(rs.getString("Name"));             test.setTestId(rs.getInt("TestId"));                  return test;         }     }          class SelectByName extends AbstractSelect {              public SelectByName(DataSource dataSource) {             super(dataSource, SELECT_BY_NAME_SQL);             declareParameter(new SqlParameter(Types.VARCHAR));         }     }          private SelectByName selectByName;          public void afterPropertiesSet() throws Exception {         if (dataSource == null) {             throw new BeanCreationException("Must set dataSource on testDao");         }              selectByName = new SelectByName(dataSource);     }          public List getByName(String name) {         return selectByName.execute(new Object[] { name });     }      } 

This code links all pieces of the puzzle together. We created an abstract subclass of Spring's MappingSqlQuery named AbstractSelect, which takes care of mapping the results retrieved from the database into a Test object. We then subclassed AbstractSelect in SelectByName to execute the SQL query that selects rows from the Test table to return a test whose name matches the parameters passed to the method. In the SelectByName constructor, we have declared that name is the only parameter and is stored as VARCHAR in the database table. To select the Test objects by name, we need to pass the parameter value to the execute() method in an Object[] array containing a single value. The execute() method returns a List of objects returned by the mapRow() method. Because SelectByName is a subclass of AbstractSelect, and because the AbstractSelect.mapRow() method returns an instance of the Test class, we can safely cast the Objects in the List to Tests.

Keep in mind that the order in which the parameters are passed is critical to the execute() method: if we accidentally change the order of the parameters, we do not get the correct results.

Let us step back and look at the code we have written. If this is the only DAO implementation class in the application, it is just fine the way it is. However, most applications have more than one DAO class. If this is the case, we need to create a DAO class that has a dataSource property and implements InitializingBean and the appropriate DAO interface. This has a nasty code smell and as a result, we need to refactor the code. Spring supports this situation by providing a JdbcDaoSupport class.

The refactored JdbcTestDao class is shown in Listing 8-17.

Listing 8-17: Refactored JdbcTestDao

public class JdbcTestDao      extends JdbcDaoSupport implements TestDao, InitializingBean {          private DataSource dataSource;          private static final String SELECT_BY_NAME_SQL =          "select * from Test where Name=?";          abstract class AbstractSelect extends MappingSqlQuery {          /* omitted for clarity */      }          class SelectByName extends AbstractSelect { /* omitted for clarity */ }          private SelectByName selectByName;          protected void initDao() throws Exception {         super.initDao();         selectByName = new SelectByName(getDataSource());     }          public void afterPropertiesSet() throws Exception {         if (dataSource == null)              throw new BeanCreationException("Must set dataSource on testDao");              selectByName = new SelectByName(dataSource);     }          public void setDataSource(DataSource dataSource) {         this.dataSource = dataSource;     }          public List getByName(String name) {         return selectByName.execute(name);     } } 

This code is much cleaner after we remove the stricken-out lines and add the lines in bold. We had to remove some of the code because JdbcDaoSupport implements InitializingBean's afterPropertiesSet as final, and it also has the dataSource property. Because we still need to instantiate the query inner classes, JdbcDaoSupport allows us to override the initDao() method, which is called after all properties have been set on JdbcDaoSupport. We can access the dataSource property by calling the getDataSource() method.

The code we now have looks like it does not need any more refactoring, and indeed, that would be true if we were only selecting and updating data. Before we take a look at the inevitable catch with inserts, we will discuss data updates.