java - Where can I see the HSQL database and tables

Question

I have downloaded a hsqldb.jar and I set to project buildpath,then next I wrote the program

  Class.forName("org.hsqldb.jdbc.JDBCDriver");
Connection conn = DriverManager.getConnection(
                     "jdbc:hsqldb:mem:mydb", "SA", "");

String bookTableSQL = "create table MY_TABLE ("+
" TITLE varchar(256) not null primary key,"+
" AUTHOR varchar(256) not null"+
");";

Statement st = conn.createStatement();
st.execute(bookTableSQL);
System.out.println(st);
String sql = "INSERT INTO MY_TABLE " +
"VALUES ('Mahnaz', 'Fatma')";

st.executeUpdate(sql);

Database and table created successfully. In the next step, I inserted a sample data and get the data is displaying

String sqlsel = "SELECT TITLE,AUTHOR FROM MY_TABLE";
 ResultSet rs = st.executeQuery(sqlsel);
 //STEP 5: Extract data from result set
 while(rs.next()){
    //Retrieve by column name
     String id  = rs.getString("TITLE");
     String age = rs.getString("AUTHOR");

    //Display values
    System.out.print("ID: " + id);
    System.out.print(", Age: " + age);

 }

My problem is I did not created "mydb" database. Also where can I see the created database and table?

Answer 1

You've created database in memory, so there is no persistent file with your tables / data and then you close your application, all your data lost.

If you want to make it persistent fix your connection creation: replace mem to file. Something like this:

Connection conn = DriverManager.getConnection("jdbc:hsqldb:file:mydb", "sa", "");

You can read about different HSQLDB modes here. Also you can specify path, where your database will store file:

jdbc:hsqldb:file:/file/path/to/test"

After first run HSQLDB will create several files. Purpose of each file you can read here.

test.properties Contains the entry 'modified'. If the entry 'modified' is set to 'yes' then the database is either running or was not closed correctly (because the close algorithm sets 'modified' to 'no' at the end).

test.script This file contains the SQL statements that makes up the database up to the last checkpoint - it is in synch with test.backup.

test.data This file contains the (binary) data records for CACHED tables only.

test.backup This is compressed file that contains the complete backup of the old test.data file at the time of last checkpoint.

test.log This file contains the extra SQL statements that have modified the database since the last checkpoint (something like the 'Redo-log' or 'Transaction-log', but just text).

---

BTW, you can get all your tables using this query:

SELECT * FROM INFORMATION_SCHEMA.SYSTEM_TABLES where TABLE_TYPE='TABLE'

You can execute this query as normal query:

Statement st = conn.createStatement();
ResultSet rs = st.executeQuery("SELECT * FROM INFORMATION_SCHEMA.SYSTEM_TABLES where TABLE_TYPE='TABLE'");

while(rs.next()) {
    ...
}

Also you can view your database using built-in manager. You can start in using command:

java -cp /path/to/hsqldb.jar org.hsqldb.util.DatabaseManager 

And then specify path your database:

jdbc:hsqldb:file:mydb

Or you can use popular tool like SQuirreL.