Sign In : Sign Up : Blog : Search: Language : Help :
This website is now frozen, please migrate your content to BotLibre.com or BotLibre.biz
Java Persistence Performance

EclipseLink supports HQL and several advanced new JPQL features

by jamesssss posted Nov 21 2014, 7:22

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

Id: 325184
Tags: jpa, eclipselink, hibernate, hql, jpql, history, arrays
Posted: Nov 21 2014, 7:22
Updated: Nov 21 2014, 7:28
Replies: 0
Views: 2176, today: 2, week: 6, month: 16
I'm sure
Flag graphic as offensive, or in violation of site rules