Writing sql query in hibernate

Question

I have a sql query:

     select COUNT (distinct agentG) as count from Test_CPView where kNum = ‘test k1’ and pName = ‘test p1’

I'm trying to write into criteria query but it hasn't worked for me:

    statelessSession = sessionFactory.openStatelessSession();
    Criteria crit = statelessSession.createCriteria(APRecord.class, "apr");

    ProjectionList projList = Projections.projectionList();

        projList.add(Projections.groupProperty("pName"));
        projList.add(Projections.groupProperty("kNum"));
        projList.add(Projections.countDistinct("agentG"));
        crit.setProjection(projList);

This produces:

Hibernate: select this_.pName as y0_, this_.kNum as y1_, count(distinct this_.agentG) as y2_ from Test_CPView this_ where (lower(this_. pName + '~' + this_. kNum) like ? or lower(this_. pName + '~' + this_. kNum) like ? or lower(this_. pName + '~' + this_. kNum) like ? or lower(this_. pName + '~' + this_. kNum) like ?) group by this_.pName, this_. kNum

and the return results are null. How can I convert the above sql query into hibernate?

score 0 · Answer 1 · answered Aug 10 '15 at 15:42

0

Session.createCriteria from Docs

You have not added Restrictions

    statelessSession = sessionFactory.openStatelessSession();
        Criteria crit = statelessSession.createCriteria(APRecord.class, "apr");
    crit .add(Restrictions.eq("kNum", "test k1"));
    crit .add(Restrictions.eq("pName ", "test k1"));
    crit.setProjection(Projections.countDistinct("agentG"));
    Integer count = crit.uniqueResult();

answered Aug 10 '15 at 15:42

Kumar Abhinav

6,565
2
24
35

It gives me a caught exception. Could not resolve property pName since pName is in Test_CPView but not in the APRecord model. – Jay Aug 10 '15 at 16:12
Your POJO class should be mapped with table and their columns.pName is your POJO member variable and it should be mapped with your table columb – Kumar Abhinav Aug 10 '15 at 16:26

score 0 · Answer 2 · answered Aug 10 '15 at 15:42

statelessSession = sessionFactory.openStatelessSession();
Criteria crit = statelessSession.createCriteria(APRecord.class, "apr");
crit.add(Expression.eq("kNum","test k1"));
crit.add(Expression.eq("pName","test p1"));
crit.setProjection(Projections.countDistinct("agentG"));

score 0 · Answer 3 · answered Aug 10 '15 at 16:20

0

Query query = session.createQuery("count(agentG) from APRecord where kNum = :kNum and pName = :pName");
query.setParameter("kNum", "test k1");
query.setParameter("pName", "test p1");
return (Integer) query.uniqueResult();

answered Aug 10 '15 at 16:20

lance-java

25,497
4
59
101

Writing sql query in hibernate

3 Answers3