Spring Boot does not use HSQLDB's file based database when configured to do so

Question

I have created a Spring Boot app and I wanted to use HSQLDB to store the data in the same directory as the executable JAR file. I created the "application.properties" file and a "schema.sql" in the resources directory. In the application config is the following;

spring.datasource.url=jdbc:hsqldb:file:data/mydb
spring.datasource.username=SA
spring.datasource.password=lEtmEIn
spring.datasource.driver-class-name=org.hsqldb.jdbc.JDBCDriver

When Spring Boot started it found the schema.sql and created the database. The problem is Spring Boot called the database "testdb" and it is apparently "memory only" mode and does not save to the location defined;

2015-09-04 08:48:00.985  INFO 30180 --- [           main] o.s.j.d.e.EmbeddedDatabaseFactory        : Creating embedded database 'testdb'
2015-09-04 08:48:01.415  INFO 30180 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : Executing SQL script from URL [file:/D:/GitHub/REDACTED/target/classes/schema.sql]
2015-09-04 08:48:01.423  INFO 30180 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : Executed SQL script from URL [file:/D:/GitHub/REDACTED/target/classes/schema.sql] in 8 ms.

How do I tell Spring Boot to quit using the memory based HSQLDB and honor my configuration?

Answer 1

After researching I have discovered that Spring JDBC actually hard codes the in-memory database despite your configuration settings for H2, Derby or HSQLDB.

I presume they saw this as beneficial for testing and learning the Spring Framework. A better solution would have simply checked if the developer had set these values first before blindly over writing them.

For my purposes where I am building a specific single purpose Web server with an embedded database in an IoT setting, Spring Boot + Spring JDBC requires additional development.

You can find the hard-coded configuration for HSQLDB here; GitHub Master Branch

--QUICK FIX [Deprecated!!! See Below For Better Fix!]

Copy the classes from the org/springframework/jdbc/datasource/embedded directory to your own. Change the files to reflect your config and add this to your config bean;

@Bean
public DataSource dataSource() {
    MyEmbeddedDatabaseBuilder builder = new MyEmbeddedDatabaseBuilder();
    return builder.setType(MyEmbeddedDatabaseType.HSQL).build();
}

The best route would do a more compliant instantiation of the Datasource object using the Spring plumbing.

They should not have made the Embedded DB classes hard coded though. It really makes it difficult to use these wonderful features right out of the box. With Web servers now being used for simple and specific tasks this need will become more apparent.

--BEST SOLUTION!

This means you have to use a different config structure for the datasource (as defined by the @ConfigurationProperties) but it works without making copies of code. Much more simple;

@SpringBootApplication
@EnableTransactionManagement
@EnableAutoConfiguration(exclude = { DataSourceAutoConfiguration.class,
        DataSourceTransactionManagerAutoConfiguration.class, HibernateJpaAutoConfiguration.class })
public class MyApplication {

    public static void main(String[] args) {
        SpringApplication app = new SpringApplication(MyApplication.class);
        app.setWebEnvironment(false);
        app.run(args);
    }

    @Bean
    @ConfigurationProperties("my.datasource")
    public DataSource dataSource() {
        return DataSourceBuilder.create().build();
    }

}

Answer 2

1. Disable datasource auto configuration:

   @SpringBootApplication
   @EnableAutoConfiguration(exclude = {DataSourceAutoConfiguration.class, DataSourceTransactionManagerAutoConfiguration.class, HibernateJpaAutoConfiguration.class})
   public class Application{  
       public static void main(String[] args) {
           SpringApplication.run(Application.class, args);
       }
   } 
   

2. Write you own datasource configuration bean:

   @Bean(initMethod="init", destroyMethod="close")
   @Profile("test")
   public DataSource getHsqlDataSource(){
       AtomikosNonXADataSourceBean ds = new AtomikosNonXADataSourceBean(); 
       ds.setUniqueResourceName("hsqldb"); 
       ds.setDriverClassName("org.hsqldb.jdbcDriver"); 
       ds.setUrl("jdbc:hsqldb:file:db/testdb;readonly=true;"); 
       ds.setUserName("sa"); 
       ds.setPassword(""); 
       ds.setPoolSize(3)
   }