How to prevent HSQLDB from persisting data between applications runs when using Spring Boot?

Question

I am run a Spring Boot/Web application and I am using an embedded HSQLDB during prototyping. As part of this I do not want to persist data between runs. I would like the only data in the system to be that from those scripts.

How can I modify my configuration to achieve that?

@Configuration
public class RepoConfig {

    @Bean
    public DataSource dataSource() {
        EmbeddedDatabaseBuilder builder = new EmbeddedDatabaseBuilder();
        EmbeddedDatabase db = builder.setType(EmbeddedDatabaseType.HSQL)
                .addScript("classpath:table.sql")
                .addScript("classpath:data.sql")
                .build()
            ;
        return db;
    }

    @Bean
    public JdbcTemplate jdbcTemplate(DataSource dataSource) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        return jdbcTemplate;
    }

}

I tried adding a script to drop the tables before creating them, like

DROP TABLE mytablename ;

but I kept getting the error:

user lacks privilege or object not found: MYTABLENAME

---

PS:

• Spring Boot 2.5.0
• HSQLDB 2.6.0

Answer 1

You can do that with the following configuration:

spring.jpa.hibernate.ddl-auto = create-drop

You can find more detail on Initialize a Database Using Hibernate

Or also you can set this property spring.datasource.data

spring.datasource.data=drop.sql,data.sql

then those files should be in

src/main/resources/drop.sql
src/main/resources/data.sql