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

Введение

Для некоторых приложений средств 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 или именованых запросов

Підписуйтеся на Telegram-канал «DOU #tech», щоб не пропустити нові технічні статті.

👍НравитсяПонравилось0
В избранноеВ избранном0
Подписаться на автора
LinkedIn



Підписуйтесь: Soundcloud | Google Podcast | YouTube


24 комментария

Подписаться на комментарииОтписаться от комментариев Комментарии могут оставлять только пользователи с подтвержденными аккаунтами.

Володя, супер! Розвеселив своїми суперечками.А Хібернейт — це сила. Це кусок великого МЕГЕГАМНОВОЗА з грудою каттінг-едж-у (JPA, JTA, JAAS, EJB) що вирішує проблеми ентерпрайс рівня.

Уявіть, що ви пишите програму з допомогою мейвена — саме такий мій стиль побудови програм. Я стараюся написати додатковий мета-код, який складає докупи тільки потрібні елементи програми уникаючи непотрібного коду.

Можете навести якийсь приклад, як це? Або може статейку зробите?)

> > Ну значит у вас такая специфика проэктов, что я могу сказать...> Нема там ніякої специфіки.Коротке пояснення, чому в мене не впираєтьс в яваскрипт.Я стараюся писати код так, щоб він був О (1) відносно кількості коду. Він дуже нагадує по стилю алгоритм роботи make/ant/maven/etc. — коли ви ставити make/ant/maven-у ціль, він ігнорує всі цілі крім тих, які потрібні для виконання поставленої цілі. Скільки б додаткових інших цілей ви б не додавали в Makefile/build.xml/pom.xml, час виконання ціє цілі завжди буде константою, так як додаткові цілі просто не будуть виконуватися.Уявіть, що ви пишите програму з допомогою мейвена — саме такий мій стиль побудови програм. Я стараюся написати додатковий мета-код, який складає докупи тільки потрібні елементи програми уникаючи непотрібного коду. На Яваскрипті в такому стилі програмувати було на порядок легше ніж на Яві (до винайдення IoC і Dependency Injection). Зараз spring чи guice — обов’язкові елементи нових проектів, тому ця проблема вже менш актуальна.

> Отлично, вы выбрали один из компромисов, а многие люди выбирают другой. В этом есть что то плохое? Це залежить від того, що вони вибирають. Ми всі — в одній лодці ІТ. Якщо велика кількість людей буде вибирати Гібернейт, а потім забивати на нього болт і лізти в базу напряму, то нікому з нас від цього краще не стане.> А при чем тут типизация SQL-а? И да, она часто статическая — в момент компиляции запроса/хранимой процедуры идет проверка типов.Так, дійсно, я помилився. Я мав на увазі, що ми часто не можемо встановити строгий зв’язок між даними в базі і типами в програмі, так само як і в Яваскрипті — поки не виконаєш не дізнаєшся, працює воно чи ні.> Ну значит у вас такая специфика проэктов, что я могу сказать...Нема там ніякої специфіки.

Я і юзаю. Тільки без Гібернейта.

Отлично, вы выбрали один из компромисов, а многие люди выбирают другой. В этом есть что то плохое?

В SQL вже з’явилася статична типізація?

А при чем тут типизация SQL-а? И да, она часто статическая — в момент компиляции запроса/хранимой процедуры идет проверка типов.

Про швидкодію скомпільованого в байт-код скрипта на фоні запиту до БД я не став би турбуватися — це не та частина коду, яку варто оптимізувати. В моїх проектах, в швидкодію яваскрипта ніколи нічого не впиралося.

Ну значит у вас такая специфика проэктов, что я могу сказать...

> У вас нема розуміння хіберу, тому суперечка безглуздаУ вас нема розуміння того, чого у мене нема. Можемо поспорити на гроші, якщо не вірите.> Порівнювати Ріно з хібером це як порівнювати Ерланг з ExtJSСпробуйте порівняти рішення на основі Ява+HQL+SQL і Ява+Яваскрипт+SQL, можливо тоді вас попустить.> Очевидно все это цена за портабельность относительно БД, то есть ваш HQL преобразуется во все популярные диалекты SQL. Если вам это не нравится, юзайте подход описанный в статье.Я і юзаю. Тільки без Гібернейта.> А языки указанные вами обладают худшей производительностью, ну и динамическая типизация привносит свой минус в больших проэктах.В SQL вже з’явилася статична типізація? Якраз для роботи за межами основного ядра системи, коли потрібно зістикувати ядро із динамічним зовнішнім світом, динамічні мови і потрібно використовувати. Про швидкодію скомпільованого в байт-код скрипта на фоні запиту до БД я не став би турбуватися — це не та частина коду, яку варто оптимізувати. В моїх проектах, в швидкодію яваскрипта ніколи нічого не впиралося.

:) У вас нема розуміння хіберу, тому суперечка безглуздаПорівнювати Ріно з хібером це як порівнювати Ерланг з ExtJS

Я, коли вперше побачив Hibernate, мало не вдавився — краще б автори використали існуючу нормальну мову (Груві, Яваскрип, і т.д.) і зверху навернули свій фреймворк ніж писати інтерпретатор, компілятор, оптимізатор, мапер, конфігуратор, і т.д. в одному флаконі.

Очевидно все это цена за портабельность относительно БД, то есть ваш HQL преобразуется во все популярные диалекты SQL. Если вам это не нравится, юзайте подход описанный в статье.А языки указанные вами обладают худшей производительностью, ну и динамическая типизация привносит свой минус в больших проэктах.

> Руки тре відривати тим хто придумав технології аля Ріно і взагалі додумався сунути скрипти на сервер сайдА Гібернейт свої скрипти святим духом виконує, чи що? Усередині стоїть інтерпретатор HQL.А от Rhino якраз нормально скомпілить яваскриптовський код в байткод і, враховуючи що його буде пару кілобайт всього, його буде не помітно на загальному фоні. Я такі системи починав писати ще в 2003-му, на компах з 128−256Мб оперативки.Об’єм коду Гібернейта цілком достатній для нормальної бази даних, а це всього-навсього прошарок. Та навіть просто подивитися на стектрейс помилки кинутої з Гібернейта і з Яваскрипта — Яваскриптовий коротший в 5−10 раз.Я, так розумію, що вам просто платять погодинно от вам Гібернейт і вигідніший.: -)

я маю на увазі що ви ж не з неба взяли код, а мабуть мали якусь тестову аплікацію на якій перевірили чи працюють ваші прикладиОт цю аплікацію було б варто викласти

Хоча вартувало б додати сирці екзамплів

Не зрозумів. Ви маєте на увазі, що потрібно більше прикладів, чи приклади мають бути складнішими?

2Володимир ЛісівкаРуки тре відривати тим хто придумав технології аля Ріно і взагалі додумався сунути скрипти на сервер сайдЩодо хібера то жпа2 його буде поволеньки тіснитиНу і в будь-якому разі кожна технологія має використовуватись для свого роду задачСтаття норма — багатьом пригодитьсяХоча вартувало б додати сирці екзамплів

очень сомневаюсь, что в нем имеются явные баги

query.setLockMode (LockMode.UPGRADE).setMaxResults (count) в случае оракла заставляет последнего ругаться, т.к. формирует абсолютно идиотский запрос. Баге лет 5 наверное. Вот только не уверен, относятся ли пессимистические блокировки к обычным задачам, наверное у кого как.

это, в таком случае, все на любителя и зависит от степени развращенности программиста — использовать джавоскрипт или же какой нибудь руби-на-рельсах или скалу чтоб достичь желаемой работодателем цели. Лично я бы задачу решил на джаве, возможно с хибернейтом, если нужно, и не переймавсь бы сложностями хибернейта — очень сомневаюсь, что в нем имеются явные баги, проявляющие себя при обычных задачах — скорей всего, только в особо извращенных случаях, зависящих, как я уже говорил, от степени изращенности программиста.

а, имеется в виду использовать джавоскрипт как оболочку к джаве...а то я уже испугался что прямо из броузера можно запросы к базе херячить...

> Что то я вообще отстал от программистской жизни: (Знаю что бухло подорожает, а вот как на джава скрипте работать с БД — совершенно не понимаю...Объясните, а? http://www.google.com/codesear...http://www.google.com/codesear...

Что то я вообще отстал от программистской жизни: (Знаю что бухло подорожает, а вот как на джава скрипте работать с БД — совершенно не понимаю...Объясните, а?

> Посмотрите вакансии Java-разроботчиков — почти в каждая требует знания HIBERNATE. Нравится он вам или нет, но пока он популярен — это факт! Всетаки вакансій без слова «Гібернейт» значно більше.> И при чем тут вообще «Яваскрипт»? Гібернейт реалізує свою власну мову програмування, яка є проміжною ланкою між Явою і SQL. Свій інтерпретатор мови, свій компілятор в SQL, свій парсер результатів і т.д. Задача розкладання довільного графу об’єктів у набір таблиць в загальному випадку не вирішується. В 95% Гібернейт працює, але в 5% від нього потрібно відмовлятися повністю — тут і починаються танці з бубном, так як викидати 95% робочого коду ніхто не буде, як і писати паралельну систему заради тих 5%.Приблизно так само ловлять мавп — роблять скриньку з диркою в яку кладуть банан, дирку роблять так, щоб рука мавпи пролазила в скриньку але щоб банан не пролазив. Мавпа бачить банан через дірку, хапає його але витягти не може, але і відпустити його теж не може — банан вже в руках, совість не дозволяє. Тут їй і настає опа.Якщо опустити Яву і HQL, а використовувати Яваскрипт, то виходить на порядок простіше. Простий мапінг об’єктів на базу виходить не складніше ніж в HQL, а от складні речі в Яваскрипті зробити можна без танців з бубном.Приклад складних речей: при відмові БД (перевантажується напр.) потрібно зберегти дані, із запитів на запис, у файл і програти їх заново після відновлення зв’язку щоб не втрачати дані, деякі запити на читання притримати секунд на 10-ть (може зв’язок з’явиться) і тільки тоді повідомити про відмову, для інших запитів на читання симулювати пусту БД щоб не лякати юзверів. Як це зробити з Гібернейтом?

Очень рад, что комментарии появились так быстро.1. Спасибо всем, кто сказал “спасибо”! 2. Сашко, если свободно владеете английским, то зачем читать русскоязычную литературу? Конечно, статья предназначена для начинающих (впрочем как и большинство остальных статей на этом сайте).3. Володимир Лісівка. Посмотрите вакансии Java-разроботчиков — почти в каждая требует знания HIBERNATE. Нравится он вам или нет, но пока он популярен — это факт! И при чем тут вообще “Яваскрипт”? 4. Avgusti, Ваш вопрос — отдельная тема, и я на данный момент не знаю ответа на него.

Лучше покажите как сохранить объет с несолькоми many to many, one to many и many to one в рамках транзакции (по два каждого типа), потом и поговорим о простоте и достоинстве хибернейт.

С хибернейтом идет отличный мануал,

Аха, только в этом отличном мануале местами встречаются неверные утверждения, часто умалчиваются важные детали... Вот поэтому и приходится или пробовать руками или радоваться что есть люди которые уже помучались и написали в статьях свой опыт использования с примерами.

Слава богу, мене минула чаша сія (Гібернейта). Трахався з ним всього декілька раз.Тому, хто зробив цей костиль, треба відірвати руки.Я, коли була так можливість, просто використовував Яваскрипт (Rhino) — виходило компактно і зрозуміло. Я, коли вперше побачив Hibernate, мало не вдавився — краще б автори використали існуючу нормальну мову (Груві, Яваскрип, і т.д.) і зверху навернули свій фреймворк ніж писати інтерпретатор, компілятор, оптимізатор, мапер, конфігуратор, і т.д. в одному флаконі.Зараз ще більша лафа — об’єктно орієнтовані бази з вбудованим Map/Reduce.

С хибернейтом идет отличный мануал, в котором очень просто описано, как делать запросы «прямо не „мяпящиеся“ на ентити». Статейка слабая, расчитанная на джава-юниоров, так — лишь бы написать что-то...

Отличная статья, спасибо! как раз недавно думал как быть если надо делать запросы прямо не «мяпящиеся» на ентити. Еще раз спасибо.

Подписаться на комментарии