JPA count NamedQuery

Question

how to get count with a namedquery, without getting all the list (it would increase performance I think). This is the named query that doesn't work:

@NamedQuery(name = "Charakteristika.findAllCount", query = "SELECT COUNT(c) FROM Charakteristika c")

Executing this:

System.out.println("a");
System.out.println(em.createNamedQuery("Charakteristika.findAllCount", Integer.class).getSingleResult().intValue());
System.out.println("b");

Output:

Although this query works:

@NamedQuery(name = "Charakteristika.findAll", query = "SELECT c FROM Charakteristika c")

Executing this:

System.out.println("a");
System.out.println(em.createNamedQuery("Charakteristika.findAll", Charakteristika.class).getResultList().size());
System.out.println("b");

Output:

a
11111
b

@JBNizet The queries are the same, the execution is different. — Richard Sitze, Aug 10 '12 at 07:10
I'm very sorry, problem is still the same, just misspelled second query. — Minutis, Aug 10 '12 at 07:16
@JBNizet The second execution specifies the result type to be `Charakteristika.class`; I'm guessing that's throwing things off, returning a count for each of `N` rows. — Richard Sitze, Aug 10 '12 at 07:23
@Minutis Generally looks reasonable, so I think more info may be helpful - what's your runtime environment? JPA engine, version, etc? — Richard Sitze, Aug 10 '12 at 07:24

score 40 · Accepted Answer · answered Aug 10 '12 at 07:49

40

Change your code likes this;

      int count = ((Number)em.createNamedQuery("Charakteristika.findAllCount").getSingleResult()).intValue();
      System.out.println(count);

answered Aug 10 '12 at 07:49

Sai Ye Yan Naing Aye

6,622
12
47
65

3

What is the Number class you're casting the result too? Shouldn't it be (int)? – Daniel Jonker Mar 04 '14 at 01:30

score 6 · Answer 2 · answered May 01 '16 at 17:55

Also you can try it: use TypeQuery interface with Long value. In the next example, "PosicionHistorialDia.findNumberSpeeding" query is a jpa count.

    @Override
    public Long findCount(String eventCode) {
         TypedQuery<Long> query = em.createNamedQuery(
            "PosicionHistorialDia.findNumberSpeeding",
            Long.class);
        query.setParameter("event", eventCode);
        return query.getSingleResult();
    }

Richard Sitze · Answer 3 · 2012-08-10T07:29:39.493

Try:

System.out.println("a");
System.out.println(em.createNamedQuery("Charakteristika.findAllCount").getSingleResult());
System.out.println("b");

And see if it's output suggests anything to you.

Failing that,

try getResultList instead of getSingleResult; otherwise the same.
try createQuery("SELECT COUNT(c) FROM Charakteristika c").getSingleResult & getResultList
Explore differences in declaration of queries; as JB points out, the queries are the same. So try both names with the different forms above.
Examine how you're building; is this part of a deployed app? stand-alone?

Something's gotta give somewhere.

Nope. No result, no error, just leaves hanging after outputing "a". — Minutis, Aug 10 '12 at 07:15

score 0 · Answer 4 · answered Aug 30 '23 at 23:53

0

another option

@NamedQuery(name = "MyTable.countAll", query = "SELECT COUNT(*) FROM MyTable")

long total = entityManager.createNamedQuery("MyTable.countAll", Long.class).getSingleResult();

answered Aug 30 '23 at 23:53

pref

1,651
14
24

JPA count NamedQuery

4 Answers4