Classes returned by the sum function of MySQL

The type returned by the MySQL sum function. When the project switches to the database today, the error code for accessing the database is probably as follows: Stringsqlselectsum (number) assumNumberOfOneDayfromtableName; ListMaprowsgetJdbcTemplate (). queryForList (SQL); for (Maprow: rows) {SomeBeanitem

The type returned by the MySQL sum function. When the project switches to the database today, the error code for accessing the database is probably as follows: String SQL = "select sum (number) as sumNumberOfOneDay from tableName "; listMap rows = getJdbcTemplate (). queryForList (SQL); for (Map row: rows) {SomeBean item =

Type returned by the sum function of MySQL
An error occurred while switching the database for today's Project

The database access code is like this:

String sql =  "select sum(number) as sumNumberOfOneDay from tableName";List rows = getJdbcTemplate().queryForList(sql);for (Map row : rows) {SomeBean item = new SomeBean();item.setSumNumberOfOneDay(objectToInt(row.get("sumNumberOfOneDay")));}private int objectToInt(Object obj) {return Integer.parseInt("" + obj);}

The table field "number" is of the int (10) unsigned type.

Connect to database DataBaseA and the test runs normally. When you switch to another database DataBaseB (database table, table name, and table structure are the same), an error is reported:
Java. lang. NumberFormatException: For input string: "10.0"

Copy the SQL statement to the MySQL command line window and run it directly. The value returned by sum (number) is 10;
But in Spring's getJdbcTemplate (). queryForList (SQL) return, it becomes 10.0,
The result of printing row. get ("sumNumberOfOneDay"). getClass () is: class java. lang. Double

Switch back to DataBaseA. The printed result is java. math. BigDecimal.

The query results of the two databases return integers in the MySQL command line window, but the floating point is returned in the Java program.

You can directly perform JDBC operations without using Spring:

Connection conn = getJdbcTemplate().getDataSource().getConnection();            Statement st = conn.createStatement();            ResultSet rs = st.executeQuery(sql);            ResultSetMetaData rsmd = rs.getMetaData();            for (int i = 1; i <= rsmd.getColumnCount(); i++) {                String name = rsmd.getColumnName(i);                String type = rsmd.getColumnTypeName(i);                System.out.println(name + ", " + type);            }

Results printed by DataBaseA:
SumNumberOfOneDay, DECIMAL

Results printed by DataBaseB:
SumNumberOfOneDay, DOUBLE

It can be determined that it is a MySQL problem.

Search online. Sure enough:

The SUM() and AVG() functions return a DECIMAL value for exact-value arguments (integer or DECIMAL), and a DOUBLE value for approximate-value arguments (FLOAT or DOUBLE). (Before MySQL 5.0.3, SUM() and AVG() return DOUBLE for all numeric arguments.)

Http://stackoverflow.com/questions/10592481/what-is-the-return-type-of-sum-in-mysql

In MySQL versions earlier than 5.0.3, the sum function returns the DOUBLE type.

Check the MySQL version:

DataBaseA:
5.1.44 Source distribution

DataBaseB:
4.1.7-standard-log

This is indeed the case.

Solution:
1. the stupid method is to override the objectToInt method:

if (obj instanceof Double) {            return ((Double)obj).intValue();        }        if (obj instanceof BigDecimal) {            return ((BigDecimal)obj).intValue();        }        return Integer.parseInt(obj.toString());

2. Use Spring BeanPropertyRowMapper:

List
 
   list = getJdbcTemplate().query(sql, new BeanPropertyRowMapper(SomeBean.class));
 

Try to use method 2 to avoid self-processing.

Check the getJdbcTemplate (). query (SQL, new BeanPropertyRowMapper (SomeBean. class) method of Spring,
The general idea is as follows:
1. Get all properties through SomeBean. class
2. Call ResultSet. getXXX () to obtain the corresponding value based on the property type.
In the ResultSet. getXXX () method, type conversion is implemented.
For example, the getInt method of com. mysql. jdbc. Result (key part of the Code ):

val = getString(columnIndex);if ((val != null) && (val.length() != 0)) {            if ((val.indexOf("e") == -1) && (val.indexOf("E") == -1)                    && (val.indexOf(".") == -1)) {                return Integer.parseInt(val);            } else {                // Convert floating point                return (int) (Double.parseDouble(val));            }        } else {            return 0;        }