HSQL file based database from script

Question

I have a question. I was playing around with JSF/ Spring/ Hibernate and HSQL. I have a small test application for this purpose and for one feature I wanted to implement I needed a possiblity to persist some data. I didn't wanted to have a full fledged database, so I choose HSQL. Starting from this tutorial (http://devcrumb.com/hibernate/hibernate-jpa-spring-and-hsqldb) I build my application. I had some problems with datatypes and I have chosen to build my database from a script.

Like in the tuorial I had defined my datasource like this in my application.xml, except that I had changed the url property to use a file.

    <bean id="dataSource"
        class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName">
            <value>org.hsqldb.jdbcDriver</value>
        </property>
        <property name="url">
            <value>jdbc:hsqldb:hsql://localhost/testdb</value>
        </property>
        <property name="username">
            <value>sa</value>
        </property>
        <property name="password">
            <value></value>
        </property>
    </bean>

<bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
        <property name="dataSource" ref="dataSource" />
        <property name="persistenceUnitName" value="jpaData" />
        <property name="jpaVendorAdapter">
            <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter" />
        </property>
        <property name="jpaProperties">
            <props>
                <prop key="hibernate.dialect">org.hibernate.dialect.HSQLDialect</prop>
                <prop key="hibernate.show_sql">true</prop>
                <prop key="hibernate.format_sql">false</prop>
                <prop key="hibernate.hbm2ddl.auto">update</prop>
            </props>
        </property>
    </bean>

Now, I have changed the datasource to something like this:

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

Everything works fine so far. My problem is, that this database is deleted when the server is shut down. Is there a possibility to use both? A script as base for the structure of my database but the db itself is written to a file on my filesystem so that it is available after a server restart? I know there are propably a few solutions to achieve this via some code, but is there a possibility to configure it that way?

Thanks in advance

score 1 · Accepted Answer · edited May 23 '17 at 12:22

1

Actually for your case, you are using the hsqldb in-memory mode, the spring is house a inmemory database indeed. To reuse the file database you need to update the hsqldb mode to use the "In-Process Mode" like "jdbc:hsqldb:file:testdb"

And a sample question is Embedded HSQLDB persist data to a file, you can refer to this.

edited May 23 '17 at 12:22

Community

1
1

answered Aug 20 '15 at 09:41

Liping Huang

4,378
4
29
46

The jdbc:initialize-database tag was actually what I needed. With that I can create my db from script. The only strange thing is, I had to drop the tables in my script before they could be created and after the creation of the database I had to remove the jdbc:initialize-database tag from the application.context.xml or there would be error messages that my tables are already existant. Not the best solution from my point of view, but for this case it is ok. – Matthias Nicklisch Aug 20 '15 at 15:18

HSQL file based database from script

1 Answers1