Execute Spring ACL SQL Script In Memory

This post describes how to execute the Spring ACL script to create ACL tables in an in-memory HSQL database instance. The code is available at GitHub in the Execute-ACL-SQL-Scripts-In-Memory directory.

In-Memory DataSource

We are using a configuration classe implementing the DisposableBean interface to shut down the created HSQL embedded database nicely.

@Configuration
public class InMemoryDataSource implements DisposableBean {

    private EmbeddedDatabase ed;

    @Bean(name="hsqlInMemory")
    public EmbeddedDatabase hsqlInMemory() {

        if ( this.ed == null ) {

            EmbeddedDatabaseBuilder builder = new EmbeddedDatabaseBuilder();

            this.ed = builder.setType(EmbeddedDatabaseType.HSQL)
                .addScript("aclSchema.sql").build();

        }

        return this.ed;

    }

    @Override
    public void destroy() {

        if ( this.ed != null ) {
            this.ed.shutdown();
        }

    }

}

When creating the database, we also have the aclSchema.sql script executed. It must be located at the root of the resource directory:

The content of the script is taken from the Spring documentation appendix.

Checking Table Creation

We extract the list table of tables in the controller. Technically speaking, we should do this at the service/DAO level, but for the sake of this example, we'll keep it simple:

@Controller
public class MyController {

    @Autowired
    EmbeddedDatabase hsqlInMemory;

    @RequestMapping(value = "/")
    public ModelAndView home() {

        ModelAndView result = new ModelAndView("index");

        ArrayList<String> tables = new ArrayList<String>();

        JdbcTemplate tplate = new JdbcTemplate(hsqlInMemory);

        SqlRowSet retr = tplate.queryForRowSet(
            "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES");

        while (retr.next()) {
            tables.add(retr.getString(1));
        }

        result.addObject("tables", tables);

        return result;

    }

}

Running The Example

One can run it using the maven tomcat:run goal. Then, browse:

  http://localhost:8585/execute-acl-sql-scripts-in-memory/

We find the tables we have created:

More Spring related posts here.