[ACCEPTED]-How to initialize in-memory HSQLDB using script via Spring-hsqldb

Accepted answer

Score: 75

If you are trying to work with in-memory 7 databases and Spring, there is a new jdbc namespace for Spring 3 that 6 makes working with embedded databases very 5 easy.

The best part is that it acts as a 4 DataSource, so it can easily be dropped in to replace 3 your existing dataSource bean.

<jdbc:embedded-database id="dataSource" type="HSQL">
    <jdbc:script location="classpath:schema.sql"/>
    <jdbc:script location="classpath:test-data.sql"/>
</jdbc:embedded-database>

If you are more interested 2 in doing this with Java Config, take a look 1 at the EmbeddedDatabaseBuilder (new in Spring 3.0).

@Configuration
public class DatabaseTestConfig {
    @Bean
    public DataSource dataSource() {
        return new EmbeddedDatabaseBuilder()
            .setType(EmbeddedDatabaseType.HSQL)
            .addScript("classpath:schema.sql")
            .addScript("classpath:test-data.sql")
            .build();
    }
}

Score: 6

Nicholas answer is perfectly fine, but you 2 can use jdbc namespace to initialize external 1 database as well:

<jee:jndi-lookup id="dataSource" jndi-name="java:comp/env/jdbc/DS"/>

<jdbc:initialize-database data-source="dataSource">
    <jdbc:script location="classpath:/META-INF/database/init.sql"/>
</jdbc:initialize-database>

Score: 3

In the tutorial you link to, one of the 5 ways of setting things up is this (after 4 obvious correction):

In-memory from a script: jdbc:hsqldb:file:path-to-file

I think that that would 3 appear to be relevant. I suggest replacing 2 path-to-file with something that looks like a fully-qualified 1 filename…

Score: 2

You could get around this by creating a 6 subclass of BasicDataSource with getters/setters for two 5 new properties, initExecuteSqlFile and destroyExecuteSqlFile, that can have a comma-seperated 4 list of SQL files to execute. The subclass 3 would have init() and destroy() methods that handle the 2 init/destroy SQL files.

Then use the following 1 bean definition:

<bean
    id="datasource"
    class="com.example.MyBasicDataSource"
    destroy-method="destroy"
    init-method="init"
>
    <property name="destroyExecuteSqlFile">
        <value>h2-destroy-01.sql</value>
    </property>
    <property name="initExecuteSqlFile">
        <value>h2-init-01.sql,h2-init-02.sql,h2-init-03.sql</value>
    </property>
    <!-- Other properties -->
</bean>

Score: 0

With embedded-database we would only be 8 able to connect to the database from the 7 same JVM. If we have two JVMs, for performance 6 or other constraints, we can:

Instead of 5 using an embedded-database, you can use 4 the datasource suggested in this answer.
Then initialize 3 like Poitrek De suggested (and suggested in previous answer too). You 2 may want to create tables only if they do 1 not exist (as suggested here).

Source: stackoverflow.com

[ACCEPTED]-How to initialize in-memory HSQLDB using script via Spring-hsqldb

Check if ResultSet is empty in Java

HSQLDB, LocalDateTime, JdbcTemplate

Junit with HSQL/H2 without Spring/Hibernate

How can I wipe data from my HSQLDB after every test?

Am I crazy? Switching an established product from HSQLDB to Apache Derby

Found: bit, expected: boolean" after Hibernate 4 upgrade

Spring data JPA with HSQLDB: user lacks privilege or object not found

How can I see table structure in HSQLDB?

Understanding the need for a DI framework

How to inject ServletContext for JUnit tests with Spring?