Backup and restore of Hsqldb database in java code

Use the BACKUP DATABASE TO command.

Here is a link to the documentation:

HSQLDB System Management Documentation

I haven't tested this, but I imagine it's something along the lines of:

String backup = "BACKUP DATABASE TO " + "'" + filePath + "' BLOCKING";

PreparedStatement preparedStatement = connection.prepareStatement(backup);  

preparedStatement.execute();

You'll want to wrap it in a try-catch block of course.

As far as restoring the db goes, I think you have to perform that while the database is offline using the DbBackupMain application. So you would issue this command at the command line:

java -cp hsqldb.jar org.hsqldb.lib.tar.DbBackupMain --extract tardir/backup.tar dbdir

Each HyperSQL database is called a catalog. There are three types of catalog depending on how the data is stored.

Types of catalog data :

mem: stored entirely in RAM - without any persistence beyond the JVM process's life

file: stored in filesystem files

res: stored in a Java resource, such as a Jar and always read-only

To back up a running catalog, obtain a JDBC connection and issue a BACKUP DATABASE command in SQL. In its most simple form, the command format below will backup the database as a single .tar.gz file to the given directory.

BACKUP DATABASE TO <directory name> BLOCKING [ AS FILES ]

The directory name must end with a slash to distinguish it as a directory, and the whole string must be in single quotes like so: 'subdir/nesteddir/'.

To back up an offline catalog, the catalog must be in shut down state. You will run a Java command like

 java -cp hsqldb.jar org.hsqldb.lib.tar.DbBackupMain --save tardir/backup.tar dbdir/dbname

. In this example, the database is named dbname and is in the dbdir directory. The backup is saved to a file named backup.tar in the tardir directory.

where tardir/backup.tar is a file path to the *.tar or *.tar.gz file to be created in your file system, and dbdir/dbname is the file path to the catalog file base name.

You use DbBackup on your operating system command line to restore a catalog from a backup.

java -cp hsqldb.jar org.hsqldb.lib.tar.DbBackupMain --extract tardir/backup.tar dbdir

where tardir/backup.tar is a file path to the *.tar or *.tar.gz file to be read, and dbdir is the target directory to extract the catalog files into. Note that dbdir specifies a directory path, without the catalog file base name. The files will be created with the names stored in the tar file. For more details refer

So in java + SPring + JdbcTemplate

Backup (On-line):

@Autowired
public JdbcTemplate jdbcTemplate;

public void mainBackupAndRestore() throws IOException {
  ...
    jdbcTemplate.execute("BACKUP DATABASE TO '" + sourceFile.getAbsolutePath() + "' BLOCKING");
}

This will save .properties, .scripts and .lobs file to a tar in sourceFile.getAbsolutePath().

Restore:

DbBackupMain.main(new String[] { "--extract", baseDir.getAbsolutePath(),
                    System.getProperty("user.home") + "/restoreFolder" });

This will get files from baseDir.getAbsolutePath() and will put them in userHome/restoreFolder where you can check if all restore is OK. lobs contains lob/blob data, scripts contains executed queries.