Расширения JPQL

В этом разделе описываются расширения Java Persistence Query Language, которые можно использовать в приложениях Jmix.

Атрибуты сессии и пользователя

Атрибуты сессии, созданные с помощью SessionData, доступны в любом запросе JPQL с префиксом session_. Например, можно задать атрибут, используя SessionData:

@Autowired
private ObjectProvider<SessionData> sessionDataProvider;

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

А затем следующим образом использовать значение атрибута customerCode в запросе:

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

Аналогично атрибутам сессии, можно получить доступ к атрибутам текущего аутентифицированного пользователя с помощью параметров с префиксом current_user_. Например, если сущность User имеет атрибут email, его можно использовать в запросе следующим образом (при условии, что атрибут Customer.manager является ссылкой на сущность User):

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

Вам не нужно задавать значение для параметров session_customerCode или current_user_email, так как они будут назначены автоматически перед выполнением запроса. Это особенно полезно при использовании тех функций фреймворка, где у вас нет полного контроля над выполнением запроса, например, JPQL-политик ролей уровня строк или JPQL-условий genericFilter компонента фильтра.

Можно использовать префикс (?i) в значении параметра запроса, чтобы легко указать условия для поиска без учета регистра по любой части строки. Например, рассмотрим запрос:

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

Если передать строку (?i)%doe% в качестве значения параметра name, запрос вернет John Doe, если такая запись существует в базе данных, даже если регистр символов отличается. Это произойдет потому, что фреймворк выполнит SQL-запрос с условием lower(C.NAME) like ? и значением %doe% параметра.

Обратите внимание, что такой поиск не будет использовать индекс для поля имени, даже если таковой существует в базе данных.

Функции

в таблице ниже перечислены функции JPQL и их уровень поддержки в Jmix.

Функция Поддержка Запрос

Агрегатные функции

ДА

SELECT AVG(o.quantity) FROM Order o

НЕТ: агрегатные функции со скалярным выражением (особенность EclipseLink)

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

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

ALL, ANY, SOME

ДА

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

Арифметические функции (INDEX, SIZE, ABS, SQRT, MOD)

ДА

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

ДА

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

НЕТ: CASE в UPDATE-запросе

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

Функции даты (CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP)

ДА

SELECT e FROM Order e WHERE e.date = CURRENT_DATE

Функции EclipseLink (CAST, REGEXP, EXTRACT)

ДА

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)

НЕТ: CAST в запросе GROUP BY

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

Операторы типов сущности

ДА: тип сущности передается как параметр

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

НЕТ: прямая ссылка на сущность

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

Вызов функций

ДА: результат с операторами сравнения

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

НЕТ: прямое использование результата функции

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

IN

ДА

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

IS EMPTY для коллекций

ДА

SELECT e FROM Employee e WHERE e.projects IS EMPTY

KEY/VALUE

НЕТ

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

Литералы

ДА

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

НЕТ: литералы даты и времени

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

ДА: поля или результаты запроса

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

НЕТ: литералы

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

NEW в SELECT

ДА

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

ДА

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

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

NULLS FIRST, NULLS LAST в order by

ДА

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

Строковые функции (CONCAT, SUBSTRING, TRIM, LOWER, UPPER, LENGTH, LOCATE)

ДА

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'

НЕТ: TRIM не поддерживается с trim char

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

Вложенные запросы

ДА

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

НЕТ: path-выражения вместо имени сущности в FROM подзапроса

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

TREAT

ДА

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

НЕТ: TREAT в WHERE-выражениях

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

Макросы

Текст запроса JPQL может содержать макросы, которые обрабатываются перед выполнением запроса. Они преобразуются в исполнимый JPQL и могут дополнительно изменять набор параметров запроса.

Макросы решают следующие проблемы:

  • Обеспечивают обходной путь для ограничения JPQL, которое делает невозможным выражение условия зависимости данного поля от текущего времени (т.е. выражения типа "current_date - 1" не работают).

  • Позволяют сравнивать поля типа Timestamp (поля даты/времени) с датой.

@between

Имеет формат @between(field_name, moment1, moment2, time_unit) или @between(field_name, moment1, moment2, time_unit, user_timezone), где:

  • field_name – это имя сравниваемого атрибута.

  • moment1, moment2 – начальная и конечная точки временного интервала, в который должно попадать значение field_name. Каждая из точек должна быть определена выражением, содержащим переменную now с добавлением или вычитанием целого числа.

  • time_unit – определяет единицу измерения для временного интервала, добавляемого или вычитаемого из now в выражениях временных точек и точности округления временных точек. Может быть одним из следующих: year, month, day, hour, minute, second.

  • user_timezone - необязательный аргумент, который, если задан, определяет, что в запросе должен учитываться часовой пояс текущего пользователя.

Макрос преобразуется в следующее выражение в JPQL: field_name >= :moment1 and field_name < :moment2

Пример 1. Customer был создан сегодня:

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

Пример 2. Customer был создан в течение последних 10 минут:

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

Пример 3. Документы, датированные в течение последних 5 дней, с учетом текущего часового пояса пользователя:

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

@today

Имеет формат @today(field_name) или @today(field_name, user_timezone) и помогает определить условие, проверяющее, что значение атрибута находится в пределах текущего дня. По сути, это частный случай макроса @between.

Пример. Customer был создан сегодня:

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

@dateEquals

Имеет формат @dateEquals(field_name, parameter) или @dateEquals(field_name, parameter, user_timezone) и позволяет определить условие, проверяющее, что значение field_name (в формате Timestamp) находится в пределах дня, переданного в качестве parameter.

Пример:

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

Можно передать текущую дату, используя параметр now. Чтобы задать смещение в днях, используйте now с + или -, например:

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

@dateBefore

Имеет формат @dateBefore(field_name, parameter) или @dateBefore(field_name, parameter, user_timezone) и позволяет определить условие, проверяющее, что значение field_name (в формате Timestamp) является более ранним, чем дата, переданная в качестве parameter.

Пример:

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

Можно передать текущую дату, используя параметр now. Чтобы задать смещение в днях, используйте now с + или -, например:

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

@dateAfter

Имеет формат @dateAfter(field_name, parameter) или @dateAfter(field_name, parameter, user_timezone) и позволяет определить условие, при котором дата значения field_name (в формате Timestamp) позднее или равна дате, переданной в качестве parameter.

Пример:

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

Можно передать текущую дату, используя параметр now. Чтобы задать смещение в днях, используйте now с + или -, например:

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

@enum

Позволяет использовать полное имя константы перечисления вместо ее идентификатора базы данных. Это упрощает поиск вариантов использования перечислений по всему коду приложения.

Пример:

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