Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Version History

Version 1 Next »

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.

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 (wink) 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. Change the code to look like:

public static void main(String[] args) throws Exception {
  TestDB.initialize();

  QCriteria<Album> q = QCriteria.create(Album.class);
  q.ilike("title", "N%");

  try(QDataContext dc = TestDB.getDataContext()) {    // we must make sure to close the connection!
    List<Album> res = dc.query(q);
    for(Album a : res)
     System.out.println("Got " + a.getTitle());
  }
}

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.





  • No labels