Query by Example or by Criteria

Again, misconception on ORM. A colleague of mine tries to query the database to find a record based on a field of the same table. He tries to use a query by example (a feature in Toplink which allows us to find the matching persistent object(s) if you provide the sample instance). I don't know whether this feature exists in other ORM technologies, e.g. Hibernate, but it's becoming quite handy.

For example, let's say we have an Employee table and a Department table. The Employee has a DepartmentID as his FK field, referring to the PK field of the Department table, with the same name.

To find a Department by its DepartmentID (PK field), all we need to do is as follows:

// initializing the query object
ReadObjectQuery query = new ReadObjectQuery ();

// preparing the example
Department dept = new Department ();
dept.setDepartmentID ("someID");

// set the example
query.setExampleObject (dept);

// execute the query on the current DB session
dept = (Department) session.executeQuery (query);

It's quite simple and straightforward (as it should be). We have retrieve a full Department object by performing such query. Depending on our indirection settings, the Department object may or may not have resolved all of its foreign key fields.

Now, moving on the next step. Let's try to find all Employees which are working on that Department (as specified by the DepartmentID). An obvious code may look something like my colleague did:

// initializing the query object
ReadAllQuery q = new ReadAllQuery ();

// preparing the example
Employee emp = new Employee ();
emp.setDepartment (dept);

// set the example
q.setExampleObject (emp);

// execute the query on the current DB session
Vector employees = (Vector) session.executeQuery (query);

At first, there's nothing wrong with the code. Of course, the results are showing correctly. Then, what's the problem? The only problem with this approach is when the Department object is actually a big table consisting of many fields, and many foreign key fields. In the background process, Toplink tries to generate a SELECT SQL Statement for the query by joining all of the foreign tables, and by putting numerous WHERE clauses on all the Department fields.

Why did this occur? It's all because we're using the wrong approach here. Query by example will read all non-null values from the example object, and create the same number (if not more) WHERE clauses as the number of non-null values in the example object. The query by example approach may be a convenient and suitable approach for query which involves the PK field of the table, or for query which involves a small number of known field values of the table. But, for a different case as shown above, a different approach needs to be chosen.

My colleague performed the query by example on his table (which more complex than the example above), and I have to wait 30 seconds to see the query results. I changed the code to use query by criteria instead, and it only cost me 3 seconds to wait.. :D

I think this could be a good example of working on/using something you don't know very well.. :D

Further Reading:
Hibernate in Action