Thursday, June 20, 2013

EclipseLink supports HQL and several advanced new JPQL features

EclipseLink 2.5 added several new JPQL features and supported syntax.
These include:
  • HQL compatibility
  • Array expressions
  • Hierarchical selects
  • Historical queries

HQL Compatibility

A common issue for users migrating from Hibernate to EclipseLink is that the HQL syntax of not having a SELECT clause is not supported, as it is not standard JPQL syntax. EclipseLink now supports this syntax. For queries that will return the entire object, this allows you to omit the SELECT clause, and start the query with the FROM clause.
from Employee e where e.salary > 50000

Array Expressions

Previously in JPQL you could use the IN operation and sub-selects to compare a single value, but what if you wanted to compare multiple values, such as composite ids? You would then have to dynamically generate very long AND/OR expression trees to compare each value one by one, which is difficult and cumbersome. Many databases provide a much better solution to this problem with array expressions, this allows for arrays within the SQL in sub-selects comparisons, or nested within an IN comparison.

EclipseLink now supports array expressions with JPQL. EclipseLink also uses array expressions internally when object comparisons are done in JPQL and objects with composite ids.

Select e from Employee e where (e.firstName, e.lastName) IN :names

Select e from Employee e where (e.firstName, e.lastName) IN (Select m.firstName, m.lastName from Managers m)

Hierarchical Selects

Traditionally it has been very difficult to query hierarchical trees in relational data. For example querying all employees who work under a particular manager. Querying one level is simple, two levels is possible, but querying the entire depth of the tree, when you don't know how deep the tree is, is very difficult.

Some databases support a special syntax for this type of query. In Oracle the CONNECT BY clause allows for hierarchical queries to be expressed. EclipseLink now supports a CONNECT BY clause in JPQL, to support hierarchical queries on databases that support CONNECT BY.

Select e from Employee e START WITH e.id = :id CONNECT BY e.managedEmployees

Select e from Employee e START WITH e.id = :id CONNECT BY e.managedEmployees ORDER SIBLINGS BY e.salary where e.salary > 100000

Historical Queries

Historical queries allow you to query back in time. This requires that you use EclipseLink's history support, or use a database that supports historical queries such as Oracle's flashback support. Historical queries use the AS OF clause to query an entity as of a point in time (or an Oracle SCN for flashback). This provides the ability to do some pretty cool queries and analytic on your data.
Select e from Employee e AS OF :date where e.id = :id

Select e from Employee e, Employee e2 AS OF :date where e = e2 and e.salary > e2.salary

8 comments :

  1. Hi James

    Would array expressions also work as an alternative to normal IN-lists? I.e. could this be an alternative solution to the problem in bug 392762? If so, that would be great news. We currently have a rather simple JPQL query that due to two long IN-lists results in an SQL query that on Oracle 11.2 requires a parse time of 40 seconds!

    Thanks,

    --knut

    ReplyDelete
  2. @knut - What is your query with a 40s parse? Are you generating your own AND/OR for a huge list of composite ids? Where did this ids come from? I assume from some original query, perhaps instead join that original query.

    ReplyDelete
  3. James,

    No, it isn't for composite IDs. The data in the IN lists comes from an external system and the query roughly looks like this:

    select distinct pv_sib
    from cu
    join cu.included pv
    join cu.required pv_sib
    where pv.uuid in :pvs
    and cu.archived = false
    and cu.latest = true
    and pv_sib.release.uuid in :releases
    and cu.uuid not in :ignoredCUs
    and not exists (select pv_inst
    from ci
    join ci.pvs pv_inst
    where ci.stage.version = :stage
    and pv_inst = pv_sib
    )

    Meanwhile I think I understand that array lists won't help here (I was mislead by the name to believe that it translated to JDBC array bind variables). I assume the best current alternative is to use a temporary table (maybe even mapped by a "technical" JPA entity) to hold the elements. But I would still be interested in your thoughts on how to differently translate IN list predicates to SQL (see https://bugs.eclipse.org/bugs/show_bug.cgi?id=392762), as mapping directly to SQL IN lists can clearly cause problems (a hard parse for every query with a different number of elements in the list(s)).

    ReplyDelete
  4. I found a good thread on this issue here,

    http://stackoverflow.com/questions/6956025/use-oracle-unnested-varrays-instead-of-in-operator

    Sounds like it would be worth some investigation, may make a good blog post.

    ReplyDelete
    Replies
    1. Yes, this is almost exactly the pattern we would like to use instead of the IN list with literals. In some cases it may possibly be necessary to throw in a CAST and to make it easier to translate (no additional tables to join in the main query) it should also be possible to formulate the predicate as an IN or EXISTS predicate with the VARRAY TABLE inside a subquery.

      I would be interested to know how I could extend EclipseLink to do this. Any pointers?

      Delete
  5. I would start just trying to execute it as a native query, binding the VARRAY as the parameter.

    ReplyDelete
    Replies
    1. OK, thanks. We have successfully used this ARRAY approach in the past with standard JDBC API. I suppose we could try this using the "EclipseLink native query API" as you suggest.

      Unfortunately we have rather many JPQL queries with this problem. As a workaround we now make sure to use fixed length IN lists (when necessary by padding with repetitions of elements) to avoid hard parses. Rewriting all these queries to native SQL is not really an option for us. Another option which also works is to have a "technical entity" containing the IN list values which gets mapped to a temporary table in the database and then joining this entity in the JPQL queries.

      Delete
  6. FWIW, I think I've found a hacky workaround for EclipseLink 2.4. I can rewrite a JPQL predicate like:

    x.y in :ys

    as:

    SQL('exists (select null from table(?) where column_value = ?)', :ys, x.y)

    Now I call this JPQL query by passing in a JDBC Array object as the paramter value for "ys". At least this lets us keep using JPQL.

    ReplyDelete