0

I have a query like this with pagination support,

select *
from (select distinct field1
from table
where field2 = 'some_value'
and filed3 > 'somevalue'
and field4 in('somevalues')
and field5 is null order by field1)
where ROWNUM <= <page_size> ;

How can i equally represent in hibernate.I don't want to hand construct the query.Instead i want the set the criteria in hibernate which matches the above query.

Karthik207
  • 493
  • 9
  • 27

2 Answers2

1

Use first result and max result from the criteria API. Link here.

Criteria queryCriteria = session.createCriteria(YourClass.class);
queryCriteria .setProjection(Projections.distinct(Projections.property("id")));
queryCriteria.setFirstResult(10);
queryCriteria.setMaxResults(20);
queryCriteria.add(Restrictions.eq( "propertyOne", 10));  //Add restrictions here
List data = queryCriteria.list();

The above query would give you records 10-20

terpinmd
  • 1,014
  • 8
  • 15
  • How will this work?.will the inner query gets executed first and then limits the page_size ? .So the inner query will return 100's of rows.The outer query will filter it based on page size. – Karthik207 May 08 '14 at 13:51
  • You dont need a subquery here. I will edit the answer to include distinct results. Hibernate will generate the correct sql so that you can limit the rows, no extra subquery or in clause is needed in this case. – terpinmd May 08 '14 at 14:10
  • Distinct gets applied after fetching the rows which matches the criteria.So if you set maxresults in the same criteria, it will first fetch say 20 rows, and after if it applies distinct , say we get 3 rows..ly that 3 rows will be returned,where as the page size is 20.Thats why i went for inner and outer query.So what inner query does is,it fetches all the rows where filed3 > 'somevalue'.say there are 100 rows.Then when it applies distinct, say we get 50 rows.So now the outer query only returns 20 which is the page size. – Karthik207 May 08 '14 at 14:13
  • 1
    Look at this post and see the DetatchedCriteria example then: http://stackoverflow.com/questions/300491/how-to-get-distinct-results-in-hibernate-with-joins-and-row-based-limiting-pagi – terpinmd May 08 '14 at 14:15
  • The outer query just limits the number of rows.How can i set that?. I'm clear with creating the detached criteria, but how will i set this detached criteria in main criteria?.Code sample will help. – Karthik207 May 08 '14 at 14:20
0
criteria.setMaxResults(pageSize);
criteria.setFirstResult((pageNumber) * 10)
NimChimpsky
  • 46,453
  • 60
  • 198
  • 311
  • The suggestion which you gave will make the query look like ,select distinct field1 from table where field2 = 'some_value' and filed3 > 'somevalue' and field4 in('somevalues') and field5 is null order by field1 where rownum <= page_size. I dont want this query. – Karthik207 May 08 '14 at 13:53