Using the RDBMS Operation Classes

JdbcTemplate is ideal for simple queries and updates, and when you need to build SQL strings dynamically, but sometimes you might want a higher level of abstraction, and a more object-oriented approach to database access. This is provided by the org.springframework.jdbc.object package. It contains the SqlQuery, SqlMappingQuery, SqlUpdate, and StoredProcedure classes that are intended to be the central classes used by most Spring JDBC applications. These classes are used together with a DataSource and the SqlParameter class. Each of the RDBMS Operation classes is based on the RDBMSOperation class and they all use a JdbcTemplate internally for database access. As a user of these classes you will have to provide either an existing JdbcTemplate or you can provide a DataSource and the framework code will create a JdbcTemplate when it needs one.

Important 

Spring's RDBMS Operation classes are parameterized operations that are threadsafe once they are prepared and compiled. You can safely create a single instance for each operation that you define. The preparation consists of providing a datasource and defining all the parameters that are needed for the operation. We just mentioned that they are threadsafe once they are compiled. This means that we have to be a little bit careful when we create these operations. The recommended method is to define the parameters and compile them in the constructor. That way there will not be any risk for thread conflicts.

SqlQuery and MappingSqlQuery

We have looked at some methods available for JdbcTemplate that make running simple queries and updates possible. There are, however, situations where you want a more complete solution as well as one that is more object oriented. These classes are part of the org.springframework.jdbc.object package. For running queries and mapping the results to Java classes, there is a class called MappingSqlQuery. It is easy to use and should give you enough power for the most demanding mappings. Let's look at a short example. Again, we will use an example table from our sample application. This time the example is based on the Performance table. We have added a few tables to our data model, and it now looks like Figure 5-3.

Figure 5-3

The additional DDL for these two new classes is as follows:

CREATE TABLE Performance (   id INTEGER NOT NULL,   date_and_time DATETIME,   Price_Structure_id INTEGER,   Show_id INTEGER,   PRIMARY KEY(id));     CREATE TABLE Price_Structure (   id INTEGER NOT NULL,   name VARCHAR(80),   PRIMARY KEY(id));     ALTER TABLE Performance   ADD CONSTRAINT fk_Price_Structure   FOREIGN KEY (Price_Structure_id)   REFERENCES Price_Structure (id)     ALTER TABLE Performance   ADD CONSTRAINT fk_Shows   FOREIGN KEY (Show_id)   REFERENCES Shows (id) 

We will start by creating a very simple mapping of just the Performance table. The class we are mapping to is also called Performance and Figure 5-4 is the class diagram with just setters and getters defined to start out with. We will most likely add some behavior as we develop the application further.

Figure 5-4

Mapping the table data to this class is straightforward, so it gives us an opportunity to focus on the basics involved in using MappingSqlQuery. We first create a class named PerformanceQuery and this class extends MappingSqlQuery which is provided by the framework. We create a constructor that accepts the DataSource, which together with the static SQL statement gets passed to a constructorof the super class. Next we declare the single parameter we included a placeholder for in the SQL statement. We do this by passing in an SQLParameter to the declareParameter method. The SQLParameter is given a name, which usually is the name of the property, and a type, which is the data type from java.sql.Types for the column parameter. Once we are done declaring parameters, we call compile to finalize the setup of this RDBMS Operations class. Here is the beginning of our PerformanceQuery class:

package org.springframework.prospring.ticket.db;     import java.sql.ResultSet; import java.sql.SQLException;     import org.springframework.jdbc.object.MappingSqlQuery; import org.springframework.prospring.ticket.bus.Performance;         class PerformanceQuery extends MappingSqlQuery {   private static String SQL_PERFORMANCE_QUERY =       "select id, date_and_time from Performance where id = ?";       public PerformanceQuery(DataSource ds) {     super(ds, SQL_PERFORMANCE_QUERY);     declareParameter(new SqlParameter("id", Types.INTEGER));     compile();   }           public Object mapRow(ResultSet rs, int rowNumber) throws SQLException {     Performance performance = new Performance();     performance.setId(rs.getInt("id"));     performance.setDateAndTime(rs.getTimestamp("date_and_time"));     return performance;   }     } 

The MappingSqlQuery is an abstract class and there is one method that you must implement: the mapRow method, which performs the actual mapping from the result set that gets passed in. In our example, the first thing we do in this method is to create a new instance of Performance. Then we populateit with data extracted from the result set. This result set is a regular java.sql.ResultSet so we can access all methods available for this interface. We should not do any ResultSet navigation like calling next() since this is all handled by the framework. We set the two instance variables and we ignore the references to Show and PriceStructure for now. The last task is to return the object we just created. This method is called once for each row in the result set, so the number of all the objects returned should be the same as the number of rows that were returned. All the returned objects will be put in a list that will be returned to the class using the query.

Let's take a quick look at an example of how the PerformanceQuery that we just built can be used:

public void setUp() {   jt.execute("delete from Performance");   jt.execute("insert into Performance (id, date_and_time)" +     " values(3, {d '2005-01-31’})"); }     public void testGetPerformance() {   PerformanceQuery performanceQuery = new PerformanceQuery(ds);   List perfList = performanceQuery.execute(3);   assertEquals("list contains one entry", 1, perfList.size()); }

First of all, you need to have a data source available. Next you create a new instance of the PerformanceQuery passing in the data source to the constructor. This can also be done using setters, but we prefer to use the constructor as we do in this example. Finally you need to execute the query. There are several methods for executing the query and all of them will return a list of objects retrieved. In our example we are doing a unique lookup for a specific ID, so we can use an execute method that takes a single int as its parameter. If we need to pass in several parameters we can use an object array like we did for the query methods of JdbcTemplate.

Note 

Note the use of an escape sequence used to specify the date in the setUp method. This makes it possible to execute this code against most databases regardless of their specific date representation syntax. The JDBC driver is required to make the translation.

Inserts and Updates with SqlUpdate

Next we will look at the class from the RDBMS Operation group of classes that is used for updating the database. This class is SqlUpdate and it is fairly straightforward to use. Here we are updating one column in the Performance table containing the date-and-time for a specific performance:

SqlUpdate updatePerformance = new SqlUpdate(); updatePerformance.setDataSource(ds); updatePerformance.setSql("update Performance set date_and_time = ? where id = ?"); updatePerformance.declareParameter(   new SqlParameter("date_and_time", Types.TIMESTAMP)); updatePerformance.declareParameter(new SqlParameter("id", Types.INTEGER)); updatePerformance.compile(); Object[] parameters = new Object[] {new Timestamp(System.currentTimeMillis()),    new Integer(3)}; int count = updatePerformance.update(parameters);  

In the next example you'll see what an insert statement would look like. It is basically the same approach as for the update statement: Provide the SQL, and if you provide parameter placeholders, then you must declare the parameters and pass in an object array containing the parameters for each execution. The order in which you declare the parameters is important because JDBC relies on them being set by position rather than name. The name is not strictly necessary here, but you are encouraged to provide a name because that makes the code self-documenting:

SqlUpdate insertPerformance = new SqlUpdate(); insertPerformance.setDataSource(ds); insertPerformance.setSql(   "insert into Performance (id, date_and_time) values(?, ?)"); insertPerformance.declareParameter(new SqlParameter("id", Types.INTEGER)); insertPerformance.declareParameter(   new SqlParameter("date_and_time", Types.TIMESTAMP)); insertPerformance.compile(); Object[] parameters = new Object[] {new Integer(1),    new Timestamp(System.currentTimeMillis())}; int count = insertPerformance.update(parameters); parameters = new Object[] {new Integer(2),    new Timestamp(System.currentTimeMillis())}; count = count + insertPerformance.update(parameters);

Updating a ResultSet Using UpdatableSqlQuery

Sometimes you have to update a large number of rows and it would be convenient to just iterate over an updatable ResultSet and make your updates as you go along. Spring provides a class named UpdatableSqlQuery for just this purpose. The alternative would be to use an SqlQuery and then issue update statements for the rows that needed to be updated. We have found the latter approach to be better performing against an Oracle database, if you use batch updates. For other databases we have not noticed this performance difference and the UpdatableSqlQuery approach has performed just as well.

The UpdatableSqlQuery works just like a MappingSqlQuery except that you provide a method named updateRow instead of mapRow in your implementation. The updateRow method is passed the ResultSet, the row number, and a Map named context that can contain any data you deem necessary for the update. You pass this context in to the execute method along with any parameters. Here is an example where we update the price for any bookings made after a certain date:

public class UpdateBookings extends UpdatableSqlQuery {       public UpdateBookings(DataSource dataSource, String sql) {     super(dataSource, sql);     declareParameter(new SqlParameter("date_made", Types.DATE));     compile();   }       public Object updateRow(ResultSet rs, int rowNum, Map context)        throws SQLException {     BigDecimal price = rs.getBigDecimal("price");     price = price.add((BigDecimal)context.get("increase"));     rs.updateBigDecimal("price", price);     return null;   } } 

Parameters and the return value follow the same usage as MappingSqlQuery, except that sometimes it does not make sense to return anything. In our case we simply return null since this is purely an update operation. We can call this class the following way:

 Map context = new HashMap(1); context.put("increase", new BigDecimal(10.00)); final String sql = "select id, price from Booking where date_made > ?"; UpdateBookings query = new UpdateBookings(dataSource, sql); java.util.Date dateMade = null; SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd"); try {   dateMade = df.parse("2004-06-01"); } catch (ParseException e) {   throw new InvalidDataAccessApiUsageException(e.getMessage(),e); } Object[] parameters = new Object[] {new java.sql.Date(dateMade.getTime())}; query.execute(parameters, context); 

Generating Primary Keys

When it comes to inserting data into your database, one issue you always face is how to generate primary keys. There is no single solution that is best in all cases, as databases use different approaches. Instead, there are a couple of strategies and you have to pick the one that suits the way you work with the database. You can rely on a database sequence to generate them for you but then you have to make an extra call to first retrieve the key before you insert the data. Another strategy is to use identity columns that automatically generate the next key when you insert the date. This means that you don't know what key was generated unless you make an additional call to retrieve the value. Also, some databases only provide one of these methods so this limits your options.

JDBC 3.0 introduced a standard way to retrieve generated keys that has yet to be implemented by many of the database vendors. The next section shows how to use Spring's support for this feature when it is available. Another strategy is to generate globally unique identifiers (GUIDs) before inserting into the database. This requires that you have control over all applications that create new rows to ensure that this strategy works for all applications.

Now, let's look at Spring's support for handling key generation for JDBC 2.0. The most common support among databases is support for either true sequences or a pseudo-sequence where you use a separate table with an identity column to generate the unique primary key. Spring provides implementations that support retrieving a sequence value for several popular databases. Let's start by looking at the table we will be using. It is a table that will contain Booking entries and its primary key is a plain INTEGER column named id:

CREATE TABLE Booking (   id INTEGER NOT NULL,   date_made DATE,   reserved_until TIMESTAMP,   price DECIMAL(15, 2) NOT NULL,   Purchase_id INTEGER,   PRIMARY KEY(id))   

We will show an example for HSQL and one for Oracle. For HSQL you must first create the sequence table. The following SQL creates a table with an identity column named value and sets it to zero (0):

create table booking_seq (value identity);  insert into booking_seq values(0); 

To retrieve the next value from this table you would use a class called HsqlMaxValueIncrementer. Here is an example of how this is used:

HsqlMaxValueIncrementer incr =      new HsqlMaxValueIncrementer(dataSource, "booking_seq", "value");    public int getNewBookingId() {   return incr.nextIntValue(); } 

The data source parameter is followed by the name of the sequence table and the name of the identity column. This class will attempt to cache a batch of values to limit the number of roundtrips that are necessary for retrieving new key values. The cache size can be set using the setCacheSize method with a parameter indicating the number of keys to retrieve in a batch.

For Oracle, the usage is similar except that Oracle uses true sequences and this eliminates the need for a column name in the setup.

To create a sequence you can use the following SQL:

create sequence booking_seq    start with 1    increment by 1    nomaxvalue;

Finally we will look at the code we need to get the next value from the sequence:

OracleSequenceMaxValueIncrementer incr =      new OracleSequenceMaxValueIncrementer(dataSource, "booking_seq");     public int getNewBookingId() {   return incr.nextIntValue(); }

Retrieving Database-Generated Keys

Another option is to have the database generate the keys using an identity column and then retrieve the generated key after the insert completes. This is supported in the JDBC 3.0 specification, so if you have a database and a JDBC driver that supports this you are in luck.

The following example uses MySQL as the database. MySQL is a database that implements support for retrieving the generated key value. We will use a slightly modified Booking table for this example:

CREATE TABLE Booking (   id INTEGER NOT NULL AUTO_INCREMENT,   date_made DATE,   reserved_until TIMESTAMP,   price DECIMAL(15, 2) NOT NULL,   Purchase_id INTEGER,   PRIMARY KEY(id))

Note the addition of AUTO_INCREMENT to the declaration of the primary key. This will cause MySQL to generate a new key if one is not provided in an insert statement.

Now we can create an update object where we don't have to provide a value for the id column. We can execute this statement with some test values. In addition to the array holding the parameter values, we pass in an object implementing the KeyHolder interface. We are using the GeneratedKeyHolder implementation that is provided as part of Spring. This key holder object will be populated with the generated key. Most of the time there is only one key value generated and we use the getKey method to retrieve the generated key. It is returned as a Number object:

SqlUpdate su = new SqlUpdate(); su.setDataSource(dataSource); su.setSql(      "insert into booking(date_made, reserved_until, price) " +      "values (?, ?, ?)"); su.declareParameter(new SqlParameter(Types.TIMESTAMP)); su.declareParameter(new SqlParameter(Types.TIMESTAMP)); su.declareParameter(new SqlParameter(Types.DECIMAL)); su.compile();     Object[] parameters = new Object[] {dateMade, reservedUntil, price}; KeyHolder keyHolder = new GeneratedKeyHolder(); su.update(parameters, keyHolder); long key = keyHolder.getKey().longValue(); 

Some databases and the JDBC API allow for multiple columns to have their value generated automatically. If that is the case, then you should use the getKeys method to retrieve the generated keys. This method returns a Map with an entry for each generated value keyed by the column name returned from the JDBC API.

Calling a Stored Procedure

The last major class of the RDBMS Operation group is the StoredProcedure class, used for calling stored procedures. Support for stored procedures is not part of the core SQL standard and is not provided by all SQL databases. It is, however, offered by several major databases, and stored procedures play a part in many enterprise applications. Hence, Spring provides sophisticated stored procedure support. Because HSQL does not provide the necessary support for a realistic example, we will be using Oracle as the database for the examples.

Let's take a look at a simple example, based on the expanded data model shown in Figure 5-5. The procedure we are going to call is used to reserve seats for a performance. We need to first expand our data model to include some new tables. We need to add Seat, Seat_Status, Booking, and Price_Band.

Figure 5-5

The stored procedure gets a performanceId, seatId, price, and reservedUntilDate passed in. It inserts a new row into the Booking table and updates the Seat_Status table for the seat. It looks as follows:

create or replace  procedure reserve_seat(in_performance_id in number,               in_seat in number,               in_price number,               in_reserved_until date,               out_new_booking_id out number) is  begin   -- Get a new pk for the booking table   select booking_seq.nextval into out_new_booking_id from dual;       -- Create a new booking   insert into booking(id, date_made, price, reserved_until)      values (out_new_booking_id, sysdate, in_price, in_reserved_until);       update seat_status set REF_Booking_id = out_new_booking_id     where REF_Seat_id = in_seat     and in_performance_id = in_performance_id;     end; 

To call this procedure we need to create a class that extends the abstract class StoredProcedure. You are required to extend this class and provide code necessary for supporting the call to the stored procedure. Syntax varies between implementations, but the JDBC specification provides an escape syntax that makes it possible to call stored procedures from different vendors using the same syntax. The StoredProcedure class will build the call statement using this escape syntax and the only thing you have to provide is the name of the stored procedure. You are also required to declare any parameters that are passed in or out to the procedure.

The StoredProcedure has an execute method that takes a Map of input parameters as its only argument. The results are also returned using a Map with one entry per output parameter. We normally define an execute method, in the class that extends StoredProcedure, with a calling signature that matches the stored procedure itself. This class then creates the input parameter Map and populates it with the parameters passed in. The final step is then to take the Map of output parameters and map that to an object structure or primitive value that is the return value for the execute method. Here is an example that calls the stored procedure reserve_seat that we saw in the preceding text:

public class CallReserveSeat extends StoredProcedure {   private static final String RESERVE_SEAT_SQL = "reserve_seat";       public CallReserveSeat(DataSource dataSource) {     super(dataSource, RESERVE_SEAT_SQL);     declareParameter(new SqlParameter("performance_id", Types.INTEGER));     declareParameter(new SqlParameter("seat", Types.INTEGER));     declareParameter(new SqlParameter("price", Types.DECIMAL));     declareParameter(new SqlParameter("reserved_until", Types.DATE));     declareParameter(new SqlOutParameter("new_booking_id", Types.INTEGER));     compile();   }       public int execute(int performanceId, int seatId, BigDecimal price,       java.util.Date reservedUntil) {     Map inParams = new HashMap(4);     inParams.put("performance_id", new Integer(performanceId));     inParams.put("seat", new Integer(seatId));     inParams.put("price", price);     inParams.put("reserved_until", new java.sql.Date(reservedUntil.getTime()));     Map outParams = execute(inParams);     if (outParams.size() > 0)       return ((Integer)outParams.get("new_booking_id")).intValue();     else       return 0;   } }

Most of the code is in the CallReserveSeat class itself, so it is very easy to use it. Just create a new class and call the execute method with a set of parameters:

CallReserveSeat proc = new CallReserveSeat(dataSource); Map result = proc.execute(1, 2, new BigDecimal("44.12"),      new java.util.Date(System.currentTimeMillis()+ 864000000L)); 

When you specify the name of the stored procedure in the class that extends stored procedures, you are creating a class that is specifically tailored for a single stored procedure. By providing a customized execute with a method signature that maps to the parameter signature of the stored procedure, you are essentially providing a one-to-one mapping between the stored procedure and a Java class and its execute method.

Important 

Like other RDBMS operation objects, StoredProcedure subclasses are threadsafe, unless your subclass implementation introduces read-write instance variables. You should normally avoid doing so.