Query Language (JPQL)

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. Этот набор образует домен сущностей • Запрос ссылается на сущности по имени • @Entity(name=“xxx”) • Имя класса сущности 8

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

10. Пути (PathExpression) • Пути позволяют осуществлять навигацию к полям состояния и/или к полям ассоциаций • Точка (.) разделяет атрибуты пути • Пути, в зависимости от количества возвращенных элементов, разделяются на: • Пути полей состояния (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 eFROM 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.employeesFROM Department d • Ключевое слово DISTINCT удаляет дубликаты: SELECT DISTINCT e.departmentFROM Employee e 14

15. Выражение SELECT • Допустимо выбирать несколько значений в выражении SELECT: SELECT e.name, e.salaryFROM 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 pFROM Project pWHERE p.employees IS NOT EMPTY • Запрос вернет сущности Project, QualityProjectи DesignProject 17

18. Выражение FROM. Идентификационные переменные • Определение идентификационной переменной называется range variable declaration • Каждый запрос должен иметь минимум одну идентификационную переменную в выражении FROM: SELECT d.employees FROM Department d • Возможен альтернативный синтаксис: <entity_name> [AS] <identifier> • Пути (path) так же могут быть привязаны к идентификационной переменной SELECT pFROM Employee e JOIN e.phonesp 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 <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

21. Inner JOIN в single-valued ассоциациях SELECT d FROM Employee e JOIN e.department d • Соединяет Employee с Department через отношение department • Семантически это эквивалентно: SELECT e.departmentFROM 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 dFROM Project p JOIN p.employees e JOIN e.department d JOIN e.address aWHERE 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, mFROM 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 <path_expression> [AS] <identifier> 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, aFROM 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 ORconditional_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 eFROM Employee eWHERE e.salary > ?1 SELECT eFROM Employee eWHERE e.salary > :sal • Один и тот же параметр может встречаться в запросе несколько раз 30

31. Выражение BETWEEN • Оператор BETWEEN определяет, попадает ли результат в диапазон, включая концы диапазона SELECT e FROM Employee e WHERE e.salary BETWEEN 40000 AND 45000 • Оператор применим для аргументов типа: • 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 eFROM Employee e WHERE e.salary = (SELECT MAX(e.salary)FROM Employee e) • Идентификационная переменная определенная в запросе (или подзапросе) доступна всем подзапросам (или запросам). Переменная подзапроса может переопределять переменную основного запроса 33

34. Подзапросы • Два запроса могут коррелировать: SELECT eFROM Employee e WHERE EXISTS (SELECT pFROM 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 eWHERE EXISTS (SELECT 1FROM phone pWHERE p.emp_id = e.id ANDp.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 eFROM Employee eWHERE e.directs IS NOT EMPTY • В SQL IS EMPTY транслируется с помощью подзапроса: SELECT mFROM Employee mWHERE (SELECT COUNT(e)FROM Employee e WHERE e.manager = m) > 0 37

38. Выражения коллекций • Оператор MEMBER OF (NOT MEMBER OF) проверяет, является ли сущность членом коллекции в collection-valued пути: SELECT eFROM Employee eWHERE :project MEMBER OF e.projects • Подобные запросы также транслируются в подзапросы: SELECT eFROM Employee e WHERE :project IN (SELECT pFROM 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 dFROM Department dWHERE SIZE(d.employees) = 2 SELECT dFROM 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 eGROUP BY d.name SELECT d.name, AVG(e.salary) FROM Department d JOIN d.employees eWHERE e.directs IS EMPTYGROUP BY d.name SELECT d.name, AVG(e.salary) FROM Department d JOIN d.employees eWHERE e.directs IS EMPTYGROUP BY d.name HAVING AVG(e.salary) > 50000 46

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