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.
Case-Insensitive Substring Search
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 |
|
Not supported: aggregate functions with scalar expression (EclipseLink feature) |
|
|
ALL, ANY, SOME |
Supported |
|
Arithmetic Functions (INDEX, SIZE, ABS, SQRT, MOD) |
Supported |
|
CASE Expressions |
Supported |
|
Not supported: CASE in UPDATE query |
|
|
Date Functions (CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP) |
Supported |
|
EclipseLink Functions (CAST, REGEXP, EXTRACT) |
Supported |
|
Not supported: CAST in GROUP BY clause |
|
|
Entity Type Expression |
Supported: entity type passed as a parameter |
|
Not supported: direct link to an entity type |
|
|
Function Invocation |
Supported: function result in comparison clauses |
|
Not supported: function result as is |
|
|
IN |
Supported |
|
IS EMPTY collection |
Supported |
|
KEY/VALUE |
Not supported |
|
Literals |
Supported |
|
Not supported: date and time literals |
|
|
MEMBER OF |
Supported: fields or query results |
|
Not supported: literals |
|
|
NEW in SELECT |
Supported |
|
NULLIF/COALESCE |
Supported |
|
NULLS FIRST, NULLS LAST in order by |
Supported |
|
String Functions (CONCAT, SUBSTRING, TRIM, LOWER, UPPER, LENGTH, LOCATE) |
Supported |
|
Not supported: TRIM with trim char |
|
|
Subquery |
Supported |
|
Not supported: path expression instead of entity name in subquery’s FROM |
|
|
TREAT |
Supported |
|
Not supported: TREAT in WHERE clauses |
|
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 offield_name
should fall into. Each of the points should be defined by an expression containing thenow
variable with an addition or subtraction of an integer number. -
time_unit
- defines the unit for time interval added to or subtracted fromnow
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)