Spring Boot - Loading Initial Data

Question

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:

@Component
public 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

//or (depending on SB version)

spring.sql.init.data-locations=classpath:accounts.sql, classpath:books.sql, file: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

// depending on spring version

spring.sql.init.schema-locations=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.

There are multiple ways how to achieve this. I prefer to use one of following options:

Option 1: Initializing with CommandLineRunner bean:

@Bean
public CommandLineRunner loadData(CustomerRepository repository) {
    return (args) -> {
        // save a couple of customers
        repository.save(new Customer("Jack", "Bauer"));
        repository.save(new Customer("Chloe", "O'Brian"));
        repository.save(new Customer("Kim", "Bauer"));
        repository.save(new Customer("David", "Palmer"));
        repository.save(new Customer("Michelle", "Dessler"));

        // fetch all customers
        log.info("Customers found with findAll():");
        log.info("-------------------------------");
        for (Customer customer : repository.findAll()) {
            log.info(customer.toString());
        }
        log.info("");

        // fetch an individual customer by ID
        Customer customer = repository.findOne(1L);
        log.info("Customer found with findOne(1L):");
        log.info("--------------------------------");
        log.info(customer.toString());
        log.info("");

        // fetch customers by last name
        log.info("Customer found with findByLastNameStartsWithIgnoreCase('Bauer'):");
        log.info("--------------------------------------------");
        for (Customer bauer : repository
                .findByLastNameStartsWithIgnoreCase("Bauer")) {
            log.info(bauer.toString());
        }
        log.info("");
    }
}

Option 2: Initializing with schema and data SQL scripts

Prerequisites:

application.properties

spring.jpa.hibernate.ddl-auto=none

Explanation:

Without ddl-auto SQL scripts will be ignored by Hibernate and trigger default behavior - scanning project for @Entity and/or @Table annotated classes.

Then, in your MyApplication class paste this:

@Bean(name = "dataSource")
public DriverManagerDataSource dataSource() {
    DriverManagerDataSource dataSource = new DriverManagerDataSource();
    dataSource.setDriverClassName("org.h2.Driver");
    dataSource.setUrl("jdbc:h2:~/myDB;MV_STORE=false");
    dataSource.setUsername("sa");
    dataSource.setPassword("");

    // schema init
    Resource initSchema = new ClassPathResource("scripts/schema-h2.sql");
    Resource initData = new ClassPathResource("scripts/data-h2.sql");
    DatabasePopulator databasePopulator = new ResourceDatabasePopulator(initSchema, initData);
    DatabasePopulatorUtils.execute(databasePopulator, dataSource);

    return dataSource;
}

Where scripts folder is located under resources folder (IntelliJ Idea)

Hope it helps someone

Update 04-2021: Both options are great to combine with Spring Profiles as this will help you to avoid creating additional config files making your life as the developer easy.