java - 由: com. mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException引起:表 'test.raw_report'不存在

Question

我正在开发一个 Spring Batch 示例并使用 junit 对其进行测试。当我开始测试它时，我看到出现以下错误。我点击了这个链接:https://www.mkyong.com/spring-batch/spring-batch-metadata-tables-are-not-created-automatically/ 。因为完全相同，我希望在作业开始时自动创建表，但我发现它不起作用。请指导。

23:05:49.494 [main] DEBUG o.s.b.c.r.dao.JdbcStepExecutionDao - Truncating long message before update of StepExecution, original message is: org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [

                insert into RAW_REPORT(DATE,IMPRESSIONS,CLICKS,EARNING) values (?, ?, ?, ?)

            ]; nested exception is java.sql.BatchUpdateException: Table 'test.raw_report' doesn't exist
    at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:231)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:649)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:662)
    at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:950)
    at org.springframework.jdbc.core.namedparam.NamedParameterBatchUpdateUtils.executeBatchUpdateWithNamedParameters(NamedParameterBatchUpdateUtils.java:40)
    at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.batchUpdate(NamedParameterJdbcTemplate.java:335)
    at org.springframework.batch.item.database.JdbcBatchItemWriter.write(JdbcBatchItemWriter.java:181)
    at org.springframework.batch.core.step.item.SimpleChunkProcessor.writeItems(SimpleChunkProcessor.java:175)
    at org.springframework.batch.core.step.item.SimpleChunkProcessor.doWrite(SimpleChunkProcessor.java:151)
    at org.springframework.batch.core.step.item.SimpleChunkProcessor.write(SimpleChunkProcessor.java:274)
    at org.springframework.batch.core.step.item.SimpleChunkProcessor.process(SimpleChunkProcessor.java:199)
    at org.springframework.batch.core.step.item.ChunkOrientedTasklet.execute(ChunkOrientedTasklet.java:75)
    at org.springframework.batch.core.step.tasklet.TaskletStep$ChunkTransactionCallback.doInTransaction(TaskletStep.java:406)
    at org.springframework.batch.core.step.tasklet.TaskletStep$ChunkTransactionCallback.doInTransaction(TaskletStep.java:330)
    at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:133)
    at org.springframework.batch.core.step.tasklet.TaskletStep$2.doInChunkContext(TaskletStep.java:271)
    at org.springframework.batch.core.scope.context.StepContextRepeatCallback.doInIteration(StepContextRepeatCallback.java:81)
    at org.springframework.batch.repeat.support.RepeatTemplate.getNextResult(RepeatTemplate.java:374)
    at org.springframework.batch.repeat.support.RepeatTemplate.executeInternal(RepeatTemplate.java:215)
    at org.springframework.batch.repeat.support.RepeatTemplate.iterate(RepeatTemplate.java:144)
    at org.springframework.batch.core.step.tasklet.TaskletStep.doExecute(TaskletStep.java:257)
    at org.springframework.batch.core.step.AbstractStep.execute(AbstractStep.java:200)
    at org.springframework.batch.core.job.SimpleStepHandler.handleStep(SimpleStepHandler.java:148)
    at org.springframework.batch.core.job.flow.JobFlowExecutor.executeStep(JobFlowExecutor.java:64)
    at org.springframework.batch.core.job.flow.support.state.StepState.handle(StepState.java:67)
    at org.springframework.batch.core.job.flow.support.SimpleFlow.resume(SimpleFlow.java:169)
    at org.springframework.batch.core.job.flow.support.SimpleFlow.start(SimpleFlow.java:144)
    at org.springframework.batch.core.job.flow.FlowJob.doExecute(FlowJob.java:134)
    at org.springframework.batch.core.job.AbstractJob.execute(AbstractJob.java:306)
    at org.springframework.batch.core.launch.support.SimpleJobLauncher$1.run(SimpleJobLauncher.java:135)
    at org.springframework.core.task.SyncTaskExecutor.execute(SyncTaskExecutor.java:50)
    at org.springframework.batch.core.launch.support.SimpleJobLauncher.run(SimpleJobLauncher.java:128)
    at com.mkyong.main.MainApp.main(MainApp.java:21)
Caused by: java.sql.BatchUpdateException: Table 'test.raw_report' doesn't exist
    at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:2054)
    at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1467)
    at org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:966)
    at org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:950)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:633)
    ... 31 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'test.raw_report' doesn't exist
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    at com.mysql.jdbc.Util.getInstance(Util.java:386)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4187)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4119)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2570)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2731)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2815)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2458)
    at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:2006)
    ... 35 more

数据库.xml

<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:jdbc="http://www.springframework.org/schema/jdbc"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:context="http://www.springframework.org/schema/context"
    xsi:schemaLocation="http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc.xsd
        http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">

    <context:property-placeholder location="classpath:database.properties"/>

    <!-- connect to database -->
    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="${mysq.db.driverClass}" />
        <property name="url" value="${mysql.jdbc.url}" />
        <property name="username" value="${mysql.jdbc.username}" />
        <property name="password" value="${mysql.jdbc.password}" />
    </bean>

    <bean id="transactionManager"
        class="org.springframework.batch.support.transaction.ResourcelessTransactionManager" />

    <!-- create job-meta tables automatically -->
    <jdbc:initialize-database data-source="dataSource">
        <jdbc:script location="org/springframework/batch/core/schema-drop-mysql.sql" />
        <jdbc:script location="org/springframework/batch/core/schema-mysql.sql" />
    </jdbc:initialize-database>
</beans>

job-report.xml

<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:batch="http://www.springframework.org/schema/batch"
    xmlns:task="http://www.springframework.org/schema/task"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:context="http://www.springframework.org/schema/context"
    xsi:schemaLocation="http://www.springframework.org/schema/batch http://www.springframework.org/schema/batch/spring-batch.xsd
        http://www.springframework.org/schema/task http://www.springframework.org/schema/task/spring-task-3.2.xsd
        http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.2.xsd">

    <bean class="org.springframework.batch.test.JobLauncherTestUtils"/>

    <bean id="report" class="com.mkyong.model.Report" scope="prototype" />

    <batch:job id="reportJob">
        <batch:step id="step1">
            <batch:tasklet>
                <batch:chunk reader="cvsFileItemReader" writer="mysqlItemWriter" commit-interval="2" />
            </batch:tasklet>
        </batch:step>
    </batch:job>


    <bean id="cvsFileItemReader" class="org.springframework.batch.item.file.FlatFileItemReader">

        <!-- Read a csv file -->
        <property name="resource" value="classpath:cvs/report.csv" />

        <property name="lineMapper">
            <bean class="org.springframework.batch.item.file.mapping.DefaultLineMapper">

                <!-- split it -->
                <property name="lineTokenizer">
                    <bean class="org.springframework.batch.item.file.transform.DelimitedLineTokenizer">
                        <property name="names" value="date,impressions,clicks,earning" />
                    </bean>
                </property>

                <property name="fieldSetMapper">

                    <!-- return back to reader, rather than a mapped object. -->
                    <!-- <bean class="org.springframework.batch.item.file.mapping.PassThroughFieldSetMapper" /> -->

                    <!-- map to an object -->
                    <bean class="org.springframework.batch.item.file.mapping.BeanWrapperFieldSetMapper">
                        <property name="prototypeBeanName" value="report" />
                    </bean>
                </property>
            </bean>
        </property>
    </bean>

    <bean id="mysqlItemWriter"  class="org.springframework.batch.item.database.JdbcBatchItemWriter">
        <property name="dataSource" ref="dataSource" />
        <property name="sql">
            <value>
            <![CDATA[        
                insert into RAW_REPORT(DATE,IMPRESSIONS,CLICKS,EARNING) values (:date, :impressions, :clicks, :earning)
            ]]>
            </value>
        </property>
        <!-- It will take care matching between object property and sql name parameter -->
        <property name="itemSqlParameterSourceProvider">
            <bean class="org.springframework.batch.item.database.BeanPropertyItemSqlParameterSourceProvider" />
        </property>
    </bean>

</beans>

context.xml

<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="
        http://www.springframework.org/schema/beans  http://www.springframework.org/schema/beans/spring-beans.xsd">

    <!-- stored job-meta in database -->
    <bean id="jobRepository" class="org.springframework.batch.core.repository.support.JobRepositoryFactoryBean">
        <property name="dataSource" ref="dataSource" />
        <property name="transactionManager" ref="transactionManager" />
        <property name="databaseType" value="mysql" />
    </bean>

    <bean id="jobLauncher" class="org.springframework.batch.core.launch.support.SimpleJobLauncher">
        <property name="jobRepository" ref="jobRepository" />
    </bean>
</beans>

我期望 Spring Batch 或 dataSource 自动创建下表。

CREATE TABLE `test`.`raw_report` (
  `DATE` DATE NOT NULL,
  `IMPRESSIONS` VARCHAR(45) NULL,
  `CLICKS` VARCHAR(45) NULL,
  `EARNING` VARCHAR(45) NULL,
  PRIMARY KEY (`DATE`)
 );

Answer 1

最好创建XXX.sql文件，这里我创建的文件名如下:schema-business-mysql.sql。在此文件名中给出表创建脚本，如下所示:

创建表测试。raw_report ( 日期 日期不为空， 印象数 VARCHAR(45) NULL, CLICKS VARCHAR(45) NULL, 赚取 VARCHAR(45) NULL, 主键(日期) )；

并加载该文件，如下所示。完成!!!!!

<jdbc:initialize-database data-source="dataSource">
        <jdbc:script location="classpath:schema-business-mysql.sql" />
        <jdbc:script location="org/springframework/batch/core/schema-drop-mysql.sql" />
        <jdbc:script location="org/springframework/batch/core/schema-mysql.sql" />
    </jdbc:initialize-database>