HSQLDB - Getting Started

This post is on learning HSQLDB. HSQLDB is Rdbms db written in java

1. Download HSQLDB from link 
2. Getting started guide useful from link 
3. Java is already installed on my laptop. Adding JAVA_HOME , PATH and CLASSPATH provided in link

Lets get started and try out some basic examples

Step 1 - To Start and create a DB

  

From command line

 

 Command text -  C:\Program Files\Java\jre7\bin>java.exe -cp "E:\HSQLDB\hsqldb-2.3.0\hsqldb-2.3.0\hsqldb\lib\hsqldb.jar" org.hsqldb.server.Server --database.0 file:mydb --dbname.0 xdb

Step 2 - DB files would be created as below

 

 

Step 3 - Opening the DB Manager

 

 

Command text - C:\Program Files\Java\jre7\bin>java.exe -cp "E:\HSQLDB\hsqldb-2.3.0\hsqldb-2.3.0\hsqldb\lib\hsqldb.jar" org.hsqldb.util.DatabaseManagerSwing

 

Command text - Connecting to DB Instance

jdbc:hsqldb:hsql://localhost/xdb

Step 4 - Basics on Table Creation

The three types of persistent tables are MEMORY tables, CACHED tables and TEXT tables

• Memory Tables - Data stored in Files
• Cached Tables - Cached detail remains in memory not in File
• Text Files - Use CSV Supported Files

Step 5 - Table Creation

 

CREATE TABLE PUBLIC.TEST_TABLE

 (COL1 INTEGER NOT NULL,

 COL2 VARCHAR(25) NOT NULL,

 PRIMARY KEY (COL1))

 

Step 6 - Load Data and Select Query

 

INSERT INTO "PUBLIC"."TEST_TABLE"( "COL1", "COL2" ) VALUES ( 10, 'Test')

INSERT INTO "PUBLIC"."TEST_TABLE"( "COL1", "COL2" ) VALUES ( 20, 'Ram')

INSERT INTO "PUBLIC"."TEST_TABLE"( "COL1", "COL2" ) VALUES ( 30, 'Raj')

INSERT INTO "PUBLIC"."TEST_TABLE"( "COL1", "COL2" ) VALUES ( 40, 'Ravi')

INSERT INTO "PUBLIC"."TEST_TABLE"( "COL1", "COL2" ) VALUES ( 50, 'Raja')

 

SELECT * FROM "PUBLIC"."TEST_TABLE"

 

Step 7 - MVCC Basics

 

Reading only committed data, Driven by isolation level settings. More details in link

Supported Isolation levels

• SET TRANSACTION READ ONLY
• SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
• SET TRANSACTION READ WRITE, ISOLATION LEVEL READ COMMITTED 

Step 8 - MVCC Example

Start Two Instances of Data Manager

Window1 - Run below query

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SET DATABASE TRANSACTION CONTROL MVCC;

set autocommit false;

INSERT INTO "PUBLIC"."TEST_TABLE"( "COL1", "COL2" ) VALUES ( 10, 'Test')

INSERT INTO "PUBLIC"."TEST_TABLE"( "COL1", "COL2" ) VALUES ( 20, 'Ram')

INSERT INTO "PUBLIC"."TEST_TABLE"( "COL1", "COL2" ) VALUES ( 30, 'Raj')

INSERT INTO "PUBLIC"."TEST_TABLE"( "COL1", "COL2" ) VALUES ( 40, 'Ravi')

INSERT INTO "PUBLIC"."TEST_TABLE"( "COL1", "COL2" ) VALUES ( 50, 'Raja')

commit

INSERT INTO "PUBLIC"."TEST_TABLE"( "COL1", "COL2" ) VALUES ( 70, '7Ravi')

INSERT INTO "PUBLIC"."TEST_TABLE"( "COL1", "COL2" ) VALUES ( 80, '8Raja')

 

Window2 - Run below query

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SELECT * FROM PUBLIC.TEST_TABLE;

The Result from Window2 will not include 70, and 80 the uncommitted records

Step 9 - Explore the system created files - mydb.script, mydb.log file using notepad, You would see details on DB Settings and properties

References

• https://wiki.documentfoundation.org/images/c/c9/Hsqldb_guide.pdf
• Connecting HSQLDB using Java - link
• Example code for embedded HSQL for java - link
• http://hsqldb.org/doc/2.0/guide/dbproperties-chapt.html
• http://hsqldb.org/web/hsqlFAQ.html
• http://alvinalexander.com/java/jwarehouse/hsqldb/testrun/hsqldb/TestSelfTransaction.txt.shtml
• http://spectral.mscs.mu.edu/tools/hypersonicsql/doc/hsqlBeginnersGuide.html#intro

Happy Learning!!!