pgsql timestamp without time zone > character varying解决方案
最近给一个项目从中去,mysql中的日期大于小于等于操作符可以直接对string生效,会进行自动转换,但是在pgsql下不行,并且会触发,项目是springboot2+mybatisplus框架,该任务由定时任务执行。解决方案如果你没有用hutool,那么使用 ,格式一般为"2022-10-01 00:00:00";如果你有用hutool,那么可以使用,建议根据实际发生的业务以及针对系统所设置的日
问题背景
最近给一个项目从mysql迁移到pgsql
中去,mysql中的日期大于小于等于操作符可以直接对string生效,会进行自动转换,但是在pgsql下不行,并且会触发“Cause: org.postgresql.util.PSQLException: ERROR: operator does not exist: timestamp without time zone >= character varying”
,项目是springboot
2+mybatis plus
框架,该任务由定时任务执行。
报错信息:
//by zhengkai.blog.csdn.net
org.springframework.jdbc.BadSqlGrammarException:
### Error querying database. Cause: org.postgresql.util.PSQLException: 错误: 操作符不存在: timestamp without time zone > character varying
建议:没有匹配指定名称和参数类型的操作符. 您也许需要增加明确的类型转换.
位置:418
### The error may exist in com/softdev/system/mapper/UnitMapper.java (best guess)
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: SELECT unit_id,tender_id,source,from_keyword,owner_unit,owner_unit_logic,bid_unit,bid_unit_logic,price,price_logic,specialist,province,city,bid_province,bid_city,open_id,url,tender_name,tender_name_format,sub_project,create_time,update_time,tags,tag_industry,status,business_status,fix_flag,detail_flag,multi_bid_flag,duplicate_flag,word_flag,remark FROM unit WHERE (status = ? AND source = ? AND create_time > ?) ORDER BY create_time DESC
### Cause: org.postgresql.util.PSQLException: 错误: 操作符不存在: timestamp without time zone > character varying
建议:没有匹配指定名称和参数类型的操作符. 您也许需要增加明确的类型转换.
位置:418
; bad SQL grammar []; nested exception is org.postgresql.util.PSQLException: 错误: 操作符不存在: timestamp without time zone > character varying
建议:没有匹配指定名称和参数类型的操作符. 您也许需要增加明确的类型转换.
位置:418
at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:101)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:79)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:79)
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:91)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:441)
at jdk.proxy2/jdk.proxy2.$Proxy66.selectList(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:224)
at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.executeForMany(MybatisMapperMethod.java:166)
at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:77)
at com.baomidou.mybatisplus.core.override.MybatisMapperProxy$PlainMethodInvoker.invoke(MybatisMapperProxy.java:148)
at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:89)
at jdk.proxy2/jdk.proxy2.$Proxy79.selectList(Unknown Source)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:568)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:208)
at jdk.proxy2/jdk.proxy2.$Proxy80.selectList(Unknown Source)
at com.softdev.system.service.FetchService.syncGGZY2(FetchService.java:632)
at com.softdev.system.task.GGZY_ZJCS_Task.run(GGZY_ZJCS_Task.java:38)
at com.softdev.system.controller.TaskController.ggzy_zjcs_Task(TaskController.java:152)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:568)
at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205)
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:150)
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:117)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:895)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:808)
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1067)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:963)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006)
at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:909)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:681)
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:764)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:197)
at org.apache.catalina.core.StandardContextValve.__invoke(StandardContextValve.java:97)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:41002)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:540)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:135)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:357)
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:382)
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:895)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1732)
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191)
at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.base/java.lang.Thread.run(Thread.java:833)
Caused by: org.postgresql.util.PSQLException: 错误: 操作符不存在: timestamp without time zone > character varying
建议:没有匹配指定名称和参数类型的操作符. 您也许需要增加明确的类型转换.
位置:418
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2674)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2364)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:354)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:484)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:404)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:162)
at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:151)
at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:497)
at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:64)
at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79)
at jdk.internal.reflect.GeneratedMethodAccessor107.invoke(Unknown Source)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:568)
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:64)
at jdk.proxy2/jdk.proxy2.$Proxy105.query(Unknown Source)
at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63)
at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:325)
at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
at com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor.intercept(MybatisPlusInterceptor.java:81)
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:62)
at jdk.proxy2/jdk.proxy2.$Proxy104.query(Unknown Source)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:151)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:145)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:568)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:427)
... 68 more
解决方案
如果你没有用hutool,那么使用Timestamp.valueOf(yourTime)
,格式一般为"2022-10-01 00:00:00";
如果你有用hutool,那么可以使用DateUtil.parse(dateStr,dateFmt)
,建议根据实际发生的业务以及针对系统所设置的日期格式对日期类型的String做处理。
原先的查询语句为
//by zhengkai.blog.csdn.net
QueryWrapper<Unit> queryWrapper = new QueryWrapper<Unit>().eq("status",1).eq("source","广州公共资源交易中心").gt("create_time",configMapper.value("sync.start")).orderByDesc("create_time");
Timestamp.valueOf(yourTime)
解决方案
//by zhengkai.blog.csdn.net
QueryWrapper<Unit> queryWrapper = new QueryWrapper<Unit>().eq("status",1).eq("source","广州公共资源交易中心").gt("create_time",Timestamp.valueOf(configMapper.value("sync.start"))).orderByDesc("create_time");
DateUtil.parse(dateStr,dateFmt)
解决方案(这里 sync.start 2022-06-01 为分析开始日期)
//by zhengkai.blog.csdn.net
QueryWrapper<Unit> queryWrapper = new QueryWrapper<Unit>().eq("status",1).eq("source","广州公共资源交易中心").gt("create_time",DateUtil.parse(configMapper.value("sync.start"),"yyyy-MM-dd")).orderByDesc("create_time");
ok,错误解决了!
更多推荐
所有评论(0)