Bean Curd 2: The SQL

Introduction

"No one appreciates the very special genius of your conversation as a dog does."

align=left vspace=4 hspace=16 border=0 />
In "Bean Curd 1"
I introduced an "explicit properties" approach, where bean properties are declared
in an "explicit bean info" class. Actually the bean info class and property descriptors
are absorbed into the components bound to the bean, for convenience.

In that example, the bean info was absorbed into a Swing table model, and the property
descriptors were absorbed into its table columns.

The property descriptor objects are configured using their own Field.
In particular, the field name is used to deduce the default property name,
and also further settings are extracted from annotations.

This avoids using string literals to refer to properties. I argue against such references
and in favour of "stringless applications" in "Explicit Reflection",
"Refactoring Translations"
and of course "Bean Curd 1".

In this article, we address the worst pile of string literal references in the world.
The database query! It's the messiest, most important integration of
another language into Java. (Apart from XML, which JAXB handles quite brilliantly.)

In the ORM example presented here, our bean is an "entity bean" eg. representing
a row from a database table. The our property descriptors are
declared in "data access objects" which double up as "explicit bean info" classes.

We then use these bean info's and their explicit property descriptors to reference
our database tables and columns in "native database queries." These are
pure Java representations of SQL queries.

In this case,
our queries are readily toolable (eg. using the
IDE's prompting, auto-completion and error-detection capabilities),
and our ORM is refactorable, eg. we can freely and safely rename our column mappings
without breaking our queries, using IDE refactorings.

Incidently, I used this approach on one of the biggest (and definitely the messiest)
databases I have ever encounted. It had a few hundred tables, although I only needed to
map around 100 of them for the queries I implemented. The biggest single report was
collated from about a dozen distinct queries involving two dozen tables, running
concurrently on up to 130 distributed databases across the UK. It was so much
fun that it shouldn't have been legal! ;)

align=right vspace=4 hspace=16 border=0 />

Entity beans

"No matter what you've done wrong, always try to make it look like the dog did it."

Butch, our CIO, recently decided that we gotta rewrite our Top Dog club
membership system. Because he was running with a dog who upgraded their
system from some legacy language to this is new Java thing, and now we gotta
do the same. Because all the puppies are being trained in Java these days, so
we gonna be in the dog pound, baby!

So check it out, dog. We implement an "entity bean" for loading data from a database
table into our application. For illustration, we throw in some EJB3 annotations,
even though this is not a POJO. I haven't made the inevitable move to Glassfish Java Persistence
yet, so please excuse any errors, omissions and lies.

@Entity
@Table(name = "dog")
<b>public class</b> ZDog <b>extends</b> MEntityBean&lt;ZEntityManager&gt; {

   <b>protected</b> String dogId;
   <b>protected</b> String dogName;
   <b>protected</b> String username;
   <b>protected</b> String password;    
   <b>protected</b> ZDogHouse dogHouse;
   <b>protected</b> String dogHouseId; // id of the above dogHouse object
   <b>protected</b> List&lt;ZDogRole&gt; dogRoleList = <b>null</b>;
   ... <i>// other fields </i>
    
   <b>public</b> ZDog() {
   }
   
   <b>public</b> Comparable[] getId() {
      <b>return new</b> Comparable[] {dogId};
   }

   <b>public</b> String getLabel() {
      <b>return</b> dogName;
   }
   
   @Id
   @Column(name = "dog_id")
   <b>public</b> String getDogId() {
      <b>return</b> dogId;
   }

   @ManyToOne(optional = <b>false</b>) 
   @JoinColumn(name = "house_id", nullable = <b>false</b>, updatable = <b>false</b>)
   <b>public</b> ZDogHouse getDogHouse() {
      <b>if</b> (dogHouse == <b>null</b> && dogHouseId != <b>null</b>) {
         dogHouse = entityManager.dogHouse.getEntityBean(dogHouseId);
      }   
      <b>return</b> dogHouse;
   }

   <b>public void</b> setDogHouse(ZDogHouse dogHouse) {
      <b>if</b> (dogHouse != <b>null</b>) {
         dogHouseId = dogHouse.getDogHouseId();
      }   
      <b>this</b>.dogHouse = dogHouse;
   }   
   
   @OneToMany(targetEntity = ZDogRole.<b>class</b>, cascade = CascadeType.ALL)
   @ElementJoinColumn(name = "dog_id")
   <b>public</b> List&lt;ZDogRole&gt; dogRoleList getDogRoleList() {
      <b>if</b> (dogRoleList == <b>null</b>) {
         dogRoleList = entityManager.dogRole.getDogRoleListByDog(dogId);
      }   
      <b>return</b> dogRoleList;
   }
    
   ... <i>// other getters and setters </i>
}

Notice that we use a naming convention where "framework" classes start with the letter M,
and application classes with the letter Z. Incidently, the M comes from
meme.dev.java.net which is an implementation-in-progress
of the approach presented here. And Z is just such a cool letter, so...

Our MEntityBean superclass might declare the getId() and
getLabel() methods as abstract. The getId() implementation returns
the primary keys of this entity bean.
The superclass can then implement a compareTo() method for us, using getId()
to get the comparative values.

The getLabel() method returns
the string representation used to render the object, eg. in a JLabel, combo box, or JTable
cell renderer. So its toString() method is free to be used for another
purpose, eg. logging.

Notice that we illustrate code for lazily fetching referenced entities, using our "entity manager"
which will be introduced later. When using JDBC rather than a sophisticated ORM engine that enhances our classes,
this is handy for avoiding null pointer exceptions. Incidently, in this case we should implement
caching in our entity manager, eg. a HashMap using getId() for the keys.

Dog Access Object

"Some days you're the dog; some days you're the fire hydrant."

I like the DAO pattern. It abstracts our persistence mechanism, eg. JDBC, JDO or EJB3.
This is attractive to me because I have a habit of doing my own thing with JDBC, but also have a
JDO comfort-zone from a previous project, and JPOX2 looks great.
And of course I love the look of the new minty Java Persistence API and Glassfish,
with its annotations. Because I like configuring stuff in my favourite
language, that my IDE fully understands and helps me every step of the way. That is to say,
in Java rather than in XML. If you ever catch
me using XML when I could be using Java, please shoot me! ;)

The DAO pattern suggests an interface. Personally
I find interfaces a problem for rapid prototyping, because I can't Alt-G into the
implementation in Netbeans. You get taken to the interface declaration, which is
a dead-end.

Considering that we never follow anything to the letter, let's introduce a concrete
"entity info object" to start with, as below. This is our "data access object."
Later we might split out an abstract superclass, to allow different implementations.

<b>public class</b> ZDogInfo <b>extends</b> MTable&lt;ZDog&gt; {
    
   @EntityPropertyAnnotation(label = "Social Security Collar Number", length = 14)
   MEntityProperty dogId = createPrimaryColumn("dog_id");
        
   @EntityPropertyAnnotation(label = "Real Name", length = 40, displayLength = 20)
   MEntityProperty dogName = createColumn("dog_name");

   @EntityPropertyAnnotation(label = "Puppyday", format = "yyyy-MM-dd")
   MEntityProperty birthDate = createColumn("birth_date");

   @EntityPropertyAnnotation(label = "Dog House Id", displayWidth = 100)
   MEntityProperty dogHouseId = createForeignId("house_id");

   @EntityPropertyAnnotation(label = "Dog House", displayWidth = 150)
   MEntityProperty dogHouse = createForeignReference(dogHouseId);
      
   @EntityPropertyAnnotation(label = "Roles", cascadeRead = <b>true</b>)
   MEntityProperty dogRoleList = createOneToManyCollection(dogId);

   ... // other explicit properties

   <b>protected void</b> configure() {
      dogHouseId.setReferentialConstraint(entityManager.dogHouse.dogHouseId);
      ... // other configuration settings
   }

   <b>public</b> List&lt;ZDog&gt; getOldDogs(int dogYears) {
      ... // select dogs where age &gt; dogYears
   }
   
   ... // other query methods    
}

So in our entity info class, we explicitly declare our entity bean property descriptors,
customised for ORM, as MEntityProperty objects.

Note that ORM frameworks create such objects, eg. from XML configuration files, or annotations. However, we choose to expose them explicitly in our application, as above.

align=right vspace=4 hspace=16 border=0 />
As in "Bean Curd 1",
we deduce the property name from the field name, rather than using string literal references.
That is, the dogName property descriptor field in ZDogInfo, is bound to the
dogName property in the ZDog bean, as exposed by the getDogName() accessor.

In addition to annotations, we have other metadata configuration options, notably
a variety of superclass methods with overloaded arguments eg. createColumn()
to which the crucial SQL column name is passed as an argument.
Also the bean property descriptors might be further configured directly.
For example, in the configure() method above,
we invoke setReferentialConstraint() on the property descriptor.

Clearly we have more than enough rope here to configure ourselves. So it's probably best
to use standard EJB3 annotations on the entity bean, and then
complement those here in the entity info to suit ourselves.

Refactoring the ORM

"Did you ever walk into a room and forget why you walked in? That happens to dogs all the time."

The entity info objects reflect on their MEntityProperty fields.
These objects resolve their java.beans.PropertyDescriptor using their
own field name as the implicit property name, eg. dogName.

So when renaming a property in the entity bean ie. the accessor and mutator methods
eg. getDogName(), we have to take care to rename the explicit property
descriptor field in the entity info as well eg. dogName, and visa versa.

This does not detract significantly from refactorability,
since it is a single trivial operation, eg. using Netbeans' refactorings. And if we neglect
to do this, we will be reminded immediately when the application is run, courtesy of an exception.

Entity manager

"The difference between cats and dogs is, dogs come when called,
cats take a message and get back to you."

We expose our entity info objects in a central entity manager as below.

<b>public class</b> ZEntityManager <b>extends</b> MEntityManager&lt;ZEntityManager&gt; {
   <b>public final</b> ZDogInfo dog = <b>new</b> ZDogInfo();
   <b>public final</b> ZDogRoleInfo dogRole = <b>new</b> ZDogRoleInfo();
   <b>public final</b> ZDogHouseInfo dogHouse = <b>new</b> ZDogHouseInfo();
   <b>public final</b> ZRegionInfo region = <b>new</b> ZRegionInfo();
   <b>public final</b> ZOrganisationInfo organisation = <b>new</b> ZOrganisationInfo();
   ... <i>// other entity info's</i>    

   <b>public</b> ZEntityManager() {
      configureReferentialConstraint(dog.dogHouseId, dogHouse.dogHouseId);
      configureReferentialConstraint(dogHouse.regionId, region.regionId);
      ...// more configuration
   }
   
   ... <i>// maybe some query methods</i>
}

All the entity info's are exposed here. So this entity manager represents our database schema.

We might perform some further configuration here, eg.
specifying foreign key constraints, as in the above constructor.
I find it convenient to configure these constraints here,
rather than in annotations in our entity beans,
or entity info objects.

When this class is instantiated, all the entity info's are created. Therefore if there is any missing property
or misnamed property descriptor, in any entity info, we'll know about it
immediately, via an exception as soon as we run the application, if not via a unit test before that.

Native queries

"That fallacy about teaching old dogs new tricks... is so often true."

We use the entity info objects (exposed by our entity manager) and their explicit properties,
to reference our database tables and columns in "native queries" as follows.

<b>public class</b> ZFidoQueryManager <b>extends</b> ZEntityManager {
   
   <b>public</b> List&lt;ZDog&gt; findFido(String regionId) {
      MSelectQuery&lt;ZResultRow&gt; query = createSelectQuery();
      query.<b>setTitle</b>("Fido's in region %s", regionId);
      query.<b>selectAll</b>(dog);
      query.<b>selectExclude</b>(dog.address);
      query.<b>select</b>(organisation.organisationId, organisation.organisationName);
      query.<b>join</b>(dog, organisation, region);
      query.<b>whereEquals</b>(region.regionId, regionId); 
      query.<b>whereStartsWithIgnoreCase</b>(dog.dogName, "fido"); 
      query.<b>whereIsNotNull</b>(organisation.organisationName); 
      query.<b>whereIs</b>(organisation.active, <b>true</b>); 
      query.<b>orderBy</b>(organisation.organisationId, dog.dogName); 
      query.<b>limit</b>(100); 
      query.<b>execute</b>(); 
      query.<b>sectionBy</b>(region.regionId, region.regionLabel);
      query.<b>calculateAverage</b>(dog.age);
      query.<b>calculateTotal</b>(dog.dependents);
      query.<b>writeResult</b>(<b>new</b> MResultPdfWriter("/tmp/fido.pdf"), <b>new</b> ZDoggyStyle()); 
      List&lt;ZDog&gt; dogList = <b>new</b> ArrayList();
      <b>while</b> (query.<b>next</b>()) {
         ZResultRow resultRow = query.<b>getResultRow</b>(ZResultRow.<b>class</b>);
         result.dog.setOrganisation(resultRow.organisation);
         dogList.add(resultRow.dog);
         logger.finer(resultRow.dog.getDogName());
      }      
      <b>return</b> dogList;
   }
   
   ... <i>// other queries </i>       
}

Notice, no string literals! Those strings that are there, shouldn't be. In particular, the
title should be in a resource bundle, and the file name (and "fido") should not be hard-coded like this.

So the really cool thing is that these native queries are readily toolable.
For example, we can type "dog.dep" and press Control-Space,
and our IDE will auto-complete "dog.dependents." Also we can type "dog." and press Control-Space,
and the IDE shows us all the column names, in neat Java notation. This is great for the induction
of new developers. "Who needs ER diagrams? Not us! We got real tools, dog."

align=left vspace=4 hspace=16 border=0 />
Which brings us to the most important point. So we map our database to nice Java names (in our entity beans).
The problem is that as soon as we use the mapped names in string queries (eg. OQL,
EJBQL, HQL), then we immediately lose refactorability. It becomes
impossible to fix up spelling errors and naming inconsistencies, without
breaking our queries. "Thaaat's mentil!"
And if you ignore a few broken windows, the next thing the whole building is run down, innit.

Our native queries are pretty much Java versions of the equivalent SQL code.
Consequently, generating the SELECT SQL from the above select query object
is relatively trivial, as we'll see later.
This close correlation to SQL also makes our native queries immediately understandable to
developers familiar with SQL.

As you can imagine, we use variable-length arguments extensively. Also, we use auto-boxing and method overloading,
particularly in the where methods. For example, whereEquals(), whereLessThan() et al,
should support comparing a column value to another column, or to a Java number, String, Date or boolean value,
eg. passed as an argument to our query method, eg. findFido(regionId) in the above example.

We can add additional functionality to our native queries fairly easily.
"It's our bone and we gonna chew the hell out of it." For example,
we can specify a title, sectioning (eg. by region in the above example),
columns to be totalled and averaged (eg. by section, and/or a grand total),
a style template, and write the output of the query to HTML, PDF and/or Excel.
All this with a few extra lines of code, as in the above example. This is handy for
generating "run of the mill" reports in record time. In theory, that is.
And "in theory there is no difference between theory and practice" so...

Traversing the result set

"To his dog, every man is Napoleon; hence the constant popularity of dogs."

The trick we use in the above native query is to populate a "result row" object
via reflection, where this class might be implemented as follows.

<b>public class</b> ZResultRow <b>implements</b> MResultRow {
   <b>public</b> ZDog dog;  
   <b>public</b> ZDogHouse dogHouse;  
   <b>public</b> ZDogRole dogRole;  
   <b>public</b> ZOrganisation organisation;  
   <b>public</b> ZRegion region;  
   ... // other entity bean declarations
   
   <b>public void</b> resolveReferences();  
      if (dog != <b>null</b> && dogHouse != <b>null</b>) dog.setDogHouse(dogHouse);
      ... // resolve other references
   }
}

align=right vspace=4 hspace=16 border=0 />
In our query.getResultRow() method, for each of the entity beans
declared in ZResultRow above, that are included in the query, we create that entity bean using newInstance(),
and set those values which are selected in the query, from the current row, using reflection.

As such, some the above references will be null, and those values which are not selected
in the query, will remain unset. However, this is the nature of relational database queries
for business intelligence reports, ie. we are selecting specific limited data
from a large dataset. We are not constructing a object graph per se,
or navigating through persistent connected objects as one might do
using an object database.

In the case of expressions, eg. SUM(dog.dependents), we can extract these
from the current row using a reference to the expression object.
Or alternatively, we can use the first operand to reference the expression indirectly,
eg. getIntResultByOperand(dog.dependents). I found that this latter approach
sufficed for my purposes.

As illustrated in the resolveReferences() method above, we might "resolve references" manually.
Alternatively, we can resolve them when we iterate through the query result set, as shown further above,
eg. resultRow.dog.setDogHouse(resultRow.dogHouse). Or even easier, we can use metadata from our entity info's to resolve them
automatically.

This obviates all those nasty JDBC getString("dog_id") methods. And I cannot tell
you how happy that makes me. Because we
don't like those, and you know we don't like string literal references. So we roll
over backwards and jump through hoops, not to use them.

Implementation

"If dogs could talk, that would take a lot of the fun out of being a dog."

Let's chew on the implementation, and see how we might spit out the SQL.

In our entity manager, we might implement methods for the convenience of our queries, as follows.

<b>public class</b> MEntityManager {
   ...
   
   <b>public</b> MCondition or(MCondition ... conditions) {
      <b>return new</b> MOr(conditions);
   }

   <b>public</b> MCondition startsWith(MEntityProperty column, String value) {
      <b>return new</b> MEquals(column, <b>new</b> MStringLiteralValue(value + "%"));
   }

   <b>public</b> MCondition equalsNumeric(MExpression expression, Object value) {
      <b>return new</b> MEquals(expression, <b>new</b> MNumericLiteralValue(value));
   }

   <b>public</b> MCondition is(MExpression expression, Boolean value) {
      <b>return new</b> MIs(expression, value); // "IS TRUE" or "IS FALSE"
   }

   <b>public</b> MCondition in(MExpression expression, MSelectQuery subselect) {
      <b>return new</b> MInSelect(expression, subselect);
   }
   
   <b>public</b> MCondition equalsExpression(MExpression expression, MExpression rightExpression) {
      <b>return new</b> MEquals(expression, rightExpression);
   }
   
   <b>public</b> MExpression sum(MExpression ... expressions) {
      <b>return new</b> MSum(<b>new</b> MAdd(expressions));
   }

  <b>public</b> MExpression max(MExpression expression) {
      <b>return new</b> MMax(expression);
   }
    
   <b>public</b> MExpression monthPart(MExpression expression) {
      <b>return new</b> MMonthPart(expression);
   }
   
   ... <i>// other convenience methods</i>    
}

Let's start digging into the above types, notably MExpression and MCondition.

Expressions

"From the dog's point of view, his master is an elongated and abnormally cunning dog. "

Our MExpression interface is implemented by expressions that we
might SELECT, or use in conditional WHERE clauses, or other clauses,
eg. ORDER BY.

<b>public interface</b> MExpression {
   <b>public</b> String emitExpression();
}

Certainly we will want to select database columns. These are declared in our entity info's as "entity properties,"
and so those implement MExpression, as shown below.

<b>public class</b> MEntityProperty <b>implements</b> MExpression {
   <b>protected</b> MPropertyDescriptor propertyDescriptor; <i>// embedded property descriptor wrapper</i>
   <b>protected</b> String columnName; <i>// SQL name eg. "dog_id" </i>  
   ...
   <b>public</b> String emitExpression() {
      <b>return</b> columnName;
   }
}

So that was easy. We just emit the SQL column name of the property. "That's too simple, there must be a catch."

Oops, I forgot the table name. Let's correct that in T minus zero, ie. now.

<b>public class</b> MEntityProperty <b>implements</b> MExpression {
   <b>protected</b> MPropertyDescriptor propertyDescriptor; <i>// embedded property descriptor wrapper</i>
   <b>protected</b> String columnName; <i>// SQL name eg. "dog_id" </i>
   <b>protected</b> MEntityInfo entityInfo; // parent bean info, eg. ZDogInfo
      ...
   <b>public</b> String emitExpression() {
      <b>return</b> entityInfo.getTableReferenceName() + "." + columnName;
   }   
}

For each entity that maps to a table, we assign an SQL "table reference name" which
by default equals the table name. This enables us to select multiple instances from the same table, using different references,
eg. "FROM region region1, region region2".
To achieve this we declare multiple instances of the entity info, with different table reference names, as follows.

<b>public class</b> ZEntityManager <b>extends</b> MEntityManager&lt;ZEntityManager&gt; {
   <b>public final</b> ZDogInfo dog = <b>new</b> ZDogInfo();
   <b>public final</b> ZDogRoleInfo dogRole = <b>new</b> ZDogRoleInfo();
   <b>public final</b> ZDogHouseInfo dogHouse = <b>new</b> ZDogHouseInfo();
   <b>public final</b> ZRegionInfo region = <b>new</b> ZRegionInfo();
   <b>public final</b> ZOrganisationInfo organisation = <b>new</b> ZOrganisationInfo();
   ...
   <b>public final</b> ZRegionInfo regionOfDogHouse = <b>new</b> ZRegionInfo("region_of_dog_house");
   <b>public final</b> ZRegionInfo regionOfOrganisation = <b>new</b> ZRegionInfo("region_of_organisation");
   ... 
   <b>public</b> List&lt;ZDog&gt; selectDogsLivingInRegionLabel(String regionLabel) {
      MSelectQuery&lt;ZResultRow&gt; query = createSelectQuery();
      query.<b>selectAll</b>(dog, dogHouse, organisation);
      query.<b>selectAll</b>(regionOfDogHouse, regionOfOrganisation);
      query.<b>join</b>(dog, organisation, regionOfOrganisation);
      query.<b>join</b>(dogHouse, regionOfDogHouse);
      query.<b>whereEquals</b>(regionOfDogHouse.regionLabel, regionLabel);
      query.<b>orderBy</b>(dog.dogName);
      ...
   }
   ...
}  

In the above example, we introduce regionOfDogHouse and regionOfOrganisation, with distinguishing table
reference names, in order to support selecting multiple ZRegion instances in a single query. We would
also need to declare these in ZResultRow to get at them.

Composite Expressions

"You can say any foolish thing to a dog, and the dog will give you this look that says, 'My God, you're right! I never would've thought of that!'
"

Consider the "sum of" expression implementation
below.

<b>public class</b> MSum <b>implements</b> MExpression {
   <b>protected</b> MExpression expression;
   
   <b>public</b> MSum(MExpression expression) {
      <b>this</b>.expression = expression;
   }
   
   <b>public</b> String emitExpression() {
      <b>return</b> "SUM(" + expression.emitExpression() + ")";
   }
}

align="left" hspace="16" border="0" />
As you can see, it's not exactly rocket science. We can knock these things off for a dime a dozen, innit.
For example, MMax, MMin and MCount.
And, if it suits us, have convenience methods to create such expressions in our
MEntityManager as shown above, so that our queries look more naturally like SQL.
Which is our goal... Sorry, I might have forgotten to mention that?

Literal values

"Dogs, the foremost snobs in creation, are quick to notice the difference between a well-clad and a disreputable stranger."

We must also support literal expressions. Let's introduce an empty interface to identify our literals.

<b>public interface</b> MLiteralValue <b>extends</b> MExpression {
}

Now let's crunch the numbers.

<b>public class</b> MNumericLiteralValue <b>implements</b> MLiteralValue {
   <b>protected</b> Object value;
   
   <b>public</b> MNumericLiteralValue(Object value) {
      <b>this</b>.value = value;
   }
   
   <b>public</b> String emitExpression() {
      <b>if</b> (value == <b>null</b>) {
        <b>throw new</b> MQueryRuntimeException("null number - mental!");
      }
      <b>return</b> value.toString();
   }
}

For text literals, the main difference is that we must use single quotes, as below.

<b>public class</b> MStringLiteralValue <b>implements</b> MLiteralValue {
   <b>protected</b> String value;
   
   <b>public</b> MStringLiteralValue(String value) {
      <b>this</b>.value = value;
   }
   
   <b>public</b> String emitExpression() {
      <b>return</b> "'" + value + "'";
   }
}

For date literals, we must also use single quotes. Oh, and format the date correctly.

<b>public class</b> MDateLiteralValue <b>implements</b> MLiteralValue {
   <b>protected static</b> MContext context = MContext.getInstance();
   
   <b>protected</b> Date value;

   <b>public</b> MDateLiteralValue(Date value) {
      <b>this</b>.value = value;
   }
   
   <b>public</b> String emitExpression() {
      <b>return</b> "'" + context.queryDateFormat.format(value) + "'";
   }
}

align=right vspace=4 hspace=16 border=0 />
We must also support timestamps, and be cognisant of the difference between dates and timestamps,
in particular in comparisons. For example, "WHERE timestamp BETWEEN begin_date AND end_date"
might be broken because the end date will be converted to a timestamp at the beginning of the
day (ie. "00:00" in the hit series "24"). This makes the right comparison exclusive, whereas if the timestamp
was a date type, it would inclusive. Yes, you guessed right, I got bitten on the bum by this.

In order to support a different SQL dialect, or even a different query language eg. EJB QL,
our native query objects might delegate to an interface eg. MQueryLanguageFormatter (via MContext),
which might be implemented differently by MPostgresFormatter, MFireflyFormatter,
MJavaPersistenceFormatter etcetera.

Conditions

"A dog teaches one fidelity, perseverance, and to turn around three times before lying down."

Now let's consider conditions, which are used primarily in our WHERE clause,
but also in the HAVING clause (which can be used together with GROUP BY).

Firstly, we introduce an interface for conditions.

<b>public interface</b> MCondition {
   <b>public</b> String emitCondition();
}

Consider the following implementation of a comparitive condition.

<b>public class</b> MGreaterThanInclusive <b>implements</b> MCondition {
   <b>protected</b> MExpression leftExpression;
   <b>protected</b> MExpression rightExpression;
   
   <b>public</b> MGreaterThanInclusive(MExpression leftExpression, MExpression rightExpression) {
      <b>this</b>.leftExpression = leftExpression;
      <b>this</b>.rightExpression = rightExpression;
   }
   
   <b>public</b> String emitCondition() {
      <b>return</b> leftExpression.emitExpression() + " >= " + rightExpression.emitExpression();
   }
}

The above is a typical condition, with two operands. The MBetween condition
takes three expressions. MIsNull, MIsNotNull, MIsTrue, and MIsFalse take one.
But their implementation is otherwise much like the above - in a word, trivial.

Let's consider one more comparitive example.

<b>public class</b> MIs <b>implements</b> MCondition {
   <b>protected</b> MExpression expression;
   <b>protected</b> Boolean value;
   
   <b>public</b> MIs(MExpression expression, Boolean value) {
      <b>this</b>.expression = expression;
      <b>this</b>.value = value;
   }
   
   <b>public</b> String emitCondition() {
      <b>if</b> (value == <b>null</b>) <b>return</b> expression.emitExpression() + " IS NULL";
      <b>if</b> (value) <b>return</b> expression.emitExpression() + " IS TRUE";
      <b>return</b> expression.emitExpression() + " IS FALSE";
   }
}

It's probably safer to throw an exception if the boolean value is null, and so we should rather use
MIsNull to test for that.

Composite conditions

"Dogs listen to you while you talk about yourself, and keep up an appearance of being interested in the conversation."

In following example, we use varargs to specify multiple conditions to be OR'ed together.

<b>public class</b> MOr <b>implements</b> MCondition {
   <b>protected</b> MCondition[] conditions;
   
   <b>public</b> MOr(MCondition ... conditions) {
      <b>this</b>.conditions = conditions;
   }
   
   <b>public</b> String emitCondition() {
      StringBuffer buffer = <b>new</b> StringBuffer();      
      <b>for</b> (MCondition condition : conditions) {
          if (buffer.length() &gt; 0) buffer.append(" OR ");
          buffer.append(condition.emitCondition());
      }
      <b>return</b> "(" + buffer.toString() + ")";
   }
}

Another composite condition is of course, MAnd. And we will see later that
we AND together multiple conditions specified by where() method invocations
on our select query.

Select query

"When in a car, dogs suddenly feel the need to bark violently at nothing, right in your ear."

Let's now consider the select query object, woohoo!

<b>public class</b> MSelectQuery <b>extends</b> MSqlStatement {
   <b>protected</b> List&lt;MExpression&gt; selectList = <b>new</b> ArrayList();
   <b>protected</b> List&lt;MSource&gt; fromList = <b>new</b> ArrayList();
   <b>protected</b> List&lt;MCondition&gt; whereList = <b>new</b> ArrayList();
   <b>protected</b> List&lt;MExpression&gt; groupByList = <b>new</b> ArrayList();
   <b>protected</b> List&lt;MCondition&gt; havingList = <b>new</b> ArrayList();
   <b>protected</b> List&lt;MExpression&gt; orderByList = <b>new</b> ArrayList();
   <b>protected</b> List&lt;MExpression&gt; totalList = <b>new</b> ArrayList();
   <b>protected</b> List&lt;MExpression&gt; sectionByList = <b>new</b> ArrayList();
   ...
   
   <b>public void</b> select(MExpression ... expressions) {
      selectList.addAll(Arrays.asList(expressions));
   }
   
   <b>public void</b> selectExclude(MExpression ... expressions) {
      selectList.removeAll(Arrays.asList(expressions));
   }
   
   <b>public void</b> selectAll(MTable ... tables) {
      <b>for</b> (MTable table : tables) {      
         selectList.addAll(table.getColumnList());
      }
   }
   
   ... // very many more methods
}

There are of course no limits to the conveniences we can incorporate into the above
select query object. So we can make SQL and other query languages look relatively
pedestrian to boot, besides being untoolable in our IDEs.

For instance, we know that all the expressions in the GROUP BY clause must
also be in the SELECT. So we introduce a method selectGroupBy()
to catch two birds with one jump, as below.

   <b>public void</b> selectGroupBy(MExpression ... expressions) {
      selectList.addAll(Arrays.asList(expressions));
      groupByList.addAll(Arrays.asList(expressions));
   }

This is especially handy when our GROUP BY expressions are complicated.

align=left hspace=16 />
Also, we can introduce decorative functionality into our select query,
which is related to printing the result. For instance, our selectTotal() method,
indicates that we should total that column when we print the result set.
Also, our selectSectionBy() method indicates that we should
break the output into sections, and show those columns in the section headings,
and not in the lines, eg. selectSectionBy(regionId, regionLabel).

   <b>public void</b> selectTotal(MExpression ... expressions) {
      selectList.addAll(Arrays.asList(expressions));
      totalList.addAll(Arrays.asList(expressions));
   } 
  
   <b>public void</b> selectSectionBy(MExpression ... expressions) {
      selectList.addAll(Arrays.asList(expressions));
      sectionByList.addAll(Arrays.asList(expressions));
   }   

Consequently, our select query object suffices for most reports that involve
a single select query only. And without any compromise on the presentation quality,
eg. titles, sections, column headings, formatting and styling.

Throwing SQL out of the house

"A door is what a dog is perpetually on the wrong side of."

But how are we ever going to execute this query? Actually we find that
is it quite easy to emit our SQL in our MSelectQuery class. Check it out, dog.

   <b>public</b> String emitQueryString() {
      validate();
      StringBuffer query = <b>new</b> StringBuffer();
      query.append("SELECT ");
      StringBuffer buffer = <b>new</b> StringBuffer();      
      <b>for</b> (MExpression expression : selectList) {
          <b>if</b> (buffer.length() &gt; 0) buffer.append(", ");
          buffer.append(expression.emitExpression());         
      }
      query.append(buffer);
      query.append("\n FROM ");
      buffer = <b>new</b> StringBuffer();      
      <b>for</b> (MSource source : fromList) { // tables and joins 
         source.emitFrom(buffer);
      }
      query.append(buffer);
      query.append("\n WHERE ");
      buffer = <b>new</b> StringBuffer();      
      <b>for</b> (MCondition clause : whereList) {
          <b>if</b> (buffer.length() &gt; 0) buffer.append("\n AND ");
          buffer.append(clause.emitCondition());
      }
      query.append(buffer);
      ... // GROUP BY, HAVING, ORDER BY
      <b>if</b> (limit != 0) query.append("\n LIMIT " + limit);
      <b>return</b> query.toString();
   }

align=right vspace=4 hspace=16 border=0 />

Notice that might implement a method validate(), to detect obvious
errors in the query before we even try to formulate and execute the SQL.
For instance, we might have selected columns from a table that has not
been specified in the FROM/JOIN clauses. That's like throwing
the frisbee straight into the ground, which is no fun.

In order to support different SQL databases, we would implement the above method
in a separate delegate class, eg. MPostgresSqlWriter. Also, we
might support other query languages, eg. via MGlassfishJavaPersistenceQueryWriter.

Constructing the SQL in the above method is easy, hey! Handling the JOIN clauses
turns out to be the hardest part, but is still easy. Let's check it out...

From tables to joins

"Labradors are lousy watchdogs. When there is a stranger about, they bark - with unmitigated joy at the chance to meet somebody new."

We use MSource objects for our FROM/JOIN clauses.

<b>public interface</b> MSource {
   <b>public void</b> emitFrom(StringBuffer buffer);
}

Since we use the entity info objects (exposed in our entity manager) as "table references," their MTable superclass implements MSource.

<b>public class</b> MTable <b>implements</b> MSource {
   <b>protected</b> String tableName; // SQL table name
   <b>protected</b> String tableReferenceName; // eg. "region_of_dog_house"
   ...   
   <b>public void</b> emitFrom(StringBuffer buffer) {
      <b>if</b> (buffer.length() > 0) buffer.append(", ");
      buffer.append(tableName);
      <b>if</b> (!tableReferenceName.equals(tableName)) {
         buffer.append(" " + tableReferenceName);
      }
   }
}

Finally, join objects are also of the MSource type.

<b>public abstract class</b> MJoin <b>implements</b> MSource {
   <b>protected</b> MTable rightTable;
   <b>protected</b> MCondition joinCondition;
   <b>protected</b> List&lt;MEntityProperty[]&gt; columnPairList = <b>new</b> ArrayList();
   ...   
   <b>public void</b> add(MEntityProperty leftColumn, MEntityProperty rightColumn) {
      columnPairList.add(<b>new</b> MEntityProperty[] {leftColumn, rightColumn});
   }
   
   <b>public String</b> emitJoinCondition() {
      StringBuffer buffer = <b>new</b> StringBuffer();      
      <b>for</b> (MEntityProperty[] columnPair : columnPairList) {
         <b>if</b> (buffer.length() > 0) buffer.append(" AND ");
         buffer.append(columnPair[0].emitExpression() + " = " + columnPair[1].emitExpression());
      }
      <b>return</b> buffer.toString();
   }
   
   <b>public abstract void</b> emitFrom(StringBuffer buffer);   
}

Remember that we have pre-configured our foreign key constraints eg. in ZEntityManager further above.
Typically, this enables our joins to be expressed very concisely in our select query,
eg. join(dog, dogHouse, region), ie. without having to specify the join columns.

For more complicated cases of joining using columns from two or more other tables, we
provide convenience methods in MSelectQuery for explicitly indicating which column pairs
to use in the join, and/or a further join MCondition might be specified.

But typically we can deduce the columns from the foreign constraints.
For example, we know to use dogHouseId to handle join(dog, dogHouse),
ie. "FROM dog JOIN dog_house ON (dog.house_id = dog_house.house_id)."

We might emit the JOIN SQL as follows.

<b>public class</b> MLeftJoin <b>extends</b> MJoin {
   ...
   <b>public void</b> emitFrom(StringBuffer buffer) {
      buffer.append("\n LEFT JOIN " + rightTable.getTableName());
      buffer.append(" ON (" + <b>super</b>.emitJoinCondition() + ")");
   }
}   

We take care to make our SQL output look neat, and to be as concise as possible. For example,
in the above method, we should try to use a JOIN USING if possible, eg.
"JOIN dog_house USING (house_id)" when the column names are the same on both
sides of the join.

So joins are a walk in the park. Which is what I wanna do sooner rather than later, so let's wrap this up sooner.
So I'm not going to talk about sub-selects. Because I forget exactly how I handled them before,
and don't want to remember because then I'll be tempted to include a discussion on them here and now.
Darn! I just remembered. OK, lemme try...

Loose endings

"Things that upset a terrier may pass virtually unnoticed by a Great Dane."

Of course we must support sub-selects, eg. "WHERE type IN (SELECT type FROM types WHERE ...)
AND id IN (SELECT MAX(id) FROM ...)".

<b>public class</b> MInSelect <b>extends</b> MCondition {
   <b>protected</b> MExpression expression;
   <b>protected</b> MSelectQuery subselect;
   
   <b>public</b> MInSelect(MExpression expression, MSelectQuery subselect) {
      <b>this</b>.expression = expression;
      <b>this</b>.subselect = subselect;
   }
   
   <b>public</b> String emitCondition() {
      <b>return</b> expression.emitExpression() + " IN (" + subselect.emitQueryString() + ")";
   }
}   

That should work! I knew it was easy. Just remembering it was the hard part.

Which reminds me, we should also implement INSERT, UPDATE and DELETE queries.
Compared to the MSelectQuery, they are easy.
And that's all I'm gonna remember about them right now!

Except to say, UPDATE and DELETE implement FROM, JOIN and WHERE exactly like SELECT of course.
So MSelectQuery, MUpdateQuery and their other friend MDeleteQuery, might all extend MQuery, with common support put there for FROM, JOIN and WHERE.

Netbeans, the adhoc query tool

"Whoever said you can't buy happiness forgot little puppies."

align=left vspace=4 hspace=16 />
I found that writing native queries eg. using Netbeans, was very convenient.
I could keep adding new methods, which was fun. And also the auto-completion of column names, and neat
column names at that, was such a pleasure. Especially when the database is big and messy and unfamiliar,
and column names are abbreviated to the point of confusion. "What is ln_d_per again?
You right, it is the line discount percentage... I knew that."

In fact, I found it infinitely more convenient than using psql. Especially
where there are many joins. Those are very tedious in SQL, compared to the above
native queries eg. join(dog, dogHouse, region). We "pre-configure"
the foreign key constraints, so our native select query can iron out the details.

So I wrote and ran adhoc support queries using Netbeans rather than psql.
"I love that F6 button, man!" I was formatting the output to print to the
console, which worked fine, because the IDE provides scroll bars of course. However,
one could get fancy and popup a JTable, and save the preferred
column widths and maybe even column order, using the Preferences API.

The bonus was that later I could cut and paste those adhoc queries into servlets,
eg. to be reused by myself, other developers, and support technicians. And with
very little extra work, I spun some of those servlets into actual production reports
for the client. A future article in this series will delve into that.

I wrote some bash scripts
that rsync'ed the updated classes onto central servers in multiple environments/networks
in multiple countries (only two, but shhh), with multiple instances per server, ie. newer "staging" code
vs stable "production" versions running on different ports, and restarted all the
updated servers, at the push of an Enter button. It was a beautiful thing to behold! :)
Hey, I should have written Ant tasks to do this, then I would never have to leave Netbeans...
except to read Thunderbird and surf Firefox, of course.

Conclusion

"I see a look in the dog's eyes, a quickly vanishing look of amazed
contempt, and I'm convinced that he thinks I'm nuts."

In "Bean Curd 1"
I introduced an "explicit bean property" approach with "no string references attached"
where property descriptors are declared explicitly in a bean info class, and their
field names are used as implicit property names, for beans binding.

These property descriptor objects are configured using their own Field.
In particular, the field name is used to deduce the default property name,
and also further settings are extracted from annotations.

align=right vspace=4 hspace=16 />
This avoids using string literal references, which I also argue against in
"Explicit Reflection"
and "Refactoring Translations."

In the ORM example presented here, our bean is an "entity bean" eg. representing
a row from a database table. Explicit bean properties are
declared in "entity info" objects, which double up as "data access objects." This provides flexibility for
mapping and configuration, via overloaded factory methods, in addition
to annotations.

We then use these explicit properties for "native queries" ie. stringless
database queries. In this case, our queries are readily toolable (eg. using the
IDE's prompting, auto-completion and error-detection capabilities), and
our ORM is refactorable, eg. we can safely rename our column mappings. "It's the dogs!"

Writing queries becomes a breeze (thanks to the toolability, particularly auto-completion)
and it becomes easy to keep the mapping clean and consistent (using IDE refactorings freely).

"We in the dog pound now, baby!" :)

Future articles in this series might be "Bean Curd 4: On Form", looking
at using our "annotated integrated explicit property" approach for HTML forms,
for the purpose of capturing parameters for native query servlets,
and "Bean Curd 5: PH&P - Pdf, Html and Poi" for producing
reports in PDF, HTML and/or Excel, using annotated property descriptors, native queries
and servlets. But first I might do, "Bean Curd 3: Swing Form Binder" just to mix it up.

In the meantime, if you're an Alien, check out "Swing and Roundabouts 1: Event DTs".

Credits: Icons:
Everaldo.com; Fotos:
pixelperfectdigital.com: Kenn Kiser;
stock.xchng: Sarah Williams, Anna B.;
flickr.com;
yotophoto.com