0

I have written a query in sql which is like...

SELECT c.clientfirstname, 
       c.clientlastname, 
       c.clientage, 
       c.status, 
       Sum(i.annualamount) AS amount, 
       p.planname, 
       c.clientid 
FROM   client c, 
       income i, 
       plan p 
WHERE  c.clientid = i.clientid 
       AND planid = (SELECT Max(p.planid) 
                     FROM   plan p 
                     WHERE  c.clientid = p.clientid 
                     GROUP  BY p.clientid) 
       AND ( c.clientfirstname LIKE 'rahul' ) 
GROUP  BY i.clientid

this query is fine and working properly, but i want to use criteria query of hibernate in place of above SQL query because i am using hibernate in my application. i am getting problem while adding condition like planId=(SELECT MAX(p.planId) FROM plan p WHERE c.clientId=p.clientId GROUP BY p.clientId) so how to write above sql query in hibernate criteria. i tried with this query

DetachedCriteria dc = DetachedCriteria.forClass(Client.class);
    dc.createAlias("plans", "pla");
    ProjectionList proj = Projections.projectionList();
    proj.add(Projections.max("pla.planId"));
        proj.add(Projections.groupProperty("clientId"));
    dc.setProjection(proj);


    Criteria criteria =      sessionFactory.getCurrentSession().createCriteria(Client.class);
    criteria.createAlias("incoms", "inco");
    criteria.createAlias("plans", "plan");

    Criterion firstname = Restrictions.like("clientFirstName", searchValue+"%");

    criteria.setProjection(
            Projections.projectionList()            
            .add(Projections.property("clientFirstName"))
            .add(Projections.property("clientLastName"))
            .add(Projections.property("clientAge"))
            .add(Projections.property("status"))
            .add(Projections.sum("inco.annualAmount"))
                .add(Projections.property("plan.planName"))
            .add(Projections.groupProperty("clientId"))
            );

    criteria.add(Subqueries.propertyEq("plan.planId", dc));

    Disjunction disjunction = Restrictions.disjunction();

    disjunction.add(firstname);

    criteria.add(disjunction);

    return criteria.list();
}

but i am getting error like this.....

Hibernate: 
select this_.clientFirstName as y0_, this_.clientLastName as y1_,
  this_.clientAge as y2_, this_.status as y3_, sum(inco1_.annualAmount) as y4_,
  plan2_.planName as y5_, this_.clientId as y6_ 
from Client this_ inner join Income inco1_ on this_.clientId=inco1_.clientId
  inner join Plan plan2_ on this_.clientId=plan2_.clientId 
where plan2_.planId = 
    (select max(pla1_.planId) as y0_, this_.clientId as y1_ 
     from Client this_ inner join Plan pla1_ on this_.clientId=pla1_.clientId
     group by this_.clientId
    ) 
  and (this_.clientFirstName like ? 
  or this_.clientLastName like ? 
  or this_.clientPhoneNo like ? 
  or this_.clientEmail like ?)
group by this_.clientId

Error:could not execute query

there are three tables income plan and client, client id is the foreign key in both plan and income table, and in plan table there are many planname available for one client id and i want to fetch last plan group by clientId from plan table...

my above (on top) SQL query is fine so i want same query using hibernate criteria.

AjV Jsy
  • 5,799
  • 4
  • 34
  • 30
viram
  • 1
  • 1
  • 1
  • Put that SQL into a query facility (like Sql Svr Management Studio) and see what the error is? – AjV Jsy Mar 02 '13 at 11:03
  • possible duplicate of [Hibernate Criteria Subquery](http://stackoverflow.com/questions/3738555/hibernate-criteria-subquery) – Paul Sweatte Oct 09 '14 at 00:59
  • I don't believe this is a duplicate of Hibernate Criteria Subquery as this is asking more than just how to write the subquery (specifically how to get the whole criteria working together). I came here looking up how to do that. – LConrad Oct 01 '18 at 20:44

0 Answers0