Using HSQLDB

These notes are for dealing with the HSQL database used in a legacy web application.

HSQL database commands

Note: These commands are required to by run where the hsqldb jar file is stored

• Starting the server:

  
  java -cp /var/lib/hsqldb/lib/hsqldb.jar org.hsqldb.Server -database.0 file:mydb -dbname.0 phonebook

  
  
• Start the GUI application for the HSQL database:
  
  

  java -cp /var/lib/hsqldb/lib/hsqldb.jar org.hsqldb.util.DatabaseManagerSwing

SQLTOOL

An Example ~/sqltool.rc file:

urlid localhost-sa
url jdbc:hsqldb:hsql://localhost/
username SA
password

To use SQL Tool:

1. Setup a ~/sqltool.rc to store the connection settings for the database (and run the server).
2. Once you have started the database (using the commands in the previous section), run the SQLTool command line utility:
   
   

   java -cp /var/lib/hsqldb/lib/hsqldb.jar:sqltool.jar org.hsqldb.cmdline.SqlTool

   
   
3. Connect to the database as indication by the urlid in your sqltool.rc file:
   
   

   \j localhost-sa

   
   
4. You can stop sqltool at any using
   
   

   \xq (table OR SELECT QUERY)

DATABASE INFO

• Grab list of all tables in the database:
  
  

  SELECT TABLE_SCHEM,TABLE_NAME FROM INFORMATION_SCHEMA.SYSTEM_TABLES WHERE TABLE_TYPE = 'TABLE';

  
  
• Grabbing a single employee record:
  
  

  SELECT LIMIT 0 1 - FROM EMPLOYEES;

  
  
• Outputting user info as a CSV file:
  
  

  SELECT A.USERNAME, A.PASSWORD, A.FIRSTNAME, A.LASTNAME, A.MIDDLENAME as OTHERNAME, B.EMAIL INTO TEXT "users_file" FROM USERS AS A INNER JOIN USER_DETAILS AS B ON A.USERNAME = B.USERNAME;

  
  
• Removing the temporary users table used to create the CSV file:
  
  

  DROP TABLE "users";

  
  
• Date based search query where date is a string (DD-MM-YYYY):
  
  

  SELECT A.USERNAME, B.FIRSTNAME FROM USERS AS A INNER JOIN EMPLOYEES AS B ON A.USERNAME = B.USERNAME WHERE SUBSTRING(A.EXPIRY,7,4) > 2016;

  
  
• Searching for string partials:
  
  

  SELECT TIMES, DATES, USERS FROM LOGS WHERE USERS LIKE 'admin%' AND DATES LIKE '%/03/2015';

  
  
• Grabbing the exact number of matches that match parameters:
  
  

  code>SELECT BUILDING, INCTYPE, COUNT(*) AS NUM FROM REPORTS WHERE BUILDING LIKE 'PLACE %' AND DATE LIKE '%/2015' GROUP BY BUILDING, INCTYPE;