Re: What databases have taught me

> > 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 = ?

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);

Which is most bloated? Still the implementation of Employee is not provided.

The only way decoupling may reduce code is if you try to limit the number of different SQL statements used. The problem with this is that you will select or update values that are not needed for the particular context, or need values are missing, causing additional selects. You will probably end up with having to use lazy-loading to overcome this problem. Performance will also be hurt in most cases.

> > 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. The argument to the method is the value object, but that doesn't mean that all columns really should be updated in every context. The natural approach would be to call update for the column(s) you actually want to change.

> > 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.

Fredrik Bertilsson
http://frebe.php0h.com Received on Sat Jul 01 2006 - 07:49:31 CEST