java - 回滚 RuntimeException : org. springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback;错误的 SQL 语法 [ - Spring Batch 问题

Question

我正在开发 Spring Batch Example – CSV File To MySQL Database 示例。我开发了整个代码，但是当我运行代码时，我看到出现以下错误。 我原以为 spring-batch 代码应该通过 Java 代码创建所有必需的表，但看起来它并没有创建它。

我看到的错误:

20:52:50.049 [main] DEBUG o.s.j.s.SQLErrorCodeSQLExceptionTranslator - Translating SQLException with SQL state '42S02', error code '1146', message [Table 'test.raw_report' doesn't exist]; SQL was [

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

            ] for task [PreparedStatementCallback]
20:52:50.049 [main] DEBUG o.s.b.core.step.tasklet.TaskletStep - Applying contribution: [StepContribution: read=2, written=0, filtered=0, readSkips=0, writeSkips=0, processSkips=0, exitStatus=EXECUTING]
20:52:50.049 [main] DEBUG o.s.b.core.step.tasklet.TaskletStep - Rollback for RuntimeException: 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
20:52:50.064 [main] DEBUG o.s.t.support.TransactionTemplate - Initiating transaction rollback on application exception
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:237) ~[spring-jdbc-3.2.16.RELEASE.jar:3.2.16.RELEASE]
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) ~[spring-jdbc-3.2.16.RELEASE.jar:3.2.16.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:605) ~[spring-jdbc-3.2.16.RELEASE.jar:3.2.16.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:617) ~[spring-jdbc-3.2.16.RELEASE.jar:3.2.16.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:890) ~[spring-jdbc-3.2.16.RELEASE.jar:3.2.16.RELEASE]
    at org.springframework.jdbc.core.namedparam.NamedParameterBatchUpdateUtils.executeBatchUpdateWithNamedParameters(NamedParameterBatchUpdateUtils.java:40) ~[spring-jdbc-3.2.16.RELEASE.jar:3.2.16.RELEASE]
    at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.batchUpdate(NamedParameterJdbcTemplate.java:326) ~[spring-jdbc-3.2.16.RELEASE.jar:3.2.16.RELEASE]
    at org.springframework.batch.item.database.JdbcBatchItemWriter.write(JdbcBatchItemWriter.java:182) ~[spring-batch-infrastructure-2.2.0.RELEASE.jar:na]
    at org.springframework.batch.core.step.item.SimpleChunkProcessor.writeItems(SimpleChunkProcessor.java:175) ~[spring-batch-core-2.2.0.RELEASE.jar:na]
    at org.springframework.batch.core.step.item.SimpleChunkProcessor.doWrite(SimpleChunkProcessor.java:151) ~[spring-batch-core-2.2.0.RELEASE.jar:na]
    at org.springframework.batch.core.step.item.SimpleChunkProcessor.write(SimpleChunkProcessor.java:274) ~[spring-batch-core-2.2.0.RELEASE.jar:na]
    at org.springframework.batch.core.step.item.SimpleChunkProcessor.process(SimpleChunkProcessor.java:199) ~[spring-batch-core-2.2.0.RELEASE.jar:na]
    at org.springframework.batch.core.step.item.ChunkOrientedTasklet.execute(ChunkOrientedTasklet.java:75) ~[spring-batch-core-2.2.0.RELEASE.jar:na]
    at org.springframework.batch.core.step.tasklet.TaskletStep$ChunkTransactionCallback.doInTransaction(TaskletStep.java:395) ~[spring-batch-core-2.2.0.RELEASE.jar:na]
    at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:131) ~[spring-tx-3.2.16.RELEASE.jar:3.2.16.RELEASE]
    at org.springframework.batch.core.step.tasklet.TaskletStep$2.doInChunkContext(TaskletStep.java:267) [spring-batch-core-2.2.0.RELEASE.jar:na]
    at org.springframework.batch.core.scope.context.StepContextRepeatCallback.doInIteration(StepContextRepeatCallback.java:77) [spring-batch-core-2.2.0.RELEASE.jar:na]
    at org.springframework.batch.repeat.support.RepeatTemplate.getNextResult(RepeatTemplate.java:368) [spring-batch-infrastructure-2.2.0.RELEASE.jar:na]
    at org.springframework.batch.repeat.support.RepeatTemplate.executeInternal(RepeatTemplate.java:215) [spring-batch-infrastructure-2.2.0.RELEASE.jar:na]
    at org.springframework.batch.repeat.support.RepeatTemplate.iterate(RepeatTemplate.java:144) [spring-batch-infrastructure-2.2.0.RELEASE.jar:na]
    at org.springframework.batch.core.step.tasklet.TaskletStep.doExecute(TaskletStep.java:253) [spring-batch-core-2.2.0.RELEASE.jar:na]
    at org.springframework.batch.core.step.AbstractStep.execute(AbstractStep.java:195) [spring-batch-core-2.2.0.RELEASE.jar:na]
    at org.springframework.batch.core.job.SimpleStepHandler.handleStep(SimpleStepHandler.java:137) [spring-batch-core-2.2.0.RELEASE.jar:na]
    at org.springframework.batch.core.job.flow.JobFlowExecutor.executeStep(JobFlowExecutor.java:64) [spring-batch-core-2.2.0.RELEASE.jar:na]
    at org.springframework.batch.core.job.flow.support.state.StepState.handle(StepState.java:60) [spring-batch-core-2.2.0.RELEASE.jar:na]
    at org.springframework.batch.core.job.flow.support.SimpleFlow.resume(SimpleFlow.java:152) [spring-batch-core-2.2.0.RELEASE.jar:na]
    at org.springframework.batch.core.job.flow.support.SimpleFlow.start(SimpleFlow.java:131) [spring-batch-core-2.2.0.RELEASE.jar:na]
    at org.springframework.batch.core.job.flow.FlowJob.doExecute(FlowJob.java:135) [spring-batch-core-2.2.0.RELEASE.jar:na]
    at org.springframework.batch.core.job.AbstractJob.execute(AbstractJob.java:301) [spring-batch-core-2.2.0.RELEASE.jar:na]
    at org.springframework.batch.core.launch.support.SimpleJobLauncher$1.run(SimpleJobLauncher.java:134) [spring-batch-core-2.2.0.RELEASE.jar:na]
    at org.springframework.core.task.SyncTaskExecutor.execute(SyncTaskExecutor.java:49) [spring-core-3.2.16.RELEASE.jar:3.2.16.RELEASE]
    at org.springframework.batch.core.launch.support.SimpleJobLauncher.run(SimpleJobLauncher.java:127) [spring-batch-core-2.2.0.RELEASE.jar:na]
    at com.mkyong.main.MainApp.main(MainApp.java:21) [classes/:na] 

我开发的代码:上下文.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>

数据库.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>

工作报告.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"
    xsi:schemaLocation="
    http://www.springframework.org/schema/batch  http://www.springframework.org/schema/batch/spring-batch.xsd
    http://www.springframework.org/schema/beans  http://www.springframework.org/schema/beans/spring-beans.xsd">


    <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>

报表.java

public class Report implements Serializable{
    private static final long serialVersionUID = 1L;

    private String Date;
    private String Impressions;
    private String Clicks;
    private String Earning;
    // setters and getters
}

主程序.java

public class MainApp {
    public static void main(String[] args) {
        String[] springConfig  = {  "spring/batch/config/database.xml", 
                "spring/batch/config/context.xml","spring/batch/jobs/job-report.xml"};

        ApplicationContext context = new ClassPathXmlApplicationContext(springConfig);

        JobLauncher jobLauncher = (JobLauncher) context.getBean("jobLauncher");
        Job job = (Job) context.getBean("reportJob");

        try {
            JobExecution execution = jobLauncher.run(job, new JobParameters());
            System.out.println("Exit Status : "+execution.getStatus());
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
        System.out.println("DONE !!");
    }
}

pom.xml

<properties>

        <!-- Generic properties -->
        <java.version>1.8</java.version>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>

        <spring.version>3.2.16.RELEASE</spring.version>

        <spring.batch.version>2.2.0.RELEASE</spring.batch.version>
        <mysql.driver.version>5.1.25</mysql.driver.version>

        <!-- Logging -->
        <logback.version>1.0.13</logback.version>
        <slf4j.version>1.7.5</slf4j.version>
        <jcl.slf4j.version>1.7.12</jcl.slf4j.version>
    </properties>

    <dependencies>
        <!-- Spring Core -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-core</artifactId>
            <version>${spring.version}</version>
        </dependency>

        <!-- Spring Core -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>${spring.version}</version>
        </dependency>

        <!-- Spring Batch dependencies -->
        <dependency>
            <groupId>org.springframework.batch</groupId>
            <artifactId>spring-batch-core</artifactId>
            <version>${spring.batch.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.batch</groupId>
            <artifactId>spring-batch-infrastructure</artifactId>
            <version>${spring.batch.version}</version>
        </dependency>

        <!-- MySQL database driver -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>${mysql.driver.version}</version>
        </dependency>

        <!-- Logging with SLF4J & LogBack -->
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-api</artifactId>
            <version>${slf4j.version}</version>
            <scope>compile</scope>
        </dependency>
        <dependency>
            <groupId>ch.qos.logback</groupId>
            <artifactId>logback-classic</artifactId>
            <version>${logback.version}</version>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>jcl-over-slf4j</artifactId>
            <version>${jcl.slf4j.version}</version>
        </dependency>
    </dependencies>

数据库.属性:

mysq.db.driverClass=com.mysql.jdbc.Driver
mysql.jdbc.url=jdbc:mysql://localhost:3306/test
mysql.jdbc.username=root
mysql.jdbc.password=root

编辑-1:

我创建了下表

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

但是看到下面的错误。我不确定我们如何在存储到数据库之前格式化日期？由于 Mysql 需要 yyyy-MM-dd 格式和具有 dd-MM-yyyy 的 CSV。 我们如何解决这个问题？

Caused by: java.sql.BatchUpdateException: Data truncation: Incorrect date value: 'Date' for column 'DATE' at row 1
    at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:2054) ~[mysql-connector-java-5.1.25.jar:na]
    at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1467) ~[mysql-connector-java-5.1.25.jar:na]
    at org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:905) ~[spring-jdbc-3.2.2.RELEASE.jar:3.2.2.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:890) ~[spring-jdbc-3.2.2.RELEASE.jar:3.2.2.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:589) ~[spring-jdbc-3.2.2.RELEASE.jar:3.2.2.RELEASE]
    ... 30 common frames omitted
Caused by: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect date value: 'Date' for column 'DATE' at row 1
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4185) ~[mysql-connector-java-5.1.25.jar:na]
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4119) ~[mysql-connector-java-5.1.25.jar:na]
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2570) ~[mysql-connector-java-5.1.25.jar:na]
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2731) ~[mysql-connector-java-5.1.25.jar:na]
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2815) ~[mysql-connector-java-5.1.25.jar:na]
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155) ~[mysql-connector-java-5.1.25.jar:na]
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2458) ~[mysql-connector-java-5.1.25.jar:na]
    at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:2006) ~[mysql-connector-java-5.1.25.jar:na]
    ... 34 common frames omitted
14:11:35.287 [main] DEBUG o.s.jdbc.datasource.DataSourceUtils - Returning JDBC Connection to DataSource
14:11:35.287 [main] DEBUG o.s.b.s.t.ResourcelessTransactionManager - Initiating transaction rollback
14:11:35.287 [main] DEBUG o.s.b.s.t.ResourcelessTransactionManager - Rolling back resourceless transaction on [org.springframework.batch.support.transaction.ResourcelessTransactionManager$ResourcelessTransaction@7bbc8656]
14:11:35.287 [main] DEBUG o.s.b.repeat.support.RepeatTemplate - Handling exception: org.springframework.dao.DataIntegrityViolationException, caused by: org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [

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

            ]; Data truncation: Incorrect date value: 'Date' for column 'DATE' at row 1; nested exception is java.sql.BatchUpdateException: Data truncation: Incorrect date value: 'Date' for column 'DATE' at row 1
14:11:35.287 [main] DEBUG o.s.b.repeat.support.RepeatTemplate - Handling fatal exception explicitly (rethrowing first of 1): org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [

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

            ]; Data truncation: Incorrect date value: 'Date' for column 'DATE' at row 1; nested exception is java.sql.BatchUpdateException: Data truncation: Incorrect date value: 'Date' for column 'DATE' at row 1
14:11:35.303 [main] ERROR o.s.batch.core.step.AbstractStep - Encountered an error executing the step
org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [

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

            ]; Data truncation: Incorrect date value: 'Date' for column 'DATE' at row 1; nested exception is java.sql.BatchUpdateException: Data truncation: Incorrect date value: 'Date' for column 'DATE' at row 1

看起来类似的问题已修复:https://jira.spring.io/browse/BATCH-528

Answer 1

你很接近。您只需将日期创建为字符串，如下所示:此外，Michael Pralow 已正确建议，我们有责任手动创建该表。

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

结果正在保存到数据库中。