Hibernate 5.2 CriteriaQuery Subquery

user_1841823 0
Viewed 479

I have Java Code which uses Hibernate 5.2 CriteriaBuilder. Code returns this SQL statement bellow

(JAVA)

   CriteriaBuilder countBuilder = session.getCriteriaBuilder();
   CriteriaQuery<Long> criteriaCount = builder.createQuery(Long.class);
   Root<Transaction> transactions = criteriaCount.from(Transaction.class);
   criteriaCount.select(builder.count(transactions));
   criteriaCount.where(generatePredicate(tRequest, countBuilder, transactions, dateFrom, dateTo));
   count = session.createQuery(criteriaCount).setMaxResults(rowNum).getSingleResult();

(SQL)

 SELECT COUNT(*)
     FROM TRANSACTIONS
   WHERE MERCHANT_ID IN (8000011) FETCH FIRST 1000 ROWS ONLY

I want to modify this java code so, that I got this sql statement bellow

 SELECT COUNT(*)
       FROM (SELECT *
               FROM TRANSACTIONS
              WHERE MERCHANT_ID IN (8000011) FETCH FIRST 1000 ROWS ONLY)

Please let me know how to modify it.

Also, in Hibernate 3 there was option to add sql Restriction to "Criterion" like this

criterionFilter =  Restrictions.and(criterionFilter, Restrictions.sqlRestriction("rownum <= ?", 1000, StandardBasicTypes.INTEGER))

is it possible in hibernate 5?

oracle hibernate sql java
2 Answers
user_34326780

The generated query 

 SELECT COUNT(*)
     FROM TRANSACTIONS
   WHERE MERCHANT_ID IN (8000011) FETCH FIRST 1000 ROWS ONLY

provides absolutely no sense, the select count(*) returns exactly one row, so there is no reason to limit the number of fetched row to 1000.

So the only correct action is to open a ticket by the hibernate folks. In the meantime use the workaround if you get a counthigher than 1000 simple cut it down to the 1000. You get the expected result.

user_48081220
Related