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.

Connect Metamask Wallet to Client Side Application

The Open/Closed Principle Applied in JavaScript

Tech: Building Dynamic Form in Angular 9 and Ivy — Part 1

How the TypeScript Exclude Type Works

How to fix — webdriver-manager Error: read ECONNRESET

Swift in React Native - The Ultimate Guide Part 2: UI Components

The Traditional vs Practical Approach of Learning Programming

Build SpaceX fan site using GraphQL with React and Apollo Client -5

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

Annotations in Java

Head First Java — Chapter 5

Annotations in JAVA

Abstract Class and Interface in Java