HQL Programming Standards

Note: This section is provided for legacy customers only. It is not recommended to alter SQL at the Hibernate level for consistency and to reduce costs.
The applications use an object relational mapping library called Hibernate (information available at http://www.hibernate.org/). This library handles persistence operations against the database for changed entities, and also provides a querying language.

The Hibernate Query Language provides a more object-oriented approach to querying against the database. Joins can more clearly be indicated via navigation to the related foreign key, letting hibernate fill in the join when it constructs the SQL.

Note: In most situations, only a subset of the hibernate query language is used. For instance, when constructing a query whose order is important, the query must programmatically specify the order by, as opposed to placing the order by clause into the HQL itself. This allows the application to perform additional operations upon the HQL that may be required for different databases, and also to apply validations to the HQL.

Here are some examples of creating and using queries. The convenience methods to create the query are available on any context managed object. That is, entities, change handlers, business components, maintenance classes, and the implementer extensions of any of them.

To select all algorithms with a given algorithm type:


AlgorithmType algorithmType = ... ;
Query query = createQuery("from Algorithm algorithm where " +
"algorithm.algorithmType = :algorithmType");
query.bindEntity("algorithmType", algorithmType);
List algorithms = query.list();

The above algorithms list will contain as elements the algorithms for that algorithm type.

To sort the above query by the algorithm's code/Id:


AlgorithmType algorithmType = ... ;
Query query = createQuery("from Algorithm algorithm where " + 
"algorithm.algorithmType = :algorithmType");
query.bindEntity("algorithmType", algorithmType);
query.addResult("algorithm", "algorithm");
query.addResult("algorithmId", "algorithm.id");
query.orderBy("algorithmId", Query.ASCENDING);
List queryResults = query.list();

The above queryResults list will contain as elements instances of the interface QueryResultRow. Each query result row will have two values, keyed by "algorithm" and "algorithmId". The list will be ordered (on the database) ascending by the algorithm's IDs.

Since HQL works with the entity's properties instead of the tables' column names, there may be extra research required when writing queries. The source of the property information is in the hibernate mapping document for each entity class- they are documents that exist in the same package as the entity, have the same root file name as the entity's interface, and end with .hbm.xml. These files will give the list of properties available for each entity that can be referenced when writing HQL.

More information can be found in the JavaDocs associated with the Query interface.