问题描述:sum求和要求返回float(或 integer或double等)类型,但当数据库不存在任何符合求和记录时,sum返回null,报类型绑定错误异常(mybatis:
org.apache.ibatis.binding.BindingException: Mapper method 'com.danaaa.cm.dao.AgentWithdrawalMapper.getTotalByWeek attempted to return null from a method with a primitive return type (float).
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:69)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:43)
at com.sun.proxy.$Proxy222.getTotalByWeek(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
at com.sun.proxy.$Proxy223.getTotalByWeek(Unknown Source)
)
解决办法:IFNULL(SUM(transfer_amount),0),当数据库不存在任何符合求和记录时,sum返回0
例如:SELECT IFNULL(SUM(transfer_amount),0) FROM cm_agent_withdrawal WHERE transfer_status in (1,2,3)
mysql IFNULL(expr1,expr2)
如果 expr1 不是 NULL,IFNULL() 返回 expr1,否则它返回 expr2。
IFNULL()返回一个数字或字符串值,取决于它被使用的上下文环境。