Finders and Queries

One of the fine arts of enterprise programming is figuring out the best way to get all of that information that is currently in your database, out of your database, into your applications, and subsequently displayed back to the user in a manner that is both efficient and effective. Hibernate provides a variety of ways to extract information from a database, from the simple load and get methods of the Hibernate Session, to HQL, named queries, and even plain old JDBC. This chapter will explore some of the more common ways to query data from the database, including:

• Hibernate Query Language expressions
• Variable Injection
• Named Queries
• Native SQL statements

The goal of this tutorial is to demonstrate the most common mechanisms of performing database queries with Hibernate, helping you to develop a strong foundation of skills upon which you can build.

The Need for a New Query Language (HQL)

Thinking about a time a few years back, I remember when I was creating EJBs and was introduced to EJBQL, the EJB Query Language. My legacy EJBs were working just fine with the loads of SQL that was already in them, so I really didn't understand why I would want to introduce something new into the mix. Of course, looking back, my EJBs were probably a little too database driven, so perhaps it was more of my lack of good design skills that obfuscated the value of EJBQL; but I must say, I wasn't too fond of it when I first saw it.

But if you really do think about it, you will realize pretty quickly that if you're developing Java components, coding in a J2EE environment, and working with an object-oriented model, well, you should probably be thinking about your data queries in an object oriented way. And furthermore, and perhaps even more to the point, since our JPA annotated POJOs effectively abstract away the underlying database layer, the fact is, when you are designing your Java based persistence applications, you really can't be sure of what is going to be going on in the underlying database layer at deployment time or runtime.

For example, perhaps you have a JavaBean named User, and that JavaBean has a property named loginName. What is the name of the underlying field in the database? If we are writing SQL statements, the names of the database fields become very, very important, because they must exactly match the column names used in the query. This implies a knowledge of the persistence layer in the application layer, and that makes most good MVC programmers a little antsy.

However, with HQL, as with EJBQL which preceded it, you write your queries based on the properties and names of the Java objects in your domain model. That way, if the names of the underlying tables or columns ever change, your Java applications are not effected - the only thing that has to change is the mappings, or column annotations, but the actual HQL queries remain the same, insulating your Java model from possible changes at the database level. And of course, insulating the model from the persistence layer is always a penultimate goal of a good application developer. HQL makes this possible.

Hibernate Query Basics: A Review

As with any other operation in Hibernate involving a database interaction, executing a Hibernate query begins with the program gaining access to the Hibernate Session. The Hibernate Session has a createQuery method that takes any valid Hibernate Query Language String, from which a Query object can be created. A very simple HQL query that would retrieve all User objects from the underlying database would be as simple as the String "from User". So, in code, a select all Users in the database request would start off looking something like this:

Session session = HibernateUtil.beginTransaction();
Query query = session.createQuery("from User");

To actually get the results of the query, you simply ask the instance of the query object to return a java.util.List. You do this by invoking the aptly named list() method of the Query instance. The List will simply be a collection of User objects, which you can loop through at your leisure.

Session session = HibernateUtil.beginTransaction();
Query query = session.createQuery("from User");
List users = query.list();

for (int i = 0; i < users.size(); i++) {
  User user = (User) users.get(i);
  System.out.println(user.getLoginName());
}

Notice how the returned elements in the List are of type User. A quick cast allows us to pull these elements out of the List and start working with all of the yummy properties of the instance.

Selecting specific elements

Notice how the from User HQL query returned a grouping of User objects. The from User query pulls all of the fields associated with our User class out of the underlying database, and appropriately initializes the corresponding fields of the User instance. In our cheap little code, we simply printed out the user's loginName to prove that everything was working:

Session session = HibernateUtil.beginTransaction();
Query query = session.createQuery("from User");
List users = query.list();
for (int i = 0; i < users.size(); i++) {
  User user = (User) users.get(i);
  System.out.println(user.getLoginName());
}

If all we were interested in was the loginName, we could have prefaced our find User HQL query with a SELECT element, and selected only the loginName:

String hql = "SELECT loginName from User";

Note though, the result of such a query, where only one parameter is specified, is a list of elements matching the data type of the property. So, in this case, since the loginName property is defined in the User class as a String, the List returned from the query.list() method will be a collection of String objects, not a collection of Users:

Session session = HibernateUtil.beginTransaction();
String hql = "SELECT loginName from User";
Query query = session.createQuery(hql);
List names = query.list();
for (int i = 0; i < names.size(); i++) {
  String name = (String) names.get(i);
  System.out.println(name);
}

If more than one property is selected, the elements of the returned List are array elements, with the array elements matching the type and order of the elements as they appear in the select portion of the HQL statement.

The HQL where Clause & Unique Results

To narrow down the size of your queries, you'll need to use the irrepressible WHERE clause. To find a User with the loginName of mj, the corresponding HQL query would look like this:

String hql="from User where loginName = 'mj' ";

Now, assuming the loginName was a unique field in the database, and there could only be one possible row in the database where the loginName was 'mj', there's no point in returning a List via the Query object. Instead, when only one result is expected, we use the uniqueResult() method of the Query class, which returns to the calling program a simple, single, java.util.Object, which can be uneventfully cast into the appropriate object of choice.

Combining the where clause and the uniqueResult() method, our Java code would look something like this:

Session session = HibernateUtil.beginTransaction();
String hql="from User where loginName = 'mj' ";
Query query = session.createQuery(hql);
Object o  = query.uniqueResult();
User u = (User)o;
System.out.println(u.getLoginName());
System.out.println("\n\n");
HibernateUtil.commitTransaction(); 

Of course, this particular HQL query hard codes the loginName 'mj' right into the query. This doesn't really make too much sense, as you generally don't know the value you need to look up until runtime. Rather than hard coding values into an HQL query, you'll typically use variable injection instead.

JDBC and PreparedStatements: A Review

When performing a query where the criteria isn't exactly known at runtime, as in the case where we select a record based on the loginName a user provides, we need to leverage HQL's variable injection facilities. If you've ever used JDBC and worked with PreparedStatements, you'll notice a number of similarities in the philosophy of how variables are injected into HQL statements, although the syntax between HQL and JDBC statements is a little bit different.

With a JDBC PreparedStatement, a select query against the user table where the value of the loginName is not known until runtime would look something like this:

String loginName;
/*obtain the loginName from user somehow!!!*/ 
String sqlQuery = 
          "select * from user where loginName = ?";
PreparedStatement ps = con.prepareStatement(sqlQuery);
/*replace the first ? mark 
with the value held by the variable*/
ps.setString(1, loginName);
ResultSet rs = ps.executeQuery();

The idea with the above code is that the value of the user's loginName is not known until it is provided to our program, so instead of hard-coding the values into your program, you can code a normal SQL statement with a question mark, ?, in the spot where the unknown value should go. You then code some way to get this value at runtime, substitute the initialized runtime variable for the question mark in the query, and then finally, send the query to the database. That's how JDBC PreparedStatements work! As I like to say, Hibernate is very similar - but different. :)

Variable Injection with Hibernate

Hibernate uses a mechanism similar to a JDBC PreparedStatement to perform runtime variable injection into SQL statements. The Hibernate syntax is a bit different from standard JDBC code, but the idea is pretty much the same.

Rather than putting a question mark into the Hibernate Query Language String where the runtime variable needs to be inserted, as we might with JDBC, Hibernate tends to use a variable name with a colon in front of it, although using the question mark syntax is still possible. Using variable names is preferred, as you can use real names where the variables need to be inserted, rather than numbering off the question marks in a JDBC preparedStatement.

Here's how our Hibernate query string might look:

String hql = "from User where loginName = :name";

This HQL String is then passed to the createQuery method of the Hibernate Session to create a Query object:

Query query = session.createQuery(hql);

From there, a setter method on the initialized query object, which I creatively named query, is used to substitute the variable, which is defined in the HQL String as :name, with an actual value. Here, we use a value passed into the program:

query.setString("name", loginName);

Once the placeholder in the HQL query has been substituted by our intended variable, we can execute the query and obtain a unique result.

Object o = query.uniqueResult();
User user = (User)o;

Once the query is executed, we take the queryResult, which is returned as an Object, and cast it into a our User type, which can then be used in the application. It's all just that easy!

Session session = HibernateUtil.beginTransaction();
String loginName = "mj";
String hql="from User where loginName = :name";
Query query = session.createQuery(hql);
query.setString("name", loginName);
Object o = query.uniqueResult();
User user = (User)o;
System.out.println(user.getLoginName());
HibernateUtil.commitTransaction();

ORDER BY and the HQL Alias

Another helpful little token you can add to an HQL statement is the ORDER BY element. Basically, you can specify a specific property of your POJO, and tell Hibernate to sort the results of your query by that property, in either an ascending (ASC) or descending (DESC) manner.

Session session = HibernateUtil.beginTransaction();
String hql = "from User as u ORDER BY u.id ASC";
Query query = session.createQuery(hql);
List users = query.list();
for (int i = 0; i < users.size(); i++) {
  User user = (User) users.get(i);
  System.out.println("Hello World");
  System.out.println(user.getLoginName());
}
HibernateUtil.commitTransaction();

You will also notice that through a little slight of hand, I used an alias in the ORDER BY query. In any HQL query, you can use the word as to specify an alias for the class you are querying against. Here, I specified the alias u, and when the ORDER BY clause was used, I simply specified the property name using the alias, which equated to u.id.

String hql = "from User as u ORDER BY u.id ASC";

GROUP BY and HAVING

The last two elements of the SELECT query that I haven't talked about are the GROUP BY and HAVING attributes. The results of a query, or aggregate query, can be grouped together using the GROUP BY clause. Furthermore, a logical expression can be provided through the HAVING token of the HQL query as well. Our User class doesn't give us any great scenarios for using the GROUP BY and HAVING tokens, but here's a simple example that effectively demonstrates the syntax.

Session session = HibernateUtil.beginTransaction();
String hql;
hql= "from User as u GROUP BY u.id HAVING u.id > 4)";
Query query = session.createQuery(hql);
List users = query.list();
for (int i = 0; i < users.size(); i++) {
  User user = (User) users.get(i);
  System.out.println("Hello World");
  System.out.println(user.getLoginName());
}
HibernateUtil.commitTransaction();

Examples of Batch UPDATE Calls with Hibernate

For performing updates and deletes on a large number of records, Hibernate provides a fairly simple batch operation syntax. The syntax is pretty much as follows:

( UPDATE | DELETE ) FROM ? EntityName (WHERE where_conditions)

So, to change the password on all users who were lazy and silly and set their own passwords to the literal string 'password', we can issue the following HQL:

String hql="update User 
               set password = 'abc123' 
                  where password='password')";

This update will effectively change every record that has a password of 'password' to a password of 'abc123'.

One thing to note about performing a batch update or delete is the fact that we do not use the query.list() or query.uniqueResult() methods, but instead, we use the special executeUpdate() method of the Query instance. Very conveniently, this method returns a value of type int that tells us how many records were actually effected. Putting all of it together in code would look like this:

Session session = HibernateUtil.beginTransaction();
String hql = "update User 
                set password = 'abc123' 
                  where password='password')";
Query query = session.createQuery(hql);
int rowCount = query.executeUpdate();
System.out.println(rowCount + " rows were updated.");
HibernateUtil.commitTransaction();

Batch DELETE Calls with Hibernate

Batch DELETE calls with Hibernate are very similar to an update, except there's obviously no need to set any update parameters. So, to delete every user in the database with a name of 'Tim', the HQL DELETE query would look like this:

String hql = "delete User where loginName = 'Tim' ";

And again, the HQL delete query must be sent to an executeUpdate method of the Query object to both run the batch command, and subsequently return the number of rows effected. Here's how a batch delete would look in code:

Session session = HibernateUtil.beginTransaction();
String hql = "delete User where loginName = 'Tim'";
Query query = session.createQuery(hql);
int rowCount = query.executeUpdate();
System.out.println(rowCount + " rows were deleted.");
HibernateUtil.commitTransaction();  

When run, this code would delete every row in the database with the loginName of Tim. Pretty sweet, isn't it?

Examples of Hibernate Named Queries

Mingling HQL statements right there alongside your Java code is neither a manageable nor maintainable solution. Instead, it's a best practice to keep all of your HQL queries in a single, manageable location, and invoke those queries by name in your code. To facilitate this functionality, Hibernate leverages the @NamedQuery JPA annotation. To use it, you simply create a class that will contain all of your named queries, and place the @NamedQuery annotation at the top of the class.

@NamedQuery(name="user.findByLoginName",    
     query="from User where loginName = :name") 

One thing you should know about named queries is that the class that defines the @NamedQuery tag must be made part of Hibernate's AnnotationConfiguration object. Our User class is added to the AnnotationConfiguration object in our custom HibernateUtil class, so we'll add the NameQuery right before the class declaration of the User.

package com.examscam.model;
import javax.persistence.*;
import org.hibernate.Session;
import com.examscam.HibernateUtil;
@Entity
@Table(name = "user", schema = "examscam")
@NamedQuery(name="user.findByLoginName",
   query="from User where loginName = :name" )
public class User {     }

Once you have defined your @NamedQuery, and the class in which it is defined is loaded into Hibernate's AnnotationConfiguration, you can use this named query anywhere that you access the Hibernate Session.

Calling a NamedQuery in Hibernate

With the class that defines the @NamedQuery annotation made part of the Hibernate Configuration, invoking the NamedQuery is as simple as calling the Hibernate Session's getNamedQuery method, and passing along the query name. From there, you can inject any variables as needed, and then execute the query as per usual.

The following main method implements the from User where loginName = :name query in the form of a NamedQuery:

public static void main(String args[]){
  String loginName = "mj";
  Session session = HibernateUtil.beginTransaction();

    Query query =       
     session.getNamedQuery("user.findByLoginName");
query.setString("name", loginName);
  Object o = query.uniqueResult();
  User user = (User)o;
  System.out.println(user.getLoginName());
  HibernateUtil.commitTransaction();
}

Hibernate and Native SQL

And though our main focus is always Hibernate, it is worth mentioning that you can indeed issue native SQL queries through the Hibernate Session using the Session's createSQLQuery method. You simply pass in a valid SQL String, and Hibernate will return the results in a java.util.List.

Now one thing to note about native SQL queries is that what gets returned in each element of the List is simply an Object array, containing the datatype to which the queried columns map, as defined by the JPA annotations of the class. Furthermore, with a SELECT * query, we would need to know the order of the columns in the database so we can cast the incoming data properly.

The following is an example of a native SQL query that goes against a User database table with id (Integer), email, name and password fields:

public static void main(String args[]) {
  String sql = "SELECT * FROM USER";
  Session session = HibernateUtil.beginTransaction();
  SQLQuery query = session.createSQLQuery(sql);
  List users = query.list();
  for (int i = 0; i < users.size(); i++) {
    Object[] o = (Object[]) users.get(i);
    System.out.print(((Integer) o[0])); //id
    System.out.print(((String) o[1]));  //email
    System.out.print(((String) o[2]));  //name
    System.out.println(((String) o[3]));//pass
  }
}