Datatype of SUM result in MySQL

Question

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?

Answer 1

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

Answer 2

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

Answer 3

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