Re: What databases have taught me

From: Robert Martin <unclebob_at_objectmentor.com>
Date: Sun, 2 Jul 2006 19:05:55 -0500
Message-ID: <2006070219055597157-unclebob@objectmentorcom>

On 2006-07-01 00:49:31 -0500, frebe73_at_gmail.com said:

>>> The main problem in OO enterprise applications is the desire to
>>> decouple SQL statements. This causes code bloats.
>> 
>> In those cases where it causes code bloat, it should not be done.  In
>> most cases that I have experience with, decoupling SQL causes a
>> *reduction* in code.

>
> Lets say I want to find all the names of employees older than a given
> age living in a given city. Using embedded SQL I just call:
>
> select name from employees where today()-date_of_birth > ? and city = ?

The ?'s get loaded from somewhere. You might want to show that code too.

>
> Using a decoupled approach I have to do this
>
> findAllEmployeesOlderThanLivingIn(age, city);
>
> [Interface definition]
> findAllEmployeesOlderThanLivingIn(age, city);
>
> [Implementation]
> findAllEmployeesOlderThanLivingIn(age, city)
> {
> select name, and all other columns
> from employees where today()-date_of_birth > ? and city = ?
> result = new Array();
> foreach (row) {
> emp = new Employee(row.name, row.aaa, row.bbb, ....);
> result.add(emp);
> }
> return emp;
> }

The two stretches of code don't do the same thing. the first simply returns the employee name. The second tries to build an employee object.

The first stage of decoupling your SQL statement might be:

RowSet findAllEmployeesOlderThanLivingIn(age, city) {   return execute_sql(
    "select name from employees where today()-date_of_birth > ? and city = ?", age, city);
}

I think you'd need this code anyway if the above query was issued from more than one place. I doubt you'd want to duplicate the string.

The second stage of decoupling would be to separate the question from the implemention that answers it:

interface EmployeeGateway {
  public RowSet findAllEmployeeOlderThanLivingIn(age, city); };

public class EmployeeGatewaySQLImplementation implements EmployeeGateway { interface EmployeeGateway {
  public RowSet findAllEmployeeOlderThanLivingIn(age, city) {     return execute_sql("select name from employees where" +

                       " today()-date_of_birth > ? and city = ?",       
    	                   age, city);

  }
}

> Which is most bloated?

That depends on how many places the query is issued from. If many different modules make that query then the first could be more bloated than the second.

>>> Another disadvantage is the fact that the number
>>> SQL statements should be limited and the the same statement should be
>>> reused in different contexts. This does not only creates problem
>>> writing the code, it causes performance problems.
>> 
>> I'm not sure I understand this.  Typically one constructs an SQL
>> statement from the arguments of some method call.  Such construction is
>> pretty simple.

>
> A very typical scenario is the save method in a DAO. In OO, one
> normally write one save method with one update statement, updating all
> columns regardless they should be updated or not.

If that's a problem, then don't do that. Update only those columns that have changed. One would use a special method in the EmployeeGateway for that purpose.

>

>>> The fact that the
>>> result from the SQL query has to be mapped to a "domain object" also
>>> introduces numerous problems.
>> 
>> It can also solve some.  Indeed, there is no rule that the result of a
>> query must be mapped to an domain object.  We do it when it makes sense.

>
> No rule? Anyway this is all what Hibernate, EJB, JDO is about. Mapping
> SQL results to domain objects.

Yes, but there is no rule that says that there is a 1:1 correspondence between tables and domain objects. One can create small domain objects for limited updates, and largers ones for more comprehsensive udates. Indeed, you can have a domain object for each different kind of query/update transaction if you wish.

-- 
Robert C. Martin (Uncle Bob)  | email: unclebob_at_objectmentor.com
Object Mentor Inc.            | blog:  www.butunclebob.com
The Agile Transition Experts  | web:   www.objectmentor.com
800-338-6716                  |

Received on Sun Jul 02 2006 - 19:05:55 CDT