Skip to main content
Jump to: navigation, search



A new JPQL keyword has been added to JPQL to support native database functions (bug 300512).

To call the DB function MyFunc(a, b, c) use FUNC('MyFunc', a, b, c)

Example: Year function

SELECT FUNC('YEAR', whenRegistered) Y, COUNT( C FROM registration GROUP BY Y

Example: Oracle NVL

EclipseLink's ExpressionOperator.ifNull will generate "NVL". How can this be used in JPQL and Criteria defined queries?

// on Oracle
SELECT FUNC('NVL', e.firstName, 'NoFirstName'), func('NVL', e.lastName, 'NoLastName') FROM Employee e
// on MySQL
SELECT FUNC('IFNULL', e.firstName, 'NoFirstName'), func('IFNULL', e.lastName, 'NoLastName') FROM Employee e

Example: Oracle Spatial

PostGIS Intersects (forum)
SELECT a FROM Asset a, Geography selectedGeography
WHERE = :id AND IN :id_list
AND FUNC('ST_Intersects', a.geometry, selectedGeography.geometry) = 'TRUE'
Oracle Spatial
SELECT ss FROM SimpleSpatial ss WHERE FUNC('mdsys.sdo_relate', ss.jGeometry, :otherGeometry, :params) = 'TRUE' ORDER BY ASC

Batching reading using exist and IN

(bug 298985)

Typically batch reading uses a join of the batch query to the source query.

This join has some issues:

  • If a m-1 or m-m, join causes duplicate rows to be selected, (a distinct is used to filter rows for m-1, but duplicates are returned for m-m as join table info is needed).
  • DISTINCT does not work with LOBs.
  • DISTINCT may be less efficient on some databases than alternatives.
  • Does not work well with cursors.
  • Needs verification if works with pagination.

Two new configuration options are introduced in EclipseLink 2.1. One such option is to use an EXISTS with a sub-select instead of a JOIN. This should not result in duplicate rows, so avoid issues with DISTINCT.

Another option is to load the target objects using a IN clause containing the source query object's primary keys. This would also work with cursors, but as the limitation of requiring custom SQL support for composite primary keys, and produces a large dynamic SQL query.

A new BatchFetchType enum will be define and the usesBatchReading flag will enhance to setBatchFetch allowing for JOIN, EXISTS or IN. This option will also be added to ObjectLevelReadQuery, rolling up the current 4 batch reading properties into a new BatchFetchPolicy, also moving them up from ReadAllQuery to allow ReadObjectQuery to also specify nested batched attributes. A new BatchFetch annotation and query hint will be added.

The EXISTS option will operate very similar to the existing JOIN batching, just putting the same join condition inside a sub-select. Although it should have be straight forward, I hit several issues with our current sub-selects support that I had to debug and fix. This also lead to discovering some issues with our JPQL support, that also needed to be fixed. EXISTS supports all of the mappings the same as JOIN, but does not require a distinct. m-1 will not use a DISTINCT by default for EXISTS (even though it would avoid duplicates, as the reason for using the EXISTS is normally to avoid the distinct), but the distinct can be enabled on the originating query if desired. EXISTS will still select duplicates for m-m, and not work well with cursors.

The IN option will query a batch of the target objects using an SQL IN clause containing the key values. For a 1-1 the foreign keys from the source rows will be extracted, if these are the primary keys of the target, they will first be filtered by checking the cache for each object. The remaining keys will be split into batches of a query configurable size, defaulting to 256. For composite keys the multi array SQL syntax for ((key1, key2), (...)) IN ((:key1, :key2), (...)) will be used. Only some databases support this syntax, so composite primary keys will only be supported on some databases. For 1-m or m-m the source rows do not contain the primary keys of the target objects. The IN will still be supported, but will be based on join the source query as in JOIN (or maybe EXISTS?) for the set of keys. For cursors the IN size will default to the cursor pageSize, and each cursor page will be processed separately.

Query Casting

JPA/ORM developers will use this query feature to query across attributes in subclasses. This feature is available in JPQL, EclipseLink Expressions and Criteria API.

These examples show on how to use EclipseLink to define queries on inheritance hierarchies with down casting to specific classes (bug 259266).

JPA2.0 Type

Extensions to the expression framework to limit the results to those of a specific subclass have already been implemented as part of the JPA 2.0 effort. Expression.type(Class) is available in the expression framework and equivalent functionality is available in JPQL.

e.g. "select p from Employee e join e.projects p where type(p) = LargeProject" can be used to retrieve all the LargeProjects (Subclass of Project) from Employee.

JPQL Extensions to use Downcast

JPQL is extended to cast in the FROM clause. The format of this will use the keyword "TREAT" and be part of the join clause. The following is an example:

select e from Employee e join TREAT(e.projects AS LargeProject) lp where lp.budget = value

Criteria API

JPA Criteria API already provides a casting operator. It is

As it is defined by JPA 2.0, does a simple cast that allows matching of types within the generics.

EclipseLink 2.1 extends criteria API to allow a cast using The as method has been extended to check the hierarchy and if type is a subclass of the type for the expression that as is being called on a cast will be implemented. Here is a criteria query that will do a cast:

Root<Employee> empRoot = cq1.from(getEntityManagerFactory().getMetamodel().entity(Employee.class));
Join<Employee, Project> join = empRoot.join("projects");
Path exp = ((Path)"budget");
cq1.where(qb1.equal(exp, new Integer(1000)) );

Calling a cast on a JOIN node will permanently alter that node. i.e. In the example above, after calling, join will refer to a LargeProject.

EclipseLink Expression Support for Downcast

We will implement The following is an example of how one could use it:

       ReadAllQuery raq = new ReadAllQuery(Employee.class);
       Expression criteria = raq.getExpressionBuilder().anyOf("projects").as(LargeProject.class).get("budget").greaterThan(100);
       List resultList = query.getResultList();

In this query Employee has a xToMany mapping to Project. LargeProject is a subclass of Project and the "budget" attribute is contained on LargeProject.

  • An exception will be thrown at query execution time if the class that is cast to is not a subclass of the class of the query key being cast.
  • Casts are only allowed on ObjectExpressions (QueryKeyExpression and ExpressionBuilder). The parent expression of a cast must be an ObjectExpression
  • Casts use the same outer join settings as the ObjectExpression they modify
  • Casts modify their parent expression. As a result, when using a cast with a parallel expression, you must use a new instance of the parent expression.
  • Casting is not supported for TablePerClass Inheritance
  • It is prudent to do a check for type in a query that does a cast.
    • The following select f from Foo f join cast(f.bars, BarSubclass) b where b.subclassAttribute = "value"
    • Should be written as: select f from Foo f join cast(f.bars, BarSubclass) b where type(b) = BarSubclass And b.subclassAttribute = "value" by users that wish to enforce the type.
    • EclipseLink will automatically append type information for cases where the cast results in a single type, but for classes in the middle of a hierarchy, no type information will not be appended to the SQL

Example SQL

The following query:

Select e from Employee e join e.projects project

Will currently produce the following sql:

SELECT <select list>
WHERE ((t2.EMP_ID = t1.EMP_ID) AND ((t4.EMPLOYEES_EMP_ID = t1.EMP_ID) AND (t3.PROJ_ID = t4.projects_PROJ_ID)))

If we augment a select criteria like the following

Expression criteria ="budget").greaterThan(100);

The following SQL will be produced:

SELECT <select list> 
WHERE (((t4.BUDGET > ?) AND (t2.EMP_ID = t1.EMP_ID)) AND ((t5.EMPLOYEES_EMP_ID = t1.EMP_ID) AND (t3.PROJ_ID = t5.projects_PROJ_ID)))
bind => [100.0]

The changes as listed above in bold.

Load Group


In order to use a FetchGroup developers must configure default, named, or dynamic FetchGroup instances for use in queries, copying, and merging of entities.

Default FetchGroup

The default FetchGroup is determined through the use of fetch=LAZY on basic mappings. There is no support for relationships in default the default FetchGroup unless the default FetchGroup is manually configured on an entity type's descriptor using API (DescritporCustomizer).

DescriptorCustomizer Example

FetchGroup phoneFG = new FetchGroup();
ClassDescriptor phoneDescriptor = session.getClassDescriptor(PhoneNumber.class);

Named FetchGroup

A named FetchGroup can be configured through annotations or API (DecsriptorCustomizer). This feature includes extensions to the @FetchGroup annotation and eclipselink-orm.xml to support defining FetchGroup items for relationships.

Simple Annotation Example

A defined named FetchGroup works the same as a dynamic FetchGroup where initially no attributes exist in the FetchGroup and only the minimal required attributes for identity and version will be added.

@FetchGroup(name="named-example", attributes={
public class MyEntity {
    private int id;
    private long version;
    private String name;
    private int size;

This same FetchGroup can be specified minimally by excluding the required identity and version attributes that will be added automatically as:

@FetchGroup(name="named-example", attributes={

Relationships Annotation Example

When specifying a relationship it is assumed (in 2.1) that the target of the relationship should be minimally loaded (id and version attributes).

@FetchGroup(name="named-example", attributes={
public class Employee{

In this example when the address attribute is loaded based on the result of a query using this named FetchGroup it will only have its minimal attributes loaded.

@FetchGroup(name="named-example", attributes={
public class Employee{

In this example above the attribute is specified meaning that when the address is loaded its minimal attributes (identity and version) plus the city attribute will be loaded.

Descriptor Customizer Example

public class EmployeeFetchGroupCustomizer implements DescriptorCustomizer {
    public void customize(ClassDescriptor descriptor) throws Exception {
        FetchGroup<Employee> fg = new FetchGroup<Employee>("Employee.fg");


A FetchGroup is used in the processing of a query when a default FetchGroup exists on the entity type's descriptor or one is specified on the query.

Named FetchGroup Example

Query query = em.createQuery("SELECT e FROM Employee e WHERE = :ID");
query.setParameter("ID", Queries.minimumEmployeeId(em));
query.setHint(QueryHints.FETCH_GROUP_NAME, "test");

Dynamic FetchGroup Example

Query query = em.createQuery("SELECT e FROM Employee e WHERE e.gender = :GENDER");
query.setParameter("GENDER", Gender.Male);
// Define the fields to be fetched on Employee
FetchGroup fg = new FetchGroup();
// Configure the dynamic FetchGroup
query.setHint(QueryHints.FETCH_GROUP, fg);
List<Employee> emps = query.getResultList();

Overlapping FetchGroup Queries

Two FetchGroup can be used on different queries for an entity but each only loads one LOB without causing the N additional LOB columns from being loaded.

// Employee has several lob attributes that are very expensive to read.
// To avoid reading the lobs a fetch group is used.
Query query = em1.createQuery("SELECT e FROM Employee e WHERE = 1");
// Employee read into em cache with only firstName and lastName attributes
// (+ primary key and version that are automatically added to any fetch group).
FetchGroup<Employee> namesFG = new FetchGroup<Employee>("namesFG");
query.setHint(QueryHints.FETCH_GROUP_NAME, namesFG);
emp = query.getSingleResult();
// After em1 is closed the Employee object remains in the shared cache.
// The Employee object has EntityFetchGroup that indicates which attributes has been fetched: {id, version, firstName, lastName}
// The user needs to work with lob1 attribute, however accessing
// not fetched attribute would trigger reading of the whole object, including not required attributes lob2, ...lob10.
// Instead the Employee object is read with a new fetch group that consists only of lob1:
FetchGroup<Employee> lob1FG = new FetchGroup<Employee>("lob1FG");
Query query = em2.createQuery("SELECT e FROM Employee e WHERE = 1");
query.setHint(QueryHints.FETCH_GROUP_NAME, lob1FG);
// the Employee object is found in the shared cache,
// the attributes that are not found in the cached Employee's EntityFetchGroup (lob1) are read from the db.
// The resulting object fetched attributes is the union of the two fetch groups: {id, version, firstName, lastName, lob1}
emp = query.getSingleResult();
// lob1 has been already fetched - no reading from the db is required.

Detached Entities

The following usage examples illustrate how a FetchGroup can be used with detached entities.


Copy using custom policy

ObjectCopyingPolicy policy = new ObjectCopyingPolicy();
JpaEntityManager elem = em.unwrap(JpaEntityManager.class);
Employee empCopy = elem.copy(emp, policy);

Copy using LoadGroup

ObjectCopyingPolicy policy = loadGroup.toCopyPolicy();
JpaEntityManager elem = em.unwrap(JpaEntityManager.class);
Employee empCopy = elem.copy(emp, policy);


Load using LoadGroup

Load the specified in LoadGroup relationships for either a single entity or a collection.

LoadGroup loadGroup = new LoadGroup();
JpaEntityManager elem = em.unwrap(JpaEntityManager.class);
elem.load(employeeObject, loadGroup);
elem.load(employeeCollection, loadGroup);

FetchGroups and LoadGroups When a FetchGroup is applied to a query by default the FetchGroup also loads its relational attributes. The qauery for Employees executed with rhe following FetchGroup

FetchGroup fetchGroup = new FetchGroup();

not only limit the results to the specified attributes, but also ensures that all specified relations are loaded. Before returning query result Eclipselink creates a LoadGroup from the FetchGroup and uses it to load the objects.

Hovewer these two functions can be separated.


ensures that only eager relationships are loaded.

A LoadGroup could be created from a FetchGroup.

LoadGroup loadGroup = fetchGroup.toLoadGroup();

All non-relational attributes present in the LoadPlan will be ignored, it will be functionally equivalent to:

LoadGroup loadGroup = new LoadGroup();

Copy using LoadGroup

ObjectCopyingPolicy policy = loadGroup.toCopyPolicy();
JpaEntityManager elem = em.unwrap(JpaEntityManager.class);
Employee empCopy = elem.copy(emp, policy);

FetchGroup "Sparse" Merge

A partial entity with a FetchGroup attached will be merged based on that FetchGroup in combination with cascade merge setting on the entity's mapping.

Copyright © Eclipse Foundation, Inc. All Rights Reserved.