Tuesday, March 11, 2014

Accessing an existing H2 DB

H2 is one of the fastest file based database available. You can download the database from http://www.h2database.com/

This post is a very short guide on how to access an already existing H2 database through the command line and the browser.

Let me assume, you have a database named test.db in your home (referring it as $HOME). So the full path of your database is $HOME/test.db

First let me explain how to access the DB in command shell.

Extract the downloaded h2 zip file from the above mentioned URL. Extract it to a location of your liking. Navigate to the bin directory of the extracted directory and enter the following.
$ java -cp h2*.jar org.h2.tools.Shell

Welcome to H2 Shell 1.3.174 (2013-10-19)
Exit with Ctrl+C
[Enter]   jdbc:h2:~/test
URL       jdbc:h2:$HOME/test
[Enter]   org.h2.Driver
Driver    org.h2.Driver
[Enter]   username
User      username
[Enter]   password
Password  password

Commands are case insensitive; SQL statements end with ';'
help or ?      Display this help
list           Toggle result list / stack trace mode
maxwidth       Set maximum column width (default is 100)
autocommit     Enable or disable autocommit
history        Show the last 20 statements
quit or exit   Close the connection and exit


Replace $HOME with your home directory name. Note at the end you do not specify .db extention as in test.db in the URL. Instead it is just 'test'.
Now you can enter sql commands against your DB schema.

Now let's see how to access the DB in the Web browser.

Type the following on top the bin directory.
$ java -jar h2*.jar

A web browser window should be opened as shown below.

Enter the following,

Saved Settings        : Generic H2 (Embedded)
Setting name           : Any name you like  (You can save or remove this configuration with the save and remove button)

Driver Class             : org.h2.Driver

JDBC URL              : jdbc:h2:/$HOME/test

Enter also your user name and password for the DB.

You should be able to see a window like the below. Now you can enter sql commands against your DB schema.

Enjoy using H2 DB in your applications.

No comments:

Post a Comment