SUM中的数据类型结果在MySQL中(Datatype of SUM result in MySQL)

在使用SUM时，将MySQL查询的结果转换为Java类时遇到了一些问题。

在MySQL中执行简单的SUM时

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

price是一个整数，看起来SUM有时会返回一个字符串，有时会返回一个整数，这取决于JDBC驱动程序的版本。

显然，服务器确实告诉JDBC驱动程序SUM的结果是一个字符串，并且JDBC驱动程序有时'方便'将其转换为整数。 （见Marc Matthews的解释 ）。

Java代码使用一些BeanInfo和Introspection以查询结果自动填充（列表）bean（s）。 但是，如果数据类型在部署应用程序的服务器之间有所不同，那么这显然不起作用。

我不在乎我得到一个字符串或一个整数，但我想总是有相同的数据类型，或者至少知道我会得到哪种数据类型。

有什么方法可以从Java代码中知道哪个数据类型将由MySQL SUM返回？ 还是有人知道一些更好的方法来处理这个问题？

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?

最满意答案

这只是一个猜测，但也许转换为整数将迫使MySQL总是告诉它是一个整数。

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

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';SUM中的数据类型结果在MySQL中(Datatype of SUM result in MySQL)

在使用SUM时，将MySQL查询的结果转换为Java类时遇到了一些问题。

在MySQL中执行简单的SUM时

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

price是一个整数，看起来SUM有时会返回一个字符串，有时会返回一个整数，这取决于JDBC驱动程序的版本。

显然，服务器确实告诉JDBC驱动程序SUM的结果是一个字符串，并且JDBC驱动程序有时'方便'将其转换为整数。 （见Marc Matthews的解释 ）。

Java代码使用一些BeanInfo和Introspection以查询结果自动填充（列表）bean（s）。 但是，如果数据类型在部署应用程序的服务器之间有所不同，那么这显然不起作用。

我不在乎我得到一个字符串或一个整数，但我想总是有相同的数据类型，或者至少知道我会得到哪种数据类型。

有什么方法可以从Java代码中知道哪个数据类型将由MySQL SUM返回？ 还是有人知道一些更好的方法来处理这个问题？

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?

最满意答案

这只是一个猜测，但也许转换为整数将迫使MySQL总是告诉它是一个整数。

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

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