Embedded HSQLDB persist data to a file

Question

I am creating a spring based web application that uses embedded hsqldb. My spring config is pretty simple:

<jdbc:embedded-database id="dataSource" type="HSQL" >
    <jdbc:script location="classpath:scripts/create-table-if-not-exists" />
</jdbc:embedded-database>

But with this config all data is stored in memory. Here is the data source url that is created

jdbc:hsqldb:mem:dataSource

I need to persist data to a file. So that I can use it again after server restart.

If you need to persist it, then why do you use the `mem` mode? — , Jul 18 '14 at 10:21
@a_horse_with_no_name I did not specify mem mode. I assume this is default configuration created by spring. — user1745356, Jul 18 '14 at 10:26
`jdbc:embedded-database` in Spring means a mem: database. Define a database youself with file: in the URL. — fredt, Jul 20 '14 at 13:28

score 9 · Accepted Answer · answered Jul 21 '14 at 10:50

9

This solution worked for me

<bean class="org.apache.commons.dbcp2.BasicDataSource" id="dataSource">
    <property name="driverClassName" value="org.hsqldb.jdbcDriver" />
    <property name="url" value="jdbc:hsqldb:file:#{systemProperties['user.home']}/db/data" />
    <property name="username" value="sa" />
    <property name="password" value="" />
</bean>

<jdbc:initialize-database data-source="dataSource">
    <jdbc:script location="classpath:scripts/create-table-if-not-exists" />
</jdbc:initialize-database>

answered Jul 21 '14 at 10:50

user1745356

4,462
7
42
70

1

In other words, the solution to persisting data from an embedded HSQLDB database is not to use an embedded database. – Paul Aug 20 '14 at 18:38
2

@Paul - I am working with a Spring MVC web application that simulates an in-memory persistence layer that is Map based. Obviously, a database is much more performant, so I want to use HSQLDB. I want the database to be embedded, i.e. start and stop with the application, but I want the data to be permanent, i.e. file-based. Is there a way to achieve this? – Web User Apr 24 '15 at 17:35
2

@WebUser, my earlier comment was in error. I should have said that "the solution to persisting data from an in-memory database is not to use an in-memory database." The solution presented in the accepted answer should work for you - the data is stored in a file and the database itself is embedded. – Paul Apr 24 '15 at 18:06
@Paul that certainly helps. Thanks! – Web User Apr 24 '15 at 18:13

Embedded HSQLDB persist data to a file

1 Answers1

Linked