How can I implement Select and Count in Hibernate using criteria

Question

I'm new using hibernate, I have query like :

select count(1) from (
SELECT COUNT (1)
FROM USR_BASE
WHERE ST_CD = 1
group by USR_NO)

How can I implement that query in Hibernate using criteria ?

Because, I already implement with method :

public int totalUser(UsrBase usrBase) {
    Criteria criteria = createCriteria();
    String stCd = usrBase.getStCd();
    criteria.setProjection(Projections.projectionList())
            .add(Projections.property("usrNo"))
            .add(Projections.property(stCd))
            .add(Projections.groupProperty("usrNo")));

    return((Long)criteria.setProjection(Projections.rowCount()).uniqueResult()).intValue();

}

the result not same with my query... Please help me.

your sql query isn't so clear as what exactly you are trying to achieve. There is no point of using group by as at the end your are counting entire result. — Ravi
– Ravi, Commented Oct 11, 2018 at 3:35
Agree with the above comment. Your query makes no sense, so there is no point to asking about Criteria syntax. — Tim Biegeleisen
– Tim Biegeleisen, Commented Oct 11, 2018 at 3:37
how about this ? select count(*) from ( select usr_mgnt_no from igib_usr_base where st_cd = 1 group by usr_mgnt_no) — anggaerlangga
– anggaerlangga, Commented Oct 11, 2018 at 3:38

Huy Nguyen · Accepted Answer · 2018-10-11 04:04:16Z

1

select count(1) from (
SELECT COUNT (1)
FROM USR_BASE
WHERE ST_CD = 1
group by USR_NO)

I think it will be more easily with

select count(distinct(USR_NO)) from USR_BASE WHERE ST_CD = 1

answered Oct 11, 2018 at 4:04

Huy Nguyen

2,0611 gold badge12 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Huy Nguyen Over a year ago

i am glad it help.

Collectives™ on Stack Overflow

How can I implement Select and Count in Hibernate using criteria

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related