0

I would like to implement the following SQL query with Hibernate Criteria:

select abc_id, count(*) from boa_rep_deed where sync_system = ? And hook_id = ? group by abc_id order by abc_id

what I have tried is this ...

List<teedObject> hytlow = null;
        Criteria criteria = session.createCriteria(teedObject.class);
        criteria.add(Restrictions.ne("abcID ", abcID));
        criteria.add(Restrictions.eq("syncSystem", syncSystem));
        criteria.add(Restrictions.eq("hookId", hookId));

        hytlow = criteria.list();

Now my question is I have the corresponding pojo for this also as shown below..

class teedObject
{

    private long abcID ;
    private String syncSystem ;
    private String hookId;

    //and consisits other properties and setters and getters

}

Now I want my criteria to fetch only certain columns in order to make the object lighter so for that I have to use the projections in the criteria , can you please advise how to use projections in my case in order to fetch only certain coulmns , I have tried this..

Criteria cr = session.createCriteria(teedObject.class);
cr.add(Restrictions.eq("syncSystem", syncSystem));
cr.add(Restrictions.eq("hookId", hookId));
cr.addOrder(Order.asc("abcID"));
cr.add(Projections.groupProperty("abcID")));
cr.setProjection(Projections.rowCount());
hytlow = cr.list();

but it is throwing the exception as .. java.lang.ClassCastException: org.hibernate.criterion.PropertyProjection.. can you please advise have I implemented the projections in an correct manner

user1633823
  • 347
  • 2
  • 5
  • 14

2 Answers2

0
        List result = session.createCriteria(teedObject.class)       
                .add(Restrictions.eq("syncSystem", syncSystem))
                .add(Restrictions.eq("hookId", hookId))
                .setProjection(Projections.projectionList()
                        .add(Projections.groupProperty("abcID"))
                        .add(Projections.count("abcID"))           
                ).list();

see Hibernate Group by Criteria Object

Community
  • 1
  • 1
Leo
  • 6,480
  • 4
  • 37
  • 52
  • thanks but how I would implement this in my case, please explain. – user1633823 Jan 26 '14 at 05:09
  • I did not get it I have tried this.. Criteria cr = session.createCriteria(teedObject.class); cr.add(Restrictions.eq("syncSystem", syncSystem)); cr.add(Restrictions.eq("hookId", hookId)); cr.addOrder(Order.asc("abcID")); cr.add(Projections.groupProperty("abcID"))); cr.setProjection(Projections.rowCount()); hytlow = cr.list(); – user1633823 Jan 26 '14 at 05:20
  • but upon implementing the above i am getting the error pls advise – user1633823 Jan 26 '14 at 05:21
  • replace "setProjection(Projections.rowCount());" with "setProjection(Projections.projectionList().add(..." and then add everything from your select clause – Leo Jan 26 '14 at 05:24
  • I could not grasp still, request you to please post the modified code , that will help me to grasp the changes. – user1633823 Jan 26 '14 at 05:28
  • Thanks a lot can you please advise can I w\start with this form also..Criteria cr = session.createCriteria(teedObject.class); as I want it to start it in similiar fashion of query as I have started it please advise – user1633823 Jan 26 '14 at 05:43
  • @Leoi here result is not of criteria type – user1633823 Jan 26 '14 at 05:44
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/46099/discussion-between-leo-and-user1633823) – Leo Jan 26 '14 at 05:57
0

any Projections you add. It saves it as an array of objects. c.list(); The returned data type is actually a Projections array.

Example code:

        Criteria c;
        ProjectionList projectionList=Projections.projectionList();
        projectionList.add(Projections.alias(Projections.sum("salary"), "sum"));
        projectionList.add(Projections.avg("salary"), "avg");
        c.setProjection(projectionList);

        //list example 1
        for(Object[] o : (List<Object[]>) c.list())
        {
            System.out.println(o[0]);
            System.out.println(o[1]);
        }
        System.out.println();
        List<?> results = c.list();
        results.forEach(s -> {
            //list example2
            for(Object o:(Object[])s)
            {
                System.out.println(o);
            }
            //list example3
            System.out.println();
            Object[] o=(Object[]) s;
            System.out.println(o[0]);
            System.out.println(o[1]);
        });
Ogün
  • 97
  • 2
  • 10