CODEWITHSUNDEEP
javahibernatejakarta-ee
Shivayan Mukherjee
Shivayan Mukherjee

Reputation: 827

How to get an integer value using hibernate createSQLQuery?

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

Upvotes: 0

Views: 11933

Answers (4)

samir vora
samir vora

Reputation: 9

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

Upvotes: 1

Nathan
Nathan

Reputation: 1659

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

Upvotes: 4

Sambhav Sharma
Sambhav Sharma

Reputation: 5860

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

Upvotes: 1

Jay
Jay

Reputation: 9509

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);

Upvotes: 1

Related Questions