关于oracle时间查询的bug
最近终于离开了 洲际铸件订单网那个公司,来到了沈阳的华晨宝马的铁西区工厂工作,这回牛逼了哈哈,不用加班了,就是离家有点远,每天坐班车得一个多小时通勤,想起今年前半年的996,衷心劝解正在996里挣扎的朋友们,即使没工作没收入也不要996,是真的伤身,我从疫情回来的这半年,心脏是心肌缺血了,肾也有点虚了,再吃中药,不说家常了,说bug,我以前用的一直是mysql,所以遇到这个bug有点懵
org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.PersistenceException:
### Error querying database. Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Error: Method queryTotal execution error of sql :
SELECT COUNT(1) FROM ( SELECT machine_plc_id,MACHINE_PLC_NAME,AREA_ID,CREATE_BY,CREATE_TIME,UPDATE_BY,UPDATE_TIME,IS_DEL,DEL_TIME,REMARKS,RANK FROM T_MP_MACHINE_PLC_INFO
WHERE CREATE_TIME >= ? AND IS_DEL = ? ORDER BY CREATE_TIME DESC ) TOTAL
### The error may exist in cn/bba/plant/mapper/TMpMachinePlcInfoMapper.java (best guess)
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Error: Method queryTotal execution error of sql :
SELECT COUNT(1) FROM ( SELECT machine_plc_id,MACHINE_PLC_NAME,AREA_ID,CREATE_BY,CREATE_TIME,UPDATE_BY,UPDATE_TIME,IS_DEL,DEL_TIME,REMARKS,RANK FROM T_MP_MACHINE_PLC_INFO
WHERE CREATE_TIME >= ? AND IS_DEL = ? ORDER BY CREATE_TIME DESC ) TOTAL
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:77)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)
at com.sun.proxy.$Proxy78.selectList(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:230)
at com.baomidou.mybatisplus.core.override.PageMapperMethod.executeForMany2(PageMapperMethod.java:128)
at com.baomidou.mybatisplus.core.override.PageMapperMethod.execute(PageMapperMethod.java:98)
at com.baomidou.mybatisplus.core.override.PageMapperProxy.invoke(PageMapperProxy.java:64)
at com.sun.proxy.$Proxy87.selectPage(Unknown Source)
at cn.bba.plant.service.impl.TMpMachinePlcInfoServiceImpl.list(TMpMachinePlcInfoServiceImpl.java:79)
at cn.bba.plant.service.impl.TMpMachinePlcInfoServiceImpl$$FastClassBySpringCGLIB$$e6f6ddd2.invoke(<generated>)
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:685)
at cn.bba.plant.service.impl.TMpMachinePlcInfoServiceImpl$$EnhancerBySpringCGLIB$$1ec0f37d.list(<generated>)
at cn.bba.plant.controller.TMpMachinePlcInfoController.list(TMpMachinePlcInfoController.java:49)
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.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:209)
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:136)
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:102)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:877)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:783)
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:991)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:925)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:974)
at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:877)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:661)
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:851)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:742)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:99)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.springframework.web.filter.HttpPutFormContentFilter.doFilterInternal(HttpPutFormContentFilter.java:109)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:81)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:200)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:198)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:496)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:81)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342)
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:803)
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:790)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1459)
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:748)
Caused by: org.apache.ibatis.exceptions.PersistenceException:
### Error querying database. Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Error: Method queryTotal execution error of sql :
SELECT COUNT(1) FROM ( SELECT machine_plc_id,MACHINE_PLC_NAME,AREA_ID,CREATE_BY,CREATE_TIME,UPDATE_BY,UPDATE_TIME,IS_DEL,DEL_TIME,REMARKS,RANK FROM T_MP_MACHINE_PLC_INFO
WHERE CREATE_TIME >= ? AND IS_DEL = ? ORDER BY CREATE_TIME DESC ) TOTAL
### The error may exist in cn/bba/plant/mapper/TMpMachinePlcInfoMapper.java (best guess)
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Error: Method queryTotal execution error of sql :
SELECT COUNT(1) FROM ( SELECT machine_plc_id,MACHINE_PLC_NAME,AREA_ID,CREATE_BY,CREATE_TIME,UPDATE_BY,UPDATE_TIME,IS_DEL,DEL_TIME,REMARKS,RANK FROM T_MP_MACHINE_PLC_INFO
WHERE CREATE_TIME >= ? AND IS_DEL = ? ORDER BY CREATE_TIME DESC ) TOTAL
at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:150)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:141)
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)
... 66 more
Caused by: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Error: Method queryTotal execution error of sql :
SELECT COUNT(1) FROM ( SELECT machine_plc_id,MACHINE_PLC_NAME,AREA_ID,CREATE_BY,CREATE_TIME,UPDATE_BY,UPDATE_TIME,IS_DEL,DEL_TIME,REMARKS,RANK FROM T_MP_MACHINE_PLC_INFO
WHERE CREATE_TIME >= ? AND IS_DEL = ? ORDER BY CREATE_TIME DESC ) TOTAL
at com.baomidou.mybatisplus.core.toolkit.ExceptionUtils.mpe(ExceptionUtils.java:41)
at com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor.queryTotal(PaginationInterceptor.java:223)
at com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor.intercept(PaginationInterceptor.java:170)
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61)
at com.sun.proxy.$Proxy114.prepare(Unknown Source)
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.$Proxy114.prepare(Unknown Source)
at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:85)
at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:62)
at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:326)
at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
at com.github.pagehelper.PageInterceptor.intercept(PageInterceptor.java:108)
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61)
at com.sun.proxy.$Proxy113.query(Unknown Source)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:148)
... 72 more
Caused by: java.sql.SQLDataException: ORA-01861: literal does not match format string
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:884)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1167)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1289)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3584)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3628)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1493)
at com.alibaba.druid.pool.DruidPooledPreparedStatement.executeQuery(DruidPooledPreparedStatement.java:228)
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.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:56)
at com.sun.proxy.$Proxy116.executeQuery(Unknown Source)
at com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor.queryTotal(PaginationInterceptor.java:208)
... 90 more
我看我传进入的时间没有显示出来,就有点疑惑,把sql拷出来执行
SELECT COUNT(1) FROM ( SELECT machine_plc_id,MACHINE_PLC_NAME,AREA_ID,CREATE_BY,CREATE_TIME,UPDATE_BY,UPDATE_TIME,IS_DEL,DEL_TIME,REMARKS,RANK FROM T_MP_MACHINE_PLC_INFO
WHERE CREATE_TIME >= '2020-10-22 16:53:04' AND IS_DEL = 0 ORDER BY CREATE_TIME DESC ) TOTAL;
还是报错,后来加了一个函数
SELECT COUNT(1) FROM ( SELECT machine_plc_id,MACHINE_PLC_NAME,AREA_ID,CREATE_BY,CREATE_TIME,UPDATE_BY,UPDATE_TIME,IS_DEL,DEL_TIME,REMARKS,RANK FROM T_MP_MACHINE_PLC_INFO
WHERE CREATE_TIME >= to_date('2020-10-22 16:53:04', 'yyyy-mm-dd hh24:mi:ss') AND IS_DEL = 0 ORDER BY CREATE_TIME DESC ) TOTAL;
这回好使了,原来是数据类型的问题,我传进去的是字符串,所以我就把入参的类型改了
if (StringUtils.isNotBlank(tMpMachinePlcInfo.getBeginTime())) {
Date parse = DateUtil.parse(tMpMachinePlcInfo.getBeginTime());
String dateTime = DateUtil.beginOfDay(parse).toString();
wrapper.ge( "CREATE_TIME", dateTime);
}
if (StringUtils.isNotBlank(tMpMachinePlcInfo.getEndTime())) {
Date parse = DateUtil.parse(tMpMachinePlcInfo.getEndTime());
String dateTime = DateUtil.beginOfDay(parse).toString();
wrapper.le( "CREATE_TIME", dateTime);
}
以上是原来
if (StringUtils.isNotBlank(tMpMachinePlcInfo.getBeginTime())) {
Date parse = DateUtil.parse(tMpMachinePlcInfo.getBeginTime());
DateTime dateTime = DateUtil.beginOfDay(parse);
wrapper.ge("CREATE_TIME", dateTime);
}
if (StringUtils.isNotBlank(tMpMachinePlcInfo.getEndTime())) {
Date parse = DateUtil.parse(tMpMachinePlcInfo.getEndTime());
DateTime dateTime = DateUtil.beginOfDay(parse);
wrapper.le("CREATE_TIME", dateTime);
}
以上是改了以后,换成datetime类型就好了