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