Using HSQLDB as a portable database with Spring, Hibernate

Question

I am newbie in hsqldb. In the project I am using Spring 4, Hibernate 5 and HSQLDB.

I am having some specific task and I am trying to use HSQLDB as a portable database, which can be transferred to a flash drive or another computer.

I already have an sql-script with all tables and basic-needed data. I have four questions that are haunting me. (I'm sorry in advance if these questions are very stupid):

1. I need to make the script run at the first launch of the program, and in the other launсhes it must to check if database already exist and (if it already exists)only update data in it. (the program would be used in many computers and the database must be created after the first launch). How can I do this? Is it possible? Can you give some basic advice or example about how I can to do that?

2. I am trying to find some information about saving all database info in some file in the file-system. Can you give me please some valid examples about saving hsqldb data in file and about using this file after another launch.

3. Can I place this file in my project.jar file and to work with all data from it update it e.t.c. ?

4. What is the best practice to make my database portable(for specific tasks) and where should I keep it? In file, in my project jar.e.t.c.?

Thanks in advance for your answers!

Answer 1

For data storage, you use a file: database. The JDBC connection URL is in the form jdbc:hsqldb:file:<file path>. HSQLDB will save all the data to file.

After connecting to the database you execute the SQL statements in your script one by one. If the tables already exist, the CREATE TABLE statements throw an error. This shows you don't have to execute them.

Because it's up to you when to keep the existing data and when to update it based on the existing contents of the database, you execute some SQL statements to decide. There is no automatic way to do this.

You can put an HSQLDB database in a jar but it cannot be updated. Jars are read-only.

The databases are fully portable. You can place them in a subdirectory of the user's home directory with the ~ symbol. See http://hsqldb.org/doc/2.0/guide/dbproperties-chapt.html#dpc_variables_url and the rest of this page for details.