Spring Boot - Loading Initial Data

You can create a data.sql file in your src/main/resources folder and it will be automatically executed on startup. In this file you can add some insert statements, eg.:

INSERT INTO users (username, firstname, lastname) VALUES  ('lala', 'lala', 'lala'),  ('lolo', 'lolo', 'lolo');

Similarly, you can create a schema.sql file (or schema-h2.sql) as well to create your schema:

CREATE TABLE task (  id          INTEGER PRIMARY KEY,  description VARCHAR(64) NOT NULL,  completed   BIT NOT NULL);

Though normally you shouldn't have to do this since Spring boot already configures Hibernate to create your schema based on your entities for an in memory database. If you really want to use schema.sql you'll have to disable this feature by adding this to your application.properties:

spring.jpa.hibernate.ddl-auto=none

More information can be found at the documentation about Database initialization.

If you're using Spring boot 2, database initialization only works for embedded databases (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

If you're using multiple database vendors, you can name your file data-h2.sql or data-mysql.sql depending on which database platform you want to use.

To make that work, you'll have to configure the spring.datasource.platform property though:

spring.datasource.platform=h2

If I just want to insert simple test data I often implement a ApplicationRunner. Implementations of this interface are run at application startup and can use e.g. a autowired repository to insert some test data.

I think such an implementation would be slightly more explicit than yours because the interface implies that your implementation contains something you would like to do directly after your application is ready.

Your implementation would look sth. like this:

@Componentpublic class DataLoader implements ApplicationRunner {    private UserRepository userRepository;    @Autowired    public DataLoader(UserRepository userRepository) {        this.userRepository = userRepository;    }    public void run(ApplicationArguments args) {        userRepository.save(new User("lala", "lala", "lala"));    }}

You can add a spring.datasource.data property to application.properties listing the sql files you want to run. Like this:

spring.datasource.data=classpath:accounts.sql, classpath:books.sql, classpath:reviews.sql

The sql insert statements in each of these files will then be run, allowing you to keep things tidy.

If you put the files in the classpath, for example in src/main/resources they will be applied. Or replace classpath: with file: and use an absolute path to the file

If you want to run DDL type SQL then use:

spring.datasource.schema=classpath:create_account_table.sql

Edit: these solutions are great to get you up and running quickly, however for a more production ready solution it would be worth looking at a framework such as flyway, or liquibase. These frameworks integrate well with spring, and provide a quick, consistent, version-controlled means of initialising schema, and standing-data.