Примеры использования HIBERNATE Native SQL для выборки информации из базы данных

💡 Усі статті, обговорення, новини про Java — в одному місці. Приєднуйтесь до Java спільноти!

Введение

Для некоторых приложений средств HQL бывает недостаточно и возникает необходимость использовать родной SQL вашей СУБД. Элементарный пример — вызов хранимой процедуры БД.

В Hibernate для исполнения «родных» запросов служит метод createSQLQuery(String queryString) объекта org.hibernate.Session, который возвращает екземпляр org.hibernate.SQLQuery. Напомню, что для запросов на языке HQL используется метод createQuery(String queryString) того же класса, который возвращает екземпляр org.hibernate.Query. Класс org.hibernate.SQLQuery имеет несколько специфичных для него методов и здесь будут использованы некоторые из них.

Во всех примерах екземпляр класса org.hibernate.Session называется sess. Он может быть получен стандартным способом:

new Configuration().configure().buildSessionFactory().getCurrentSession();

Класс SomeClass здесь называется отображенным, если для него существует SomeClass.nbm.xml, который зарегистрирован в hibernate.cfg.xml. Класс имеет свойство property, если в нем определены публичные методы setProperty и getProperty (setter и getter) для соответствующего поля property согласно соглашению Sun по наименованию методов класоов:

<code class="java">property: setProperty/getProperty
proPerty: setProPerty/getProPerty
PROPERTY: setPROPERTY/getPROPERTY</code>

Для демонстрации примеров используется база данных Oracle, состоящая из двух таблиц, связанных внешним ключом. Таблица TREE содержит информацию о группах деревьев ботанического сада. Каждая такая группа имеет уникальный номер, название, сорт (в группу входят деревья одного сорта) и количество деревьев в группе. Таблица SORT содержит информацию о сортах деревьев ботанического сада.

Схема базы данных приведена ниже:

Диаграмма классов, отображенных на эти таблицы выглядит так:

Связь между классами я здесь не показал — она очевидна.

Структура этих классов вместе с методами-акцессорами (getters & setters) в Eclipse:

Здесь не описана структура конфигурационных файлов (hibernate.cfg.xml, Tree.nbm.xml и Sort.nbm.xml), так как объектно-реляционное отображение довольно тривиально и его легко реализовать при необходимости самостоятельно.

Примеры

Выборка отображенных объектов одного класса

Задача. Необходимо получить список персистентных объектов класса Tree, который отображен в hibernate.cfg.xml.

Решение. Воспользуемся методом createSQLQuery для выполнения запроса. Обратите внимание: точка с запятой в конце запроса не ставится!

<code class="java">sess.createSQLQuery("SELECT * FROM TREE").addEntity(Tree.class).list();</code>

В данном случае класс Tree должен быть отображен на таблицу TREE. Вызов addEntity(Tree.class) предписывает рассматривать результат запроса как набор классов Tree.

Выборка отображенных объектов разных классов в одном запросе

Задача. Получить декартово произведение таблиц TREE и SORT.

Решение.

<code class="java">session.createSQLQuery("select {tree.*}, {sort.*} from tree, sort").addEntity("tree", Tree.class).addEntity("sort", Sort.class).list();</code>

Код {[aliasname}.*} внутри запроса «подставляет» вместо себя в конечный запрос названия всех свойств отображенного класса, который соотносится с алиасом aliasname. Таким образом, вместо {tree.*} в конечном запросе будет помещен код, эквивалентный следующему:

tree.id, tree.name, tree.sort, tree.count

А вместо {sort.*} —

sort.id, sort.name

Во избежание конфликта имен (id есть в обоих классах) HIBERTNATE всегда подставляет сгенерированные алиасы, которые заканчиваются цифрами и знаками подчеркивания. Именно поэтому код будет не такой же, как тот, что выше, а эквивалентный ему.

Отдельно следует описать код который обрабатывает полученный список значений. Этот список содержит объекты типа Object[]. Таким образом, результатом запроса будет список одномерных массивов базового типа Object. Для получения экземпляров классов Tree и Sort нужно сначала привести элемент списка к типу Object[], а потом привести к нужному типу каждый из элементов этого массива.

Ниже приведен код, который выводит в стандартный поток результат декартового произведения таблиц TREE и SORT:

<code class="java">List list = session.createSQLQuery("select {tree.*}, {sort.*} from tree, sort").addEntity("tree", Tree.class).addEntity("sort", Sort.class).list();

for (Object listelement : result) {
    Object[] objectarray = (Object[]) listelement;
    Tree tree = (Tree) objectarray[0];
    Sort sort = (Sort) objectarray[1];
    System.out.println("Tree: " + tree + ", sort: " + sort);
}</code>

Обратите внимание на порядок объектов классов Tree и Sort в objectarray — он соответствует порядку следования {tree.*} и {sort.*} в запросе!

Выборка отображенных объектов связанных классов

Задача. Есть класс Sort, который отображен на таблицу SORTS. В классе Tree есть свойство treeSort типа Sort (таблицы TREES и SORTS связанны внешним ключом). Необходимо получить список персистентных объектов класса Tree вместе с объектами Sort, на которые они ссылаются.

Решение.

<code class="java">sess.createSQLQuery("SELECT * FROM TREE").addEntity("tree", Tree.class).addJoin("tree.treeSort").list(); </code>

Выборка неотображенных объектов

Задача. Получить количество деревьев всех сортов. Результат выборки представить в виде POJO, который не отображен в файле конфигурации HIBERNATE.

Решение. Создаем класс SortCount со свойствами String sortName и int countOfSort (свойства должны иметь корректные методы-акцессоры). Результат может быть получен после исполнения кода:

<code class="java">session.createSQLQuery("select name sortname, sum(count) countofsort from tree, sort where tree.sort=sort.id group by sortname").addScalar("sortName", Hibernate.STRING).addScalar("countOfSort", Hibernate.INTEGER).setResultTransformer(Transformers.aliasToBean(SortCount .class)).list();</code>

Метод addScalar(String string, Type type) подсказывает HIBERNATE названия и тип полей результата запроса, а цепочка методов setResultTransformer(Transformers.aliasToBean(Contact.class)).list() «превращает» его в список объектов класса SortCount. Если убрать из кода оба метода addScalar, возникнет исключение org.hibernate.PropertyNotFoundException, вызванное отсутствием в классе Contact сеттеров для свойств SORTNAME и COUNTOFSORT. Причина возникновения исключения в том, что большинство СУБД регистронезависимые и передают названия полей результата запроса в составе ResultSetMetaData в верхнем регистре. Как следствие — названия свойств SORTNAME и COUNTOFSORT, а не sortName и countOfSort. Один из вариантов решения проблемы — создание в классе SortCount сеттеров setSORTNAME и setCOUNTOFSORT, а другим — использование методов addScalar с явным указанием названий (и типов) полей результата запроса. Второй вариант ликвидирует разногласие между метаданными СУБД и правилом наименования методов в Java, а также быстрее работает.

Вызов хранимой функции базы данных Oracle

Задача. В базе данных есть хранимая процедура (функция) GETPOSITION, которая принимает id группы деревьев сада в качестве аргумента и в качестве результата возвращает место этой группы среди всех по количеству деревьев в ней. Получить результат работы такой хранимой процедуры базы данных Oracle.

Решение. Функция принимает в качестве параметра и возвращает значение целочисленного типа. SQL-запрос для получения результата имеет такой вид:

SELECT GETPOSITION(12) FROM DUAL;

Точку с запятой нужно опустить, а число 12 заменить на целочисленную переменную, которая содержит id некоторой группы деревьев. Также обязательно добавить алиас, иначе СУБД сгенерирует свой (Oracle, например, так и назовет поле — GETPOSITION(12)), акцессоры для которого часто просто невозможно создать. Результат запроса можно поместить в любой класс, в котором есть свойство с именем алиаса.

Назовем алиас position и создадим класс Position:

<code class="java">public class Position {
    private int position;
    
    public int getPosition() {
        return position;
    }

    public void setPosition(int position) {
        this.position = position;
    }
}</code>

В данном случае результат работы функции можно получить таким образом:

<code class="java">int group = 25;

Position position = (Position) session.createSQLQuery("select getposition("+group+") position from dual").addScalar("position", Hibernate.INTEGER).setResultTransformer(Transformers.aliasToBean(Position.class)).uniqueResult();</code>

Результат будет помещен в переменную position. Ничего нового в этом коде нет, разве что метод uniqueResult(), который возвращает не список значений, ка метод list(), а одно значение.

Выводы

HIBERNATE — довольно гибкий фреймворк, он позволяет многие действия произвести разными способами. Например, запрос на выборку можно осуществить с помощью HQL, Criteria и Native SQL. Тот или иной выбор зависит скорее от личных предпочтений разработчика, но в ряде случаев без использования родного языка запросов СУБД не обойтись. Это справедливо в первую очередь для СУБД с развитыми процедурными расширениями SQL: Oracle, MS SQL Server, IBM DB2.

Дополнительная литература

1. HIBERNATE — Relational Persistence for Idiomatic Java — официальное руководство (входит в состав архива с фреймворком), в особенности Chapter 17. Native SQL
2. Hibernate JavaDoc
3. Chapter 10. Native query из руководства по Hibernate EntityManager
4. Использование Hibernate Java Persistence
5. Hibernate 3.2 Transformers for HQL and SQL
6. Hibernate Tutorial на javatalks.ru
7. Hibernate. Создание named query или именованых запросов

Все про українське ІТ в телеграмі — підписуйтеся на канал DOU