Getting form DB XXX.0E0 ( XX stand for a number)

Question

I'm trying to query select statements using JDBCTamplate. select statement:

SELECT currency, SUM(amount) AS total
  FROM table_name
 WHERE user_id IN (:userIdList)
 GROUP BY currency

DB Table has three columns:

• user_id
• currency
• amount

table for example

user_id    currency   amount   
1           EUR        9000  
2           EUR        1000  
3           USD        124  

When I'm trying to run this code

namedParamJDBCTemplate.query(query,
                             new MapSqlParameterSource('user_id', userIdList),
                             new ResultSetExtractor<Map>() {
                                 @Override
                                 public Map extractData(ResultSet resultSet) throws SQLException, DataAccessException {
                                     HashMap<String,Object> mapRet = new HashMap<String,Object>();
                                     while(resultSet.next()){
                                         mapRet.put(resultSet.getString("currency"), resultSet.getString("total"));
                                     }
                                     return mapRet;
                                 }
                             });

I'm getting the result set as a map, but the result of the amount looks like this :

EUR -> 10000.0E0  
USD -> 124.0E0  

When I run the same query in DB ( not via code) the result set is fine and without the '0E0'.

How can I get only EUR -> 10000 and USD-> 124 without the '0E0'?

Answer 1

.0E0 is the exponent of the number, as I think. So 124.0E0 stands for 124.0 multiplied with ten raised to the power of 0 (written 124 x 10^0). Anything raised to the power of 0 is 1, so you've got 124 x 1, which, of course, is the right value.

(If it was, e. g., 124.5E3, this would mean 124500.)

This notation is used more commonly to work with large numbers, because 5436.7E20 is much more readable than 543670000000000000000000.

Without knowing your database background, I can only suppose that this notation arises from the conversion of the numeric field to a string (in result.getString("total")). Therefore, you should ask yourself, if you really need the result as a string (or just use .getFloat or so, also changing your HashMap type). If so, you still have some possibilities:

• Convert the value to a string later → e. g. String.valueOf(resultSet.getFloat("total"))

• Truncate the .0E0 → e. g. resultSet.getString("total").replace(".0E0", "") (Attention, of course this won't work if, for some reason, you get another suffix like .5E3; it will also cut off any positions after the decimal point)

• Perhaps find a database, JDBC or driver setting that suppresses the E-Notation.