Issue with get max id in criteria?

Question

I am unable to get max id from table. How can we get max id. My query is as follows,

Criteria tot = sessionFactory.getCurrentSession().createCriteria(student.class);
tot.add(Restrictions.eq("surveyId",send_Survey));
tot.add(Restrictions.eq("testId", "2" )); // Instead of hard coding as 2 I need to the maximum testId
//tot.addOrder(Order.desc("testId"));
ScrollableResults sc=tot.scroll();
sc.last();
int rowcount=sc.getRowNumber()+1;
System.out.println(" Here is the total count--- "+ rowcount);

How can I make a condition in query as maximum testId

score 0 · Answer 1 · answered May 05 '14 at 16:55

0

Use projections.

tot.setProjection(Projection.max("testId"));

answered May 05 '14 at 16:55

Zeus

6,386
6
54
89

I will try this and let you know. – Vinod May 05 '14 at 18:55
I tried your suggestion, I got the following error. "The method max(string) is undefined for the type Projection". – Vinod May 06 '14 at 05:09
I tried the following but only one row is getting selected not all rows, tot.setProjection(Projections.max("testId")); – Vinod May 06 '14 at 05:24
how can I write this with a DetachedQuery? how can I get max value any suggestions please – Vinod May 06 '14 at 05:36
http://docs.jboss.org/hibernate/orm/3.3/reference/en-US/html/querycriteria.html#querycriteria-detachedqueries this has details here – Zeus May 06 '14 at 14:50

score 0 · Accepted Answer · answered May 06 '14 at 16:09

Your code looks like you have some confusion between SQL and ORM approaches. With Criteria API you don't want to specify foreign keys, but entity properties. Given a Student class like this

    public class Student {
        private Long id;
        private Set<Survey> surveys;
        private Set<Test> tests;

        ... accessor methods etc

    }

Your code would look more like this

   tot.createAlias("surveys", "survey");
   tot.createAlias("tests", "test");
   tot.add(Restrictions.eq("survey.id",send_Survey));

And a likely subquery like this

    DetachedCriteria subquery = DetachedCriteria.forClass(Test.class);
    subquery.setProjection(Projections.max("id"));

    tot.add(Subqueries.propertyIn("test.id", subquery));

Issue with get max id in criteria?

2 Answers2