SQLServerException: 数据类型 time 和 datetime 在 less than o
发布时间：2020-12-12 13:34:31 所属栏目：MsSql教程 来源：网络整理
导读：org.springframework.jdbc.BadSqlGrammarException: ### Error querying database. Cause: com.microsoft.sqlserver.jdbc.SQLServerException: 数据类型 time 和 datetime 在 less than or equal to 运算符中不兼容。 ### The error may exist in file [D:c
|
org.springframework.jdbc.BadSqlGrammarException:
### Error querying database. Cause: com.microsoft.sqlserver.jdbc.SQLServerException: 数据类型 time 和 datetime 在 less than or equal to 运算符中不兼容。
### The error may exist in file [D:companyProjectcatering-daotargetclassescomhnustsqlmaporder.sqlMap.xml]
### The error may involve com.hnust.dao.OrderDao.queryFuzzyOrdersByDateAndTime-Inline
### The error occurred while setting parameters
### SQL: select o.id as id,o.user_id as userId,o.address as address,o.s_date as sDate,o.e_date as eDate,o.s_time as sTime,o.e_time as eTime,o.f_flag as fFlag from tb_order o WHERE o.s_date <= ? and o.s_date >=? and o.s_time <= ? and o.s_time >=?
### Cause: com.microsoft.sqlserver.jdbc.SQLServerException: 数据类型 time 和 datetime 在 less than or equal to 运算符中不兼容。
; bad SQL grammar []; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: 数据类型 time 和 datetime 在 less than or equal to 运算符中不兼容。
?at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:98)
?at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
?at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
?at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
?at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:71)
?at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:364)
?at $Proxy8.selectList(Unknown Source)
?at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:194)
?at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:114)
?at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:58)
?at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:43)
?at $Proxy19.queryFuzzyOrdersByDateAndTime(Unknown Source)
?at com.hnust.service.impl.OrderServiceImpl.queryFuzzyOrdersByDateAndTime(OrderServiceImpl.java:53)
?at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
?at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
?at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
?at java.lang.reflect.Method.invoke(Method.java:597)
?at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:309)
?at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:196)
?at $Proxy20.queryFuzzyOrdersByDateAndTime(Unknown Source)
?at com.hnust.service.test.OrderServiceTest.queryFuzzyOrdersByDateAndTimeTest(OrderServiceTest.java:94)
?at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
?at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
?at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
?at java.lang.reflect.Method.invoke(Method.java:597)
?at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:45)
?at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15)
?at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:42)
?at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:20)
?at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:28)
?at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:263)
?at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:68)
?at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:47)
?at org.junit.runners.ParentRunner$3.run(ParentRunner.java:231)
?at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:60)
?at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:229)
?at org.junit.runners.ParentRunner.access$000(ParentRunner.java:50)
?at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:222)
?at org.junit.runners.ParentRunner.run(ParentRunner.java:300)
?at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50)
?at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
?at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)
?at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)
?at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)
?at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: 数据类型 time 和 datetime 在 less than or equal to 运算符中不兼容。
?at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:196)
?at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1454)
?at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:388)
?at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:338)
?at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4026)
?at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1416)
?at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:185)
?at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:160)
?at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.execute(SQLServerPreparedStatement.java:320)
?at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.execute(NewProxyPreparedStatement.java:911)
?at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
?at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
?at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
?at java.lang.reflect.Method.invoke(Method.java:597)
?at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:55)
?at $Proxy23.execute(Unknown Source)
?at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:56)
?at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:70)
?at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:57)
?at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:259)
?at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:132)
?at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:105)
?at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:81)
?at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:104)
?at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:98)
?at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
?at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
?at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
?at java.lang.reflect.Method.invoke(Method.java:597)
?at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:354)
?... 39 more
在sqlserver中用time(0)存储时间，在java程序中用 java.sql.Time存储，如果使用time来做范围查询的话，报了以上错误！解决办法是： ? ? ? ? ? ? ? ? ? ? ? ? ?使用 HashMap<String,Object > ?param ?= new HashMap<String,Object>; 来存储 数据，其中格式使用字符串，如下： ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? Time time = new Time(Calender.getInstance().getTimeInMillis()) ?; ? ? ? ? ? ? ?? param.put("sTime",time.toString() ) ; 问题解决，这就可以使用比较了！ （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |