Notice: This Wiki is now read only and edits are no longer possible. Please see: https://gitlab.eclipse.org/eclipsefdn/helpdesk/-/wikis/Wiki-shutdown-plan for the plan.

Jump to: navigation, search

< EclipseLink‎ | UserGuide‎ | JPA‎ | Basic JPA Development‎ | Querying

EclipseLink JPA

EclipseLink
Website
Download
Community
Mailing List • Forums • IRC • mattermost
Issues
Open • Help Wanted • Bug Day
Contribute
Browse Source

Key API

Query

Native API

1 JPQL

JPQL

The Java Persistence Query Language (JPQL) is the query language defined by JPA. JPQL is similar to SQL, but operates on objects, attributes and relationships instead of tables and columns. JPQL can be used for reading (SELECT), as well as bulk updates (UPDATE) and deletes (DELETE). JPQL can be used in a NamedQuery (through annotations or XML) or in dynamic queries using the EntityManager createQuery() API.

For more information, see Chapter 4 "Query Language" in the JPA Specification.

Select Queries

Select queries can be used to read objects from the database. Select queries can return a single object or data element, a list of objects or data elements, or an object array of multiple objects and data.

Select query examples

// Query for a List of objects.
Query query = em.createQuery("Select e FROM Employee e WHERE e.salary > 100000");
List<Employee> result = query.getResultList();
 
// Query for a single object.
Query query = em.createQuery("Select e FROM Employee e WHERE e.id = :id");
query.setParameter("id", id);
Employee result2 = (Employee)query.getSingleResult();
 
// Query for a single data element.
Query query = em.createQuery("Select MAX(e.salary) FROM Employee e");
BigDecimal result3 = (BigDecimal)query.getSingleResult();
 
// Query for a List of data elements.
Query query = em.createQuery("Select e.firstName FROM Employee e");
List<String> result4 = query.getResultList();
 
// Query for a List of element arrays.
Query query = em.createQuery("Select e.firstName, e.lastName FROM Employee e");
List<Object[]> result5 = query.getResultList();

SELECT Clause

The SELECT clause can contain object expressions, attribute expressions, functions, sub-selects, constructors and aggregation functions.

Aggregation functions

Aggregation functions can include summary information on a set of objects. These include MIN, MAX, AVG, SUM, COUNT. These functions can be used to return a single result, or can be used with a GROUP BY to return multiple results.

SELECT COUNT(e) FROM Employee e

SELECT MAX(e.salary) FROM Employee e

Constructors

The NEW operator can be used with the fully qualified class name to return data objects from a JPQL query. These will not be managed objects, and the class must define a constructor that matches the arguments of the constructor and their types. Constructor queries can be used to select partial data or reporting data on objects, and get back a class instance instead of an object array.

SELECT NEW com.acme.reports.EmpReport(e.firstName, e.lastName, e.salary) FROM Employee e

FROM Clause

The FROM clause defines what is being queried. A typical FROM clause will contain the entity name being queried and assign it an alias.

SELECT e FROM Employee e

JPQL allows for multiple root level objects to be queried. Caution should be used when doing this, as it can result in Cartesian products of the two table. The WHERE or ON clause should ensure the two objects are joined in some way.

SELECT e, a FROM Employee e, MailingAddress a WHERE e.address = a.address

The entity name used in JPQL comes from the name attribute of the @Entity annotation or XML. It defaults to the simple entity class name. EclipseLink also allows for the fully qualified class name of the entity to be used (as of EclipseLink 2.4).

SELECT e FROM com.acme.Employee e

JOIN

A JOIN clause can also be used in the FROM clause. The JOIN clause allows any of the object's relationships to be joined into the query so they can be used in the WHERE clause. JOIN does not mean the relationships will be fetched, unless the FETCH option is included.

SELECT e FROM Employee e JOIN e.address a WHERE a.city = :city

JOIN can be used with OneToOne, ManyToOne, OneToMany, ManyToMany and ElementColleciton mappings. When used with a collection relationship you can join the same relationship multiple times to query multiple independent values.

SELECT e FROM Employee e JOIN e.projects p JOIN e.projects p2 WHERE p.name = :p1 AND p2.name = :p2

JOIN FETCH

The FETCH option can be used on a JOIN to fetch the related objects in a single query. This avoids additional queries for each of the object's relationships, and ensures that the relationships have been fetched if they were LAZY. EclipseLink also supports batch fetching through query hints.

SELECT e FROM Employee e JOIN FETCH e.address

JOIN FETCH normally does not allow an alias, but as of EclipseLink 2.4 an alias is allowed. The alias should be used with caution, as it can affect how the resulting objects are built. Objects should normally always have the same data, no matter how they were queried, this is important for caching and consistency. This is only an issue if the alias is used in the WHERE clause on a collection relationship to filter the related objects that will be fetched. This should not be done, but is sometimes desirable, in which case the query should ensure it has been set to BYPASS the cache.

SELECT e FROM Employee e JOIN FETCH e.address a ORDER BY a.city

LEFT JOIN

By default all joins in JPQL are INNER joins. This means that results that do not have the relationship will be filtered from the query results. To avoid this, a join can be defined as an OUTER join using the LEFT options.

SELECT e FROM Employee e LEFT JOIN e.address a ORDER BY a.city

ON

The join condition used for a join comes from the mapping's join columns. This means that the JPQL user is normally free from having to know how every relationship is joined. In some cases it is desirable to append additional conditions to the join condition, normally in the case of outer joins. This can be done through the ON clause. The ON clause is defined in the JPA 2.1 draft, and is supported in EclipseLink 2.4. EclipseLink also supports usage of the ON clause between two root level objects.

SELECT e FROM Employee e LEFT JOIN e.address ON a.city = :city

SELECT e FROM Employee e LEFT JOIN MailingAddress a ON e.address = a.address

For INNER joins EclipseLink will normally append the join condition to the WHERE clause, but this can be configured in the DatabasePlatform.

Sub-selects in FROM clause

As of EclipseLink 2.4, sub-selects are supported in the FROM clause. This requires that the database supports this functionality.

SELECT e, c.city FROM Employee e, (SELECT DISTINCT a.city FROM Address a) c WHERE e.address.city = c.city

ORDER BY clause

ORDER BY allows the ordering of the results to be specified. Multiple values can be ordered, either ascending (ASC) or descending (DESC). EclipseLink allows functions, sub-selects and other operations in the ORDER BY clause. EclipseLink allows objects expressions to be used in the ORDER BY. In the case of entity objects, they are ordered by their Id, in case of embeddable objects, they are ordered by all of their fields. EclipseLink (as of 2.4) also allows for NULL ordering to be specified (either FIRST or LAST).

SELECT e FROM Employee e ORDER BY e.lastName ASC, e.firstName, ASC

SELECT e FROM Employee e ORDER BY UPPER(e.lastName)

SELECT e FROM Employee e LEFT JOIN e.manager m ORDER BY m.lastName NULLS FIRST

SELECT e FROM Employee e ORDER BY e.address

GROUP BY Clause

GROUP BY allows for summary information to be computed on a set of objects. GROUP BY is normally used in conjunction with aggregation functions. EclipseLink supports using objects, functions and sub-selects in the GROUP BY clause.

SELECT AVG(e.salary), e.address.city FROM Employee e GROUP BY e.address.city

SELECT AVG(e.salary), e.address.city FROM Employee e GROUP BY e.address.city ORDER BY AVG(e.salary)

SELECT e, COUNT(p) FROM Employee e LEFT JOIN e.projects p GROUP BY e

HAVING Clause

The HAVING clause allows for the results of a GROUP BY to be filtered. EclipseLink supports using comparisons, objects, functions and sub-selects in the HAVING clause.

SELECT AVG(e.salary), e.address.city FROM Employee e GROUP BY e.address.city HAVING AVG(e.salary) > 100000

UNION

EclipseLink supports UNION, INTERSECT and EXCEPT operations (as of 2.4). UNION allows the results of two queries with equivalent result structures to be combined into a single query. The unique results from both queries will be returned. If the ALL option is used, then results found in both queries will be duplicated.

INTERSECT returns only the results that are found in both queries. EXCEPT removes the results from the second query from the results from the first query.

The JPA spec does not support union operations.

SELECT MAX(e.salary) FROM Employee e WHERE e.address.city = :city1
UNION SELECT MAX(e.salary) FROM Employee e WHERE e.address.city = :city2

SELECT e FROM Employee e JOIN e.phones p WHERE p.areaCode = :areaCode1
INTERSECT SELECT e FROM Employee e JOIN e.phones p WHERE p.areaCode = :areaCode2

SELECT e FROM Employee e
EXCEPT SELECT e FROM Employee e WHERE e.salary > e.manager.salary

WHERE Clause

The WHERE clause is normally the main part of the query as it defines the conditions that filter what is returned. The WHERE clause can use any comparison operation, logical operation, functions, attributes, objects, and sub-selects. The comparison operations include =, <, >, <=, >=, <>, LIKE, BETWEEN, IS NULL, and IN. NOT can also be used with any comparison operation (NOT LIKE, NOT BETWEEN, IS NOT NULL, NOT IN). The logical operations include AND, OR, and NOT.

EclipseLink also supports the REGEXP operation to perform regular expression comparisons (requires database to support regular expressions). EclipseLink allows for functions and sub-selects to be used with any operation.

Comparison operations
Operation	Description	Example
=	equal	e.firstName = 'Bob'
<	less than	e.salary < 100000
>	greater than	e.salary > :sal
<=	less than or equal	e.salary <= 100000
>=	greater than or equal	e.salary >= :sal
LIKE	evaluates if the two string match, '%' and '_' are valid wildcards, and ESCAPE character is optional	e.firstName LIKE 'A%' OR e.firstName NOT LIKE '%._%' ESCAPE '.'
BETWEEN	evaluates if the value is between the two values	e.firstName BETWEEN 'A' AND 'C'
IS NULL	compares the value to null, databases may not allow or have unexpected results when using = with null	e.endDate IS NULL
IN	evaluates if the value is contained in the list	e.firstName IN ('Bob', 'Fred', 'Joe')

The IN operation allows for a list of values or parameters, a single list parameter, or a sub-select.

e.firstName IN (:name1, :name2, :name3)
e.firstName IN (:name1)
e.firstName IN :names
e.firstName IN (SELECT e2.firstName FROM Employee e2 WHERE e2.lastName = 'Smith')

A sub-select can be used with any operation provided it returns a single value, or if the ALL or ANY options are used. ALL indicates the operation must be true for all elements returned by the sub-select, ANY indicates the operation must be true for any of the elements returned by the sub-select.

e.firstName = (SELECT e2.firstName FROM Employee e2 WHERE e2.id = :id)
e.salary < (SELECT e2.salary FROM Employee e2 WHERE e2.id = :id)
e.firstName = ANY (SELECT e2.firstName FROM Employee e2 WHERE e.id <> e.id)
e.salary <= ALL (SELECT e2.salary FROM Employee e2)

EclipseLink allows the =, <>, IS NULL, IS NOT NULL, IN and NOT IN operations on objects. If IN is used on an object and the object has a composite Id, this requires the database to support nested IN lists.

e.manager = e2.manager
e.manager = :manager
e.manager <> :manager
e.manager IS NULL
e.manager IS NOT NULL
e.manager IN (SELECT e2 FROM Employee e2 WHERE SIZE(e2.managedEmployees) < 2)
e.manager NOT IN (:manager1, :manager2)

Update Queries

You can perform bulk update of entities with the UPDATE statement. This statement operates on a single entity type and sets one or more single-valued properties of the entity subject to the condition in the WHERE clause. Update queries provide an equivalent to the SQL UPDATE statement, but with JPQL conditional expressions.

Update queries do not allow joins, but do support sub-selects. OneToOne and ManyToOne relationships can be traversed in the WHERE clause. Collection relationships can still be queried through using an EXISTS in the WHERE clause with a sub-select. Update queries can only update attributes of the object or its embeddables, its relationships cannot be updated. Complex update queries are dependent on the database's update support, and may make use of temp tables on some databases.

Update queries should only be used for bulk updates, regular updates to objects should be made by using the object's set methods within a transaction and committing the changes.

Update queries return the number of modified rows on the database (row count).

This example demonstrates how to use an update query to give employees a raise. The WHERE clause contains the conditional expression.

Update query example

Query query = em.createQuery("UPDATE Employee e SET e.salary = 60000 WHERE e.salary = 50000");
int rowCount = query.executeUpdate();

The persistence context is not updated to reflect results of update operations. If you use a transaction-scoped persistence context, you should either execute the bulk operation in a transaction all by itself, or be the first operation in the transaction. That is because any entity actively managed by the persistence context will remain unaware of the actual changes occurring at the database level.

The objects in the shared cache that match the update query will be invalidated to ensure subsequent persistence contexts see the updated data.

Delete Queries

You can perform bulk removal of entities with the DELETE statement. Delete queries provide an equivalent to the SQL DELETE statement, but with JPQL conditional expressions.

Delete queries do not allow joins, but do support sub-selects. OneToOne and ManyToOne relationships can be traversed in the WHERE clause. Collection relationships can still be queried through using an EXISTS in the WHERE clause with a sub-select. Complex delete queries are dependent on the database's delete support, and may make use of temp tables on some databases.

Delete queries should only be used for bulk deletes, regular deletes to objects should be performed through calling the EntityManager remove() API.

Delete queries return the number of deleted rows on the database (row count).

This example demonstrates how to use a delete query to remove all employees who are not assigned to a department. The WHERE clause contains the conditional expression.

Delete query example

Query query = em.createQuery("DELETE FROM Employee e WHERE e.department IS NULL");
int rowCount = query.executeUpdate();

Note: Delete queries are polymorphic: any entity subclass instances that meet the criteria of the delete query will be deleted. However, delete queries do not honor cascade rules: no entities other than the type referenced in the query and its subclasses will be removed, even if the entity has relationships to other entities with cascade removes enabled. Delete queries will delete the rows from join and collection tables.

The persistence context is not updated to reflect results of delete operations. If you use a transaction-scoped persistence context, you should either execute the bulk operation in a transaction all by itself, or be the first operation in the transaction. That is because any entity actively managed by the persistence context will remain unaware of the actual changes occurring at the database level.

The objects in the shared cache that match the delete query will be invalidated to ensure subsequent persistence contexts do not see the removed objects.

Parameters

JPA defines named parameters, and positional parameters. Named parameters can be specified in JPQL using the syntax :<name>. Positional parameters can be specified in JPQL using the syntax ? or ?<position>. Positional parameters start at position 1 not 0.

Named parameter query example

Query query = em.createQuery("SELECT e FROM Employee e WHERE e.firstName = :first and e.lastName = :last");
query.setParameter("first", "Bob");
query.setParameter("last", "Smith");
List<Employee> = query.getResultList();

Positional parameter query example

Query query = em.createQuery("SELECT e FROM Employee e WHERE e.firstName = ? and e.lastName = ?");
query.setParameter(1, "Bob");
query.setParameter(2, "Smith");
List<Employee> = query.getResultList();

Literals

Literal values can be in-lined in JPQL for standard Java types. In general it is normally better to use parameters instead of in-lining values. In-lined arguments will prevent the JPQL from benefiting from the EclipseLink's JPQL parser cache, and can potentially make the application vulnerable to JPQL injections attacks.

Each Java types defines its own in-lining syntax:

String - '<string>'
```
SELECT e FROM  Employee e WHERE e.name = 'Bob'
```
- To define a ' (quote) character in a string, the quote is double quoted, i.e. 'Baie-D''Urfé'.

Integer - +|-<digits>

SELECT e FROM  Employee e WHERE e.id = 1234

Long - +|-<digits>L

SELECT e FROM  Employee e WHERE e.id = 1234L

Float - +|-<digits>.<decimale><exponent>F

SELECT s FROM  Stat s WHERE s.ratio > 3.14F

Double - +|-<digits>.<decimale><exponent>D

SELECT s FROM  Stat s WHERE s.ratio > 3.14e32D

Boolean - TRUE | FALSE

SELECT e FROM  Employee e WHERE e.active = TRUE

Date - {d'yyyy-mm-dd'}

SELECT e FROM  Employee e WHERE e.startDate = {d'2012-01-03'}

Time - {t'hh:mm:ss'}

SELECT e FROM  Employee e WHERE e.startTime = {t'09:00:00'}

Timestamp - {ts'yyyy-mm-dd hh:mm:ss.nnnnnnnnn'} -

SELECT e FROM  Employee e WHERE e.version = {ts'2012-01-03 09:00:00.000000001'}

Enum - package.class.enum

SELECT e FROM  Employee e WHERE e.gender = org.acme.Gender.MALE

null - NULL

UPDATE Employee e SET e.manager = NULL WHERE e.manager = :manager

Functions

JPQL supports several database functions. These functions are database independent in name and syntax, but require database support. If the database supports an equivalent function or different syntax the standard JPQL function is supported, if the database does not provide any way to perform the function, then it is not supported. For mathematical functions (+, -, /, *) BEDMAS rules apply.

In EclipseLink's JPQL support functions can be used in the SELECT, WHERE, ORDER BY, GROUP BY and HAVING clauses, as well as inside other functions, with comparison operators, and in constructors.

EclipseLink provides support for several functions beyond the JPA spec. EclipseLink also supports calling specific database functions through FUNCTION, FUNC, and OPERATOR.

JPQL supported functions
Function	Description	Example
-	subtraction	e.salary - 1000
+	addition	e.salary + 1000
*	multiplication	e.salary * 2
/	division	e.salary / 2
ABS	absolute value	ABS(e.salary - e.manager.salary)
CASE	defines a case statement	CASE e.STATUS WHEN 0 THEN 'active' WHEN 1 THEN 'consultant' ELSE 'unknown' END
COALESCE	evaluates to the first non null argument value	COALESCE(e.salary, 0)
CONCAT	concatenates two or more string values	CONCAT(e.firstName, ' ', e.lastName)
CURRENT_DATE	the current date on the database	CURRENT_DATE
CURRENT_TIME	the current time on the database	CURRENT_TIME
CURRENT_TIMESTAMP	the current date-time on the database	CURRENT_TIMESTAMP
LENGTH	the character/byte length of the character or binary value	LENGTH(e.lastName)
LOCATE	the index of the string within the string, optionally starting at a start index	LOCATE('-', e.lastName)
LOWER	convert the string value to lower case	LOWER(e.lastName)
MOD	computes the remainder of dividing the first integer by the second	MOD(e.hoursWorked / 8)
NULLIF	returns null if the first argument to equal to the second argument, otherwise returns the first argument	NULLIF(e.salary, 0)
SQRT	computes the square root of the number	SQRT(o.RESULT)
SUBSTRING	the substring from the string, starting at the index, optionally with the substring size	SUBSTRING(e.lastName, 0, 2)
TRIM	trims leading, trailing, or both spaces or optional trim character from the string	TRIM(TRAILING FROM e.lastName), TRIM(e.lastName), TRIM(LEADING '-' FROM e.lastName)
UPPER	convert the string value to upper case	UPPER(e.lastName)

EclipseLink supported functions
Function	Description	Example
CAST	casts the value to the database type (as of 2.4)	CAST(e.salary NUMERIC(10,2))
EXTRACT	extracts the date part from the date/time value (as of 2.4)	EXTRACT(YEAR FROM e.startDate)
REGEXP	evaluates if the string matches the regular expression (as of 2.4)	e.lastName REGEXP '^Dr\.*'

Special Operators

JPQL defines several special operators that are not database functions, but have special meaning in JPQL. These include INDEX, KEY, SIZE, IS EMPTY, TYPE, FUNCTION and TREAT. EclipseLink (as of 2.4) also defines several special functions FUNC, OPERATOR, SQL and COLUMN.

JPQL special operators
Function	Description	Example
INDEX	the index of the ordered List element, only supported with @OrderColumn	SELECT toDo FROM Employee e JOIN e.toDoList toDo WHERE INDEX(toDo) = 1
KEY	the key of the Map element	SELECT p FROM Employee e JOIN e.priorities p WHERE KEY(p) = 'high'
SIZE	the size of the collection relationships, this evaluates to a sub-select	SELECT e FROM Employee e WHERE SIZE(e.managedEmployees) < 2
IS EMPTY, IS NOT EMPTY	evaluates to true if the collection relationship is empty, this evaluates to a sub-select	SELECT e FROM Employee e WHERE e.managedEmployees IS EMPTY
MEMBER OF, NOT MEMBER OF	evaluates to true if the collection relationship contains the value, this evaluates to a sub-select	SELECT e FROM Employee e WHERE 'write code' MEMBER OF e.responsibilities
TYPE	the inheritance discriminator value	SELECT p FROM Project p WHERE TYPE(p) = LargeProject
TREAT	treat (cast) the object as its subclass value (JPA 2.1 draft)	SELECT e FROM Employee JOIN TREAT(e.projects AS LargeProject) p WHERE p.budget > 1000000
FUNCTION	call a database function (JPA 2.1 draft)	SELECT p FROM Phone p WHERE FUNCTION('TO_NUMBER', p.areaCode) > 613

EclipseLink special operators

EclipseLink defines several special JPQL operators that allow performing database operations that are not possible in basic JPQL. These include FUNC, OPERATOR, SQL and COLUMN.

FUNC

FUNC allows for a database function to be call from JPQL. It allows calling any database functions not supported directly in JPQL, and calling user or library specific functions. FUNC is database specific, in that it does not translate the function call in any way to support different databases as other JPQL functions do. FUNC can only be used to call functions with normal syntax, functions that require special syntax cannot be called with FUNC. FUNC has been replaced by FUNCTION in JPA 2.1, so FUNCTION should be used as of EclipseLink 2.4.

FUNC examples

SELECT p FROM Phone p WHERE FUNC('TO_NUMBER', e.areaCode) > 613

SELECT FUNC('YEAR', e.startDate) AS YEAR, COUNT(e) FROM Employee e GROUP BY YEAR

FUNC Oracle Spatial examples

The following examples show how to use FUNC with Oracle Spatial queries:

SELECT a FROM Asset a, Geography geo WHERE geo.id = :id AND a.id IN :id_list AND FUNC('ST_INTERSECTS', a.geometry, geo.geometry) = 'TRUE'

SELECT s FROM SimpleSpatial s WHERE FUNC('MDSYS.SDO_RELATE', s.jGeometry, :otherGeometry, :params) = 'TRUE' ORDER BY s.id ASC

OPERATOR

OPERATOR allows for any EclipseLink operator to be called. EclipseLink supports many database functions using standard operator names that are then translated to different databases. EclipseLink operators are supported on any database that has an equivalent function (or set of functions). The EclipseLink ExpressionOperator clas can be used to allow a DatabasePlatform to override an operator, or define custom operators. OPERATOR is similar to FUNC, but allows the function to be database independent, and allows calling functions that require special syntax. OPERATOR requires EclipseLink 2.4.

The supported EclipseLink operators include:

Abs
ToUpperCase
ToLowerCase
Chr
Concat
Coalesce
Case
HexToRaw
Initcap
Instring
Soundex
LeftPad
LeftTrim
RightPad
RightTrim
Substring
Translate
Ascii
Length
CharIndex
Cast
Extract

CharLength
Difference
Reverse
Replicate
Right
Locate
ToNumber
ToChar
AddMonths
DateToString
MonthsBetween
NextDay
RoundDate
AddDate
DateName
DatePart
DateDifference
TruncateDate
NewTime
Nvl
NewTime
Ceil

Cos
Cosh
Acos
Asin
Atan
Exp
Sqrt
Floor
Ln
Log
Mod
Power
Round
Sign
Sin
Sinh
Tan
Tanh
Trunc
Greatest
Least

Add
Subtract
Divide
Multiply
Atan2
Cot
Deref
Ref
RefToHex
Value
ExtractXml
ExtractValue
ExistsNode
GetStringVal
GetNumberVal
IsFragment
SDO_WITHIN_DISTANCE
SDO_RELATE
SDO_FILTER
SDO_NN
NullIf

OPERATOR examples

SELECT e FROM Employee e WHERE OPERATOR('ExtractXml', e.resume, '@years-experience') > 10

SQL

SQL allows for the usage and integration of SQL within JPQL. SQL includes the SQL string to inline into the JPQL and the arguments to translate into the SQL string. The '?' character is used to define parameters within the SQL that are translated from the SQL function arguments. SQL allows for the calling of database functions with non standard syntax, the embedding of SQL literals, and performing any other SQL operations within JPQL. The SQL function offers an alternative to using native SQL queries just because one part of the query requires something that is not supported in JPQL. Now JPQL can still be used for the query, and the SQL function used for the SQL specific parts. SQL requires EclipseLink 2.4.

SQL examples

SELECT p FROM Phone p WHERE SQL('CAST(? AS CHAR(3))', e.areaCode) = '613'

SELECT SQL('EXTRACT(YEAR FROM ?)', e.startDate) AS YEAR, COUNT(e) FROM Employee e GROUP BY YEAR

SELECT e FROM Employee e ORDER BY SQL('? NULLS FIRST', e.startDate)

SELECT e FROM Employee e WHERE e.startDate = SQL('(SELECT SYSDATE FROM DUAL)')

COLUMN

COLUMN allows access to unmapped columns in an object's table. This can be used to access foreign key columns, inheritance discriminators, or primitive columns such as ROWID. COLUMN can also be used in JPQL fragments inside the @AdditionalCriteria annotation. COLUMN requires EclipseLink 2.4.

COLUMN examples

SELECT e FROM Employee e WHERE COLUMN('MANAGER_ID', e) = :id

SELECT e FROM Employee e WHERE COLUMN('ROWID', e) = :id

TABLE

TABLE allows access to unmapped tables. This can be used to access join, collection, history, auditing, or system tables for use in JPQL queries. TABLE requires EclipseLink 2.4.

TABLE example

SELECT e, a.LAST_UPDATE_USER FROM Employee e, TABLE('AUDIT') a WHERE a.TABLE = 'EMPLOYEE' AND a.ROWID = COLUMN('ROWID', e)

EclipseLink Extensions (EQL)

EclipseLink provides many extensions to the standard JPA JPQL. These extensions provide access to additional database features many of which are part of the SQL standard, provide access to native database features and functions, and provide access to EclipseLink specific features. EclipseLink JPQL extensions are referred to as the EclipseLink Query Language (EQL).

EclipseLink's JPQL extensions include:

Less restrictions than JPQL, allows sub-selects and functions within operations such as LIKE, IN, ORDER BY, constructors, functions etc.
Allow != in place of <>
FUNC operation to call database specific functions (now FUNCTION in JPA 2.1)
TREAT operation to downcast related entities with inheritance
OPERATOR operation to call EclipseLink database independent functions (EL 2.4)
SQL operation to mix SQL with JPQL (EL 2.4)
CAST and EXTRACT functions (EL 2.4)
REGEXP function for regular expression querying (EL 2.4)
Usage of sub-selects in the SELECT and FROM clause (EL 2.4)
ON clause support for defining JOIN and LEFT JOIN conditions (EL 2.4)
Joins between independent entities (EL 2.4)
Usage of an alias on a JOIN FETCH (EL 2.4)
COLUMN operation to allow querying on non mapped columns (EL 2.4)
TABLE operation to allow querying on non mapped tables (EL 2.4)
UNION, INTERSECT, EXCEPT support (EL 2.4)
Usage of object variables in =, <>, IN, IS NULL, and ORDER BY

Version: 2.4 DRAFT
Other versions...

Querying	Querying	Criteria API
EclipseLink Home JPA User Guide: Table of Contents, Search
How to contribute to this guide...

Categories:

Retrieved from "https://wiki.eclipse.org/index.php?title=EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/JPQL&oldid=415300"

Breadcrumbs