Как получить rownum в hql

Как получить rownum в hql

Предположим у вас есть серверное java приложение использующее hibernate с Oracle в качестве персистенс провайдера.

rownum это псевдоколонка в базе данных Oracle в которой храниться порядковый номер строки результата какого-либо select запроса. Бывает очень полезно получить например номер записи удовлетворяющей таким-то условиям, или взять из результирующего dataset-а строки в диапазоне от n до m. В общем, в ряде случаев rownum может очень пригодится.

Но вот беда, Hibernate Query Language (hql) не поддерживает rownum! Хуже того, нет эквивалента этой полезной фиче. Есть, конечно, возможность взять записи в диапазоне с помощью методов setFirstResult, setMaxResults в hibernate-овской Query, но это только половина фичи, а если хочется именно получить номер записи?

Вынужден вас огорчить - это невозмжно.
Но.. Как вы знаете, если очень хочется, и очень нужно, то нет ничего невозможного!
Ломать так ломать..

Умные дядьки на форумах советуют использовать native query которую можно сделать в том же EntityManager-е - нам это не подходит т.к. хотелось бы писать на hql-е а не на sql. В случае простых запросов можно, конечно, не поленится использовать sql, но в большом проекте, где hql запрос может создаваться динамически на основе десятков условий и опций, сгенерить подобный sql запрос может оказаться чересчур сложной задачей.

То что не можем сделать мы, отлично делает хибернет! Транслировать hql в sql - это его каждодневная задача. Итак, общая идея такова: берем наш суперсложный hql запрос, средствами хибернета перегоняем его в sql и результат встраиваем в виде подзапроса в довольно тривиальный sql запрос, который в конечном итоге и вытягивает rownum.

А теперь по пунктам:

1. Преобразование hql в sql. Способ может не совсем легальный, но этот код работает - проверено. Несколько советов:
   
   
   • SessionFactory можно вытянуть из EntityManager-а: ((Session)entityManager.getDelegate()).getSessionFactory()
   • После преобразования вы можете с удивлением заметить, что все именованные параметры в запросе стали позиционными (т.е. в теле запроса появились вопросики .. ? .. ?.. вместо ваших .. :myParam1 .. :myParam2 ..). Возможно есть способ как-то допилять приведенный по ссылке код, запретить ему учинять это безобразие - я так и не разобрался. Вместо этого можно написать некий вспомогательный код который перед преобразованием "запомнит" расположение параметров и потом при формировании запроса выставит нужные значения параметров в правильных позициях.
   
   
2. Собственно оберточный sql запрос вытягивающий rownum. Предположим что sql запрос полученный на предыдущем шаге хранится в стринге identifiersCriteriaNativeSql. Предположим также, что результатом предыдущего запроса является одна колонка - уникальные идентификаторы некой вашей сущности. Задача: получить rownum записи с заданным идентификатором.
   
   Результирующий sql запрос будет формироваться так:
   
   String firstColumnName = "col_0_0_"
   String subQuery = "select rownum as rn, " + firstColumnName + " from (" + identifiersCriteriaNativeSql + ")";
   String query = "select rn from (" + subQuery + ")" + " where " + firstColumnName + " = :identifierParameter";
   
   
   
   • Вы наверно обратили внимание на магическое "col_0_0_" - так хибернет обозвал первую колонку в select части identifiersCriteriaNativeSql, после преобразования из hql в sql - и это второе разочарование - все ваши алиасы были безвозвратно утеряны. Т.е. col_0_0_ указывает на id вашей сущности.
   • Второй непонятный момент может быть вызван двойной вложенностью запросов. Почему не просто
     
     String query = "select rownum from (" + identifiersCriteriaNativeSql + ")" + " where " + firstColumnName + " = :identifierParameter";
     
     А потому что в этом случае rownum будет всегда 1 - т.к. инициализация этой колонки происходит после наложение условий в where. Поэтому нужен еще один подзапрос subQuery без всяких условий.

Ура, работает!!!!......