Getting started with h2 database in no time

h2[1] is an upcoming database written purely in Java. It is open source and gaining wide popularity among other projects as well as community[2]. It claims to be faster than most of other open source & non open source databases[3].

Here is a short guide which will get you started with h2 in no time.

Note : These steps are performed on Linux. It may would work on Windows subjected to slight modifications

Step 1

Download H2 Database engine from http://www.h2database.com [1] (http://www.h2database.com/h2-2008-06-21.zip [2] as @ 22/06/2008)

Step 2

Extract contents of the zip file (eg: /home/sumedha/databases/h2)

unzip h2-2008-06-21.zip

Step 3

You will see following folder structure under h2 folder

|-- bin
|   |-- h2.bat
|   |-- h2.jar
|   `-- h2.sh
|-- build.bat
|-- build.sh
|-- build.xml
|-- docs
|   |-- h2.pdf
|   |-- html
|   |-- index.html
|   `-- javadoc
|-- service
|   |-- 0_run_server_debug.bat
|   |-- 1_install_service.bat
|   |-- 2_start_service.bat
|   |-- 3_start_browser.bat
|   |-- 4_stop_service.bat
|   |-- 5_uninstall_service.bat
|   |-- serviceWrapperLicense.txt
|   |-- wrapper.conf
|   |-- wrapper.dll
|   |-- wrapper.exe
|   `-- wrapper.jar
`-- src
    |-- docsrc
    |-- installer
    |-- main
    |-- test
    `-- tools

Step 4

Execute h2.sh using a command line window

$ ./h2.sh

(Before doing so, you might have to assign execute permissions to it : chmod 755 h2.sh )

Step 5

The script will start the database engine & bring up a popup window similar to following.

Step 6

"Start Browser" button will open a web browser containing a client application using which you can connect to a database.

Step 7

Few basic facts,

- Admin user account is 'sa' & password is empty by default.

- h2 will automatically create a database, if a database does not exist by the name you provide in the 'JDBC URL' text box

For example following url,

jdbc:h2:tcp://localhost/~/DATASERVICE_SAMPLE

will create a database called 'DATASERVICE_SAMPLE'.

Step 8

Once you connect to 'DATASERVICE_SAMPLE' database, following SQL(DDL) shows how you can create a table

CREATE TABLE Employees(
        employeeNumber INT,
        lastName VARCHAR(50),
        firstName VARCHAR(50),
        extension VARCHAR(10),
        email VARCHAR(100),
        officeCode VARCHAR(10),
        reportsTo INT,
        jobTitle VARCHAR(50)
)

Step 9

Now let's insert some data to our table as follows

insert into Employees values (1002,'Murphy','Diane','x5800','dmurphy@classicmodelcars.com','1',null,'President');
insert into Employees values (1056,'Patterson','Mary','x4611','mpatterso@classicmodelcars.com','1',1002,'VP Sales');
insert into Employees values (1076,'Firrelli','Jeff','x9273','jfirrelli@classicmodelcars.com','1',1002,'VP Marketing');
insert into Employees values (1088,'Patterson','William','x4871','wpatterson@classicmodelcars.com','6',1056,'Sales Manager (APAC)');
insert into Employees values (1102,'Bondur','Gerard','x5408','gbondur@classicmodelcars.com','4',1056,'Sale Manager (EMEA)');
insert into Employees values (1143,'Bow','Anthony','x5428','abow@classicmodelcars.com','1',1056,'Sales Manager (NA)');
insert into Employees values (1165,'Jennings','Leslie','x3291','ljennings@classicmodelcars.com','1',1143,'Sales Rep');

Step 10

and let's read back the data we inserted...

References

[1] http://www.h2database.com/ [3]

[2] http://www.h2database.com/html/frame.html?links.html&main [4]

[3] http://www.h2database.com/html/frame.html?performance.html%23performance_comparison&main [5]