35

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.

Betlista
  • 10,327
  • 13
  • 69
  • 110
John B
  • 32,493
  • 6
  • 77
  • 98
  • 1
    `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 Feb 17 '12 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 Feb 17 '12 at 14:25

5 Answers5

75

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();
    }
}
Betlista
  • 10,327
  • 13
  • 69
  • 110
nicholas.hauschild
  • 42,483
  • 9
  • 127
  • 120
  • 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. – John B Feb 17 '12 at 14:13
  • 2
    +1. My [answer](http://stackoverflow.com/a/9329628/649852) works for Spring 2.X; now that we're on Spring 3.X, I think we'll move to this approach. – Go Dan Feb 17 '12 at 14:30
  • 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. – nicholas.hauschild Feb 17 '12 at 14:38
  • 3
    I think it is also nice to mention the required dependency on hsqldb – iwein Jun 18 '12 at 08:58
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>
Betlista
  • 10,327
  • 13
  • 69
  • 110
omnomnom
  • 8,911
  • 4
  • 41
  • 50
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…

Donal Fellows
  • 133,037
  • 18
  • 149
  • 215
  • 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? – John B Feb 17 '12 at 14:00
  • @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…) – Donal Fellows Feb 17 '12 at 14:19
  • 1
    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. – Tim Perry Mar 13 '12 at 15:34
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>
Betlista
  • 10,327
  • 13
  • 69
  • 110
Go Dan
  • 15,194
  • 6
  • 41
  • 65
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:

  1. Instead of using an embedded-database, you can use the datasource suggested in this answer.

  2. 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).

Community
  • 1
  • 1