为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。
【DM版本】:v8
【操作系统】:windows11专业版
【CPU】:Intel(R) Core(TM) i7-14700F
【问题描述】*:
使用的url为:
jdbc:dm://localhost:5236?schema=XXL_JOB&serverTimezone=GMT%2B8&useSSL=false&compatibleMode=mysql
驱动为管理工具目录下的:/drivers/jdbc/DmJdbcDriver18.jar
项目中使用的是Mybatis,需要运行如下SQL。
用Mybatis来跑,就会有问题。出问题的SQL:
<select id="findDead" parameterType="java.util.HashMap" resultType="java.lang.Integer" >
SELECT t.id
FROM xxl_job_registry AS t
WHERE t.update_time <![CDATA[ < ]]> TIMESTAMPADD(SECOND, -#{timeout}, TIMESTAMP #{nowTime})
</select>
上面的#{timeout}是Integer类型,#{nowTime}是java.sql.Date类型
t.update_time是TIMESTAMP(0)类型.
我将参数填充后,放到管理工具跑的SQL:
SELECT t.id, t.registry_group, t.registry_key, t.registry_value, t.update_time FROM xxl_job_registry AS t WHERE t.update_time > TIMESTAMPADD(SECOND, -'10', TIMESTAMP '2024-09-12 20:40:12.0')
后者是可以正常运行的,但mybatis跑就出问题,具体错误信息如下:
### Error querying database. Cause: dm.jdbc.driver.DMException: 第 3 行, 第 65 列[?]附近出现错误:
语法分析出错
### The error may exist in file [E:\git_repo\xxl-job\xxl-job-admin\target\classes\mybatis-mapper\XxlJobRegistryMapper.xml]
### The error may involve com.xxl.job.admin.dao.XxlJobRegistryDao.findDead
### The error occurred while executing a query
### SQL: SELECT t.id FROM xxl_job_registry AS t WHERE t.update_time < TIMESTAMPADD(SECOND, -?, TIMESTAMP ?)
### Cause: dm.jdbc.driver.DMException: 第 3 行, 第 65 列[?]附近出现错误:
语法分析出错
; bad SQL grammar []; nested exception is dm.jdbc.driver.DMException: 第 3 行, 第 65 列[?]附近出现错误:
语法分析出错
at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:101)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:82)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:82)
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:92)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:439)
at com.sun.proxy.$Proxy77.selectList(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:224)
at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:147)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:80)
at org.apache.ibatis.binding.MapperProxy$PlainMethodInvoker.invoke(MapperProxy.java:141)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:86)
at com.sun.proxy.$Proxy84.findDead(Unknown Source)
at com.xxl.job.admin.core.thread.JobRegistryHelper$3.run(JobRegistryHelper.java:66)
at java.lang.Thread.run(Thread.java:750)
Caused by: dm.jdbc.driver.DMException: 第 3 行, 第 65 列[?]附近出现错误:
语法分析出错
at dm.jdbc.driver.DBError.throwException(SourceFile:744)
at dm.jdbc.a.a.y.r(SourceFile:623)
at dm.jdbc.a.a.f.r(SourceFile:138)
at dm.jdbc.a.a.y.z(SourceFile:555)
at dm.jdbc.a.a.y.L(SourceFile:536)
at dm.jdbc.a.a.a(SourceFile:267)
at dm.jdbc.a.a.a(SourceFile:791)
at dm.jdbc.driver.DmdbPreparedStatement.prepareSql(SourceFile:321)
at dm.jdbc.driver.DmdbPreparedStatement.allocateHandle(SourceFile:246)
at dm.jdbc.driver.DmdbPreparedStatement.<init>(SourceFile:148)
at dm.jdbc.driver.DmdbPreparedStatement.<init>(SourceFile:198)
at dm.jdbc.driver.DmdbConnection.do_prepareStatement(SourceFile:797)
at dm.jdbc.driver.DmdbConnection.do_prepareStatement(SourceFile:783)
at dm.jdbc.driver.DmdbConnection.prepareStatement(SourceFile:1426)
at com.zaxxer.hikari.pool.ProxyConnection.prepareStatement(ProxyConnection.java:337)
at com.zaxxer.hikari.pool.HikariProxyConnection.prepareStatement(HikariProxyConnection.java)
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.ConnectionLogger.invoke(ConnectionLogger.java:53)
at com.sun.proxy.$Proxy91.prepareStatement(Unknown Source)
at org.apache.ibatis.executor.statement.PreparedStatementHandler.instantiateStatement(PreparedStatementHandler.java:88)
at org.apache.ibatis.executor.statement.BaseStatementHandler.prepare(BaseStatementHandler.java:90)
at org.apache.ibatis.executor.statement.RoutingStatementHandler.prepare(RoutingStatementHandler.java:60)
at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:90)
at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:64)
at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:336)
at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:158)
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:110)
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:90)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:154)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:142)
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:425)
... 9 common frames omitted
因为 SECOND 是关键字,管理工具估计加双引号了。
,
需要修改连接串过滤下
jdbc:dm://192.168.1.1:5236/DAMENG?keywords=(SECOND, second)