How to write a query in hibernate for count(*)

Question

I want to execute the below query in Hibernate?

select count(*) from login where emailid='something' and password='something'

Um... how about `select count(*) from Login login where login.emailid='something' and login.password='something'`? Or are you asking how to execute a HQL query at all? — Joachim Isaksson, Jun 29 '13 at 19:08

score 94 · Accepted Answer · answered Jun 29 '13 at 19:12

94

Suppose your login table is mapped by a LoginClass class, with emailid and password instance variables. Then you'll execute something like:

Query query = session.createQuery(
        "select count(*) from LoginClass login where login.emailid=:email and login.password=:password");
query.setString("email", "something");
query.setString("password", "password");
Long count = (Long)query.uniqueResult();

It should return in count the result you're looking for. You just have to adapt the name to your class and your parameter names.

answered Jun 29 '13 at 19:12

eternay

3,754
2
29
27

1

On using it the same way, I am getting an error, table or view does not exist. The query with the tablename, is working fine in db. Any idea what the error is? – Aparna Aug 16 '16 at 07:03
Works fine with Hibernate 5.3.1. – BobC Jul 06 '19 at 02:04
I had to use getSingleResult() instead uniqueResult(). The latter was not available – Davide Nov 13 '20 at 10:07
1

If anyone like me faced the error of _BigDecimal cannot cast it to Long_ when trying to use the solution above, use this: `Long count = ((BigDecimal) query.uniqueResult()).longValue();` – Houari Zegai Dec 28 '20 at 08:49
This query is not valid HQL and my no longer work if Hibernate will become more strict. It should be select count(login) from LoginClass login – Simon Martinelli Mar 24 '21 at 13:03

borchvm · Answer 2 · 2021-03-24T13:31:34.583

1

The result returns BigDecimal and you need to convert it from Bigdecimal (java.math.BigDecimal) to Long so that no error, he solution would be this:

Query query = session.createSQLQuery(
        "select count(*) from login where login.emailid=:email and login.password=:password");
query.setString("email", "something");
query.setString("password", "password");
Long count = ((BigDecimal) query.uniqueResult()).longValue();

edited Mar 24 '21 at 13:31

answered Mar 24 '21 at 12:52

borchvm

3,533
16
44
45

2

This query is not valid HQL and my no longer work if Hibernate will become more strict. It should be select count(login) from LoginClass login – Simon Martinelli Mar 24 '21 at 13:04
no, it I did is with hibernate but native, it is already changed – borchvm Mar 24 '21 at 13:33
Ah ok. Thanks for the fix – Simon Martinelli Mar 24 '21 at 13:34

score 0 · Answer 3 · answered May 28 '23 at 11:31

You can query in Hibernate 6 this way:

public int getCount() {
    int result = 0;
    try {
        session.beginTransaction();

        result = session.createNativeQuery("select count(*) from user", Integer.class).uniqueResult();

        session.getTransaction().commit();

    } catch (Exception exception) {
        exception.printStackTrace();
    }

    return result;
}

How to write a query in hibernate for count(*)

3 Answers3

Linked