Populating stored procs into a HSQL DB

I recently encountered a problem trying to load stored procedures into a HSQL DB used for testing. The problem was caused by the script runner provided by spring which separates each statement to be executed in a script file by a semicolon. If a stored proc has statements inside it (which most do), then the proc isn't executed as a single statement. This is further compounded by each statement executed must be understandable by JDBC. For example the following stored proc causes issues:

CREATE PROCEDURE MY_PROC(IN  param1 VARCHAR(30), OUT out_param VARCHAR(100))
  READS SQL DATA
  BEGIN ATOMIC
    SELECT the_value INTO out_param FROM my_table WHERE field = param1;
  END
.;

This problem is solved by using the script runners provided by HSQL in the "org.hsqldb:sqltool" dependency as they parse can correctly parse the scripts containing stored procedures. Here is a Spring Boot test, using an in memory database but using HSQL's script runners:

@RunWith(SpringRunner.class)
@AutoConfigureJdbc
@Slf4j
public class MyDaoTest {

  @Autowired
  private MyDao dao;

  @Test
  public void myTest() throws Exception {
    String response = dao.invokeProc("1234");
    assertThat(response, notNullValue());
  }

  @Configuration
  @ComponentScan("net.devgrok.sample.dao")
  public static class Config {

    @Bean
    public EmbeddedDatabaseFactoryBean dataSource() {
      EmbeddedDatabaseFactoryBean factory = new EmbeddedDatabaseFactoryBean();
      factory.setDatabaseType(EmbeddedDatabaseType.HSQL);
      factory.setDatabasePopulator(databasePopulator("./src/db/sql/stored_proc.sql"));
      return factory;
    }

    @Bean
    public HsqlDbPopulator databasePopulator(String... scripts) {
      return new HsqlDbPopulator(scripts);
    }
  }

  public static class HsqlDbPopulator implements DatabasePopulator {
    private final String[] scriptFiles;

    public HsqlDbPopulator(String[] scripts) {
      this.scriptFiles = scripts;
    }

    @Override
    public void populate(Connection connection) throws SQLException, ScriptException {
      FileSystemResourceLoader resourceLoader = new FileSystemResourceLoader();
      for (String scriptFile : scriptFiles) {
        try {
          SqlFile file = new SqlFile(resourceLoader.getResource(scriptFile).getFile(), null, false);

          log.info("Running script {}", scriptFile);
          file.setConnection(connection);
          file.execute();
        } catch (IOException | SqlToolError e) {
          log.error("Error executing script {}", scriptFile, e);
          throw new UncategorizedScriptException("Error executing script " + scriptFile, e);
        }
      }
    }
  }
}

Note: this test uses an in memory database, if you want to use another variation, you'll need to create a custom EmbeddedDatabaseConfigurer instance to do this which is slightly painful due to Spring's insistence on making everything either private or final or both.