Section 4.4. Using Access Objects

4.4. Using Access Objects

In this example, you're going to take a simple query object and wrap it up in a reusable form. Spring provides an API called RDBMS operational objects to help you wrap stored procedures, queries, and updates.

You may find that you're repetitively defining a block of SQL or a stored procedure. Wrapping it up into a reusable form adds convenience and clarity to your code. Plus, you'll create a thread-safe, reusable form.

4.4.1. How do I do that?

In this case, you'll use this feature to look for reservations. To do so, create a new MappingSqlQuery class for each kind of reservation search. Specify parameters for each of the parameters of the query, and then set the types for each of the parameters. As before, you'll specify a method to map each row, with an inner class. Example 4-9 shows the code we have so far.

Example 4-9. JDBCRentABike.java

abstract class FindReservations extends MappingSqlQuery {    protected List reservations = new ArrayList( );    protected FindReservations(DataSource dataSource, String query) {       super(dataSource, query);    }    protected Object mapRow(ResultSet rs, int rownum)        throws SQLException {       int resId = rs.getInt(1);       int bikeId = rs.getInt(2);       int custId = rs.getInt(3);       Date resDate = rs.getDate(4);       Bike bike = getBike(bikeId);       Customer customer = getCustomer(custId);       Reservation reservation = new Reservation(resId, bike,           customer, resDate);       reservations.add(reservation);       return reservation;    }    abstract List findReservations(int param); } class FindReservationsByCustomer extends FindReservations {    public FindReservationsByCustomer(DataSource dataSource) {       super(dataSource,           "SELECT * FROM reservations WHERE custId = ?");       declareParameter(new SqlParameter(Types.INTEGER));        compile( );    }    public List findReservations(int param) {       execute(param);       return this.reservations;    } } class FindReservationsByBike extends FindReservations {    public FindReservationsByBike(DataSource dataSource) {       super(dataSource,           "SELECT * FROM reservations WHERE bikeId = ?");       declareParameter(new SqlParameter(Types.INTEGER));       compile( );    }    public List findReservations(int param) {       execute(param);       return reservations;    } }

You can use the new query whenever you need to query for appointments, as in Example 4-10.

Example 4-10. JDBCRentABike.java

public List getReservations(Customer customer) {        return new FindReservationsByCustomer(dataSource).           findReservations(customer.getCustId( ));     }     public List getReservations(Bike bike) {        return new FindReservationsByBike(dataSource).           findReservations(bike.getBikeId( ));     }

You'll run the code as before.

At this point, you will also add two new domain classes that represent customers and reservations.

Example 4-11. Customer.java

package com.springbook; import java.util.Set; public class Customer {     private int custId;     private String firstName;     private String lastName;     private Set reservations;     public Set getReservations( ) { return reservations; }     public void setReservations(Set reservations)          { this.reservations = reservations; }     public int getCustId( ) { return custId; }     public void setCustId(int custId) { this.custId = custId; }     public String getFirstName( ) { return firstName; }     public void setFirstName(String firstName) { this.firstName = firstName; }     public String getLastName( ) { return lastName; }     public void setLastName(String lastName) { this.lastName = lastName;}     public Customer(int custId, String firstName, String lastName) {         this.CustId = custId;         this.firstName = firstName;         this.lastName = lastName;     }     public Customer( ) {}     public String toString( ) {         return "Customer : " +             "custId -- " + custId +             "\n: firstName --" + firstName +             "\n: lastName --" + lastName +             ".\n";     } }

Example 4-12. Reservation.java

package com.springbook; import java.util.Date; public class Reservation {     private int reservationId;     private Date reservationDate;     private Bike bike;     private Customer customer;     public Reservation( ) {}     public int getReservationId( ) { return reservationId; }     public void setReservationId(int reservationId)          { this.reservationId = reservationId; }     public Date getReservationDate( ) { return reservationDate; }     public void setReservationDate(Date reservationDate)         { this.reservationDate = reservationDate; }     public Bike getBike( ) { return bike; }     public void setBike(Bike bike) { this.bike = bike; }     public Customer getCustomer( ) { return customer;}     public void setCustomer(Customer customer) { this.customer = customer; }     public Reservation(int id, Bike bike, Customer customer, Date date) {         this.reservationId = id;         this.bike = bike;         this.customer = customer;         this.reservationDate = date;     }     public String toString( ) {         return "Reservation : " +             "reservationId -- " + reservationId +             "\n: reservationDate -- " + reservationDate +             "\n: bike -- " + bike +             "\n: customer -- " + customer +             ".\n";     } }

4.4.2. What just happened?

You created a reusable, parameterized package. It's clean and simple. The MapRow method converts each row of the data set into an object. The query then returns a list of bikes in the store that will fit criteria we specify. You created an object hierarchy to allow for two different kinds of reservation queries; to add more (search by date, by customer and date, etc.), you just need to make more subclasses of FindReservations.

4.4.3. What about...

...stored procedures? Most object-heads despise stored procedures, but they're part of the landscape in many places. You can't just hide your head in the sand. Placing a stored procedure in a cleaner object oriented package that maps the results into objects certainly can make them more palatable.

I'll go a step farther. While stored procedures do not provide the cleanest or most elegant code, you can and should use them for spot performance optimizations. A stored procedure can often reduce round trips to the database by more than an order of magnitude. I can recall a performance benchmark that compared DB2 with a set of other databases. My team noticed that DB2 was slower by a factor of eight behind one of our rivals. After several late nights and a few rolls of Tums, we noticed that the benchmarking testing company had used stored procedures for the competition, but not DB2. When the testing laboratory reran the tests, DB2 was once again on top. You don't have to go crazy with them, but never underestimate stored procedures when you're spot-tuning and none of the quicker optimizations are working.