How to get an integer value using hibernate createSQLQuery?

Question

I need to fetch the result of the following query but i am getting a typecast exception. Kindly help out!

SELECT COUNT(*) FROM ( SELECT DISTINCT a.PROPSTAT_CODE,a.PROPSTAT_DESC,a.PROPSTAT_TYPE FROM CNFGTR_PROPSTAT_MSTR a WHERE 1 = 1 )

My code is given below,

Query query = session.createSQLQuery(sqlQuery);  

listRes = query.list();

int ans = ((Integer)listRes.get(0)).intValue();

Thanks in advance

why do you need to cast it to Integer first and then do a .intvalue()? — Sambhav Sharma
– Sambhav Sharma, Commented Apr 28, 2014 at 10:20
i found it as a solution on a site.Please suggest the right code. — Shivayan Mukherjee
– Shivayan Mukherjee, Commented Apr 28, 2014 at 10:22
You are trying to fetch multiple values from your query.. So I am guessing it might return a List of Maps.. Can you confirm this? — Sambhav Sharma
– Sambhav Sharma, Commented Apr 28, 2014 at 10:30
No,i am trying to get a count on the inner query so it will return exactly one integer value. — Shivayan Mukherjee
– Shivayan Mukherjee, Commented Apr 28, 2014 at 10:34

Nathan · Accepted Answer · 2015-12-05 10:40:54Z

Since you say that you are wrapping the above query in another query that returns the count, then this will give you want, without having to convert to any other data types.

    Integer count = (Integer) session.createSQLQuery("select count(*) as num_results from (SELECT DISTINCT a.PROPSTAT_CODE,a.PROPSTAT_DESC,a.PROPSTAT_TYPE FROM CNFGTR_PROPSTAT_MSTR a WHERE 1 = 1)")
        .addScalar("num_results", new IntegerType())
        .uniqueResult();
    System.err.println(count);

The trick is the call to "addScalar". This tells Hibernate you want the data type of "num_results" pre-converted to an Integer, regardless of what your specific DB implementation or JDBC driver prefers. Without this, Hibernate will use the type preferred by the JDBC driver, which explains why different answers here have different casts. Setting the desired result type specifically removes all guesswork about your returned data type, gives you the correct results, and has the added bonus of being more portable, should you ever wish to run your application against a different relational database. If you make the call to "list" instead of "uniqueResult" then you can assign the results directly to a List

Jay · Accepted Answer · 2014-04-28 11:08:14Z

1

Use long instead of int. Hibernate returns count(*) as long not int.

Query query = session.createSQLQuery(sqlQuery);  

listRes = query.list();

long ans = (long)listRes.get(0);

answered Apr 28, 2014 at 11:08

Jay

9,55915 gold badges63 silver badges101 bronze badges

5 Comments

Jay Over a year ago

Did you try exactly like, long ans = (long)listRes.get(0); ?

Sambhav Sharma Over a year ago

,java.lang.ClassCastException: java.math.BigDecimal cannot be cast to java.lang.Integer is the exception.. in case you try to cast to long.. you will get an exception with Integer replaced by Long

Shivayan Mukherjee Over a year ago

Have finally figured it out guys,direct conversion isn't possible. First it needs to be converted into String and then into the required datatype. Integer.parseInt((listRes.get(0).toString)))

Sambhav Sharma Over a year ago

yup.. that would be one way too.. but the BigDecimal thing did not work for you? did you try that out?

Jay Over a year ago

Before doing all those conversion, try to understand what class it is actually returning. You can find out by System.out.println(listRes.get(0).getClass());

Sambhav Sharma · Accepted Answer · 2014-04-28 11:14:14Z

1

Well.. I suppose this should work:

Query query = session.createSQLQuery(sqlQuery);  

List listRes = query.list();

int ans = ((BigDecimal) listRes.get(0)).intValue();

Note: you need to import java.math.BigDecimal

edited Apr 28, 2014 at 11:14

answered Apr 28, 2014 at 10:37

Sambhav Sharma

5,86910 gold badges59 silver badges101 bronze badges

1 Comment

Shivayan Mukherjee Over a year ago

No,Still the exception persists.Here is it,java.lang.ClassCastException: java.math.BigDecimal cannot be cast to java.lang.Integer

user3335966 · Accepted Answer · 2015-12-05 11:11:08Z

1

List number=session.createSQLQuery("SELECT COUNT(*) FROM devicemaster WHERE ClientId="+id).list();
session.getTransaction().commit();
int ans = ((java.math.BigInteger) number.get(0)).intValue();

edited Dec 5, 2015 at 11:11

user3335966

2,7494 gold badges32 silver badges33 bronze badges

answered Dec 5, 2015 at 10:16

samir vora

94 bronze badges

1 Comment

Bono Over a year ago

While this code may answer the question, it would be better to include some context, explaining how it works and when to use it. Code-only answers are not useful in the long run.

Collectives™ on Stack Overflow

How to get an integer value using hibernate createSQLQuery?

4 Answers 4

Comments

5 Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

5 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related