【SpringBatch】spring batch-批量-Oracle环境下的初始化建表语句：java.sql.SQLSyntaxErrorException: ORA-02289

一、问题

最近在自己搭建spring batch的框架，一步一步趟坑中；

java.sql.SQLSyntaxErrorException: ORA-02289

二、解决

通过Debug发现，就是在这一句代码执行时报错的：

JobExecution execution = this.jobLauncher.run(job, nextParameters);

但是，我们找到这个run()方法，发现也没有特别的地方：只有一个接口：

//
// Source code recreated from a .class file by IntelliJ IDEA
// (powered by Fernflower decompiler)
//

package org.springframework.batch.core.launch;

import org.springframework.batch.core.Job;
import org.springframework.batch.core.JobExecution;
import org.springframework.batch.core.JobParameters;
import org.springframework.batch.core.JobParametersInvalidException;
import org.springframework.batch.core.repository.JobExecutionAlreadyRunningException;
import org.springframework.batch.core.repository.JobInstanceAlreadyCompleteException;
import org.springframework.batch.core.repository.JobRestartException;

public interface JobLauncher {
    JobExecution run(Job var1, JobParameters var2) throws JobExecutionAlreadyRunningException, JobRestartException, JobInstanceAlreadyCompleteException, JobParametersInvalidException;
}

但是它一直在报，没有序列号这个错误，很郁闷，明明还没有到执行sql那一步啊？后来恍然大悟，spring batch搭建框架时，有6张基础的配置表，这6张基础的配置是必须的，如果没有，就会报错；Oracle环境下6张配置表的基本建表语句如下：

--1、批量实例表
CREATE TABLE BATCH_JOB_INSTANCE  (
    JOB_INSTANCE_ID NUMBER(19,0)  NOT NULL PRIMARY KEY ,
    VERSION NUMBER(19,0) ,
    JOB_NAME VARCHAR2(100) NOT NULL,
    JOB_KEY VARCHAR2(32) NOT NULL,
    constraint JOB_INST_UN unique (JOB_NAME, JOB_KEY)
) ;


--2、批量执行表
CREATE TABLE BATCH_JOB_EXECUTION  (
    JOB_EXECUTION_ID NUMBER(19,0)  NOT NULL PRIMARY KEY ,
    VERSION NUMBER(19,0)  ,
    JOB_INSTANCE_ID NUMBER(19,0) NOT NULL,
    CREATE_TIME TIMESTAMP NOT NULL,
    START_TIME TIMESTAMP DEFAULT NULL ,
    END_TIME TIMESTAMP DEFAULT NULL ,
    STATUS VARCHAR2(10) ,
    EXIT_CODE VARCHAR2(2500) ,
    EXIT_MESSAGE VARCHAR2(2500) ,
    LAST_UPDATED TIMESTAMP,
    JOB_CONFIGURATION_LOCATION VARCHAR(2500) NULL,
    constraint JOB_INST_EXEC_FK foreign key (JOB_INSTANCE_ID)
    references BATCH_JOB_INSTANCE(JOB_INSTANCE_ID)
) ;


--3、批量执行参数表
CREATE TABLE BATCH_JOB_EXECUTION_PARAMS  (
    JOB_EXECUTION_ID NUMBER(19,0) NOT NULL ,
    TYPE_CD VARCHAR2(6) NOT NULL ,
    KEY_NAME VARCHAR2(100) NOT NULL ,
    STRING_VAL VARCHAR2(250) ,
    DATE_VAL TIMESTAMP DEFAULT NULL ,
    LONG_VAL NUMBER(19,0) ,
    DOUBLE_VAL NUMBER ,
    IDENTIFYING CHAR(1) NOT NULL ,
    constraint JOB_EXEC_PARAMS_FK foreign key (JOB_EXECUTION_ID)
    references BATCH_JOB_EXECUTION(JOB_EXECUTION_ID)
) ;

--4、批量步骤执行表
CREATE TABLE BATCH_STEP_EXECUTION  (
    STEP_EXECUTION_ID NUMBER(19,0)  NOT NULL PRIMARY KEY ,
    VERSION NUMBER(19,0) NOT NULL,
    STEP_NAME VARCHAR2(100) NOT NULL,
    JOB_EXECUTION_ID NUMBER(19,0) NOT NULL,
    START_TIME TIMESTAMP NOT NULL ,
    END_TIME TIMESTAMP DEFAULT NULL ,
    STATUS VARCHAR2(10) ,
    COMMIT_COUNT NUMBER(19,0) ,
    READ_COUNT NUMBER(19,0) ,
    FILTER_COUNT NUMBER(19,0) ,
    WRITE_COUNT NUMBER(19,0) ,
    READ_SKIP_COUNT NUMBER(19,0) ,
    WRITE_SKIP_COUNT NUMBER(19,0) ,
    PROCESS_SKIP_COUNT NUMBER(19,0) ,
    ROLLBACK_COUNT NUMBER(19,0) ,
    EXIT_CODE VARCHAR2(2500) ,
    EXIT_MESSAGE VARCHAR2(2500) ,
    LAST_UPDATED TIMESTAMP,
    constraint JOB_EXEC_STEP_FK foreign key (JOB_EXECUTION_ID)
    references BATCH_JOB_EXECUTION(JOB_EXECUTION_ID)
) ;

--5、批量步骤执行内容表
CREATE TABLE BATCH_STEP_EXECUTION_CONTEXT  (
    STEP_EXECUTION_ID NUMBER(19,0) NOT NULL PRIMARY KEY,
    SHORT_CONTEXT VARCHAR2(2500) NOT NULL,
    SERIALIZED_CONTEXT CLOB ,
    constraint STEP_EXEC_CTX_FK foreign key (STEP_EXECUTION_ID)
    references BATCH_STEP_EXECUTION(STEP_EXECUTION_ID)
) ;

--6、批量任务执行上下文；
CREATE TABLE BATCH_JOB_EXECUTION_CONTEXT  (
    JOB_EXECUTION_ID NUMBER(19,0) NOT NULL PRIMARY KEY,
    SHORT_CONTEXT VARCHAR2(2500) NOT NULL,
    SERIALIZED_CONTEXT CLOB ,
    constraint JOB_EXEC_CTX_FK foreign key (JOB_EXECUTION_ID)
    references BATCH_JOB_EXECUTION(JOB_EXECUTION_ID)
) ;


--1、批量任务序列号
create sequence BATCH_JOB_SEQ
minvalue 0
maxvalue 9999999999999999999
start with 0
increment by 1
cache 20;

--2、批量任务执行序列号
create sequence BATCH_JOB_EXECUTION_SEQ
minvalue 0
maxvalue 9999999999999999999
start with 0
increment by 1
cache 20;


--3、批量步骤执行序列号
create sequence BATCH_STEP_EXECUTION_SEQ
minvalue 0
maxvalue 9999999999999999999
start with 0
increment by 1
cache 20;


--查询语句
select a.*,rowId from    BATCH_JOB_INSTANCE a; 
select a.*,rowId from    BATCH_JOB_EXECUTION a; 
select a.*,rowId from    BATCH_JOB_EXECUTION_PARAMS a; 
select a.*,rowId from    BATCH_STEP_EXECUTION a; 
select a.*,rowId from    BATCH_STEP_EXECUTION_CONTEXT a; 
select a.*,rowId from    BATCH_JOB_EXECUTION_CONTEXT a; 

其他具体内容，请见【spring batch（二）】;

删除表的语句如下：

drop table   BATCH_JOB_INSTANCE cascade constraints;
drop table BATCH_JOB_EXECUTION cascade constraints;
drop table BATCH_JOB_EXECUTION_PARAMS;
drop table BATCH_STEP_EXECUTION cascade constraints;
drop table BATCH_STEP_EXECUTION_CONTEXT;
drop table BATCH_JOB_EXECUTION_CONTEXT;

注意；
这里使用了cascade constraints，级联删除了约束关系；因为这里存在父子数据关联；我们先删除这种关联关系，然后再删除表（但是子数据不会被删除）；