5.4.4.4.3. Macros in JPQL
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 (i.e. expressions like "current_date -1" do not work).
-
Enable comparing
Timestamp
type fields (the date/time fields) with a date.
Let us consider them in more detail:
- @between
-
Has the format
@between(field_name, moment1, moment2, time_unit)
, 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 containingnow
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
.
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 sales$Customer where @between(c.createTs, now, now+1, day)
Example 2. Customer was created within the last 10 minutes:
select c from sales$Customer where @between(c.createTs, now-10, now, minute)
Example 3. Documents dated within the last 5 work days (for the projects including workflow):
select d from sales$Doc where @between(d.createTs, now-5, now, workday)
-
- @today
-
Has the format
@today(field_name)
and helps to define a condition checking that the attribute value falls into the current date. Essentially, this is a special case of the@between
macro.Example. Customer was created today:
select d from sales$Doc where @today(d.createTs)
- @dateEquals
-
Has the format
@dateEquals(field_name, parameter)
and allows you to define a condition checking thatfield_name
value (inTimestamp
format) falls into the date passed asparameter
.Example:
select d from sales$Doc where @dateEquals(d.createTs, :param)
- @dateBefore
-
Has the format
@dateBefore(field_name, parameter
) and allows you to define a condition checking thatfield_name
value (inTimestamp
format) is smaller than the date passed asparameter
.Example:
select d from sales$Doc where @dateBefore(d.createTs, :param)
- @dateAfter
-
Has the format
@dateAfter(field_name, parameter
) and allows you to define a condition that the date of thefield_name
value (inTimestamp
format) is more or equal to the date passed asparameter
.Example:
select d from sales$Doc where @dateAfter(d.createTs, :param)
- @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 r from sec$Role where r.type = @enum(com.haulmont.cuba.security.entity.RoleType.SUPER) order by r.name