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

Just an ordinary dev trying to make other devs life easier

Love podcasts or audiobooks? Learn on the go with our new app.

Learning Go Lang Days 28 — Finding the next Fibonacci Number with Go Lang #100daysofcode

30 Day code challenge-python

Uncle Bob — The Dev behind Agile Manifesto & Clean code

From the batch of FW14 from Masai School , we are creating this blog about our experience on a…

Killing me softly: Graceful shutdowns in Clojure

Egodact roadmap 2019

“[listeo_home_slider]”: “value”: “%5B%5D”, “type”: “option”, “user_id”: 1…

Useful Calculators for Students!

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Mubasil Bokhari

Mubasil Bokhari

Just an ordinary dev trying to make other devs life easier

More from Medium

Spring Boot Initializr

Variables

Tutorial on Minecraft java edition

Redis, Kafka or RabbitMQ: Which MicroServices Message Broker To Choose?