Execute SQL Script in Spring Boot.

Introduction

Spring boot comes with several built-in features that allow the execution of {file-name}.sql files. From the Spring doc:

Spring Boot can automatically create the schema (DDL scripts) of your DataSource and initialize it (DML scripts). It loads SQL from the standard root classpath locations: schema.sql and data.sql, respectively. In addition, Spring Boot processes the schema-${platform}.sql and data-${platform}.sql files (if present), where platform is the value of spring.datasource.platform.

The execution of these files is primarily for database initialization. I will show how to use the JDBC ResourceDatabasePopulator Class to execute SQL scripts.

Create a Spring Boot application

Creating the app from https://start.spring.io/

Now we will create the appropriate package structure.

Directory Structure.

We will connect our spring boot app to a MySQL image running on docker container so here is the docker-compose.yml:

version: '2'
services:
  db:
    image: 'mysql:5.7'
    restart: 'always'
    expose:
      - '3306'
    ports:
      - '3306:3306'
    environment:
      MYSQL_ROOT_PASSWORD: 'test'
      MYSQL_DATABASE: 'db'
      MYSQL_USER: 'user'
      MYSQL_PASSWORD: 'test'

Simply run:

docker-compose up

Overview:

In ExecuteSqlScriptRunner we will first collect all of the {file-name}.sql scripts located in dirName and store it in a Resource [] array and then use stream API to execute each script using the runScript method located in the ExecuteScriptService.

ExecuteSqlScriptRunner.LOGGER.info(" The application is about to run for {}", dirName);
List<Future<Boolean>> tasks;
try {
    PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
    Resource[] resources = resolver.getResources(ExecuteSqlScriptRunner.BASE_SCRIPT_FOLDER + dirName + "/*.sql");
    tasks = Arrays.stream(resources).map(e -> this.service.runScript(e)).collect(Collectors.toList());
} catch (IOException ex) {
    ExecuteSqlScriptRunner.LOGGER.warn("Could not open files from directory '{} '", dirName);
    return;
}

The ExecuteScriptService will use an instance of Datasource class which may be injected by the @Autowired annotation.

@Autowired
private DataSource dataSource;

This instance is configured by Spring Boot automatically at startup by using the properties defined in the application.properties file. It can also be used to configure datasource manually if a program has to deal with multiple data sources. The application.property file:

spring.datasource.url = jdbc:mysql://localhost:3306/db
spring.datasource.username = user
spring.datasource.password = test
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

spring.datasource.testWhileIdle = true
spring.datasource.validationQuery = SELECT 1

spring.jpa.show-sql = false
spring.jpa.hibernate.naming-strategy = org.hibernate.cfg.ImprovedNamingStrategy
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect

Below is the runScript method and an execute method located in ExecuteScriptService.

public void executeScript(Resource script) {
     ResourceDatabasePopulator databasePopulator = new ResourceDatabasePopulator(script);
     boolean success = true;
     String error = null;
     try {
         databasePopulator.execute(this.dataSource);
     } catch (ScriptException e) {
         success = false;
error = "Error executing script "+ ", script: "+script.getFilename()+". Error: "+e.getMessage();
LOGGER.error("Error executing script , script: {}. Error: {}",
      script.getFilename(), e.getMessage(), e);
         throw e;
     } finally {
     }
 }

 @Async
 public Future<Boolean> runScript(Resource script) {

     ResourceDatabasePopulator databasePopulator = new ResourceDatabasePopulator(script);
     try {

         LOGGER.info("Working with script: {}", script.getFilename());
         this.executeScript(script);

     } catch (ScriptException e) {
         return new AsyncResult<Boolean>(false);
     }
     LOGGER.info("Done with script {}...", script.getFilename());
     return new AsyncResult<Boolean>(true);
 }

Hope this helps!

#Java8 #MySQL #Database #Spring #SpringBoot #Maven