The Generic Query framework - QCriteria

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

To implement this we have to "generate" the SQL select statement to include a "where" clause, and that can get quite complex. The where clause needs to have an "and" statement if we're querying multiple conditions, and every condition itself can be of several types: a numeric value for the "Album" foreign key, a String for the Artist's name etc. To make matters worse we cannot just create the SQL statement as a big string, because this would open the door to SQL injection trouble which can compromise the security of our application.

To help with this DomUI defines a generic query layer called the QCriteria layer. For people that know Hibernate or JPA: this is the same idea as its "Criteria API", but with a cleaner implementation and some important differences. Lets see how we do queries using this layer.

Where is the code

The examples in here can be found in the "tutorial" module. Get the domui code from github, then find the module at examples/tutorial. The tutorial project is a standalone project configured with Maven, and you can open it in IntelliJ by just opening the directory and importing the POM file.

My first query

To do a QCriteria query you need two things:

• You need a QCriteria<T> object which represents the select statement or query question itself
• and a QDataContext object which is a Database Connection or often a Hibernate Session in disguise

Let's start with QCriteria. All QCriteria queries use the entity objects, the Java classes that represent the "tables" in your database. In our tutorial these are the Artist, Album etc classes we just added. To create a simple query on Album we'd write:

QCriteria<Album> q = QCriteria.create(Album.class);

This is the equivalent of "select * from ALBUM", i.e. a select for all records in the database. To actually add a restriction, so called because it restricts the number of results, we add one of the comparator methods on it, like this:

public class CritTest {
 public static void main(String[] args) throws Exception {
   QCriteria<Album> q = QCriteria.create(Album.class);
   q.eq("title", "Nevermind");

   System.out.println("Q= " + q);
 }
}

When you run this class the console output looks like this:

Q= FROM my.test.db.Album WHERE title='Nevermind'

It looks like a select statement  but instead of selecting columns it selects the entire record (the class) - which is what we usually want.

We can easily extend the query by adding more restrictions on the query:

QCriteria<Album> q = QCriteria.create(Album.class);
q.eq("title", "Nevermind");
q.ilike("artist.name", "a%");

when we run the program again it now shows:

Q= FROM my.test.db.Album WHERE title='Nevermind' and artist.name ilike 'a%'

As you can see it automatically added the "and". But there are a few more things to notice here:

• QCriteria's are typeful; they specify the type that they are querying.
• You query by using the property names of the data objects, not the column names
• There are many comparator methods present on a QCriteria, eq, like, ilike, gt, ge, lt, le etc.
• Multiple restrictors are combined with "and" on a QCriteria
• You can query parent relation properties as easily as the properties on the actual object itself. By querying a parent object (the artist.name above) you will force a join to take place.

So far we only printed output. Lets actually do a query. Let's make a new class "Criteria1":

package to.etc.domui.tutorial.criteria;

import to.etc.domui.derbydata.db.Album;
import to.etc.webapp.query.QCriteria;

/**
 * @author <a href="mailto:jal@etc.to">Frits Jalvingh</a>
 * Created on 2-4-18.
 */
public class CritTest {
    public static void main(String[] args) throws Exception {
        QCriteria<Album> q = QCriteria.create(Album.class);
        q.eq("title", "Nevermind");

        System.out.println("Q= " + q);
    }
}

This code does the following:

• It initializes the test database, and creates the Hibernate configuration.
• It creates a QCriteria query
• Then we allocate a QDataContext (which is like the JDBC Connection, Hibernate's Session or JPA's EntityManager) and run the query with it
• And finally we loop through the result.

The query that is sent to the database is both typesafe and also injection safe: it looks like:

select this_.AlbumId as AlbumId1_0_, this_.ArtistId as ArtistId1_0_, this_.Title as Title1_0_ from Album this_ where lcase(this_.Title) like ?

The parameter is actually a JDBC parameter (the ?) so SQL injection is not possible.

The result of the class is as we would expect:

Combinators: and and or in QCriteria queries

We have seen that when we use multiple comparator methods on a QCriteria it will combine them with the "and" operator. So using "and" is easy ;-) But how do we make an "or"? Before we can answer that we need to explain something.

The QCriteria instance you get from the create operation is an instance of a QRestrictor<T>. The QRestrictor "restricts" the result set by adding stuff to the where clause. This QRestrictor has a "combination" mode which is "AND" - so all comparator methods added to it are combined with AND's. To make an OR you must create a QRestrictor that uses a combination mode of OR. You get such a thing as the result of the or() call: a QRestrictor<T> where every comparator method called is combined with or. For example: the query

where year=2010 or year=2011

is coded as follows:

QCriteria<Album> q = QCriteria.create(Album.class);
QRestrictor<Album> or = q.or();
or.eq("year", 2010);
or.eq("year", 2011);

When you add new conditions to the "parent" QRestrictor (q) they will be combined with and:

QCriteria<Album> q = QCriteria.create(Album.class);
QRestrictor<Album> or = q.or();
or.eq("year", 2010);
or.eq("year", 2011);
q.ilike("name", "A%");

will result in:

where (year=2010 or year=2011) and name ilike 'A%'

To create something like

where (year=2010 and name ilike 'A%') or (year=2011 and name ilike 'Z%')

you would code:

QCriteria<Album> q = QCriteria.create(Album.class);
QRestrictor<Album> or = q.or();

or.and().eq("year", 2010).ilike("name", "A%");
or.and().eq("year", 2011).ilike("name", "Z%");

The "or.and()" method call returns another restrictor that uses and again.

Building trees

When you use QCriteria you are building an expression tree. The tree has nodes that represent operations and leaves that represent subexpressions or terms in the expression. For instance, the expression tree for:

(2 + 2) + (2 + 2) + (3 + 3)

is:

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