How to initialize in-memory HSQLDB using script via Spring

Question

I am attempting to do unit testing of my DAO (using Spring and Hibernate). I am using HSQLDB per this tutorial. The tutorial states that the in-memory HSQLDB database can be initialized using a SQL script but I cannot find information on how to do so in Spring. Here is the pertinent Spring context config:

 <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
    <property name="driverClassName" value="org.hsqldb.jdbcDriver" />
    <property name="url" value="jdbc:hsqldb:mem:mydb" />
    <property name="username" value="sa" />
    <property name="password" value="" />
    <property name="initialSize" value="5" />
    <property name="maxActive" value="10" />
    <property name="poolPreparedStatements" value="true" />
    <property name="maxOpenPreparedStatements" value="10" />
</bean>

Any help would be appreciated. Thanks.

The difference between the in-memory and the file mode is that the in-memory database is empty, but the file mode is initialized with data. One strategy that I have employed in the past is to create a standalone database, allow Hibernate to create the tables and add data for me, save the data into a script, and then use the file-based URL to point to the script. The good thing about the script is that it is raw SQL so you are free to pre-populate the database with whatever data you want to test against. this from the post you linked, it clearly mentions the process. — baba.kabira
– baba.kabira, Commented Feb 17, 2012 at 14:17
I read the above but I guess I didn't put 2 & 2 together that then you would use the "file" version of the HSQLDB and it would do in-memory with the script as the startup. — John B
– John B, Commented Feb 17, 2012 at 14:25

Betlista · Accepted Answer · 2016-01-15 08:23:41Z

76

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

The best part is that it acts as a DataSource, so it can easily be dropped in to replace 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 in doing this with Java Config, take a look 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();
    }
}

edited Jan 15, 2016 at 8:23

Betlista

10.6k14 gold badges76 silver badges115 bronze badges

answered Feb 17, 2012 at 14:03

nicholas.hauschild

42.9k9 gold badges129 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

John B Over a year ago

This is looking like the answer. I was playing around with HSQLDB and embedded-database trying to get either working. So far the EB seems to be working well.

Go Dan Over a year ago

+1. My answer works for Spring 2.X; now that we're on Spring 3.X, I think we'll move to this approach.

nicholas.hauschild Over a year ago

I found this just cruising the Spring 3 documentation about 6 months ago. It is incredibly easy to use (not that your answer's method isn't) and I have been able to put it to the test a handful of times already.

iwein Over a year ago

I think it is also nice to mention the required dependency on hsqldb

Betlista · Accepted Answer · 2016-01-15 08:23:26Z

6

Nicholas answer is perfectly fine, but you can use jdbc namespace to initialize external 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>

edited Jan 15, 2016 at 8:23

Betlista

10.6k14 gold badges76 silver badges115 bronze badges

answered Feb 19, 2014 at 19:09

omnomnom

9,1694 gold badges43 silver badges52 bronze badges

Comments

Donal Fellows · Accepted Answer · 2012-02-17 13:50:09Z

3

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

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

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

answered Feb 17, 2012 at 13:50

Donal Fellows

139k19 gold badges161 silver badges222 bronze badges

3 Comments

John B Over a year ago

But does that maintain the DB in the file instead of in memory? Can I assume this is safe if I roll back all transations?

Donal Fellows Over a year ago

@John: It generally maintains it in memory by default (configurable on a per-table basis) though it will migrate it to disk so it will persist. Alas, the documentation isn't very clear about the fine details of what exactly needs to be there; I guess that you'll have to experiment a little and everything will become clear. (That's a bit of a lame thing to say on my part…)

Tim Perry Over a year ago

For other people's future reference: when you do this, it does run it principally in memory, it's extremely quick, but if you make changes it'll eventually flush them back to the disk, which is probably not what you want. To fix this, you need to set the hsqldb 'files_readonly' option. Doing this in the connection string isn't valid, but you can do it in the DB properties file: This first time you run the above, it'll create [path-to-file].properties if it doesn't already exist. Add a new line saying 'hsqldb.files_readonly=true' to the bottom of that, and you're done.

Betlista · Accepted Answer · 2016-01-15 08:23:10Z

2

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

Then use the following 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>

edited Jan 15, 2016 at 8:23

Betlista

10.6k14 gold badges76 silver badges115 bronze badges

answered Feb 17, 2012 at 14:03

Go Dan

15.6k6 gold badges45 silver badges67 bronze badges

1 Comment

udalmik Over a year ago

So we still need to implement some populating logic inside init()?

Community · Accepted Answer · 2017-05-23 12:17:18Z

0

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

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

edited May 23, 2017 at 12:17

CommunityBot

11 silver badge

answered May 5, 2014 at 23:02

user1026870

Collectives™ on Stack Overflow

How to initialize in-memory HSQLDB using script via Spring

5 Answers 5

4 Comments

Comments

3 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

4 Comments

Comments

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related