Is it possible to compact an embedded HSQL DB from a Spring Boot application?

Question

I've created a Spring Boot application with an embedded, file-based HSQL database. The data file being created is getting fairly large, especially given the usage model, so I'm wondering if there is a way it can be compacted? Either manually or automatically?

The HSQL documentation indicates that there is a SHUTDOWN COMPACT command (which might take a while, according to the documentation), but I can't figure out how to configure Spring Boot to use it.

I'm open to forcing a SHUTDOWN COMPACT when shutting down the Spring Boot application (if that's the only option), or finding a way to issue a manual "compact" command (if HSQLDB supports such a command), or any other suggestions folks might have.

Answer 1

As mentioned, previous answer is one option, but the suggestion by boly38 is cleaner, and became my preferred approach.

@PreDestroy
public void preDestroy() {
    try {
        jdbcTemplate.execute("SHUTDOWN COMPACT");
    } catch (DataAccessException e) {
        // do nothing
    }
}

This allows the application to control when the DB is compressed, and removes a potentially bad option from the end-user's hand.

Answer 2

Not sure if this is the best solution, but I did manage to find a solution. I added a REST API that can manually execute a CHECKPOINT DEFRAG command on the database.

In the main Spring Boot application class, I added a method for getting a JdbcTemplate like this:

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

I then decided to create a new REST controller (rather than use an existing one), to provide an API to manually compact the DB:

@RestController
@RequestMapping("/admintools")
public class TEVAdminToolsController {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @GetMapping("/compressDB")
    public Boolean compressDB() {
        try {
            jdbcTemplate.execute("CHECKPOINT DEFRAG");
        } catch (DataAccessException e) {
            return false;
        }

        return true;
    }
}

This isn't great from a security perspective; for my use case that isn't a concern but for others it's probably a non-starter.

The two main points in this:

1. The @Bean for getting a JdbcTemplate
2. The call to jdbcTemplate.execute() for executing the "raw" SQL command