Spring Boot - Loading Data Using a SQL Script

This post demonstrates how to load data from an SQL script for a particular database platform. This post is ideal for the development stage when you need some seed data to fill in existing database tables.

Spring Boot Application

Let’s create a new Spring Boot Application using IntelliJ (optional) and Spring Initialzr (or http://start.spring.io/).

Step 1: Create a New Project

Choose Spring Initialzr. Then, click Next.

Step 2: Provide project information

Then, provide some information for Maven.

Step 3: Choose JPA and H2

Then, choose JPA and H2.

Step 4: Save the new project

Step 5: Create an entity class

The following class represents the Person table in our H2 database.

package com.turreta.springboot.loadfromsqlscript.domain;

import javax.persistence.*;

@Entity
public class Person {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private int id;

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

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

    public int getId() {
        return id;
    }

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

    public String getLastName() {
        return lastName;
    }

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

    public String getFirstName() {
        return firstName;
    }

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

package com.turreta.springboot.loadfromsqlscript.domain;

import javax.persistence.*;

@Entity

public class Person {

@Id

@GeneratedValue(strategy = GenerationType.AUTO)

private int id;

@Column(name = "last_name")

private String lastName;

@Column(name="first_name")

private String firstName;

public int getId() {

return id;

}

public void setId(int id) {

this.id = id;

}

public String getLastName() {

return lastName;

}

public void setLastName(String lastName) {

this.lastName = lastName;

}

public String getFirstName() {

return firstName;

}

public void setFirstName(String firstName) {

this.firstName = firstName;

}

Step 5: Modify application.properties

Update the application.properties file with the following value.

spring.datasource.platform=h2

1	spring.datasource.platform=h2

With this setting, Spring will look for an SQL script data-h2.sql in the src/main/java/resources/ directory. Then, it will execute all the SQL statements in the script.

If we had spring.datasource.platform=hsqldb, Spring will try to load an SQL script named data-hsqldb.sql

For more information, please visit https://docs.spring.io/spring-boot/docs/current/reference/html/howto-database-initialization.html

Step 6: Unit Test

Lastly, we test our codes.

package com.turreta.springboot.loadfromsqlscript;

import com.turreta.springboot.loadfromsqlscript.domain.Person;
import org.junit.Assert;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import java.util.List;

@RunWith(SpringRunner.class)
@SpringBootTest
public class ComTurretaSpringbootLoadfromsqlscriptApplicationTests {

	@PersistenceContext
	private EntityManager em;

	@Test
	public void testSelectFromPerson() {

		List<Person> personList = em.createQuery("select c from Person c", Person.class).getResultList();
		Assert.assertTrue(personList.size() == 2);

		Person p = personList.get(0);

		Assert.assertTrue(p.getFirstName().equals("James"));
		Assert.assertTrue(p.getLastName().equals("Dean"));

		p = personList.get(1);

		Assert.assertTrue(p.getFirstName().equals("Marlon"));
		Assert.assertTrue(p.getLastName().equals("Brando"));

	}

}

package com.turreta.springboot.loadfromsqlscript;

import com.turreta.springboot.loadfromsqlscript.domain.Person;

import org.junit.Assert;

import org.junit.Test;

import org.junit.runner.RunWith;

import org.springframework.boot.test.context.SpringBootTest;

import org.springframework.test.context.junit4.SpringRunner;

import javax.persistence.EntityManager;

import javax.persistence.PersistenceContext;

import java.util.List;

@RunWith(SpringRunner.class)

@SpringBootTest

public class ComTurretaSpringbootLoadfromsqlscriptApplicationTests {

@PersistenceContext

private EntityManager em;

@Test

public void testSelectFromPerson() {

List<Person> personList = em.createQuery("select c from Person c", Person.class).getResultList();

Assert.assertTrue(personList.size() == 2);

Person p = personList.get(0);

Assert.assertTrue(p.getFirstName().equals("James"));

Assert.assertTrue(p.getLastName().equals("Dean"));

p = personList.get(1);

Assert.assertTrue(p.getFirstName().equals("Marlon"));

Assert.assertTrue(p.getLastName().equals("Brando"));

}

Download the codes

https://github.com/Turreta/Spring-Boot-Loading-Data-Using-a-SQL-Script