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 (http://www.hibernate.org/hib_docs/reference/en/html/queryhql.html) 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 that 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, maintenances, 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.
Contents
Even with all of the above, there are a few cases that stand out with possibly needing examples in order to help. Notably, dealing with language entries and lookups may be confusing.
Here is an example of selecting all algorithm types where the description is like some input:
String likeDescription = …;
Query query = createQuery(“from AlgorithmType_Language algTypeLang join algTypeLang.id.parent algType where algTypeLang.description like :likeDescription and algTypeLang.id.language = :language”);
query.bindEntity(“language”, getActiveContextLanguage());
query.addResult(“algType”, “algType);
query.bindLikableStringProperty(“likeDescription”, AlgorithmType.properties.languageDescription, likeDescription);
List algorithmTypes = query.list();
The algorithmTypes list will contain as elements the algorithm types whose description is like likeDescription. Note that the string likeDescription will have a trailing ‘%’ appended when it is bound to the query.
Here is an example of selecting particular lookup values, with descriptions like an input value:
String description = header.getString(STRUCTURE.HEADER.DESCR);
Query query = createQuery("from LookupValue_Language lookupValLang "
+ "where upper(lookupValLang.description) like upper(:description) and lookupValLang.id.language = :language and "
+ "lookupValLang.id.parent.id.fieldName = 'RPT_OPT_FLG');
query.bindLikableStringProperty("description", LookupValue.properties.languageDescription, description);
query.bindEntity("language", getActiveContextLanguage());
query.addResult(“lookupValue”, "lookupValLang.id.parent");
query.addResult("description", "lookupValLang.description");
query.orderBy("description");
List results = query.list();
The list results will contain QueryResultRows, with values keyed by “lookupValue” and “description”.
You may note that hibernate’s HQL does not allow unions, as this does not reconcile with the object oriented approach of HQL. However, as this can be a common technique to apply, a programmatic union has been provided in the Oracle Utilities Application Framework. The application will actually open two cursors and flip back and forth between rows from each cursor when each would be the next one, based upon the order by clause. This should at most read one extra row from each cursor opened than may be needed (in the case of limited maximum rows).
In order to union two queries, they must have identical result columns, order by clauses, and max rows setting. Note that some of the properties of the union query be modified directly, leaving the individual queries to omit those properties.
Creating a union query is simple. Given two queries that need to be unioned together, simply issue:
UnionQuery union = query.unionWith(query2);
If a third (or later) query needs to be unioned, add it to the union directly:
union.addQueryToUnion(query3);
In order to evaluate the performance of HQL queries, it is necessary to first run the HQL through the hibernate engine at run-time in order to produce the equivalent SQL. First, code the initial HQL into the application or a unit test or standalone executable program. Start the application or test program with SQL tracing turned on. When the HQL under construction executes, grab the SQL from the log/console. Then follow the directions in ??? 07 SQL Programming Standards to check the performance of the SQL.
In general, most of the advice under the SQL programming standards applies equally for coding HQL when applicable at all.
In rare cases, it may be necessary to forgo the use of HQL and instead use raw SQL. This is not a preferred approach, as the data returned will not be Java entities, but columns of primitive data types. However, for possible performance reasons (no db hints are allowed in HQL) or if a table is not mapped into a Java entity, this approach exists.
There are parallel methods available on subclasses of GenericBusinessObject that create spl PreparedStatements, instead of Query objects. So, instead of createQuery, the method createPreparedStatement should be called on a Raw SQL statement.
The PreparedStatement is similar to the regular jdbc PreparedStatement, but has some extra functionality, and a slightly different interface so that it is similar to the regular HQL Query interface (they are interchangeable in some cases).
The main difference is that the prepared statement is created with raw SQL—use the actual table and column names instead of the Java entity names and property names. Also, the select clause must exist as in normal SQL but not HQL.
Additionally, this break-out into raw SQL allows SQL statements that update table data. Again, this is normally frowned upon, and instead should be done by entity manipulation. However, in cases where a set-based SQL could update many rows at once, this option is available, whereas HQL is ONLY meant for querying without any updates.
For more help on constructing raw SQL queries please see SQL Programming Standards.