Save embedded HSQL database to file

Question

For logging and debugging purposes I want to dump an embedded/in-memory HSQL database to a file. Schema + Data. I'm using the spring-framework with hibernate.

I've tried both:

    Session session = sessionFactory.openSession();
    Transaction transaction = session.beginTransaction();

    session.createSQLQuery("BACKUP DATABASE TO '/tmp/backup.tar.gz' BLOCKING");

    transaction.commit();
    session.close();

and

    Session session = sessionFactory.openSession();
    Transaction transaction = session.beginTransaction();

    session.createSQLQuery("SCRIPT '/tmp/backup-data.sql'");

    transaction.commit();
    session.close();

Both to no avail.

There is no special configuration.

Hibernate config:

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

<bean id="sessionFactory"
      class="org.springframework.orm.hibernate4.LocalSessionFactoryBean"
      p:dataSource-ref="dataSource">
    <property name="packagesToScan" value="com.domain.*.model"/>
    <property name="hibernateProperties">
        <value>
            hibernate.format_sql=true
            hibernate.dialect=org.hibernate.dialect.HSQLDialect
        </value>
    </property>
</bean>

What did I miss, do I need a different approach, is it even possible?

Answer 1

Your code is only creating an instance of a SQLQuery and throws it away. You aren't executing the query.

Add/change to the following

SQLQuery query = session.createSQLQuery("BACKUP DATABASE TO '/tmp/backup.tar.gz' BLOCKING");
query.executeUpdate();

This will execute the query.