Chapter 11. Querying data

Table of Contents

Useful tools
Beam me into the code
JBoss Tools
Squirrel SQL Client
Select objects, scalars, etc
Simple select
Select with a unique result
Select with join returning multiple objects
Select with join returning one object
Select with join returning distinct results
Selecting a single column (scalar values)
Selecting multiple columns (scalar values)
Selecting objects and scalar values
Selecting selective properties of a class
Simple where condition
Walking through relations
Where condition in a related object
Where condition with parameters
Conditions on collections
Where condition with mapped class
Where condition with mapped class on the multiple side
All, In, Some, Exists, Any elements queries
Criteria Queries
Simple select
Select with a unique result
Select with join returning objects multiple times
Select with join returning distinct objects
Select with a where condition on a related object
Selecting a single column (scalar values, projections)
Simple where condition
Where condition in relation
Where with or condition
Conditions on collections
All, In, Some, Any elements queries
Native SQL
SQL to Entity
SQL Resultset Mapping
Named SQL Queries
JDBC Connection

You have got three options to query data: HQL, criteria queries and SQL.

HQL is a object-oriented query language of Hibernate and supports all relation and inheritance mappings.

List orders = session.createQuery(
   "from Order o where o.invoice.paid=true order by o.datePurchased desc")

Criteria queries are useful to dynamically generate queries. They support relation and inheritance mappings as well. Of course you can replace HQL completely with criteria query. The only disadvantage is that nearly everybody knowing SQL will be able to understand HQL easily. Criteria queries are completely different. The following method receives an array of payment stati and creates a query dynamically. This is very simple, isn’t it?

public List findOrderByPaymentstatus(Integer paymentstatus[])
         throws DBLayerException {

   Session session = InitSessionFactory.getInstance().getCurrentSession();
   Criteria criteria = session.createCriteria(Order.class)
   List orders = criteria.list();

   return orders;

I recommend to use HQL for all known queries and criteria queries when you need to generate complex queries dynamically. SQL does not know any relations or inheritance mappings, so it is by far more narrative to type SQL queries as compared to HQL. Only in rare cases when you need some special optimisation you could think of using SQL.

Useful tools

When starting to learn HQL it can be annoying to test queries. Every time, you want to execute a query, you need to startup a Hibernate configuration, which can take a couple of seconds.

There are a number of tools you might use.

Beam me into the code

Did you ever want to beam yourself into your code. Don’t search any more. You can achieve this using JRuby. JRuby has an interactive shell, which allows to execute any kind of code. JRuby can import and execute Java classes.

Start the Jruby shell by typing jirb. Below you can see the commands I have typed in the shell to start up Hibernate and to execute queries.

include Java

Dir["/path/toallLibraries/\*.jar"].each { |jar| require jar }

c =
sf = c.buildSessionFactory
s = sf.openSession

s.createQuery('select h from Hedgehog h').list

JBoss Tools

JBoss tools is a plugin for the Eclipse IDE. It provides an HQL editor which allows to execute instant HQL queries.

Squirrel SQL Client

Squirrel is a well known SQL client which supports many databases. There is a Hibernate plugin which allows to execute HQL, see the resulting SQL and the result set.