JPQL Extensions

This section describes Java Persistence Query Language extensions that can be used in Jmix applications.

Session and User Attributes

Session attributes created using SessionData are available in any JPQL query with the session_ prefix. For example, you can set an attribute using SessionData:

@Autowired
private ObjectProvider<SessionData> sessionDataProvider;

void setCustomerCodeInSession(String code) {
    sessionDataProvider.getObject().setAttribute("customerCode", code);
}

Then you can use the customerCode attribute value in a query as follows:

select e from Customer e where e.code = :session_customerCode

Similar to session attributes, you can access attributes of the currently authenticated user through parameters with the current_user_ prefix. For example, if the user entity has the email attribute, you can use it in a query as follows (provided that the Customer.manager attribute is a reference to the user entity):

select e from Customer e where e.manager.email = :current_user_email

You don’t have to set the value for the session_customerCode or current_user_email parameter. They will be assigned automatically before the query execution. This is especially useful in the framework features where you don’t have full control over the execution of the query, like JPQL policies of row-level roles or JPQL conditions of the genericFilter component.

You can use the (?i) prefix in the value of a query parameter to easily specify conditions for case-insensitive search by any part of the string. For example, consider the query:

select c from Customer c where c.name like :name

If you pass the (?i)%doe% string as a value of the name parameter, the query will return John Doe if such a record exists in the database, even though the case of characters is different. It will happen because the framework will run the SQL query with the lower(C.NAME) like ? condition and %doe% parameter value.

Note that such search will not use the index on the name field, even if such exists in the database.

Functions

The table below lists the JPQL functions and their level of support in Jmix.

Function Support Query

Aggregate Functions

Supported

SELECT AVG(o.quantity) FROM Order o

Not supported: aggregate functions with scalar expression (EclipseLink feature)

SELECT AVG(o.quantity)/2.0 FROM Order o

SELECT AVG(o.quantity * o.price) FROM Order o

ALL, ANY, SOME

Supported

SELECT emp FROM Employee emp WHERE emp.salary > ALL (SELECT m.salary FROM app_Manager m WHERE m.department = emp.department)

Arithmetic Functions (INDEX, SIZE, ABS, SQRT, MOD)

Supported

SELECT w.name FROM Course c JOIN c.studentWaitlist w WHERE c.name = 'Calculus' AND INDEX(w) = 0

SELECT w.name FROM Course c WHERE c.name = 'Calculus' AND SIZE(c.studentWaitlist) = 1

SELECT w.name FROM Course c WHERE c.name = 'Calculus' AND ABS(c.time) = 10

SELECT w.name FROM Course c WHERE c.name = 'Calculus' AND SQRT(c.time) = 10.5

SELECT w.name FROM Course c WHERE c.name = 'Calculus' AND MOD(c.time, c.time1) = 2

CASE Expressions

Supported

SELECT e.name, f.name, CONCAT(CASE WHEN f.annualMiles > 50000 THEN 'Platinum ' WHEN f.annualMiles > 25000 THEN 'Gold ' ELSE '' END, 'Frequent Flyer') FROM Employee e JOIN e.frequentFlierPlan f

Not supported: CASE in UPDATE query

UPDATE Employee e SET e.salary = CASE e.rating WHEN 1 THEN e.salary * 1.1 WHEN 2 THEN e.salary * 1.05 ELSE e.salary * 1.01 END

Date Functions (CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP)

Supported

SELECT e FROM Order e WHERE e.date = CURRENT_DATE

EclipseLink Functions (CAST, REGEXP, EXTRACT)

Supported

SELECT EXTRACT(YEAR FROM e.createTs) FROM MyEntity e WHERE EXTRACT(YEAR FROM e.createTs) > 2012

SELECT e FROM MyEntity e WHERE e.name REGEXP '.*'

SELECT CAST(e.number text) FROM MyEntity e WHERE e.path LIKE CAST(:ds$myEntityDs.id text)

Not supported: CAST in GROUP BY clause

SELECT e FROM Order e WHERE e.amount > 100 GROUP BY CAST(e.orderDate date)

Entity Type Expression

Supported: entity type passed as a parameter

SELECT e FROM Employee e WHERE TYPE(e) IN (:empType1, :empType2)

Not supported: direct link to an entity type

SELECT e FROM Employee e WHERE TYPE(e) IN (app_Exempt, app_Contractor)

Function Invocation

Supported: function result in comparison clauses

SELECT u FROM User u WHERE function('DAYOFMONTH', u.createTs) = 1

Not supported: function result as is

SELECT u FROM User u WHERE function('hasRoles', u.createdBy, u.login)

IN

Supported

SELECT e FROM Employee e, IN(e.projects) p WHERE p.budget > 1000000

IS EMPTY collection

Supported

SELECT e FROM Employee e WHERE e.projects IS EMPTY

KEY/VALUE

Not supported

SELECT v.location.street, KEY(i).title, VALUE(i) FROM VideoStore v JOIN v.videoInventory i WHERE v.location.zipcode = '94301' AND VALUE(i) > 0

Literals

Supported

SELECT e FROM Employee e WHERE e.name = 'Bob'

SELECT e FROM Employee e WHERE e.id = 1234

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

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

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

SELECT e FROM Employee e WHERE e.active = TRUE

Not supported: date and time literals

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

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

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

MEMBER OF

Supported: fields or query results

SELECT d FROM app_Department d WHERE (select e from Employee e where e.id = :eParam) MEMBER OF d.employees

Not supported: literals

SELECT e FROM Employee e WHERE 'write code' MEMBER OF e.codes

NEW in SELECT

Supported

SELECT NEW com.company.example.CustomerDetails(c.id, c.status, o.count) FROM Customer c JOIN c.orders o WHERE o.count > 100

NULLIF/COALESCE

Supported

SELECT NULLIF(emp.salary, 10) FROM Employee emp

SELECT COALESCE(emp.salary, emp.salaryOld, 10) FROM Employee emp

NULLS FIRST, NULLS LAST in order by

Supported

SELECT h FROM GroupHierarchy h ORDER BY h.level DESC NULLS FIRST

String Functions (CONCAT, SUBSTRING, TRIM, LOWER, UPPER, LENGTH, LOCATE)

Supported

SELECT x FROM Magazine x WHERE CONCAT(x.title, 's') = 'JDJs'

SELECT x FROM Magazine x WHERE SUBSTRING(x.title, 1, 1) = 'J'

SELECT x FROM Magazine x WHERE LOWER(x.title) = 'd'

SELECT x FROM Magazine x WHERE UPPER(x.title) = 'D'

SELECT x FROM Magazine x WHERE LENGTH(x.title) = 10

SELECT x FROM Magazine x WHERE LOCATE('A', x.title, 4) = 6

SELECT x FROM Magazine x WHERE TRIM(TRAILING FROM x.title) = 'D'

Not supported: TRIM with trim char

SELECT x FROM Magazine x WHERE TRIM(TRAILING 'J' FROM x.title) = 'D'

Subquery

Supported

SELECT goodCustomer FROM Customer goodCustomer WHERE goodCustomer.balanceOwed < (SELECT AVG(c.balanceOwed) FROM Customer c)

Not supported: path expression instead of entity name in subquery’s FROM

SELECT c FROM Customer c WHERE (SELECT AVG(o.price) FROM c.orders o) > 100

TREAT

Supported

SELECT e FROM Employee e JOIN TREAT(e.projects AS LargeProject) p WHERE p.budget > 1000000

Not supported: TREAT in WHERE clauses

SELECT e FROM Employee e JOIN e.projects p WHERE TREAT(p as LargeProject).budget > 1000000

Macros

JPQL query text can include macros, which are processed before the query is executed. They are converted into the executable JPQL and can additionally modify the set of query parameters.

The macros solve the following problems:

  • Provide a workaround for the limitation of JPQL, which makes it impossible to express the condition of dependency of a given field on current time (for example, expressions like "current_date - 1" do not work).

  • Enable comparing Timestamp type fields (the date/time fields) with a date.

@between

Has the format @between(field_name, moment1, moment2, time_unit) or @between(field_name, moment1, moment2, time_unit, user_timezone), where

  • field_name is the name of the compared attribute.

  • moment1, moment2 - start and end points of the time interval where the value of field_name should fall into. Each of the points should be defined by an expression containing the now variable with an addition or subtraction of an integer number.

  • time_unit - defines the unit for time interval added to or subtracted from now in the time point expressions and time points rounding precision. May be one of the following: year, month, day, hour, minute, second.

  • user_timezone - an optional argument that if set, defines that the current user’s time zone must be considered in the query.

The macro gets converted to the following expression in JPQL: field_name >= :moment1 and field_name < :moment2.

Example 1. Customer was created today:

select c from Customer where @between(c.createTs, now, now+1, day)

Example 2. Customer was created within the last 10 minutes:

select c from Customer where @between(c.createTs, now-10, now, minute)

Example 3. Documents dated within the last 5 days, considering current user time zone:

select d from Doc where @between(d.createTs, now-5, now, day, user_timezone)

@today

Has the format @today(field_name) or @today(field_name, user_timezone) and helps to define a condition checking that the attribute value is within the current day. Essentially, this is a special case of the @between macro.

Example. Customer was created today:

select d from Doc where @today(d.createTs)

@dateEquals

Has the format @dateEquals(field_name, parameter) or @dateEquals(field_name, parameter, user_timezone) and allows you to define a condition checking that field_name value (in Timestamp format) is within the day passed as parameter.

Example:

select d from Doc where @dateEquals(d.createTs, :param)

You can pass the current date using the now parameter. To set an offset in days, use now with + or -, for example:

select d from sales_Doc where @dateEquals(d.createTs, now-1)

@dateBefore

Has the format @dateBefore(field_name, parameter) or @dateBefore(field_name, parameter, user_timezone) and allows you to define a condition checking that field_name value (in Timestamp format) is earlier than the date passed as parameter.

Example:

select d from Doc where @dateBefore(d.createTs, :param, user_timezone)

You can pass the current date using the now attribute. To set an offset in days, use now with + or -, for example:

select d from sales_Doc where @dateBefore(d.createTs, now+1)

@dateAfter

Has the format @dateAfter(field_name, parameter) or @dateAfter(field_name, parameter, user_timezone) and allows you to define a condition that the date of the field_name value (in Timestamp format) is after or equal to the date passed as parameter.

Example:

select d from Doc where @dateAfter(d.createTs, :param)

You can pass the current date using the now attribute. To set an offset in days, use now with + or -, for example:

select d from Doc where @dateAfter(d.createTs, now-1)

@enum

Allows you to use a fully qualified enum constant name instead of its database identifier. This simplifies searching for enum usages throughout the application code.

Example:

select d from Doc where d.type = @enum(com.company.sample.entity.DocType.INVOICE)