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.
Difference between revisions of "EclipseLink/UserGuide/JPA/Basic JPA Development/Querying/JPQL"
(→OPERATOR) |
(→OPERATOR) |
||
Line 257: | Line 257: | ||
The supported EclipseLink operators include: | The supported EclipseLink operators include: | ||
+ | <table> | ||
+ | <tr><td> | ||
* ToUpperCase | * ToUpperCase | ||
* ToLowerCase | * ToLowerCase | ||
Line 297: | Line 299: | ||
* Nvl | * Nvl | ||
* NewTime | * NewTime | ||
+ | </td><td> | ||
* Ceil | * Ceil | ||
* Cos | * Cos | ||
Line 343: | Line 346: | ||
* SDO_NN | * SDO_NN | ||
* NullIf | * NullIf | ||
− | + | </td></tr> | |
+ | </table> | ||
=====OPERATOR examples===== | =====OPERATOR examples===== |
Revision as of 12:01, 17 April 2012
EclipseLink JPA
EclipseLink | |
Website | |
Download | |
Community | |
Mailing List • Forums • IRC • mattermost | |
Issues | |
Open • Help Wanted • Bug Day | |
Contribute | |
Browse Source |
Contents
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
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();
FROM
ORDER BY
GROUP BY
HAVING
UNION
WHERE
UPDATE
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 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
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 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.
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.
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.
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) |
Function | Description | Example |
CAST | casts the value to the database type | CAST(e.salary NUMERIC(10,2)) |
EXTRACT | extracts the date part from the date/time value | EXTRACT(YEAR, e.startDate) |
REGEXP | evaluates if the string matches the regular expression | e.lastName REGEXP '^Dr\.*' |
Special Functions
JPQL defines several special functions that are not database functions, but have special meaning in JPQL. These include INDEX, KEY, SIZE, IS EMPTY, TYPE, FUNCTION and TREAT. EclipseLink also defines several special functions FUNC, OPERATOR, SQL and COLUMN.
Function | Description | Example |
INDEX | the index of the ordered List element, only supported with @OrderColumn | SELECT l FROM Employee e JOIN e.toDoList l WHERE INDEX(l) = 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 | 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 |
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) | SELECT e FROM Employee JOIN TREAT(e.projects AS LargeProject) p WHERE p.budget > 1000000 |
FUNCTION | call a database function (JPA 2.1) | SELECT p FROM Phone p WHERE FUNCTION('TO_NUMBER', e.areaCode) > 613 |
EclipseLink special functions
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
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.
The supported EclipseLink operators include:
|
|
OPERATOR examples
SELECT e FROM Employee e WHERE OPERATOR('ExtractXml', e.resume, '@years-experience') > 10
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)
- UNION, INTERSECT, EXCEPT support (EL 2.4)
- Usage of object variables in =, <>, IN, IS NULL, and ORDER BY