Page Comparison

An important part of many applications is to use a database and generate queries in it. Normally when you do you'd create some SQL and fire it off to get a result set, but for a user interface this will be less optimal. For instance, we have an Artists table, and it is big.. So instead of having to show the entire table we want the user to fill in some lookup criteria in a Search Panel, then do a query to find only those artists that match those criteria, like only the artists born in 1964, or only those on the album "In Search Of Sunrise 6: Ibiza".

...

QCriteria creates such a tree for boolean expressions, and important parts in it are the and and or operations:

Now if we look at this tree we see something special. We have multiple levels, and "ands" and "ors" are each on a separate level. This behavior occurs because "and" and "or" nodes can be merged:

a and (b and c) = a and b and c

a or (b or c) = a or b or c

So every and or or "sublevel" that has the same operation as the level above it can be merged in that level above it, and QCriteria will do that.

Parent and child relation queries: joins

TBD

Differences between JPA/Hibernate Criteria and DomUI QCriteria

There are some important differences between Hibernate's Criteria API and DomUI's QCriteria API. These are not just in use but also in behaviour. It is important to understand the differences if you have used Hibernate's implementation before - QCriteria is not simply a Hibernate Criteria wrapper.

Simple differences

There are some simple differences:

• QCriteria queries are more like Hibernate's "Detached Criteria". They do not know about a Session or data connection at all. This is better than Hibernate's Criteria implementation because it does not bind the QCriteria to any session or database, so they can exist standalone and are reusable.
• Hibernate's DetachedCriteria and Criteria are a kludge: they serve mostly the same goal, have the same API, but do not derive from each other nor implement a common interface. Very bad design.
• QCriteria are typed, and consequently calls using them are typed better. The disadvantage is that slightly more typing is required...
• QCriteria are fully database and ORM agnostic: they do not know anything about how they are actually executed. Special factories called query executor factories are handing the translation of QCriteria queries to the ORM or database used.
• This makes it easy to "switch" ORM implementations. You can even write your own simple variant, and a JDBC-only "poor man's" implementation is actually present in DomUI, look for the @QJdbcTable and related annotations.
• Because QCriteria's are typed it is easier to create code to check them for correctness. For example the DomUI Eclipse plugin uses the typefulness of QCriteria to help with filling in the property names of the criteria, and can also check if QCriteria are not referring to some unknown property and report an error.

Big differences

Join explosions and the limit clause

Take the tutorial's database Artist and Album tables. An Artist has a "child" relation which is a list of albums by that artist. Now lets create a query that returns all Artists that have an album that was released in 2010. This query in Hibernate is generated as a SQL Join between Artist and Album, and adds a condition on album.year = 2010:

select artists.*, albums.* from artists, albums where artists.id = albums.artistid and albums.year=2010

Now consider what happens if artist 'Henk' has released 5 albums in 2010. Because this is a join, the result set will contain 5 rows for Henk. All of these rows are for the same 'Henk' (i.e. all artists.* fields are the same in those 5 rows), but for separate albums (the albums.* fields differ).

In Hibernate/JPA the result set of this query will return the "Henk" Artist instance 5 times in the result List<>, even though they are the same. You can do some special magic to prevent this- but you MUST do that magic or you'll get 5.. Even when you do that magic another problems remains: the above query will cause trouble with any "limit" or "start" clause. The database will count the actual number of rows in the result set (the joined rows), not just the number of "Henks in rows". So limit(10), instead of limiting to 10 artists, actually limits to 10 joined rows- which usually means less than 10 artists even when more can be returned.

It means that these limits do not work, and do not work without any indication that they are failing!

Another way to say it is that limit() and start() in DomUI are defined as limits on it's model, i.e. it formally limits the number of Artist instances, while in Hibernate these are defined as limits on the underlying implementation. The latter is very confusing and can be seen as a model violation.

Hibernate has more places where this "Join Explosion" occurs unexpectedly (for instance it often happens when eagerly fetching data too). Instead of failing a request that tries to limit() they run the code with wrong results. Bad design.

Circumventing the join problem

DomUI does not allow you to specify a query in this way: it tries to have different semantics for QCriteria queries. We define the above query not as a join, but as an "exists subselect". What we really want in the above query is:

Give me all Artists that (Have an album in 2010).

Instead of creating a direct Join we create a subselect join statement like:

select * from Artists a where exists ( select 1 from Albums b where b.year=2010 and b.artistid=a.id);

In this query we will always have only one Artist, even when he has 5 albums in 2010. Because the number of rows is now correct limit() and start() work as they should too. It's still a join in database terms but of a different kind. It's often also a cheaper statement than the earlier statement because the database needs to read less data: instead of reading all 5 album rows to create the result set it can stop reading when it sees the 1st row with year=2010.

Sidestep: how about fetching the albums - eager fetch problems

A common misunderstanding is that the above makes it harder to fetch the child "album" records to put into the List<Album> field for each Artist that is read (eager fetching). This is of course not true. In the first statement the join does contain some of the albums to put in the list, but not all of them - it only contains the 2010 albums. But the List<Album> for an Artist must, by definition, contain all albums for the artist, not just those you queried. If we would allow only those 2010 albums there- what would we need to do to get the full list later? And what would it mean when we add a single record there- must the missing ones be deleted too?

What this makes clear is that the where part of the query (the part that decides on which records to return) in ORM queries should be fully separate from the select part of the query (the part that decides what data to return). If we want to eagerly fetch data we would have to rewrite the query like:

select a.*,f.* from Artists a,Albums f where exists ( select 1 from Albums b where b.year=2010 and b.artistid=a.id) and f.artistid=a.id

In here we have two joins to the same table. The subselect joins but just returns as soon as a row is found with year=2010. The second one does the full join, returning all albums per artists - but again repeating the artist in the result!

This kind of eager fetch cannot be done at all with limit() and start() restrictions in place. DomUI will abort your query if it can see that you are eagerly fetching with limit() in place. You need to find another solution.

Child conditions in QCriteria

In DomUI, very simple conditions on a child relation like the above will be automatically translated into an "exists" subquery by it. This only works for simple queries because it is hard to do right for complex ones (combinatory conditions on both a child and the parent record are hard to specify properly). The best way to handle child relation criteria is to create the subselect yourself using QCriteria, like this:

QCriteria<Artist> q = QCriteria.create(Artist.class);
QRestrictor<Album> subq = q.exists(Album.class, "albums");
subq.eq("year", 2010);

This uses the "exists" method to explicitly create the joined subselect. It returns the QCriteria for the subselect, so you can add to it's where clause at will.

There are some unfortunate oddities in the interface: because Java is still lacking first-class properties (sigh) the compiler cannot know the type of the "albums" list. But QCriteria is typeful and requires that type.. This means you have to pass it in, hence the Album.class parameter. As soon as Oracle gets a brain and provides proper support for Java Properties this will be fixed; this is expected just before Hell freezes over.