Spring Boot: Load initial data to database tables

To load initial data to database tales, just create ‘data.sql’ (or data-h2.sql if you only want it to be applied in case H2 is your database) file in src/main/resources folder.

If you're using Spring boot 2, database initialization only works for embedded databases like H2, HSQLDB. If you want to use it for other databases as well, you need to change the spring.datasource.initialization-mode property.

spring.datasource.initialization-mode=always

Find the below working application.

App.java

package com.sample.app;
 
import java.util.List;
 
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;
 
import com.sample.app.entity.Employee;
import com.sample.app.repository.EmployeeRepository;
 
@SpringBootApplication
public class App {
 
    public static void main(String[] args) {
        SpringApplication.run(App.class, args);
    }
 
    @Bean
    public CommandLineRunner demo(EmployeeRepository employeeRepository) {
        return (args) -> {
            List<Employee> emps = employeeRepository.findAll();
 
            for(Employee emp: emps) {
                System.out.println(emp);
            }
        };
    }
 
}
 

Employee.java

package com.sample.app.entity;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "employee")
public class Employee {

    @Id
    @GeneratedValue
    @Column(name = "id")
    private int id;

    @Column(name = "first_name")
    private String firstName;

    @Column(name = "last_name")
    private String lastName;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getFirstName() {
        return firstName;
    }

    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    @Override
    public String toString() {
        StringBuilder builder = new StringBuilder();
        builder.append("Employee [id=").append(id).append(", firstName=").append(firstName).append(", lastName=")
                .append(lastName).append("]");
        return builder.toString();
    }

}

EmployeeRepository.java

package com.sample.app.repository;

import org.springframework.data.jpa.repository.JpaRepository;

import com.sample.app.entity.Employee;

public interface EmployeeRepository extends JpaRepository<Employee, Integer>{

}

application.properties

logging.level.root=WARN
logging.level.org.hibernate=ERROR

## H2 specific properties
spring.h2.console.enabled=true
spring.h2.console.path=/h2

spring.datasource.url=jdbc:h2:file:~/db/myOrg.db;DB_CLOSE_ON_EXIT=FALSE;DB_CLOSE_DELAY=-1;

spring.datasource.username=krishna
spring.datasource.password=password123

spring.datasource.driverClassName=org.h2.Driver

## JPA specific properties
# Creates the schema, destroying previous data.
spring.jpa.hibernate.ddl-auto=create

spring.jpa.database-platform=org.hibernate.dialect.H2Dialect

spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true

## Database connection pooling properties
# Number of ms to wait before throwing an exception if no connection is available.
spring.datasource.max-wait=10000

# Maximum number of active connections that can be allocated from this pool at the same time.
spring.datasource.tomcat.max-active=10
spring.datasource.tomcat.max-idle=5
spring.datasource.tomcat.min-idle=3

spring.jpa.properties.hibernate.enable_lazy_load_no_trans=true

#This is not required for embedded databases like H2, but if you are using non-embedded databases, you should use this
#spring.datasource.initialization-mode=always

data.sql

INSERT INTO employee (id, first_name, last_name) VALUES
  (1, 'rama', 'krishna'),
  (2, 'Mridhu', 'Latha'),
  (3, 'Gowthami','Prasad');

pom.xml

<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>springbootMisc</groupId>
    <artifactId>springbootMisc</artifactId>
    <version>1</version>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.6.RELEASE</version>
    </parent>

    <packaging>jar</packaging>

    <dependencies>


        <!-- https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-starter-data-jpa -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>


        <!-- https://mvnrepository.com/artifact/com.h2database/h2 -->
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
        </dependency>
    </dependencies>

</project>

Total project structure looks like below.

Run App.java, you can see below messages in console.    

  .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: Spring Boot ::        (v2.1.6.RELEASE)

Hibernate: 
    
    drop table employee if exists
Hibernate: 
    
    drop sequence if exists hibernate_sequence
Hibernate: create sequence hibernate_sequence start with 1 increment by 1
Hibernate: 
    
    create table employee (
       id integer not null,
        first_name varchar(255),
        last_name varchar(255),
        primary key (id)
    )
Hibernate: 
    select
        employee0_.id as id1_0_,
        employee0_.first_name as first_na2_0_,
        employee0_.last_name as last_nam3_0_ 
    from
        employee employee0_
Employee [id=1, firstName=rama, lastName=krishna]
Employee [id=2, firstName=Mridhu, lastName=Latha]
Employee [id=3, firstName=Gowthami, lastName=Prasad]

You can download complete working application from this link.    

Previous                                                    Next                                                    Home