How to load initial data in Spring Boot with and without defining schema?

Let’s say that you want to populate your database with initial set of values as soon as your spring boot application starts.

You may particularly need it if you are using an in memory database like H2.

The process differs a bit when you define the schema yourself or let Spring Data define it. Spring Data uses hibernate by default and will create the schema itself if you have entity classes defined in your project.

Let’s consider the case where you define the schema yourself.

To do this , you need to create a file named schema.sql and place it under resources folder in your spring project.

Here is a sample schema I created for using with in memory database H2:

schema.sql:



DROP TABLE IF EXISTS DEV_TEAM;

CREATE TABLE DEV_TEAM (

ID INT AUTO_INCREMENT PRIMARY KEY,

NAME VARCHAR(100) ,

EXPERIENCE VARCHAR(100),

TECHNOLOGY VARCHAR(100)

);

Now if you need to load initial set of data , place them in a file named data.sql under the same resources folder.

Here is a sample data.sql file which I used:

data.sql



INSERT INTO DEV_TEAM VALUES

(1,'JOHN','2 YEARS','JAVA'),
(2,'Gaurav','5 YEARS','SQL'),
(3,'Tom','3 YEARS','ANGULAR');

Now in your application.properties file , you need to specify the database details:

spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect

In addition to the above you need to set the below property to load data:

spring.datasource.initialization-mode=always

This won’t still work since you created the schema.sql . You need to explicitly tell Spring Data not to use hibernate to generate the schema. You can do this by setting the below property in application.properties:

spring.jpa.hibernate.ddl-auto=none

This will allow schema definition to be loaded from schema.sql file as specified by you.

That’s it, the data gets loaded when you start your spring boot project.

Here is the data which got loaded for me :

To see the above console , you need to set two properties in application.properties:

spring.h2.console.enabled=true

spring.h2.console.path=/h2

Now let’s consider the other case where you don’t specify the schema.sql .

In this case the schema is generated by Spring Boot using the entity class defined by you.

Here is the entity class I used:

package com.fullstackdeveloper.springboot.data.query;

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

@Entity
@Table(name = "DEV_TEAM")
public class DevTeam {
	
	
	@Id
	@Column(name = "id")
	private Integer id;
	
	
	@Column(name="name")
	private String name;
	
	
	@Column(name="experience")
	private String experience;
	
	@Column(name="technology")
	private String technology;

	public Integer getId() {
		return id;
	}

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

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getExperience() {
		return experience;
	}

	public void setExperience(String experience) {
		this.experience = experience;
	}

	public String getTechnology() {
		return technology;
	}

	public void setTechnology(String technology) {
		this.technology = technology;
	}

	@Override
	public String toString() {
		return "DevTeam [id=" + id + ", name=" + name + ", experience=" + experience + ", technology=" + technology
				+ "]";
	}
	
	

}

Make sure there is no schema.sql file in your resources folder.

Also remove the below property :

spring.jpa.hibernate.ddl-auto=none

If you are not removing it update it to any of the values other than ‘none’.

Examples:

The value of ‘create’ will always generate the schema new.

The value of ‘update’ will only update the schema if there is any new change like addition of new column.

Other than that there are no changes required.

Here it the code :

https://github.com/vijaysrj/spring-boot

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s