Create tables automatically by calling .sql file in Spring Batch?

Question

I followed many links like Spring Batch Framework - Auto create Batch Table and https://docs.spring.io/spring-boot/docs/2.0.0.M7/reference/htmlsingle/#howto-initialize-a-spring-batch-database, but that doesn't satisfy my requirement

In Spring Boot 2.0, how we can call sql file (created manually and have all the schema details) so that Spring Batch can create it manually for us and if schema is already present then skip the execution ?

<!-- Create meta-tables -->
<jdbc:initialize-database data-source="dataSource">
        <jdbc:script location="classpath:hsqldb/initial-query.sql" />
        <jdbc:script location="org/springframework/batch/core/schema-drop-hsqldb.sql" />
        <jdbc:script location="org/springframework/batch/core/schema-hsqldb.sql" />
 </jdbc:initialize-database>

I am looking to do 1. If Schema already present then don't create any tables 2. If schema is not present then create the tables

Project structure

DROP TABLE IF EXISTS report;

CREATE TABLE report  (
    id INT NOT NULL PRIMARY KEY,
    date DATETIME,
    impression BIGINT,
    clicks INT,
    earning DECIMAL(12,4)
  )ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

application.properties

# Data Source
spring.datasource.url=jdbc:mysql://localhost:3306/spring_batch
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.username=root
spring.datasource.password=root
spring.jpa.hibernate.ddl-auto=update


# SPRING BATCH (BatchProperties)
# Database schema initialization mode.
spring.batch.initialize-schema=always

# Execute all Spring Batch jobs in the context on startup.
#spring.batch.job.enabled=false

# Path to the SQL file to use to initialize the database schema.
spring.batch.schema=classpath:schema-all.sql

Carlos Nantes · Answer 1 · 2018-07-06T01:49:20.023

You can run sql files manually using org.springframework.jdbc.datasource.init.ScriptUtils class. It probably is not the whole solution, but it may help you get there.

import java.nio.charset.Charset;
import java.sql.SQLException;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationContext;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.EncodedResource;
import org.springframework.jdbc.datasource.init.ScriptUtils;
import org.springframework.stereotype.Component;

@Component
public class SqlUtil {
    @Autowired private ApplicationContext context;
    @Autowired private DataSource datasource;

    // SQLFileName could be your schema-all.sql
    // inject this class somewhere and call sqlUtil.runSqlFile("schema-all.sql");
    public void runSqlFile(String SQLFileName) {
        Resource resource = context.getResource(SQLFileName);
        EncodedResource encodedResource = new EncodedResource(resource, Charset.forName("UTF-8"));
        try {
            ScriptUtils.executeSqlScript(datasource.getConnection(), encodedResource);
        } catch (SQLException ex) {
            throw new RuntimeException(ex);
        }
    }
}

Also, mysql can check if table exists using IF NOT EXISTS as the doc shows. The following would create a table if it doesn't exist:

CREATE TABLE report IF NOT EXISTS (
    id INT NOT NULL PRIMARY KEY,
    date DATETIME,
    impression BIGINT,
    clicks INT,
    earning DECIMAL(12,4)
  )ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

To check if schema exists, maybe you could use the following as shown in this other topic:

SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'DBName'

score 3 · Accepted Answer · answered Jul 06 '18 at 17:14

What I've observed that is -

if I enabled below

spring.batch.initialize-schema=always

then all the tables creates fine

 +------------------------------+
    | Tables_in_test               |
    +------------------------------+
    | batch_job_execution          |
    | batch_job_execution_context  |
    | batch_job_execution_params   |
    | batch_job_execution_seq      |
    | batch_job_instance           |
    | batch_job_seq                |
    | batch_step_execution         |
    | batch_step_execution_context |
    | batch_step_execution_seq     |

If I put the below line then it only creates the report.xml

spring.batch.schema=classpath:schema-all.sql

Is there any way if we can create both ?

Create tables automatically by calling .sql file in Spring Batch?

2 Answers2

Linked