Execute schema.sql and data.sql on startup of Spring Boot

An SQL database can be initialized manually and can also be done through code. You can create the table and insert the data into tables using JPA.

Initialise the database using JPA

JPA has feature for DDL(Data definition language: DROP, RENAME, CREATE, ALTER, TRUNCATE) generation, and these can be setup to run on startup against database. This is controlled through two external properties:

• spring.jpa.generate-ddl (boolean)
By default, its value is false. Whether to initialize schema on startup and it is vendor independent.
• spring.jpa.hibernate.ddl-auto
It is a Hibernate feature that control the behavior in more fine grained way. It is DDL mode. This is actually a shortcut for the hibernate.hbm2ddl.auto property. Defaults to create-drop when using an embedded database and no schema manager was detected. Otherwise, defaults to none.

Initialize a database using Hibernate

You can set spring.jpa.hibernate.ddl-auto explicitly and standard hibernate properties values are enum: none,validate, update, create, create-drop. Spring boot choose a default value type based on the embedded database. It defaults to create-drop if no schema manager has been detected, and none is all other cases. Available embedded databases in spring boot are hsqldb, h2, and derby. An embedded database is detected by looking at Connection type.

You can create the schema from scratch by setting the ddl-auto properties to create or create-drop along with a file import.sql should be in the root of classpath is executed on startup. This is the Hibernate feature and has nothing to do with Spring framework. It is advisable to do for testing or demos, not in production environment.

Initialize a Database

Spring boot automatically create the schema(DDL scripts) of your DataSource and initialize it (DML scripts). It loads the SQL from the standard root classpath locations: schema.sql and data.sql respectively. You can create the schema and initialize it based on the platform. Platform value is of spring.datasource.platform. Now you can create files schema-${platform}.sql and data-${platform}.sql which is processed by Spring Boot. It allows you to choose the database specific scripts if necessary. You can choose the vendor name of database(platform) like hsqldb, h2, oracle, mysql, postgresql, and so on.

In the JPA based app, You should not use both spring.jpa.hibernate.ddl-auto and schema.sql. Make sure to disable spring.jpa.hibernate.ddl-auto if you use schema.sql.

Spring Boot automatically creates the schema of embedded DataSource. You can customize this behaviour using spring.datasource.initialization-mode property. You can always initialize the DataSource regardless of its type setting spring.datasource.initialization-mode=always. The property spring.datasource.initialization-mode supports three values:

• always
Always initialize the datasource.
• embedded
Only initialize an embedded datasource.
• never
Do not initialize the datasource.

By default Spring Boot enables the fail-fast feature of the Spring JDBC initializer. This means application fails to start when scripts causes exception. You can tune that behavior by setting spring.datasource.continue-on-error.

Initialize a Spring Batch Database

In Spring Batch, Spring Boot detect your database type and execute the scripts on startup and it happens by defaults for the embedded database. You can enable it for any database by setting spring.batch.initialize-schema=always. You can also switch off the initialization explicitly by setting spring.batch.initialize-schema=never.

Execute schema.sql and data.sql with Spring Boot

Lets initialize a database with spring boot. Create schema.sql and data.sql files under resources folder. Lets understand with spring boot maven based sample application.

Project dependencies

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.walking.techie</groupId>
    <artifactId>sample-data-schema-sql</artifactId>
    <version>0.0.1</version>
    <packaging>jar</packaging>

    <name>sample-data-schema-sql</name>
    <description>This is a sample spring boot project which demonstrate to load schema.sql and data.sql file
    </description>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.0.6.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>


</project>

application.properties

Here, spring.jpa.generate-ddl is false and spring.jpa.hibernate.ddl-auto is none so that It will not auto generate schema scripts. In this example User is an entity class so corresponding table will not create in Database. The property spring.datasource.initialization-mode control the initialisation of datasource with available DDL and DML scripts.

spring.jpa.generate-ddl=false
spring.jpa.hibernate.ddl-auto=none
spring.datasource.url=jdbc:mysql://localhost:3306/test?useSSL=true
spring.datasource.username=root
spring.datasource.password=santosh
spring.datasource.initialization-mode=always

schema.sql

The schema.sql contains the DDL scripts. We will create person table in Database.

DROP TABLE IF EXISTS `person`;
CREATE TABLE `person` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

data.sql

The data.sql contains the DML scripts. We will insert some data into person table.

INSERT INTO `person` (name) VALUES ('Santosh');
INSERT INTO `person` (name) VALUES ('Kumar');
INSERT INTO `person` (name) VALUES ('Walking');
INSERT INTO `person` (name) VALUES ('Techie');

Java model class

The User is an entity class.

package com.walking.techie.entity;

import lombok.Data;

import javax.persistence.*;

@Data
@Table(name = "user")
@Entity
public class User {
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Long id;

  private String name;
}

Run Application

package com.walking.techie;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class Application {

    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }
}

Once you run this spring boot application. You can see that table person is created in test Database.

You can also create user table through spring boot auto generate script if you set spring.jpa.generate-ddl to true or set spring.jpa.hibernate.ddl-auto to any validate, update, create, create-drop.

You can also run scripts based on platform. Lets suppose you want to run for mysql plateform then create files schema-mysql.sql and data-mysql.sql under resources folder and set property spring.datasource.platform=mysql in application.properties file.

Recommended Posts:

• Spring Batch listener example
• Spring Batch Example in Spring boot - CSV File to MySql Database
• Spring Batch Hello world example using Spring boot
• Spring Boot - Remove _class field from MongoDB document
• Spring Boot Feature
• CSV Files with different delimiter to Mongo Database
• Using multiple data sources of MongoDB with Spring Boot and Spring Data
• Spring Batch Example in Spring boot - CSV File to Mongo Database
• Method hiding in Java
• Interface in Java 8