SQL Tutorial with HSQLDB

This assumes that you have Java setup on your machine as illustrated in the "simple" Java tutorial.

Q. Why use HSQLB?
A. It can be useful in a number of ways.
  • Easy to get started and if you are a beginner, you can acquire or sharpen your SQL and database skills.
  • It is open-sourced, hence you can use it for your self-taught projects as a database. An alternative toMySQL database.
  • It is used in commercial projects to write unit tests for your data access layer. Apache Derby database is another alternative.
  • It is used in the ensuing tutorials on JDBC and Hibernate as the database.

Step 1: Download the latest version of HSQLB from  http://hsqldb.org/. I downloaded hsqldb-2.2.9.zip at the time of writing and extracted zip files. The HSQLDB is unpacked under c:\Tools as as hown below.



Step 2: You can start the server with the following command.

?
1
c:\Tools\hsqldb-2.2.9>java -cp ./hsqldb/lib/hsqldb.jar org.hsqldb.Server

Step 3: Open up another command prompt and start the DatabaseManager, with which you can execute SQLs. If you are new to SQL, then this is handy to practice and gain some SQL skills. Note: You need to have the HSQLDB server running before you can open the DatabaseManager.

?
1
c:\Tools\hsqldb-2.2.9>java -cp ./hsqldb/lib/hsqldb.jar org.hsqldb.util.DatabaseManager




Once you press "Ok", the next window will look like



Step 4: Here you can type and execute some DDL(i.e. Data Definition Language) to create new table schema, etc and DML (i.e. Data Manipulation Language) to insert new data, etc. Type in the following commands  as grouped and press the "Execute" button.

a. DDL to create a table named "Course"

?
1
create table Course (course_id integer, name varchar(50), course varchar(50), PRIMARY KEY (course_id));

b. DML to insert a few records

?
1
2
3
4
5
insert into Course values (1,'Sam', 'Java'); 
insert into Course values (2,'peter', 'J2EE'); 
insert into Course values (3,'paul', 'JSF'); 
insert into Course values (4,'jonathan', 'Hibernate'); 
insert into Course values (5,'james', 'Spring'); 

c. DML to select the records we just had created

?
1
select * from Course;

You can now see the in memory table and the data by selecting the menu options View --> Refresh Tree.



Note: The syntax for these files are .properties, .lck etc. Since we have not specified any database name, it defaults to “test”. 

This was a quick overview. Now, let's look at creating a named database.


HSQLDB supports three types of persistent tables, MEMORY tables, CACHED tables and TEXT tables.
I will be using the default MEMORY tables where data is held entirely in memory but any change to their structure or contents is written to the .script file. The script file is read the next time the database is opened, and the MEMORY tables are recreated with all their contents. So, the MEMORY tables are persistent. It is important to remember that the data in memory is written to the .script file when you shutdown your database properly (i.e. naturally by executing SQL “SHUTDOWN  (COMPACT | IMMEDIATELY”), the saved file will load the data into memory the next time the SQLDB server starts up. But if you stop the HSQLDB server abruptly in the command line by pressing [Ctrl] + [C] the data will not be written to the script file and consequently lost. Refer documentation for CACHED & TEXT tables.


Step 1: Cretate a file named server.properties under c:\Tools\hsqldb-2.2.9\hsqldb and type in the following database file name (i.e. tutorialdb) and the database alias name (mytestdb)

?
1
2
server.database.0=file:mydatabases/tutorialdb
server.dbname.0=mytestdb

You can now start the database server with the following command

?
1
c:\Tools\hsqldb-2.2.9\hsqldb>java -cp ./lib/hsqldb.jar org.hsqldb.Server




Once the server is started, you an see the following folder and files being created.



Step 2: Once the server has started, start the DatabaseManager where you can manage your schemas and data.

c:\Tools\hsqldb-2.2.9\hsqldb>java -cp ./lib/hsqldb.jar org.hsqldb.util.DatabaseManager


Note that the protocol is "hsql" and not "mem".

You can type in the relevant DDL and DML SQL queries as you did earlier. This is still an in-memory database and the data will be lost in between invocations.


Note: To persist your data from memory, you need to execute the "SHUTDOWN" SQL command, which will delete the tutorialdb.lock and and tutorialdb.log files and if you open the tutorialdb.script file, you can see your SQL commands that get executed when you reconnect again.

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CREATE MEMORY TABLE PUBLIC.COURSE(COURSE_ID INTEGER PRIMARY KEY,NAME VARCHAR(50),COURSE VARCHAR(50))
ALTER SEQUENCE SYSTEM_LOBS.LOB_ID RESTART WITH 1
SET DATABASE DEFAULT INITIAL SCHEMA PUBLIC
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.YES_OR_NO TO PUBLIC
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.TIME_STAMP TO PUBLIC
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.CARDINAL_NUMBER TO PUBLIC
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.CHARACTER_DATA TO PUBLIC
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.SQL_IDENTIFIER TO PUBLIC
GRANT DBA TO SA
SET SCHEMA SYSTEM_LOBS
INSERT INTO BLOCKS VALUES(0,2147483647,0)
SET SCHEMA PUBLIC
INSERT INTO COURSE VALUES(1,'Sam','Java')
INSERT INTO COURSE VALUES(2,'peter','J2EE')
INSERT INTO COURSE VALUES(3,'paul','JSF')
INSERT INTO COURSE VALUES(4,'jonathan','Hibernate')
INSERT INTO COURSE VALUES(5,'james','Spring')


If you want to persist it directly to a file without having an alias, you can try.



Once you know how HSQLDB works, you can work on JDBC and Hibernate tutorials.

Comments

Post a Comment

Popular posts from this blog

Hibernate tutorial with HSQLDB

Image
This tutorial is basically an extension to the  "simple" Java  and  JDBC tutorials . Hibernate is an ORM (Object to Relational Mapping) tool. Step 1 :  First step is to bring in the relevant  Hibernate  framework jar files like  hibernate-xxx.jar ,  hibernate-annotations-xxxx.jar , and other dependent jar files that are transitively brought in from hibernate framework's pom.xml file. Here is the pom.xml file for simple Java project extended for hibernate. ? 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 <project xmlns= " http://maven.apache.org/POM/4.0.0 " xmlns:xsi= " http://www.w3.org/2001/XMLSchema-instance "   xsi:schemaLocation= " http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd " >   <modelVersion...
Read more

Java Interview Questions & Answers: user defined key class

Image
This is one of my favorite core Java interview questions as not knowing this can cause subtle and intermittent issues in Java. The issues that arise from not understanding equals( ) and hashCode( ) contract can be hard to debug.    Q.  When providing a user defined key class for storing objects in a  Map (e.g. HashMap) , what methods do you have to provide or override (i.e. method overriding)? A.  You should override the  equals( ... )  and  hashCode( .. )  methods from the  Object  class. The default implementation of the  equals( ... )  and  hashCode( ... )  methods are inherited from the  java.lang.Object  class  uses an object instance’s memory location (e.g. MyObject@6c60f2ea). This can cause problems when two instances of car objects have the same color but the inherited  equals( .. )  will return false because it uses the memory location as oppose to the color, which is different ...
Read more