Hello everyone,

Greetings today!

Today, we'll add spring metadata tables to the Oracle database, which are required for running spring batch applications.

Below are tables that we will be creating 

  • BATCH_JOB_INSTANCE
  • BATCH_JOB_EXECUTION_PARAMS
  • BATCH_JOB_EXECUTION
  • BATCH_STEP_EXECUTION
  • BATCH_JOB_EXECUTION_CONTEXT
  • BATCH_STEP_EXECUTION_CONTEXT

So please find the below scripts.

CREATE TABLE BATCH_JOB_INSTANCE  (
  JOB_INSTANCE_ID NUMBER  PRIMARY KEY ,
  VERSION NUMBER,
  JOB_NAME VARCHAR(100) NOT NULL ,
  JOB_KEY VARCHAR(2500)
);

CREATE TABLE BATCH_JOB_EXECUTION  (
  JOB_EXECUTION_ID NUMBER  PRIMARY KEY ,
  VERSION NUMBER,
  JOB_INSTANCE_ID NUMBER NOT NULL,
  CREATE_TIME TIMESTAMP NOT NULL,
  START_TIME TIMESTAMP DEFAULT NULL,
  END_TIME TIMESTAMP DEFAULT NULL,
  STATUS VARCHAR(10),
  EXIT_CODE VARCHAR(20),
  EXIT_MESSAGE VARCHAR(2500),
  LAST_UPDATED TIMESTAMP,
  JOB_CONFIGURATION_LOCATION VARCHAR(2500) NULL,
  constraint JOB_INSTANCE_EXECUTION_FK foreign key (JOB_INSTANCE_ID)
  references BATCH_JOB_INSTANCE(JOB_INSTANCE_ID)
) ;

CREATE TABLE BATCH_JOB_EXECUTION_PARAMS  (
JOB_EXECUTION_ID NUMBER NOT NULL ,
TYPE_CD VARCHAR(6) NOT NULL ,
KEY_NAME VARCHAR(100) NOT NULL ,
STRING_VAL VARCHAR(250) ,
DATE_VAL TIMESTAMP DEFAULT NULL ,
LONG_VAL NUMBER ,
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)
);

CREATE TABLE BATCH_STEP_EXECUTION  (
  STEP_EXECUTION_ID NUMBER  PRIMARY KEY ,
  VERSION NUMBER NOT NULL,
  STEP_NAME VARCHAR(100) NOT NULL,
  JOB_EXECUTION_ID NUMBER NOT NULL,
  START_TIME TIMESTAMP NOT NULL ,
  END_TIME TIMESTAMP DEFAULT NULL,
  STATUS VARCHAR(10),
  COMMIT_COUNT NUMBER ,
  READ_COUNT NUMBER ,
  FILTER_COUNT NUMBER ,
  WRITE_COUNT NUMBER ,
  READ_SKIP_COUNT NUMBER ,
  WRITE_SKIP_COUNT NUMBER ,
  PROCESS_SKIP_COUNT NUMBER ,
  ROLLBACK_COUNT NUMBER ,
  EXIT_CODE VARCHAR(20) ,
  EXIT_MESSAGE VARCHAR(2500) ,
  LAST_UPDATED TIMESTAMP,
  constraint JOB_EXECUTION_STEP_FK foreign key (JOB_EXECUTION_ID)
  references BATCH_JOB_EXECUTION(JOB_EXECUTION_ID)
) ;


CREATE TABLE BATCH_JOB_EXECUTION_CONTEXT  (
  JOB_EXECUTION_ID NUMBER PRIMARY KEY,
  SHORT_CONTEXT VARCHAR(2500) NOT NULL,
  SERIALIZED_CONTEXT CLOB,
  constraint JOB_EXEC_CTX_FK foreign key (JOB_EXECUTION_ID)
  references BATCH_JOB_EXECUTION(JOB_EXECUTION_ID)
) ;

CREATE TABLE BATCH_STEP_EXECUTION_CONTEXT  (
  STEP_EXECUTION_ID NUMBER PRIMARY KEY,
  SHORT_CONTEXT VARCHAR(2500) NOT NULL,
  SERIALIZED_CONTEXT CLOB,
  constraint STEP_EXEC_CTX_FK foreign key (STEP_EXECUTION_ID)
  references BATCH_STEP_EXECUTION(STEP_EXECUTION_ID)
) ;

Also, we need to create the below sequence in order to start the spring batch application

  • BATCH_STEP_EXECUTION_SEQ
  • BATCH_JOB_EXECUTION_SEQ
  • BATCH_JOB_SEQ
Please find below scripts to add this sequence

CREATE SEQUENCE BATCH_STEP_EXECUTION_SEQ;
CREATE SEQUENCE BATCH_JOB_EXECUTION_SEQ;
CREATE SEQUENCE BATCH_JOB_SEQ;

Thanks
Enjoy your learning!

Another post you can refer to is