38.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 getProductDescriptionData( OID oid ) {...}
public ResultSet getSaleData( OID oid ) {...}
...
}
So that, for example, a mapper has code like this:
The following benefits accrue from this Pure Fabrication:
class ProductDescriptionRDBMapper extends AbstractPersistenceMapper
{
protected Object getObjectFromStorage( OID oid )
{
ResultSet rs =
RDBOperations.getInstance().getProductDescriptionData( oid );
ProductDescription ps = new ProductDescription();
ps.setPrice( rs.getDouble( "PRICE" ) );
ps.setOID( oid );
return ps;
}
- 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.