Search code examples
javajdbchsqldb

How to backup and restore specific tables in an HSQLDB database with Java code

I want to backup specific tables in an HSQLDB database. We have 50+ tables in our database, but I want approximately 12 of those to be backed up and then restored, including data (not table structure only).

I want to achieve this using Java JDBC code. It would be great if I can specify some table names in the command, and I get the data as well as the table structure (DDL) in the file.

From the HSQLDB docs, I can see that it's possible to backup the entirety of the database in a .tar or .tar.gz file. But that's not our requirement.

Here are some commands I have tried:

  1. PERFORM EXPORT SCRIPT FOR DATABASE
  2. PERFORM EXPORT SCRIPT FOR TABLE <table name> DATA
  3. SCRIPT
Solution

  • There is also the PERFORM EXPORT SCRIPT FOR TABLE ... and PERFORM IMPORT SCRIPT DATA .. commands. The first command can export the contents of a single table as SQL statements. The second command can import the contents of a saved export.

    See the Guide: http://hsqldb.org/doc/2.0/guide/management-chapt.html#mtc_data_management_statements