Благодарность

Администрация сайта выражает благодарность автору, присланной нам на электронную почту, статьи, за помощь в развитии ресурса www.spring-source.ru.
Автор статьи: Лач Андрей Игорьевич
email: [email protected]

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

Введение

Данная статья не является переводом Chapter 16. Native SQL руководства "HIBERNATE - Relational Persistence for Idiomatic 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 по наименованию методов класоов:
property: setProperty/getProperty
proPerty: setProPerty/getProPerty
PROPERTY: setPROPERTY/getPROPERTY

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

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

Native SQL Hibernate

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

Native SQL Hibernate

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

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

Native SQL Hibernate

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

Примеры

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

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

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

      
sess.createSQLQuery("SELECT * FROM TREE").addEntity(Tree.class).list();

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

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

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

Решение.

      
session.createSQLQuery("select {tree.*}, 
{sort.*} from tree, sort").addEntity("tree", 
Tree.class).addEntity("sort", Sort.class).list();

Код {[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:

      
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);

}

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

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

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

Решение.

      
sess.createSQLQuery("SELECT * FROM TREE").addEntity("tree", Tree.class).addJoin("tree.treeSort").list();

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

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

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

      
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();

Метод 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:

      
public class Position {

	private int position;
	
	public int getPosition() {
		return position;
	}
	
	public void setPosition(int position) {
		this.position = position;
	}
}

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

      
int group = 25;

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

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

Выводы

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