How best to retrieve result of SELECT COUNT(*) from SQL query in Java/JDBC - Long? BigInteger?

Question

I'm using Hibernate but doing a simple SQLQuery, so I think this boils down to a basic JDBC question. My production app runs on MySQL but my test cases use an in memory HSQLDB. I find that a SELECT COUNT operation returns BigInteger from MySQL but Long from HSQLDB.

MySQL 5.5.22 HSQLDB 2.2.5

The code I've come up with is:

SQLQuery tq = session.createSQLQuery(
    "SELECT COUNT(*) AS count FROM calendar_month WHERE date = :date");
tq.setDate("date", eachDate);
Object countobj = tq.list().get(0);
int count = (countobj instanceof BigInteger) ?
    ((BigInteger)countobj).intValue() : ((Long)countobj).intValue();

This problem of the return type negates answers to other SO questions such as getting count(*) using createSQLQuery in hibernate? where the advice is to use setResultTransformer to map the return value into a bean. The bean must have a type of either BigInteger or Long, and fails if the type is not correct.

I'm reluctant to use a cast operator on the 'COUNT(*) AS count' portion of my SQL for fear of database interoperability. I realise I'm already using createSQLQuery so I'm already stepping outside the bounds of Hibernates attempts at database neutrality, but having had trouble before with the differences between MySQL and HSQLDB in terms of database constraints

Any advice?

In pure JDBC you could simply using ResultSet.getLong(1). The driver is responsible to do the correct datatype mapping. I have no idea what the equivalent in Hibernate would be. — user330315
– user330315, Commented Apr 20, 2012 at 6:35
Thanks horse.. yeah, pure JDBC would possibly make this easier, but via Hibernate there is only get(int) which returns Object. It seems a very clumsy interface in terms of type safety, and leaves one open to these vagaries of the underlying JDBC driver, if it is that that's choosing exactly what type to return to me. — Neek
– Neek, Commented Apr 24, 2012 at 3:02

astonchan · Accepted Answer · 2014-12-09 18:56:35Z

1

You can downcast to Number and then call the intValue() method. E.g.

SQLQuery tq = session.createSQLQuery("SELECT COUNT(*) AS count FROM calendar_month WHERE date = :date");
tq.setDate("date", eachDate);
Object countobj = tq.list().get(0);
int count = ((Number) countobj).intValue();

answered Dec 9, 2014 at 18:56

astonchan

112 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Michał Niklas · Accepted Answer · 2012-04-20 06:27:31Z

0

Two ideas:

You can get result value as String and then parse it to Long or BigInteger
Do not use COUNT(*) AS count FROM ..., better use something like COUNT(*) AS cnt ... but in your example code you do not use name of result column but it index, so you can use simply COUNT(*) FROM ...

answered Apr 20, 2012 at 6:27

Michał Niklas

54.5k19 gold badges77 silver badges125 bronze badges

1 Comment

Neek Over a year ago

I cannot accept this as a solution. To get the result value as a String, I presume you mean to CAST it to VARCHAR in the SQL? That seems absolutely horrible and a nasty workaround to this problem. There must be a way to retain the purely numerical data types implicit in a COUNT aggregation. Your second point is valid but irrelevant to the question.

jordeu · Accepted Answer · 2012-04-20 06:26:15Z

-1

I don't known a clear solution for this problem, but I will suggest you to use H2 database for your tests.

H2 database has a feature that you can connect using a compatibility mode to several different databases.

For example to use MySQL mode you connect to the database using this jdbc:h2:~/test;MODE=MySQL URL.

answered Apr 20, 2012 at 6:26

jordeu

6,8171 gold badge22 silver badges19 bronze badges

1 Comment

Neek Over a year ago

Thanks for this good suggestion. I'm stuck on HSQLDB until I find time to consider a migration. I feel sure there's a similar solution given my current toolset, rather than have to switch to H2.

Collectives™ on Stack Overflow

How best to retrieve result of SELECT COUNT(*) from SQL query in Java/JDBC - Long? BigInteger?

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related