07-jpql
TRANSCRIPT
Query Language (JPQL)
1
ОбзорОбзор
Что такое и что такое не JPQL
Терминология
Пути (Path Expression)
SELECT запросы
Выражение SELECT
Выражения конструирования
Выражение SELECT и полиморфизм
Выражение FROM. Идентификационные переменные
ОбзорОбзор
Объединения (Joins)
Inner JOIN в collection-valued ассоциациях
Inner JOIN в single-valued ассоциациях
Неявный JOIN
Определение Joint в условии WHERE
Multiple JOIN
Outer JOIN
Fetch JOIN
Выражение WHERE
ОбзорОбзор
Условные операции
Параметры
Выражение BETWEEN
Выражение LIKE
Подзапросы
Выражение IN
Выражения коллекций
Выражение EXISTS
Выражение ANY, ALL, SOME
ОбзорОбзор
Функции
Выражение ORDER BY
Агрегатные запросы
Агрегатные функции
Выражение Group BY
Выражение Having
Запросы Update
Запросы Delete
Что такое и что такое Что такое и что такое нене JPQLJPQL
JPQL это не SQL JPQL оперирует в терминах сущностей, а не в
терминах таблиц. Таким образом, отсутствие некоторых возможностей, которые есть в SQL это не лимитация JPQL, просто они логически не нужны
Похожесть SQL и JPQL только для удобства освоения
Запросы, написанные на JPQL транслируются в специфический SQL диалект современных БД Это означает портируемость приложения
6
Что такое и что такое Что такое и что такое нене JPQLJPQL
Нет необходимости знать мэпинги всех сущностей при написании запроса Программист оперирует объектами доменной
модели
Критические места приложения могут быть написаны на явном SQL
Каждый провайдер предоставляет возможность вывести на консоль SQL, который он генерирует
7
ТерминологияТерминология
Запросы разделяются на 4 категории: Select Aggregate Update Delete
Запросы оперируют на наборе сущностей, определенных в persistence unit. Этот набор образует домен сущностей
Запрос ссылается на сущности по имени @Entity(name=“xxx”) Имя класса сущности
8
ТерминологияТерминология
Сущность состоит из одного или нескольких атрибутов, которые делятся на: Простые persistent атрибуты – атрибуты
состояния (состояние) Атрибуты, являющиеся отношениями –
атрибуты ассоциаций (ассоциация) Запросы являются нечувствительными к регистру,
за исключением: Имен сущностей Атрибутов сущностей
9
Пути (Пути (PathPath Expression)Expression)
Пути позволяют осуществлять навигацию к полям состояния и/или к полям ассоциаций
Точка (.) разделяет атрибуты пути Пути, в зависимости от количества возвращенных
элементов, разделяются на: Пути полей состояния (e.name) Пути single-valued ассоциаций (e.department) Пути collection-valued ассоциаций (e.managers)
Пути может содержать навигацию по нескольким атрибутам e.department.name
Пути являются ключевым понятием в написании эффективных запросов
10
ПримерПример
11
SELECT SELECT запросызапросы
Наиболее распространенный вид запросов
В общем виде выглядит:
select_statement :: = select_clause from_clause [where_clause] [groupby_clause][having_clause] [orderby_clause]
Алиас называется переменной идентификации и является обязательным в JPQL
12
ПримерПример. SELECT . SELECT запросзапрос
Простейшая форма, содержащая обязательные поля:SELECT e FROM Employee e
Запрос вернет 0 или несколько объектов Employee
Провайдер сгенерирует SQL:SELECT id, name, salary, manager_id, dept_id, address_id
FROM emp
В случае ассоциаций провайдер сгенерирует дополнительный SQL
13
Выражение Выражение SELECTSELECT
В выражении SELECT может использоваться: Идентификационная переменная SELECT d FROM
Department d
Поле состояния SELECT d.name FROM Department d
Single-valued ассоциация SELECT e.department FROM Employee e
Следующий запрос неправилен:SELECT d.employees FROM Department d
Ключевое слово DISTINCT удаляет дубликаты:SELECT DISTINCT e.department FROM Employee e
14
Выражение Выражение SELECTSELECT
Допустимо выбирать несколько значений в выражении SELECT:SELECT e.name, e.salary FROM Employee e
Результат вернется в виде списка массивов, каждый массив состоит из двух элементов – имени и зарплаты
Подобный способ возвращения части состояния сущности называется projection
Широко используемый подход в отчетах
15
Выражения конструированияВыражения конструирования
Полезная возможность projection состоит в конструировании специфического объекта:SELECT NEW example.EmployeeDetails(e.name, e.salary,
e.department.name) FROM Employee e
Результатом выполнения запроса является список объектов типа example.EmployeeDetails
example.EmployeeDetails не обязан иметь какие-либо JPA мэпинги
Класс обязан иметь соответствующий конструктор
Полезная техника при создании DTO объектов
16
Выражение Выражение SELECTSELECT и полиморфизм и полиморфизм
JPA позволяет использовать отношение наследования между сущностями
При выборке таких сущностей никакой специфический синтаксис не предусмотрен
Query Processor выберет все совместимые сущности из БД, которые могут быть сконвертированы (cast) к базовому типу:SELECT p FROM Project p WHERE p.employees IS NOT
EMPTY
Запрос вернет сущности Project, QualityProject и DesignProject
17
Выражение Выражение FROM. FROM. Идентификационные переменныеИдентификационные переменные
Определение идентификационной переменной называется range variable declaration
Каждый запрос должен иметь минимум одну идентификационную переменную в выражении FROM:SELECT d.employees FROM Department d
Возможен альтернативный синтаксис:<entity_name> [AS] <identifier>
Пути (path) так же могут быть привязаны к идентификационной переменнойSELECT p FROM Employee e JOIN e.phones p
18
Объединения (Объединения (JoinsJoins))
Join это запрос, объединяющий результаты нескольких сущностей
Join может возникать:
Когда две или более идентификационных переменных объявлены в выражении FROM
Используется JOIN оператор
В запросе используется путь для навигации к какой-либо ассоциации
Join JPQL вероятно будет оттранслирован в соответствующий SQL JOIN
Inner JOIN между двумя сущностями возвращает объекты, удовлетворяющими всем условиям соединения
Outer JOIN это Inner JOIN + набор объектов одной сущности (left), которые не отвечают условиям соединения в другой 19
Inner JOIN Inner JOIN в в collection-valued collection-valued ассоциацияхассоциациях
Синтаксис: сущность [INNER] JOIN <path_expression> [AS] <identifier>
SELECT p FROM Employee e JOIN e.phones p
Соединяет Employee с Phone через отношение phones Зеленым цветом выделен запрос соединения (join
query), он определяет набор сущностей Phone, несмотря на то, что сущность Phone явно в запросе не фигурирует
Результатом JOIN является набор (не коллекция!) сущностей, стоящих справа от JOIN
Соответствующий SQL:SELECT p.id, p.phone_num, p.type, p.emp_id FROM emp e, phone p
WHERE e.id = p.emp_id
20
Inner JOIN Inner JOIN в в single-valued single-valued ассоциацияхассоциациях
SELECT d FROM Employee e JOIN e.department d
Соединяет Employee с Department через отношение department
Семантически это эквивалентно:SELECT e.department FROM Employee e
JOIN в single-valued ассоциациях в основном используется в OUTER JOIN соединениях
21
Неявный Неявный JOINJOIN
Необходимо помнить о неявных JOIN, которые возникают при определении путей (path)
SELECT DISTINCT e.department FROM Project p JOIN p.employees e WHERE p.name = 'Release1' AND e.address.state = 'CA'
В этом запросе 4 логических JOIN:SELECT DISTINCT d FROM Project p JOIN p.employees e JOIN
e.department d JOIN e.address a WHERE p.name = 'Release1' AND a.state = 'CA'
Запрос транслируется в 5 физических JOIN в SQL:SELECT DISTINCT d.id, d.name
FROM project p, emp_projects ep, emp e, dept d, address a
WHERE p.id = ep.project_id AND ep.emp_id = e.id AND e.dept_id = d.id AND e.address_id = a.id AND p.name = 'Release1' AND a.state = 'CA'
Необходимо помнить про неявные соединения, определяя длинные или много путей
22
Определение Определение Joint Joint в условии в условии WHEREWHERE
Соединение также можно определить в выражении WHERE:SELECT DISTINCT d FROM Department d, Employee e
WHERE d = e.department
Данная форма предпочтительна, когда JOIN не может использоваться по причине отсутствия явного отношения между сущностями:SELECT d, m FROM Department d, Employee m
WHERE d = m.department AND m.manager IS NOT EMPTY
23
Multiple JOINMultiple JOIN
JOIN запросы могут соединяться в более крупные подзапросыSELECT DISTINCT p
FROM Department d JOIN d.employees e JOIN e.projects p
Запрос возвращает проекты, принадлежащие рабочему, принадлежащему департаменту
После того, как идентификационная переменная декларирована, она может использоваться в других частях запроса (d, e, p)
24
Outer JOINOuter JOIN
Требуется, чтобы обязательно выполнялась только левая часть отношения
Синтаксис: сущность LEFT [OUTER] JOIN <path_expression> [AS]
<identifier>
SELECT e, d FROM Employee e LEFT JOIN e.department d
Возвращает всех работников и департамент работника, если департамент определен
25
Fetch JOINFetch JOIN
Выполняют явную загрузку (eager load) тех отношений, которые объявлены в мэпинге, как lazySELECT e FROM Employee e JOIN FETCH e.address
Важно! Запрос JOIN FETCH не определяет идентификационной переменной Результат запроса должен быть не адрес, а
работник с подгруженным (pre-fetch) адресом Логически это эквивалентно SELECT e, a FROM Employee e
JOIN e.address a с последующим добавлением адреса в коллекцию адресов сущности работник
FETCH JOIN с collection-valued ассоциацией приводит к дублированию данных
26
Выражение Выражение WHERE WHERE
Служит для указания фильтрующих условий, уменьшающих выборку
where_clause ::= WHERE conditional_expression
JPQL содержит широкий набор условных выражений, позволяющих выполнять сложные выборки
Условные выражения в большей части были заимствованы из SQL
27
Условные операцииУсловные операции
Спецификация определяет приоритеты условных операций, а так же грамматику выражения WHERE в форме BNF
conditional_expression ::= conditional_term | conditional_expression OR conditional_term
conditional_term ::= conditional_factor | conditional_term AND conditional_factor
conditional_factor ::= [ NOT ] conditional_primary
conditional_primary ::= simple_cond_expression | (conditional_expression)
simple_cond_expression ::=comparison_expression |between_expression |like_expression |in_expression |null_comparison_expression |empty_collection_comparison_expression |collection_member_expression |exists_expression
28
Условные операцииУсловные операции
Мы рассмотрим: Выражение BETWEEN Выражение LIKE Подзапросы Выражение IN Выражения коллекций Выражение EXISTS Выражение ANY, ALL, SOME Функции
29
ПараметрыПараметры
Параметры могут быть указаны: Именами Позициями
SELECT e FROM Employee e WHERE e.salary > ?1
SELECT e FROM Employee e WHERE e.salary > :sal
Один и тот же параметр может встречаться в запросе несколько раз
30
Выражение BETWEENВыражение BETWEEN
Оператор BETWEEN определяет, попадает ли результат в диапазон, включая концы диапазона
SELECT e FROM Employee e WHERE e.salary BETWEEN 40000 AND 45000
Оператор применим для аргументов типа: Numeric String Date
Оператор BETWEEN может быть обращен. NOT BETWEEN
31
Выражение LIKEВыражение LIKE
Оператор LIKE производит проверку строки на соответствие шаблону
В качестве wildcards используются: _ для указания произвольного значения одного
символа % для указания произвольного значения
группы символовSELECT d FROM Department d WHERE d.name LIKE '__Eng%'
Для поиска строки, содержащей символы _ или %, используется оператор ESCAPE, указывающий символ, после которого _ или % является литералом, а не wildcard:
SELECT d FROM Department d WHERE d.name LIKE 'QA\_%' ESCAPE '\'
32
ПодзапросыПодзапросы
Подзапрос может использоваться в WHERE и HAVING выражениях основного запроса
Подзапрос представляет законченное SELECT предложение, заключенное в скобки
SELECT e FROM Employee e
WHERE e.salary = (SELECT MAX(e.salary) FROM Employee e)
Идентификационная переменная определенная в запросе (или подзапросе) доступна всем подзапросам (или запросам). Переменная подзапроса может переопределять переменную основного запроса
33
ПодзапросыПодзапросы
Два запроса могут коррелировать:SELECT e FROM Employee e
WHERE EXISTS (SELECT p FROM Phone p WHERE p.employee = e AND p.type = 'Cell')
Все работники, у которых есть сотовый телефон Концептуально подзапрос выполняется для
каждого работника. Фактически, БД оптимизирует весь запрос через joins или inline view
34
ПодзапросыПодзапросы
Коррелирующие запросы могут быть переписаны с использованием JOIN:
SELECT e FROM Employee e
WHERE EXISTS (SELECT p FROM e.phones p WHERE p.type = 'Cell‘)
Для этого запроса будет сгенерирован SQL:SELECT e.id, e.name, e.salary, e.manager_id, e.dept_id, e.address_id
FROM emp e WHERE EXISTS
(SELECT 1 FROM phone p WHERE p.emp_id = e.id AND p.type = 'Cell')
Так как JPQL не поддерживает литерал в выражении SELECT, переменная p должна указываться, при генерации SQL она будет игнорироваться
35
Выражение INВыражение IN
Выражение IN используется для проверки, является ли выражение single-valued пути элементом коллекции
SELECT e FROM Employee e WHERE e.address.state IN ('NY', 'CA')
SELECT e FROM Employee e
WHERE e.department IN (SELECT DISTINCT d FROM Department d JOIN d.employees de JOIN de.projects p WHERE p.name LIKE 'QA%')
Выражение IN может быть обращено (NOT):SELECT p FROM Phone p WHERE p.type NOT IN ('Office', 'Home')
36
Выражения коллекцийВыражения коллекций
Оператор IS EMPTY логически эквивалентен оператору IS NULL для коллекций
IS EMPTY (IS NOT EMPTY) служит для проверки пустоты (не пустоты) множества collection-valued пути
SELECT e FROM Employee e WHERE e.directs IS NOT EMPTY
В SQL IS EMPTY транслируется с помощью подзапроса:
SELECT m FROM Employee m WHERE (SELECT COUNT(e) FROM Employee e WHERE e.manager = m) > 0
37
Выражения коллекцийВыражения коллекций
Оператор MEMBER OF (NOT MEMBER OF) проверяет, является ли сущность членом коллекции в collection-valued пути:
SELECT e FROM Employee e WHERE :project MEMBER OF e.projects
Подобные запросы также транслируются в подзапросы:
SELECT e FROM Employee e
WHERE :project IN (SELECT p FROM e.projects p)
38
Выражение EXISTSВыражение EXISTS
Условие EXISTS возвращает true, если подзапрос возвращает не нулевое количество записей
Условие EXISTS может обращаться оператором NOT:
SELECT e
FROM Employee e
WHERE NOT EXISTS (SELECT p
FROM e.phones p
WHERE p.type = 'Cell')
39
Выражение ANY, ALL, SOMEВыражение ANY, ALL, SOME
ALL, ANY, SOME используются для сравнения выражения с результатом подзапроса
ALL является ложным, если результат сравнения ложен хотя бы для одной записи
ANY (синоним SOME) является истинным, если результат сравнения истинен хотя бы для одной записи
Операторы =, <, <=, >, >=, <> используются для сравнения
SELECT eFROM Employee eWHERE e.salary > ALL (SELECT m.salary FROM Manager m WHERE m.department = e.department)
40
ФункцииФункции
Условные выражения могут использовать функции в выражениях WHERE и HAVING
ABS(number) The ABS function returns the unsigned version of the number argument. The result type is the same as the argument type (integer, float, or double).
CONCAT(string1, string2) The CONCAT function returns a new string that is the concatenation of its arguments, string1 and string2.
CURRENT_DATE The CURRENT_DATE function returns the current date as defined by the database server.
CURRENT_TIME The CURRENT_TIME function returns the current time as defined by the database server.
CURRENT_TIMESTAMP The CURRENT_TIMESTAMP function returns the current timestamp as defined by the database server.
41
ФункцииФункции
LENGTH(string) The LENGTH function returns the number of characters in the string argument.
LOCATE(string1, string2 [, start]) The LOCATE function returns the position of string2 in string1, optionally starting at the position indicated by start. The result is zero if the string cannot be found.
LOWER(string) The LOWER function returns the lowercase form of the string argument.
SIZE(collection) The SIZE function returns the number of elements in the collection, or zero if the collection is empty.
Выражение для SIZE транслируется в подзапросSELECT d FROM Department d WHERE SIZE(d.employees) = 2
SELECT d FROM Department dWHERE (SELECT COUNT(e) FROM d.employees e) = 2
42
ФункцииФункции
MOD(number1, number2) The MOD function returns the modulus of numeric arguments number1 and number2 as an integer.
SQRT(number) The SQRT function returns the square root of the number argument as a double.
SUBSTRING(string, start, end) The SUBSTRING function returns a portion of the input string, starting at the index indicated by start up to length characters. String indexes are measured starting from one.
UPPER(string) The UPPER function returns the uppercase form of the string argument.
TRIM([[LEADING|TRAILING|BOTH] [char] FROM] string) The TRIM function removes leading and/or trailing characters from a string. If the optional LEADING, TRAILING, or BOTH keyword is not used, then both leading and trailing characters are removed. The default trim character is the space character.
43
Выражение Выражение ORDER BYORDER BY
Запрос может быть отсортирован, используя выражение, составленное из:
Идентификационной переменной
Пути поля состояния
Пути single-valued ассоциации
Ключевые слова ASС и DESC указывают порядок сортировки
SELECT e
FROM Employee e
ORDER BY e.name DESC
SELECT e
FROM Employee e JOIN e.department d
ORDER BY d.name, e.name DESC44
Выражение Выражение ORDER BYORDER BY
Когда выражение SELECT использует поля состояния, выражение ORDER BY ограничено теми путями, которые используются в SELECT:
SELECT e.name FROM Employee e ORDER BY e.salary DESC
e.salary не входит в SELECT, поэтому сортировать по нему запрещено
45
Агрегатные запросыАгрегатные запросы
Агрегатный запрос группирует свои результаты, применяет агрегатные функции, чтобы получить обобщенную информацию (отчет) о своих результатах
SELECT AVG(e.salary) FROM Employee e
SELECT d.name, AVG(e.salary) FROM Department d JOIN d.employees e GROUP BY d.name
SELECT d.name, AVG(e.salary) FROM Department d JOIN d.employees e WHERE e.directs IS EMPTY GROUP BY d.name
SELECT d.name, AVG(e.salary) FROM Department d JOIN d.employees e WHERE e.directs IS EMPTY GROUP BY d.name
HAVING AVG(e.salary) > 50000 46
Агрегатные функцииАгрегатные функции
Существует 5 агрегатных функций, которые могут быть помещены в выражение SELECT: AVG COUNT MAX MIN SUM
47
AVGAVG
Принимает состояние поля в качестве аргумента, вычисляет среднее значение этого поля в группе
Тип поля должен быть численным Результат вычисления - double
48
COUNTCOUNT
Принимает в качестве аргумента путь или идентификационную переменную, выражающие: Поле состояния Single-valued ассоциацию
Вычисляет количество элементов в группе Результат вычисления – Long
SELECT e, COUNT(p) FROM Employee e JOIN e.phones p GROUP BY e
49
MAXMAX
Принимает состояние поля в качестве аргумента, вычисляет максимальное значение этого поля в группе
Тип поля должен быть численным Результат вычисления - double
50
MINMIN
Принимает состояние поля в качестве аргумента, вычисляет минимальное значение этого поля в группе
Тип поля должен быть численным Результат вычисления - double
51
SUMSUM
Принимает состояние поля в качестве аргументов, вычисляет сумму значение этих полей в группе
Тип поля должен быть численным Результат – должен совпадать с типом агрументов
52
Выражение Выражение Group BYGroup BY
Выражение GROUP BY определяет группировку, согласно которое будут агрегироваться результаты
Принимает в качестве аргумента путь или идентификационную переменную, выражающие: Поле состояния Single-valued ассоциацию
Важно! Одно и тоже выражение для не агрегированных значений должно использоваться в SELECT и GROUP BY. Количество агрегаций над получившейся группой не ограничено:
SELECT d.name, COUNT(e), AVG(e.salary) FROM Department d JOIN d.employees e GROUP BY d.name
53
Выражение Выражение Group BYGroup BY
Допустима множественная группировка
SELECT d.name, e.salary, COUNT(p) FROM Department d JOIN d.employees e JOIN e.projects p GROUP BY d.name, e.salary
В отсутствие условия GROUP BY, вся выборка рассматривается как одна группа
SELECT COUNT(e), AVG(e.salary) FROM Employee e
54
Выражение Выражение HavingHaving
Условие HAVING определяет фильтр, который применяется к результату выполнения запроса, после того, как эти результаты сгруппированы
Агрегатные функции над уже сгруппированными данными так же могут применяться в выражении HAVING
SELECT e, COUNT(p)
FROM Employee e JOIN e.projects p
GROUP BY e
HAVING COUNT(p) >= 2
55
Запросы Запросы UpdateUpdate
UPDATE <entity name> [[AS] <identification variable>]
SET <update_statement> {, <update_statement>}*
[WHERE <conditional_expression>]
Обновляться может только одна сущность <update_statement> (левая сторона) может быть
Полем состояния Single-valued ассоциацией
Правая сторона может быть: Литералом Базовым типом Функцией Идентификационной переменной single-valued
ассоциации Параметром
56
Запросы Запросы UpdateUpdate
UPDATE Employee e SET e.salary = 60000 WHERE e.salary = 55000
Область видимости идентификационных переменных так же распространяется на подзапрос:
UPDATE Employee e SET e.salary = e.salary + 5000
WHERE EXISTS (SELECT p FROM e.projects p WHERE p.name = 'Release2')
В выражении UPDATE может быть обновлено несколько значений:
UPDATE Phone p
SET p.number = CONCAT('288', SUBSTRING(p.number, LOCATE(p.number, ‘ '), 4)), p.type = 'Business‘
WHERE p.employee.address.city = 'Ottawa' AND p.type = 'Office' 57
Запросы Запросы DeleteDelete
DELETE FROM <entity name> [[AS] <identification variable>]
[WHERE <condition>]
Удаляют одну сущность Запросы Delete полиморфны Каскадные правила (cascade rule) не
распространяются на зависимые сущности Задача обеспечения целостности решается
приложением
DELETE FROM Employee e
WHERE e.department IS NULL
58