一、问题背景
笔者在运行一个分布式项目时,首先依据需求从数据库中查询出所需要的数据,封装到对象中,然后用集合来存储
@Select("select\n" +
"b.category_id1 categoryId1,\n" +
"b.category_id2 categoryId2,\n" +
"b.category_id3 categoryId3,\n" +
"DATE_FORMAT(a.pay_time,\"%Y-%m-%d\") countDate,\n" +
"sum(b.money) money,\n" +
"sum(b.num) num\n" +
"from\n" +
"tb_order a,tb_order_item b\n" +
"where\n" +
"a.id=b.order_id and DATE_FORMAT(a.pay_time,\"%Y-%m-%d\")=#{date}\n" +
"GROUP BY \n" +
"b.category_id1,b.category_id2,b.category_id3,DATE_FORMAT(a.pay_time,\"%Y-%m-%d\")")
List<CategoryReport> countEveryDay(@Param("date") String date);
tb_order表中pay_time字段为datetime类型,也就是统计日期的 数据来源是datatime类型的,但我们将它处理为date类型查出来。
~~~~~~~~~~~~~~~~~~~~
从实体化工具中可以看到我们能够查询出4条数据,即集合长度为4
实用定时任务来进行统计,传入的时间也就是“2022-11-02”,这是一个date类型的数据
然后我们在业务层来完成添加操作
@Override
public void countEveryDay(String date) {
List<CategoryReport> list = categoryReportMapper.countEveryDay(date);
if (!list.isEmpty()) {
for (CategoryReport categoryReport : list) {
categoryReportMapper.insertSelective(categoryReport);
}
}
}
此时出现了一个异常,数据库中也只添加了一条数据
java.lang.RuntimeException: org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.executor.ExecutorException: Error selecting key or setting result to parameter object. Cause: org.apache.ibatis.executor.result.ResultMapException: Error attempting to get column 'LAST_INSERT_ID()' from result set. Cause: java.sql.SQLException: Value '0' can not be represented as java.sql.Timestamp
org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.executor.ExecutorException: Error selecting key or setting result to parameter object. Cause: org.apache.ibatis.executor.result.ResultMapException: Error attempting to get column 'LAST_INSERT_ID()' from result set. Cause: java.sql.SQLException: Value '0' can not be represented as java.sql.Timestamp
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:77)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)
at com.sun.proxy.$Proxy45.insert(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:278)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:57)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)
at com.sun.proxy.$Proxy53.insertSelective(Unknown Source)
at com.lemeng.service.impl.CategoryReportServiceImpl.countEveryDay(CategoryReportServiceImpl.java:26)
at com.lemeng.service.impl.CategoryReportServiceImpl$$FastClassBySpringCGLIB$$952755a5.invoke(<generated>)
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:747)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:294)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:689)
at com.lemeng.service.impl.CategoryReportServiceImpl$$EnhancerBySpringCGLIB$$8658f158.countEveryDay(<generated>)
at com.alibaba.dubbo.common.bytecode.Wrapper0.invokeMethod(Wrapper0.java)
at com.alibaba.dubbo.rpc.proxy.javassist.JavassistProxyFactory$1.doInvoke(JavassistProxyFactory.java:45)
at com.alibaba.dubbo.rpc.proxy.AbstractProxyInvoker.invoke(AbstractProxyInvoker.java:71)
at com.alibaba.dubbo.config.invoker.DelegateProviderMetaDataInvoker.invoke(DelegateProviderMetaDataInvoker.java:48)
at com.alibaba.dubbo.rpc.protocol.InvokerWrapper.invoke(InvokerWrapper.java:52)
at com.alibaba.dubbo.rpc.filter.ExceptionFilter.invoke(ExceptionFilter.java:61)
at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:68)
at com.alibaba.dubbo.monitor.support.MonitorFilter.invoke(MonitorFilter.java:74)
at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:68)
at com.alibaba.dubbo.rpc.filter.TimeoutFilter.invoke(TimeoutFilter.java:41)
at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:68)
at com.alibaba.dubbo.rpc.protocol.dubbo.filter.TraceFilter.invoke(TraceFilter.java:77)
at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:68)
at com.alibaba.dubbo.rpc.filter.ContextFilter.invoke(ContextFilter.java:71)
at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:68)
at com.alibaba.dubbo.rpc.filter.GenericFilter.invoke(GenericFilter.java:131)
at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:68)
at com.alibaba.dubbo.rpc.filter.ClassLoaderFilter.invoke(ClassLoaderFilter.java:37)
at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:68)
at com.alibaba.dubbo.rpc.filter.EchoFilter.invoke(EchoFilter.java:37)
at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:68)
at com.alibaba.dubbo.rpc.protocol.dubbo.DubboProtocol$1.reply(DubboProtocol.java:98)
at com.alibaba.dubbo.remoting.exchange.support.header.HeaderExchangeHandler.handleRequest(HeaderExchangeHandler.java:96)
at com.alibaba.dubbo.remoting.exchange.support.header.HeaderExchangeHandler.received(HeaderExchangeHandler.java:168)
at com.alibaba.dubbo.remoting.transport.DecodeHandler.received(DecodeHandler.java:50)
at com.alibaba.dubbo.remoting.transport.dispatcher.ChannelEventRunnable.run(ChannelEventRunnable.java:79)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
Caused by: org.apache.ibatis.executor.ExecutorException: Error selecting key or setting result to parameter object. Cause: org.apache.ibatis.executor.result.ResultMapException: Error attempting to get column 'LAST_INSERT_ID()' from result set. Cause: java.sql.SQLException: Value '0' can not be represented as java.sql.Timestamp
at tk.mybatis.mapper.mapperhelper.SelectKeyGenerator.processGeneratedKeys(SelectKeyGenerator.java:101)
at tk.mybatis.mapper.mapperhelper.SelectKeyGenerator.processAfter(SelectKeyGenerator.java:63)
at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:50)
at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74)
at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50)
at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63)
at com.sun.proxy.$Proxy62.update(Unknown Source)
at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:198)
at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:185)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433)
... 44 more
Caused by: org.apache.ibatis.executor.result.ResultMapException: Error attempting to get column 'LAST_INSERT_ID()' from result set. Cause: java.sql.SQLException: Value '0' can not be represented as java.sql.Timestamp
at org.apache.ibatis.type.BaseTypeHandler.getResult(BaseTypeHandler.java:68)
at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.createPrimitiveResultObject(DefaultResultSetHandler.java:724)
at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.createResultObject(DefaultResultSetHandler.java:611)
at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.createResultObject(DefaultResultSetHandler.java:590)
at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.getRowValue(DefaultResultSetHandler.java:392)
at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleRowValuesForSimpleResultMap(DefaultResultSetHandler.java:351)
at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleRowValues(DefaultResultSetHandler.java:326)
at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleResultSet(DefaultResultSetHandler.java:299)
at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleResultSets(DefaultResultSetHandler.java:192)
at org.apache.ibatis.executor.statement.SimpleStatementHandler.query(SimpleStatementHandler.java:74)
at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79)
at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63)
at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:324)
at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:83)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.ibatis.plugin.Invocation.proceed(Invocation.java:49)
at com.github.pagehelper.SqlUtil._processPage(SqlUtil.java:401)
at com.github.pagehelper.SqlUtil.processPage(SqlUtil.java:374)
at com.github.pagehelper.PageHelper.intercept(PageHelper.java:254)
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61)
at com.sun.proxy.$Proxy62.query(Unknown Source)
at tk.mybatis.mapper.mapperhelper.SelectKeyGenerator.processGeneratedKeys(SelectKeyGenerator.java:77)
... 63 more
Caused by: java.sql.SQLException: Value '0' can not be represented as java.sql.Timestamp
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1094)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:997)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:983)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:928)
at com.mysql.jdbc.ResultSetRow.getTimestampFast(ResultSetRow.java:1102)
at com.mysql.jdbc.ByteArrayRow.getTimestampFast(ByteArrayRow.java:127)
at com.mysql.jdbc.ResultSetImpl.getTimestampInternal(ResultSetImpl.java:6587)
at com.mysql.jdbc.ResultSetImpl.getTimestamp(ResultSetImpl.java:6187)
at com.mysql.jdbc.ResultSetImpl.getTimestamp(ResultSetImpl.java:6225)
at com.alibaba.druid.pool.DruidPooledResultSet.getTimestamp(DruidPooledResultSet.java:366)
at org.apache.ibatis.type.DateTypeHandler.getNullableResult(DateTypeHandler.java:39)
at org.apache.ibatis.type.DateTypeHandler.getNullableResult(DateTypeHandler.java:28)
at org.apache.ibatis.type.BaseTypeHandler.getResult(BaseTypeHandler.java:66)
... 89 more
at com.alibaba.dubbo.rpc.filter.ExceptionFilter.invoke(ExceptionFilter.java:105)
at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:68)
at com.alibaba.dubbo.monitor.support.MonitorFilter.invoke(MonitorFilter.java:74)
at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:68)
at com.alibaba.dubbo.rpc.filter.TimeoutFilter.invoke(TimeoutFilter.java:41)
at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:68)
at com.alibaba.dubbo.rpc.protocol.dubbo.filter.TraceFilter.invoke(TraceFilter.java:77)
at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:68)
at com.alibaba.dubbo.rpc.filter.ContextFilter.invoke(ContextFilter.java:71)
at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:68)
at com.alibaba.dubbo.rpc.filter.GenericFilter.invoke(GenericFilter.java:131)
at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:68)
at com.alibaba.dubbo.rpc.filter.ClassLoaderFilter.invoke(ClassLoaderFilter.java:37)
at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:68)
at com.alibaba.dubbo.rpc.filter.EchoFilter.invoke(EchoFilter.java:37)
at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:68)
at com.alibaba.dubbo.rpc.protocol.dubbo.DubboProtocol$1.reply(DubboProtocol.java:98)
at com.alibaba.dubbo.remoting.exchange.support.header.HeaderExchangeHandler.handleRequest(HeaderExchangeHandler.java:96)
at com.alibaba.dubbo.remoting.exchange.support.header.HeaderExchangeHandler.received(HeaderExchangeHandler.java:168)
at com.alibaba.dubbo.remoting.transport.DecodeHandler.received(DecodeHandler.java:50)
at com.alibaba.dubbo.remoting.transport.dispatcher.ChannelEventRunnable.run(ChannelEventRunnable.java:79)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
这是因为在插入数据时数据类型不一致导致的。
但是我们需要的只是一个日期,不需要具体的时间,所以在sql语句中我们将获得日期时间进行处理后只保留到日期。我们也需要让它能够添加到数据库中。
二、解决方案
这种情况,实际上是在我们对datetime类型数据处理时出现的异常,即查询过程中出现的异常,只需要在数据库相关的配置中添加
&zeroDateTimeBehavior=convertToNull
的代码即可解决,如下面代码所示
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://127.0.0.1:3306/lemeng_order?characterEncoding=UTF8&zeroDateTimeBehavior=convertToNull
jdbc.username=root
jdbc.password=root
添加上相应的配置之后就可以解决问题,并能顺利将数据添加到数据库中。
三、小结
是用这种方式能够解决上述的的问题,但不是直接处理,而是做了规避处理,从而避免了这种异常,事实上最好的解决方案是在查询时就以日期时间的格式查询出来,避免出现数据类型的转换。关于上述方法还可查看https://blog.csdn.net/qq_42782063/article/details/90691597