Home Spring Boot Spring Boot - Loading Initial Data

Spring Boot – Loading Initial Data

Spring Boot provides several ways to load initial data. In this guide you will see configuration support for initializing data and load schema.sql, data.sql and sql scripts with custom names.

1. Initialize data using JPA and Hibernate

Let’s assume that we have an User entity and we need to create a schema and initialize sample data for User entity in database.

@Entity
public class User {

	@Id
	private Long id;
	private String userName;
	private String password;
	private String email;
	private Date createdTime;
	private Date updatedTime;
	@Column("DOB")
	private Date dateofBirth;
	private UserType userType; // Enum Type
}

1.1. Creating Schema : To create Schemas for your entities in database you can set property spring.jpa.hibernate.ddl-auto value to create or create-drop in Spring Boot configuration file. If you set ddl-auto value create or create-drop Hibernate generates schema for your entity based on it’s mapping.

application.properties :

spring.jpa.hibernate.ddl-auto=create

1.2. Initailize Data : Hibernate looks for import.sql in class path for data initialization if ddl-auto value create or create-drop.

1.3. Drawbacks : The above approach has drawbacks, each line should be a single sql statement in import.sql file. Let’s have a look into following sample import.sql data, that syntax doesn’t work in this approach.

INSERT INTO `USER` VALUES 
     (1,'PeterM','ABC123abc*','[email protected]','2020-03-17 07:13:30',NULL,'STUDENT','2020-03-17'),
     (2,'Mike','password','[email protected]','2020-03-18 14:59:35',NULL,'EMPLOYEE','2020-03-18'),
     (3,'KingPeter','password','[email protected]','2020-03-19 12:19:15',NULL,'EMPLOYEE','2020-03-18');

To make import.sql work, sql statements should be like following sample data. Otherwise column names order and types will be mismatch and there should be one statement for one line.

INSERT INTO `USER` (ID, USER_NAME, PASSWORD, EMAIL, CREATED_TIME, UPDATED_TIME, USER_TYPE, DOB) VALUES (1,'PeterM','ABC123abc*','[email protected]','2020-03-17 07:13:30',NULL,'STUDENT','2020-03-17');
INSERT INTO `USER` (ID, USER_NAME, PASSWORD, EMAIL, CREATED_TIME, UPDATED_TIME, USER_TYPE, DOB) VALUES (2,'Mike','password','[email protected]','2020-03-18 14:59:35',NULL,'EMPLOYEE','2020-03-18');
INSERT INTO `USER` (ID, USER_NAME, PASSWORD, EMAIL, CREATED_TIME, UPDATED_TIME, USER_TYPE, DOB) VALUES (3,'KingPeter','password','[email protected]','2020-03-19 12:19:15',NULL,'EMPLOYEE','2020-03-18');

2. Initialize using schema.sql and data.sql

Both schema and data related sql statements can also be included in single sql file, but by naming convention it’s better approach to maintain all schema related scripts in scema.sql and all the data manipulation related statements in data.sql.

Example schema.sql :

CREATE TABLE `USER` (
	`ID` int(11) NOT NULL AUTO_INCREMENT,
	`USER_NAME` varchar(45) NOT NULL,
	`PASSWORD` varchar(45) NOT NULL,
	`EMAIL` varchar(100) DEFAULT NULL,
	`CREATED_TIME` datetime NOT NULL,
	`UPDATED_TIME` datetime DEFAULT NULL,
	`USER_TYPE` varchar(45) NOT NULL,
	`DOB` date NOT NULL,
	PRIMARY KEY (`ID`),
	UNIQUE KEY `USER_NAME_UNIQUE` (`USER_NAME`)
);

Example data.sql :

INSERT INTO `USER` VALUES 
     (1,'PeterM','ABC123abc*','[email protected]','2020-03-17 07:13:30',NULL,'STUDENT','2020-03-17'),
     (2,'Mike','password','[email protected]','2020-03-18 14:59:35',NULL,'EMPLOYEE','2020-03-18'),
     (3,'KingPeter','password','[email protected]','2020-03-19 12:19:15',NULL,'EMPLOYEE','2020-03-18');

2.1. Embedded Databases : For embedded databases like H2, HSQL, DERBY etc.. 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. 

2.2. For other Relational databases like MySql, PostgreSql etc.. you need to set spring.datasource.initialization-mode property in Spring Boot configuration file. The default value is embedded. Have to set always to load sql script files, automatically loads schema.sql and data.sql from classpath. You can switch this value to never to not initialize.

spring.datasource.initialization-mode=always

Note : If you are using JPA or Hibernate and sql scripts to initialize schema and data for entities in your database, make sure set spring.jpa.hibernate.ddl-auto value to none. if you are not using Jpa or Hibernate ignore this property.

3. Loading Scripts for multiple database vendors

It’s quite common having multiple databases to work with in different environments (example H2 in DEV and MySql in production). Spring Boot processes the schema-${platform}.sql and data-${platform}.sql files (if present process, ignores if not presents), where platform is the value of spring.datasource.platform. This allows you to switch to database-specific scripts if necessary.

For example if you have in class path schema-mysql.sql to intialize data in Mysql database and schema-h2.sql to initialize data in H2 database. Following is the configuration to work on Mysql database.

spring.datasource.initialization-mode=always
spring.datasource.platform=mysql

4. Scripts with custom names

If sql script file names different than schema.sql and data.sql , like for example, if you have the complete schema and data in schema-data-dump.sql file, we can make it work by providing script names using spring.datasource.schema or spring.datasource.data in configuration. if you have multiple script files for different databases use platform property like above example.

spring.datasource.initialization-mode=always
spring.datasource.schema=classpath:schema-data-dump.sql

5. Loading Data for Integration Tests

Spring framework provides @Sql annotation to annotate a test class or test method to configure SQL scripts() and statements() to be executed against a given database during integration tests.

@SpringBootTest
@Sql(scripts = "/basic_mapping.sql") // to create DB tables and init sample DB data
@TestMethodOrder(value = OrderAnnotation.class) // to run tests in order
public class Basic_Mapping_Test extends BaseTest {

	@Autowired
	private UserRepository userRepository;

	@Test
	@DisplayName("Create-User-Test ")
	@Order(1)
	void createUserTest() {

		User created = userRepository.save(getUser());

		assertTrue(created != null && created.getId() != null);
	}
}

By default sql scripts will be loaded before test execution starts, if you would like change it to after test execution for data cleanup or to do some other data initialization, have set executionPhase attribute value.

@Sql(scripts = "/basic_mapping.sql",
  executionPhase = ExecutionPhase.AFTER_TEST_METHOD)
public class Basic_Mapping_Test extends BaseTest {
   //.....
}

5.1. Inline Sql Statements

we can declare multiple sql statements inline for @Sql annotation.

@SpringBootTest
@Sql(statements = { 
		"TRUNCATE TABLE USER;",
		"INSERT INTO `USER` VALUES (1,'PeterM','ABC123abc*','[email protected]','2020-03-17 07:13:30',NULL,'STUDENT','2020-03-17');",
		"INSERT INTO `USER` VALUES (2,'Mike','password','[email protected]','2020-03-18 14:59:35',NULL,'EMPLOYEE','2020-03-18');",
		"INSERT INTO `USER` VALUES (3,'KingPeter','password','[email protected]','2020-03-19 12:19:15',NULL,'EMPLOYEE','2020-03-18');",
		}
)
@TestMethodOrder(value = OrderAnnotation.class) // to run tests in order
public class Basic_Mapping_Test extends BaseTest {

	@Autowired
	private UserRepository userRepository;

	@Test
	@DisplayName("Create-User-Test ")
	@Order(1)
	void userTest() {

		User created = userRepository.findById(Long.valueOf(3)).get();
		assertTrue(created.getUserName().equals("KingPeter"));
	}
}

5.2. @SqlConfig

@SqlConfig defines metadata that is used to determine how to parse and execute SQL scripts configured via the @Sql annotation. Let’s see an example.

	@Test
	@Sql(scripts = "/stored_procedures.sql", config = @SqlConfig(encoding = "utf-8", separator = "$"))
	@DisplayName("Stored Procedure Test ")
	void calStoredProcedureTest() {

		List<User> users = userRepository.searchUsersByName("Pet");

		System.err.println(users);
		users.forEach(u -> Assert.assertTrue(u.getUserName().contains("Pet")));

		int users_found = userRepository.countSearchUsersByName("pet");
		assertTrue(users_found == 2);
	}

For more @SqlConfig option contact @SqlConfig document.

5.3. @SqlGroup

@SqlGroup can be used to group @Sql annotations with different configurations. Also this annotation can be used to create custom composed annotations.

 @SqlGroup({ 
      @Sql("/basic_mapping.sql"),
      @Sql(scripts = "/stored_procedures.sql", 
           config = @SqlConfig(encoding = "utf-8", separator = "$")) 
})

6. Conclusion

In this guide we have covered configuration support for initializing data and load schema.sql, data.sql and sql scripts with custom names. Also loading scripts using @Sql annotation for integration tests.

In short,

  1. For embedded databases Spring Boot loads schema.sql and data.sql automatically from classpath.
  2. For Relational Databases, have to set spring.datasource.initialization-mode=always to load schema.sql and data.sql
  3. When working with multiple databases, use platform name as suffix to the script name like schema-mysql.sql, schema-h2.sql, data-mysql.sql, data-h2.sql.. so on etc and then use spring.datasource.platform=h2 to load scripts for H2 platform.
  4. If sql scripts have custom names, use spring.datasource.schema=classpath:custom1.sql,custom2.sql … etc.
  5. For integration tests, use @Sql, @SqlGroup annotations to load scripts to initialize data.

7. References

  1. Spring Document
  2. Spring Boot JUnit 5 test
  3. JUnit 5 Tutorial
  4. Spring Boot Data JDBC Example

Satish Varma
Satish Varmahttps://javabydeveloper.com
Satish is post graduated in master of computer applications and experienced software engineer with focus on Spring, JPA, REST, TDD and web development. Follow him on LinkedIn or Twitter or Facebook

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Stay in Touch

Categories