HSQLDB file mode is not working

Question

I am using HSQLDB with Hibernate 5 and Spring 4.

I have some beans definitions in my spring-context.xml:

<tx:annotation-driven transaction-manager="transactionManager" proxy-target-class="true"/>

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

    <beans:bean id="sessionFactory" class="org.springframework.orm.hibernate5.LocalSessionFactoryBean">
        <beans:property name="dataSource" ref="dataSource"/>
        <beans:property name="configLocation" value="hibernate/hibernate.cfg.xml"/>
        <beans:property name="annotatedClasses">
            <beans:list>
               ...lot of project beans path values..
            </beans:list>
        </beans:property>
    </beans:bean>

    <beans:bean id="transactionManager" class="org.springframework.orm.hibernate5.HibernateTransactionManager">
        <beans:property name="sessionFactory" ref="sessionFactory"/>
    </beans:bean>

And Hibernate.cfg.xml contains:

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
        "-//Hibernate/Hibernate Configuration DTD//EN"
        "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
    <session-factory>
        <property name="hibernate.enable_lazy_load_no_trans">true</property>
        <property name="hibernate.dialect">org.hibernate.dialect.HSQLDialect</property>
        <property name="hibernate.hbm2ddl.auto">update</property>
        <property name="hibernate.show_sql">true</property>
        <property name="connection.driver_class">org.hsqldb.jdbcDriver</property>
        <property name="connection.url">jdbc:hsqldb:file:D/database</property>
        <property name="connection.pool_size">10</property>
        <property name="connection.username">SA</property>
        <property name="connection.password"></property>
    </session-factory>
</hibernate-configuration>

All dao and services layers is working fine. Data is always saved and all is all right.

But in the next launch all data is deleting from database.

I am trying to save my database data in file and to use this data again, and I have tried many variants. The last of them is from question about HSQLDB url config

I have added the line to my hibernate.cfg.xml file:

 <property name="connection.url">jdbc:hsqldb:file:D/database</property>

It doesn't helped to me. There is no file creating in this path.

Also i am trying to use the advice from question and to use SCRIPT SQL command, but I do not fully understand how to properly use it and where i must to place it directly?

Question: How should I configure HSQLDB to save all data from my database to a file after closing app and to re-use this data after opening this application again? Can you write some small and valid example with the correct configuration of HSQLDB for this question?

Edit #1: Thank's jchampemont, i tried your variant and all your steps, but still nothing. The file is still doesn't exists :( Hibernate file is picked, all hibernate config is working fine.

Issue is fixed! Thank's to jchampemont and to fredt

I have forgot to delete this values in hibernate.cfg.xml...:

        <property name="connection.driver_class">org.hsqldb.jdbcDriver</property>
        <property name="connection.url">jdbc:hsqldb:file:D:/database.lck</property>
        <property name="connection.pool_size">10</property>
        <property name="connection.username">SA</property>
        <property name="connection.password"></property>

And i have created the bean dataSource like in jchampemont answer After deleting this 4 lines - everything start's to work fine, base were created in D:\database folder and everything is cool. Thank's for answers! Peace :)

Answer 1

Are you sure your Hibernate.cfg.xml is being picked up by Spring? Depending on where the file is, you may need to add a classpath: prefix to its location.

Instead of using the jdbc:embedded-database, you could also do the following to create a DataSource bean and specify the database URL:

<bean class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close" id="dataSource">
    <property name="driverClassName" value="org.hsqldb.jdbcDriver" />
    <property name="url" value="jdbc:hsqldb:file:D:/database" />
    <property name="username" value="sa" />
    <property name="password" value="" />
</bean>


<jdbc:initialize-database data-source="dataSource">
    <jdbc:script location="classpath*:database/database_schema.sql" />
 </jdbc:initialize-database>

Answer 2

I have tried your setup in my example project and HSQL file JDBC URL works fine.

I have an idea that the problem is in you JDBC URL link (D/database). According to that docs HSQL will try to create all the DB storage files with prefix-name database in the root of your D drive. Like:

D:/database.tmp

D:/database.lck

D:/database.log

D:/database.properties

D:/database.script

I suppose there may be some problems with permissions.

Try to change to something like D/database/test-db.