Hibernate 5.2 CriteriaQuery Subquery

Question

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?

score 0 · Answer 1 · answered Nov 12 '18 at 13:55

Considering the following answer/questions:

it is not possible to do that in JPQL. Unfortunately, this is a strong limitation. You should consider using a native query to do that or other approaches like views.

score 0 · Answer 2 · answered Nov 13 '18 at 21:50

0

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.

answered Nov 13 '18 at 21:50

Marmite Bomber

19,886
4
26
53

Yes but, if there is 10000000 record in table for this restriction, it will check all of them and it will take very long time. If I had option to add rownum < 1000 in hibernate 5.2 "Predicate", in this case operation will be much more faster. because it will check only 1000 row and then stop. the workaround for this is to "fetch first 1000 rows only" but it works only when you are getting records from table not count(*). so i have to add one more "select count(*) from (select...)" at the top. – JiboOne Nov 14 '18 at 09:47
Sadly this is true. But in a larger sense (and I know it is not always easy with existing frameworks) you shoud not *count* the number of rows in the first page at all. The count sould be derived implicitely from the number of rows obtained in the *fetch data* of the first page. – Marmite Bomber Nov 14 '18 at 16:25

Hibernate 5.2 CriteriaQuery Subquery

2 Answers2