Table of Contents
- Useful tools
- HQL
- 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
- NamedQuery
- 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
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") .list();
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();
session.beginTransaction();
Criteria criteria = session.createCriteria(Order.class)
.add(Property.forName("orderStatus").in(paymentstatus))
.addOrder(Order.desc("datePurchased"));
List orders = criteria.list();
session.getTransaction().commit();
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.
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.
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 = Java::OrgHibernateCfg::Configuration.new
c.configure
sf = c.buildSessionFactory
s = sf.openSession
s.createQuery('select h from Hedgehog h').listJBoss tools is a plugin for the Eclipse IDE. It provides an HQL editor which allows to execute instant HQL queries.
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.
 |