Mybatis 调试 range for the datatype

1.先看报错日志(全文见附件)

主要报错信息

a.

[2017-11-01_14:47:40.626] [http-nio-8080-exec-6] [ERROR] c.c.b.c.aop.CommonExceptionHandler [CommonExceptionHandle:88] - /mind/tradeManage/queryList
[2017-11-01_14:47:40.627] [http-nio-8080-exec-6] [ERROR] c.c.b.c.aop.CommonExceptionHandler [CommonExceptionHandle:91] - request 参数: {"commercialIds":["810006646,810007078,810007273,860017127"],"currentPage":["1"],"deliveryType":["4","1","2","3","15"],"endDate":["2017-11-01"],"fuzzyQueryType":["1"],"multipleCheck-check-all":["on"],"multipleCheck-item":["-2","-20","-6","-7","-15","-5","-3","-4","-1","16585972112188884","787282","575889","578109","578110","578111","578112","583827","607998","566467","578108","578107","578103","578104","578105","578106","566493","603979","578101","578100","578099","578102"],"nameOrMobile":[""],"orderDateType":["1"],"pageSize":["20"],"shopNameSearch":[""],"shopPopover-check-all":["on"],"shopPopover-item":["810006646","810007078","810007273","860017127"],"sort":["2"],"sourceChild":[""],"startDate":["2017-11-01"],"tradeStatus":[""]}
[2017-11-01_14:47:40.666] [http-nio-8080-exec-6] [ERROR] c.c.b.c.aop.CommonExceptionHandler [CommonExceptionHandle:102] - 
### Error querying database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLDataException: '1.6585972112188884E16' in column '7' is outside valid range for the datatype INTEGER.
### The error may exist in file [D:\IdeaProjects\mind\target\mind\WEB-INF\classes\com\calm\b\order\dao\OrderMapper.xml]
### The error may involve com.calm.b.order.dao.OrderMapper.getTradePayDataList-Inline
### The error occurred while setting parameters

b.### SQL: SELECT t.id, SUM(a.shop_actual_amount) receivedAmount, a.padNo cashDeviceNo, a.payment_time,         SUM(a.custRealPay) custRealPay,         SUM(CASE WHEN a.payment_type=2 THEN a.custRealPay else 0 END) refundAmount,         CASE WHEN a.payModeId = -22 THEN t.source_child ELSE a.payModeId END payModeId,         CASE WHEN MAX(a.payCount) is not NULL and MAX(a.payCount)>1 THEN '组合支付'         WHEN a.payModeId = -22 THEN         (SELECT dict_data_name FROM dictionary_data dd         WHERE dd.dict_data_code = 'tradeChildSource' AND dd.dict_data_value= t.source_child) ELSE a.`name` END         payName         FROM trade t         LEFT JOIN (         SELECT p.relate_id, p.shop_actual_amount, i.padNo, p.payment_time, sum(pi.face_amount -         pi.change_amount) custRealPay,COUNT(1) payCount,pb.`name`,p.payment_type,pb.erp_mode_id payModeId         FROM payment p         LEFT JOIN payment_item pi ON p.id = pi.payment_id AND pi.status_flag = 1 AND pi.pay_status         IN (3,4, 5,6,8 ,10) AND pi.recycle_status = 1         LEFT JOIN init_system i ON p.device_identy = i.deviceID AND i. STATUS = 0 and i.commercialID = p.shop_identy         LEFT JOIN                   (SELECT         pmb.brand_identy,pmb.erp_mode_id,min(pmb.name) 'name'         FROM payment_mode_brand pmb         WHERE pmb.brand_identy = ?         GROUP BY pmb.erp_mode_id)               pb         on pi.pay_mode_id=pb.erp_mode_id         and p.brand_identy=pb.brand_identy         WHERE p.payment_type IN (1, 2) AND p.is_paid = 1         AND p.relate_id IN          (               ?          ,              ?          ,              ?          ,              ?          ,              ?          ,              ?          ,              ?          ,              ?          ,              ?          ,              ?          ,              ?          ,              ?          ,              ?          )          GROUP BY p.id         ) a ON a.relate_id = t.id         WHERE t.id IN          (               ?          ,              ?          ,              ?          ,              ?          ,              ?          ,              ?          ,              ?          ,              ?          ,              ?          ,              ?          ,              ?          ,              ?          ,              ?          )          GROUP BY t.id
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLDataException: '1.6585972112188884E16' in column '7' is outside valid range for the datatype INTEGER.
; SQL []; '1.6585972112188884E16' in column '7' is outside valid range for the datatype INTEGER.; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLDataException: '1.6585972112188884E16' in column '7' is outside valid range for the datatype INTEGER.

2.分析

step1.首先定位到核心错误: '1.6585972112188884E16' in column '7' is outside valid range for the datatype INTEGER

我们知道,mytatis 处理数据的时候会把超长数字用科学计数法表示,这里错误的意思说的很清楚了,这个数字超出Integer数据类型的范围所以报错.

step2.根据 ### The error may exist in file [D:\IdeaProjects\mind\target\mind\WEB-INF\classes\com\calm\b\order\dao\OrderMapper.xml]
### The error may involve com.calm.b.order.dao.OrderMapper.getTradePayDataList-Inline
### The error occurred while setting parameters     跟踪到具体报错的文件OrderMapper和方法   getTradePayDataList()

问题似乎变得so easy,找到那个报错的Integer参数  把它的类型改成Long 就ok了吧?

下面来看SQL

这用了这个参数list   item.id的类型为long

WTF??????    说好的有个参数为Integer呢??????

卡住不动.......一个小时   

这种Google 百度,

[2017-11-01_14:47:40.626] [http-nio-8080-exec-6] [ERROR] c.c.b.c.aop.CommonExceptionHandler [CommonExceptionHandle:88] - /mind/tradeManage/queryList
[2017-11-01_14:47:40.627] [http-nio-8080-exec-6] [ERROR] c.c.b.c.aop.CommonExceptionHandler [CommonExceptionHandle:91] - request 参数: {"commercialIds":["810006646,810007078,810007273,860017127"],"currentPage":["1"],"deliveryType":["4","1","2","3","15"],"endDate":["2017-11-01"],"fuzzyQueryType":["1"],"multipleCheck-check-all":["on"],"multipleCheck-item":["-2","-20","-6","-7","-15","-5","-3","-4","-1","16585972112188884","787282","575889","578109","578110","578111","578112","583827","607998","566467","578108","578107","578103","578104","578105","578106","566493","603979","578101","578100","578099","578102"],"nameOrMobile":[""],"orderDateType":["1"],"pageSize":["20"],"shopNameSearch":[""],"shopPopover-check-all":["on"],"shopPopover-item":["810006646","810007078","810007273","860017127"],"sort":["2"],"sourceChild":[""],"startDate":["2017-11-01"],"tradeStatus":[""]}

因为项目是spring3  各种拦截器.........最后也没收获  因为看不到哪里使用了"16585972112188884"这个参数.

step3.这个时候多亏身边老司机帮忙.

a    当时我们正在做全局id优化,可能是全局id  在join 时的问题     经验证不是这个原因.

 b   返回TradeNewEntity对象中第七列  的类型为integer,应改为long   就是这个问题.     感谢老司机 ,特此记录