我使用mysql 8.0和spring batch 4.3.5with spring boot 2.x。我在运行批处理时得到这个异常。异常是java.sql. sqlsyntaxerroreexception: Table 'dsm-batch。batchJOB_INSTANCE'不存在
这是完整的堆栈跟踪。
2022-06-22T04:35:16,377 DEBUG [schedulerPool-1] o.s.j.c.JdbcTemplate:Executing prepared SQL statement [SELECT JOB_INSTANCE_ID, JOB_NAME from batchJOB_INSTANCE where JOB_NAME = ? and JOB_KEY = ?]
2022-06-22T04:35:16,446 ERROR [schedulerPool-1] o.s.a.i.SimpleAsyncUncaughtExceptionHandler:Unexpected exception occurred invoking async method: public void com.tecnotree.dsm.scheduler.Scheduler.runJob() throws org.springframework.batch.core.repository.JobExecutionAlreadyRunningException,org.springframework.batch.core.repository.JobRestartException,org.springframework.batch.core.repository.JobInstanceAlreadyCompleteException,org.springframework.batch.core.JobParametersInvalidException
org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT JOB_INSTANCE_ID, JOB_NAME from batchJOB_INSTANCE where JOB_NAME = ? and JOB_KEY = ?]; nested exception is java.sql.SQLSyntaxErrorException: Table 'dsm-batch.batchJOB_INSTANCE' doesn't exist
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:239)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70)
at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1541)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:667)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:713)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:744)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:757)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:815)
at org.springframework.batch.core.repository.dao.JdbcJobInstanceDao.getJobInstance(JdbcJobInstanceDao.java:151)
at org.springframework.batch.core.repository.support.SimpleJobRepository.getLastJobExecution(SimpleJobRepository.java:269)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:566)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:388)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:215)
at com.sun.proxy.$Proxy132.getLastJobExecution(Unknown Source)
at org.springframework.batch.core.launch.support.SimpleJobLauncher.run(SimpleJobLauncher.java:104)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:566)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
at org.springframework.batch.core.configuration.annotation.SimpleBatchConfiguration$PassthruAdvice.invoke(SimpleBatchConfiguration.java:128)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:215)
at com.sun.proxy.$Proxy95.run(Unknown Source)
at com.tecnotree.dsm.scheduler.Scheduler.runJob(Scheduler.java:36)
at com.tecnotree.dsm.scheduler.Scheduler$$FastClassBySpringCGLIB$$cf5bb24e.invoke(<generated>)
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:793)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:763)
at org.springframework.aop.interceptor.AsyncExecutionInterceptor.lambda$invoke$0(AsyncExecutionInterceptor.java:115)
at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: java.sql.SQLSyntaxErrorException: Table 'dsm-batch.batchJOB_INSTANCE' doesn't exist
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:1009)
at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:722)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:651)
... 42 more
2022-06-22T04:40:16,353 DEBUG [schedulerPool-2] c.t.d.s.Scheduler:running job... start time is:2022-06-22T04:40:16.353233200
我在spring batch core jar schema文件中搜索mysql，这个表在那个文件中真的不存在。这是我的配置。
spring.datasource.url=jdbc:mysql://localhost:3306/dsm-batch?useUnicode=yes
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.platform=mysql
spring.batch.schema=classpath:/org/springframework/batch/core/schema-mysql.sql
spring.batch.jdbc.table-prefix=batch
spring.batch.jdbc.initialize-schema = ALWAYS
任何帮助都将是感激的。
根据错误信息:Table 'dsm-batch.batchJOB_INSTANCE' doesn't exist，表前缀为dsm-batch，但在属性spring.batch.jdbc.table-prefix=batch中设置为不同的值。
你应该相应地更新这个属性:
spring.batch.jdbc.table-prefix=dsm-batch
我把spring.batch.jdbc。Table-prefix =batch在我的属性文件中。当spring批处理创建它的表时，它添加了batch_tablename，例如BATCH_JOB_EXECUTION_CONTEXT。但是当它查询时，搜索没有_字符的batchtable。例如,batchjob_execution_context。删除spring.batch.jdbc。Table-prefix =batch解决了我的问题