Returning result of count native query using EntityManager in Java?

Question

I have the following SQL Query :

SELECT COUNT(*) FROM DOG where ID = 'SampleId';

I am trying to write this in java :

public int returnCountOfDogTable(String id){

        String sql= "SELECT COUNT(*) FROM DOG WHERE ID =:id";
        Query query = persistence.entityManager().createNativeQuery(sql);
        query.setParameter("id", id);
        List<Integer> resultList = query.getResultList();
        int result = resultList.get(0);
        return result;
    }

However I get this exception:

java.lang.ClassCastException: java.math.BigDecimal cannot be cast to java.lang.Integer

How can I solve this?

score 33 · Accepted Answer · edited May 21 '19 at 14:03

33

You can also use Number and call intValue():

Query query = entityManager.createNativeQuery("SELECT COUNT(*) FROM DOG WHERE ID =:id");
query.setParameter("id", 1);
int count = ((Number) query.getSingleResult()).intValue();

edited May 21 '19 at 14:03

Matthias

7,432
6
55
88

answered Aug 07 '18 at 19:55

Dherik

17,757
11
115
164

score 13 · Answer 2 · answered Oct 04 '16 at 16:26

13

Make sense to use Query#getSingleResult method:

Query query = entityManager.createNativeQuery("SELECT COUNT(*) FROM DOG WHERE ID =:id");
query.setParameter("id", 1);
int count = ((BigInteger) query.getSingleResult()).intValue();

answered Oct 04 '16 at 16:26

Boris

492
4
9

Shark · Answer 3 · 2016-10-04T16:16:13.897

3

Simply try this:

public int returnCountOfDogTable(String id) {
//...
    List<BigDecimal> resultList = query.getResultList();
    BigDecimal result = resultList.get(0);
    return result.toIntValue();
}

edited Oct 04 '16 at 16:16

answered Oct 04 '16 at 16:15

Shark

6,513
3
28
50

ok, and how to then change big decimal back to int in return value? – java123999 Oct 04 '16 at 16:16
perhaps `result.toIntValue()` ? – Shark Oct 04 '16 at 16:17

score -1 · Answer 4 · answered Jan 20 '22 at 05:03

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

@PersistenceContext
protected EntityManager em;

public int getCountQuery(String sql) {
    int resultCount = 0;
    try {
        Query query = em.createNativeQuery(sql);
        resultCount = ((Number) query.getSingleResult()).intValue();
    } catch (Exception e) {
        log.error("SQL Error" + e.getMessage());
    }
    return resultCount;
}

Returning result of count native query using EntityManager in Java?

4 Answers4