This post is on learning HSQLDB. HSQLDB is Rdbms db written in java
- Download HSQLDB from link
- Getting started guide useful from link
- 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
Happy Learning!!!
