34.15. Consolidating and Hiding SQL Statements in One Class

Hard-coding SQL statements into different RDB mapper classes is not a terrible sin, but it can be improved upon. Suppose instead:

• There is a single Pure Fabrication class (and it's a singleton) RDBOperations where all SQL operations (SELECT, INSERT, ...) are consolidated.

• The RDB mapper classes collaborate with it to obtain a DB record or record set (for example, ResultSet).

• Its interface looks something like this:

  								class RDBOperations
  								{
  								public ResultSet getProductSpecificationData( OID oid ) {...}
  								public ResultSet getSaleData( OID oid ) {...}
  								...
  								}
  							

So that, for example, a mapper has code like this:

						class ProductSpecificationRDBMapper extends AbstractPersistenceMapper
						{
						protected Object getObjectFromStorage( OID oid )
						{
						ResultSet rs =
						RDBOperations.getInstance().getProductSpecificationData( oid );
						ProductSpecification ps = new ProductSpecification();
						ps.setPrice( rs.getDouble( "PRICE" ) );
						ps.setOID( oid );
						return ps;
						}
					

The following benefits accrue from this Pure Fabrication:

• Ease of maintenance and performance tuning by an expert. SQL optimization requires a SQL aficionado, rather than an object programmer. With all the SQL embedded in this one class, it is easy for the SQL expert to find and work on it.

• Encapsulation of the access method and details. For example, hard-coded SQL could be replaced by a call to a stored procedure in the RDB in order to obtain the data. Or a more sophisticated metadata-based approach to generating the SQL could be inserted, in which SQL is dynamically generated from a metadata schema description read from an external source.

As an architect, the interesting aspect of this design decision is that it is influenced by developer skills. A trade-off between high cohesion and convenience for a specialist was made. Not all design decisions are motivated by “pure” software engineering concerns such as coupling and cohesion.