Using the Data Source Explorer to connect to an existing H2 database

Download H2 database runtime

We will use H2 for the database engine. H2 is a database engine written in Java.

Download it from http://www.h2database.com/h2-2019-03-13.zip

(The downloaded version should be 1.4.199. It is important to use this version, not a prior version).

Create a Database connection

First, make sure the Data Source Explorer toolwindow is visible: Window > Show View > Data Source

Explorer (or Window > Show View > Other… then search “Data Source Explorer”).

Right-click the Database Connections item, and select New…

Click the Generic JDBC item then enter NorthwindH2 as the connection Name :

CC BY-NC-ND

Now select Next >

Click the New Driver Definition button.

Click the Generic JDBC Driver line once – this should highlight the line with a blue background.

CC BY-NC-ND

Click the Add JAR/Zip… button

Select the H2 runtime JAR file name (h2-1.4.199.jar) and validate. Now click the Properties tab.

CC BY-NC-ND

Click the […] button on the Driver Class line. WARNING : this button may be difficult to see on Linux,

you may have to press the ENTER key.

Select the org.h2.Driver class and press OK.

CC BY-NC-ND

Now you need to enter jdbc:h2:, followed by the full file path of the H2 database, without the .mv.db

extension.

In the example above, the H2 database file is the following file on disk :

C:\Users\Fred\Documents\repos\fcres\fc.Test\h2database

The connection string is therefore :

jdbc:h2:C:\Users\Fred\Documents\repos\fcres\fc.Test\h2database;CASE_

INSENSITIVE_IDENTIFIERS=TRUE;DB_CLOSE_ON_EXIT=FALSE;AUTO_SERVER=TRUE

Make sure you add the options at the end of the connection string. They are very important.

On Linux, the file path part will look more like :

/home/jeanp01/Documents/h2database

Once you have done all of this, press OK.

Back in the New Connection Profile dialog :

Make sure you enter SA as the user name, then click the Test Connection button.You should see the

following dialog :

CC BY-NC-ND

Press OK and click Finish. You can now see the database connection in the Data Source Explorer

view :

You can now open the SQL scrapbook to run SQL statements and consult data, create tables (with

CREATE TABLE commands), or anything you want.

CC BY-NC-ND

In the scrapbook window, successively :

1) Select the database connection Name (New Generic JDBC in this example)

2) Select the database (H2DATABASE)

3) Write an SQL query

4) Execute the query by right-clicking the editor window and using the context menu item

« Execute All »

The results will be displayed in the SQL Results view :

Note : you may have to specify the database name in front of table names.

For instance :

select * from suppliers;

May not work. Instead, type :

select * from northwind.suppliers;

Also note that code completion works in the editor window :

CC BY-NC-ND

There also is an history of SQL queries :

Warning : you may have to re-execute a query using « Execute All » to make sure query output is up-

to-date. Selecting a query and visualizing results may show old results.

CC BY-NC-ND

Using H2 Database with Data Source Explorer

Using H2 Database with Data Source Explorer