CODEWITHSUNDEEP
javahibernatejpacountcriteria
user1409708
user1409708

Reputation: 1053

Hibernate Criteria - get count of subselect

I would like to count the number of events that fulfill a certain condition. If the row count is less than a million I would like to get the actual number. If it is more than a million I do not need to know the exact number and it is ok to return the count as 1 million.

I thought about this query 

select count(*) from (select id from events where x=10  limit 1000000) a

How can it be done Detached Criteria / sub criteria?

Is there a better way of doing it other than the above sql? I'm using postgresql 9.3

Upvotes: 1

Views: 1162

Answers (2)

xrcwrn
xrcwrn

Reputation: 5325

As I understood You have to count some recode if it is >1000000 it should show 1 Million + else exact count. For this you cat try this query

String t;
            t = (String) session.createQuery(
                    "select case when count(*)>1000000   "
                    + "then '1Million +' "
                    + "else count(*) "
                    + "end "
                    + "from events").uniqueResult();
               session.getTransaction().commit();

               System.out.println("size is " + t);

Use CASE [ WHEN {test_conditional} THEN {match_result} ]* ELSE {miss_result} END https://docs.jboss.org/hibernate/orm/4.3/devguide/en-US/html/ch11.html#d5e3280

Upvotes: 0

Vlad Mihalcea
Vlad Mihalcea

Reputation: 153710

That query won't perform better than a regular count, so try this instead:

long count = ((Number) 
    session.createCriteria(Event.class)
           .setProjection(Projections.rowCount())
           .add(Restrictions.eq("x", 10))
           .uniqueResult()).longValue();
long countLimit = Math.min(count, 1000000);

Upvotes: 1

Related Questions