views:

1237

answers:

3

I'm having a bit of a problem with converting the result of a MySQL query to a Java class when using SUM.

When performing a simple SUM in MySQL

SELECT SUM(price) FROM cakes WHERE ingredient = 'chocolate';

with price being an integer, it appears that the SUM sometimes returns a string and sometimes an integer, depending on the version of the JDBC driver.

Apparently the server does tell the JDBC driver that the result of SUM is a string, and the JDBC driver sometimes 'conveniently' converts this to an integer. (see Marc Matthews' explanation).

The Java code uses some BeanInfo and Introspection to automagically fill in a (list of) bean(s) with the result of a query. But this obviously can't work if the datatypes differ between servers where the application is deployed.

I don't care wether I get a string or an integer, but I'd like to always have the same datatype, or at least know in advance which datatype I'll be getting.

Is there some way to know which datatype will be returned by a MySQL SUM from within the Java code? Or does anyone know some better way to deal with this?

+4  A: 

This is just a guess, but maybe casting to integer will force MySQL to always tell it is an integer.

SELECT CAST(SUM(price) AS SIGNED) FROM cakes WHERE ingredient = 'marshmallows';
Vinko Vrsalovic
what's with the marshmallows ?
andyk
I like cakes with marshmallows
Vinko Vrsalovic
So focused on fiddling with the driver I completely overlooked the bleeding obvious mysql workaround :) Thanks a lot
Pieter
Dykam
@Dykam: That site is probably not very funny, then.
Vinko Vrsalovic
The fun-level isn't very high indeed, but this made me smile.
Dykam
+2  A: 

I have never worked with MySQL before so I cannot say why but If you say:

ResultSet rs = statement.executeQuery("SELECT SUM(price) FROM cakes WHERE ingredient = 'chocolate'");
int sum = 0;
if(rs.next())
size = Integer.parseInt(rs.getString(1));

Then you should not have a problem regardless of the returned datatype as the documentation says:

String getString(int columnIndex) throws SQLException


Retrieves the value of the designated column in the current row of this ResultSet object as a String in the Java programming language

gtRpr
A: 

I found COALESCE(SUM(price),0) good for always ensuring the field returned 0 if there was no result.

See: http://lists.mysql.com/mysql/177427

Treffynnon