Query Language (JPQL) 1. Обзор Что такое и что такое не JPQL Терминология Пути (Path Expression) SELECT запросы Выражение SELECT Выражения конструирования. - презентация

1 Query Language (JPQL) 1

2 Обзор Что такое и что такое не JPQL Терминология Пути (Path Expression) SELECT запросы Выражение SELECT Выражения конструирования Выражение SELECT и полиморфизм Выражение FROM. Идентификационные переменные

3 Обзор Объединения (Joins) Inner JOIN в collection-valued ассоциациях Inner JOIN в single-valued ассоциациях Неявный JOIN Определение Joint в условии WHERE Multiple JOIN Outer JOIN Fetch JOIN Выражение WHERE

4 Обзор Условные операции Параметры Выражение BETWEEN Выражение LIKE Подзапросы Выражение IN Выражения коллекций Выражение EXISTS Выражение ANY, ALL, SOME

5 Обзор Функции Выражение ORDER BY Агрегатные запросы Агрегатные функции Выражение Group BY Выражение Having Запросы Update Запросы Delete

6 Что такое и что такое не JPQL JPQL это не SQL JPQL оперирует в терминах сущностей, а не в терминах таблиц. Таким образом, отсутствие некоторых возможностей, которые есть в SQL это не лимитация JPQL, просто они логически не нужны Похожесть SQL и JPQL только для удобства освоения Запросы, написанные на JPQL транслируются в специфический SQL диалект современных БД Это означает портируемость приложения 6

7 Что такое и что такое не JPQL Нет необходимости знать мэпинги всех сущностей при написании запроса Программист оперирует объектами доменной модели Критические места приложения могут быть написаны на явном SQL Каждый провайдер предоставляет возможность вывести на консоль SQL, который он генерирует 7

8 Терминология Запросы разделяются на 4 категории: Select Aggregate Update Delete Запросы оперируют на наборе сущностей, определенных в persistence unit. Этот набор образует домен сущностей Запрос ссылается на сущности по Имя класса сущности 8

9 Терминология Сущность состоит из одного или нескольких атрибутов, которые делятся на: Простые persistent атрибуты – атрибуты состояния (состояние) Атрибуты, являющиеся отношениями – атрибуты ассоциаций (ассоциация) Запросы являются нечувствительными к регистру, за исключением: Имен сущностей Атрибутов сущностей 9

10 Пути (Path Expression) Пути позволяют осуществлять навигацию к полям состояния и/или к полям ассоциаций Точка (.) разделяет атрибуты пути Пути, в зависимости от количества возвращенных элементов, разделяются на: Пути полей состояния (e.name) Пути single-valued ассоциаций (e.department) Пути collection-valued ассоциаций (e.managers) Пути может содержать навигацию по нескольким атрибутам e.department.name Пути являются ключевым понятием в написании эффективных запросов 10

11 Пример 11

12 SELECT запросы Наиболее распространенный вид запросов В общем виде выглядит: select_statement :: = select_clause from_clause [where_clause] [groupby_clause] [having_clause] [orderby_clause] Алиас называется переменной идентификации и является обязательным в JPQL 12

13 Пример. SELECT запрос Простейшая форма, содержащая обязательные поля: SELECT e FROM Employee e Запрос вернет 0 или несколько объектов Employee Провайдер сгенерирует SQL: SELECT id, name, salary, manager_id, dept_id, address_id FROM emp В случае ассоциаций провайдер сгенерирует дополнительный SQL 13

14 Выражение SELECT В выражении 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

15 Выражение SELECT Допустимо выбирать несколько значений в выражении SELECT: SELECT e.name, e.salary FROM Employee e Результат вернется в виде списка массивов, каждый массив состоит из двух элементов – имени и зарплаты Подобный способ возвращения части состояния сущности называется projection Широко используемый подход в отчетах 15

16 Выражения конструирования Полезная возможность projection состоит в конструировании специфического объекта: SELECT NEW example.EmployeeDetails(e.name, e.salary, e.department.name) FROM Employee e Результатом выполнения запроса является список объектов типа example.EmployeeDetails example.EmployeeDetails не обязан иметь какие-либо JPA мэпинги Класс обязан иметь соответствующий конструктор Полезная техника при создании DTO объектов 16

17 Выражение SELECT и полиморфизм JPA позволяет использовать отношение наследования между сущностями При выборке таких сущностей никакой специфический синтаксис не предусмотрен Query Processor выберет все совместимые сущности из БД, которые могут быть сконвертированы (cast) к базовому типу: SELECT p FROM Project p WHERE p.employees IS NOT EMPTY Запрос вернет сущности Project, QualityProject и DesignProject 17

18 Выражение FROM. Идентификационные переменные Определение идентификационной переменной называется range variable declaration Каждый запрос должен иметь минимум одну идентификационную переменную в выражении FROM: SELECT d.employees FROM Department d Возможен альтернативный синтаксис: [AS] Пути (path) так же могут быть привязаны к идентификационной переменной SELECT p FROM Employee e JOIN e.phones p 18

19 Объединения (Joins) Join это запрос, объединяющий результаты нескольких сущностей Join может возникать: Когда две или более идентификационных переменных объявлены в выражении FROM Используется JOIN оператор В запросе используется путь для навигации к какой-либо ассоциации Join JPQL вероятно будет оттранслирован в соответствующий SQL JOIN Inner JOIN между двумя сущностями возвращает объекты, удовлетворяющими всем условиям соединения Outer JOIN это Inner JOIN + набор объектов одной сущности (left), которые не отвечают условиям соединения в другой 19

20 Inner JOIN в collection-valued ассоциациях Синтаксис: сущность [INNER] JOIN [AS] 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

21 Inner JOIN в 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

22 Неявный JOIN Необходимо помнить о неявных 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

23 Определение Joint в условии WHERE Соединение также можно определить в выражении 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

24 Multiple JOIN JOIN запросы могут соединяться в более крупные подзапросы SELECT DISTINCT p FROM Department d JOIN d.employees e JOIN e.projects p Запрос возвращает проекты, принадлежащие рабочему, принадлежащему департаменту После того, как идентификационная переменная декларирована, она может использоваться в других частях запроса (d, e, p) 24

25 Outer JOIN Требуется, чтобы обязательно выполнялась только левая часть отношения Синтаксис: сущность LEFT [OUTER] JOIN [AS] SELECT e, d FROM Employee e LEFT JOIN e.department d Возвращает всех работников и департамент работника, если департамент определен 25

26 Fetch JOIN Выполняют явную загрузку (eager load) тех отношений, которые объявлены в мэпинге, как lazy SELECT 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

27 Выражение WHERE Служит для указания фильтрующих условий, уменьшающих выборку where_clause ::= WHERE conditional_expression JPQL содержит широкий набор условных выражений, позволяющих выполнять сложные выборки Условные выражения в большей части были заимствованы из SQL 27

28 Условные операции Спецификация определяет приоритеты условных операций, а так же грамматику выражения 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

29 Условные операции Мы рассмотрим: Выражение BETWEEN Выражение LIKE Подзапросы Выражение IN Выражения коллекций Выражение EXISTS Выражение ANY, ALL, SOME Функции 29

30 Параметры Параметры могут быть указаны: Именами Позициями SELECT e FROM Employee e WHERE e.salary > ?1 SELECT e FROM Employee e WHERE e.salary > :sal Один и тот же параметр может встречаться в запросе несколько раз 30

31 Выражение BETWEEN Оператор BETWEEN определяет, попадает ли результат в диапазон, включая концы диапазона SELECT e FROM Employee e WHERE e.salary BETWEEN AND Оператор применим для аргументов типа: Numeric String Date Оператор BETWEEN может быть обращен. NOT BETWEEN 31

32 Выражение 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

33 Подзапросы Подзапрос может использоваться в WHERE и HAVING выражениях основного запроса Подзапрос представляет законченное SELECT предложение, заключенное в скобки SELECT e FROM Employee e WHERE e.salary = (SELECT MAX(e.salary) FROM Employee e) Идентификационная переменная определенная в запросе (или подзапросе) доступна всем подзапросам (или запросам). Переменная подзапроса может переопределять переменную основного запроса 33

34 Подзапросы Два запроса могут коррелировать: SELECT e FROM Employee e WHERE EXISTS (SELECT p FROM Phone p WHERE p.employee = e AND p.type = 'Cell') Все работники, у которых есть сотовый телефон Концептуально подзапрос выполняется для каждого работника. Фактически, БД оптимизирует весь запрос через joins или inline view 34

35 Подзапросы Коррелирующие запросы могут быть переписаны с использованием 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

36 Выражение 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

37 Выражения коллекций Оператор 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

38 Выражения коллекций Оператор 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

39 Выражение EXISTS Условие EXISTS возвращает true, если подзапрос возвращает не нулевое количество записей Условие EXISTS может обращаться оператором NOT: SELECT e FROM Employee e WHERE NOT EXISTS (SELECT p FROM e.phones p WHERE p.type = 'Cell') 39

40 Выражение ANY, ALL, SOME ALL, ANY, SOME используются для сравнения выражения с результатом подзапроса ALL является ложным, если результат сравнения ложен хотя бы для одной записи ANY (синоним SOME) является истинным, если результат сравнения истинен хотя бы для одной записи Операторы =,, >=, используются для сравнения SELECT e FROM Employee e WHERE e.salary > ALL (SELECT m.salary FROM Manager m WHERE m.department = e.department) 40

41 Функции Условные выражения могут использовать функции в выражениях 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

42 Функции 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

43 Функции 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

44 Выражение ORDER 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 DESC 44

45 Выражение ORDER BY Когда выражение SELECT использует поля состояния, выражение ORDER BY ограничено теми путями, которые используются в SELECT: SELECT e.name FROM Employee e ORDER BY e.salary DESC e.salary не входит в SELECT, поэтому сортировать по нему запрещено 45

46 Агрегатные запросы Агрегатный запрос группирует свои результаты, применяет агрегатные функции, чтобы получить обобщенную информацию (отчет) о своих результатах 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 HAVING AVG(e.salary) >

47 Агрегатные функции Существует 5 агрегатных функций, которые могут быть помещены в выражение SELECT: AVG COUNT MAX MIN SUM 47

48 AVG Принимает состояние поля в качестве аргумента, вычисляет среднее значение этого поля в группе Тип поля должен быть численным Результат вычисления - double 48

49 COUNT Принимает в качестве аргумента путь или идентификационную переменную, выражающие: Поле состояния Single-valued ассоциацию Вычисляет количество элементов в группе Результат вычисления – Long SELECT e, COUNT(p) FROM Employee e JOIN e.phones p GROUP BY e 49

50 MAX Принимает состояние поля в качестве аргумента, вычисляет максимальное значение этого поля в группе Тип поля должен быть численным Результат вычисления - double 50

51 MIN Принимает состояние поля в качестве аргумента, вычисляет минимальное значение этого поля в группе Тип поля должен быть численным Результат вычисления - double 51

52 SUM Принимает состояние поля в качестве аргументов, вычисляет сумму значение этих полей в группе Тип поля должен быть численным Результат – должен совпадать с типом агрументов 52

53 Выражение Group 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

54 Выражение Group 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

55 Выражение Having Условие HAVING определяет фильтр, который применяется к результату выполнения запроса, после того, как эти результаты сгруппированы Агрегатные функции над уже сгруппированными данными так же могут применяться в выражении HAVING SELECT e, COUNT(p) FROM Employee e JOIN e.projects p GROUP BY e HAVING COUNT(p) >= 2 55

56 Запросы Update UPDATE [[AS] ] SET {, }* [WHERE ] Обновляться может только одна сущность (левая сторона) может быть Полем состояния Single-valued ассоциацией Правая сторона может быть: Литералом Базовым типом Функцией Идентификационной переменной single-valued ассоциации Параметром 56

57 Запросы Update UPDATE Employee e SET e.salary = WHERE e.salary = Область видимости идентификационных переменных так же распространяется на подзапрос: UPDATE Employee e SET e.salary = e.salary 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

58 Запросы Delete DELETE FROM [[AS] ] [WHERE ] Удаляют одну сущность Запросы Delete полиморфны Каскадные правила (cascade rule) не распространяются на зависимые сущности Задача обеспечения целостности решается приложением DELETE FROM Employee e WHERE e.department IS NULL 58