SUM data type leads to MySQL

Question

I had a problem converting the result of a MySQL query to a Java class when using SUM.

When executing a simple SUM in MySQL

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

Since price is an integer, it seems that SUM sometimes returns a string, and sometimes an integer, depending on the version of the JDBC driver.

Obviously, the server tells the JDBC driver that the SUM result is a string, and the JDBC driver sometimes "conveniently" converts this to an integer. (see Mark Matthews explanation ).

The Java code uses BeanInfo and Introspection to automatically fill in the (list) bean (s) with the query result. But this obviously cannot work if the data types differ between the servers on which the application is deployed.

I don't care if I get a string or an integer, but I would like to have the same data type or at least know in advance which data type I will receive.

Is there any way to know which data type MySQL SUM will return from Java code? Or does anyone know of any better way to handle this?

Answer 1

This is just an assumption, but possibly casting to integers will force MySQL to always indicate that it is an integer.

 SELECT CAST(SUM(price) AS SIGNED) FROM cakes WHERE ingredient = 'marshmallows'; 

Answer 2

I have never worked with MySQL, so I can’t 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 problems, regardless of the returned data type, as indicated in the documentation:

String getString (int columnIndex) throws SQLException

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

Answer 3

I found COALESCE(SUM(price),0) good in order to always guarantee the returned field 0 if there was no result.

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