Spring Boot application with H2 file database

Question

I'm trying to have a H2 database setup on spring boot application startup. I have configured the database in application.properties:

spring.datasource.url = jdbc:h2:file:~/testdb
spring.datasource.username = sa
spring.datasource.password = sa
spring.datasource.driverClassName = org.h2.Driver

The Application.java file:

@Configuration
@ComponentScan
@EnableAutoConfiguration
public class Application {
    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);

        CreateH2Database createH2Database = new CreateH2Database();
        createH2Database.create();
    }
}

CreateH2Database.java:

public class CreateH2Database {

    private Logger log = Logger.getLogger(CreateH2Database.class);

    @Autowired
    protected JdbcTemplate jdbcTemplate;

    public void create() {
        log.info("Creating H2 Database");
        createUsers();
    }

    private void createUsers() {
        log.info("Creating users table");
        jdbcTemplate.execute("create table if not exists users (id serial, first_name varchar(255), last_name varchar(255))");
        String[] names = "John Woo;Jeff Dean;Josh Bloch;Josh Long".split(";");
        for (String fullname : names) {
            String[] name = fullname.split(" ");
            log.info("Inserting user record for " + name[0] + " " + name[1] + "\n");
            jdbcTemplate.update(
                "INSERT INTO users(first_name,last_name) values(?,?)",
                name[0], name[1]);
            }
        }
    }

Once the application is started it should create the Users table if it doesn't already exist, and insert the users into the table. If the table does already exist, I don't want it to be modified.

1. I get a NullPointerException on jdbcTemplate.execute. How can I get the jdbcTemplate injected? All the example I've seen require the datasource to be created manually, and then the JdbcTemplate is created. However, the datasource in this example seems to be created based on the application.properties values.
2. Is this the correct approach to setup the database (i.e. calling a CreateH2Database after starting the SpringApplication)? Will this approach work if I want to run the application as a WAR on another application server?

Answer 1

Since you are using Spring Boot, you should take advantage of it's database initialization features. There is no need to roll out your own implementation.

All you have to do is have the files schema.sql and data.sql on the root of the classpath (most likely under /resources). Spring Boot will auto-detect these and run the first one in order to create the database and the second one to populate it.

Check out this part of the Spring Boot documentation

If you need to perform the initialization conditionally (perhaps only when running integration tests), you can advantage of Spring profiles. What you would do in that case is have the properties file for the test profile contain

spring.datasource.initialize=true

while the properties file for the other profiles would contain

spring.datasource.initialize=false