Java Connect to H2 Database Examples

In this article of JDBC series, you will learn the fundamentals of H2 database with Java programming language. In details, I’ll walk you through the following content:

• What is H2 Database?
• Download H2 Binary Distribution
• How to use H2 Console Web App
• How to add H2 JDBC driver to a Java project
• Code example connect to H2 in In-Memory mode
• Code example connect to H2 in Embedded mode
• Code example connect to H2 in Server mode

Let’s get started learning H2 database.

1. What is H2 Database?

H2 is pure Java SQL database, which means you can embed H2 directly in your Java applications without any database installation. The main features of H2 are:

- Very fast, open source, JDBC API compatible.

- You can use H2 in different modes, depending on your need: in-memory, embedded or client/server.

- H2 provides a browser-based console application which you can use to access SQL database.

- Lightweight: about 2.5MB jar file size.

With such features, H2 can be used for rapid prototype development, testing purpose or embedded in Java applications.

2. Download H2 Binary Distribution

If you want to use H2 browser-based console app, its JDBC driver or H2 server, you need to download H2 binary distribution from its home page at https://h2database.com. Click the link All Platforms (zip, 9.5 MB) to download the zip file which can be used on any operating system.

Extract the downloaded zip file to a directory. You can see the distribution comes with source code, binary, docs, scripts… but in most cases, you need to use only the h2-version.jar file in bin folder.

3. How to use H2 Console Web App

You can use the H2 Console to access your H2 database or any SQL database via JDBC. It’s a Java webapp with embedded server, so type the following command to start H2 Console:

java -jar h2-version.jar

Before running this command, be sure that you change the current directory to the bin directory of the extracted zip file. Then it will open the H2 login page in your browser, as shown below:

Just click Connect and it will create a new database named test under your user home directory, and get connected in embedded mode. Then you can use its web interface to run SQL statements:

The H2 Console app is as simple as that. You can use it to browse your database and execute SQL statements. It doesn’t provide rich features as phpMyAdmin or MySQL Workbench.

To stop the H2 Console app, press Ctrl + C in the command prompt or terminal window which launched it.

4. How to Add H2 JDBC Driver to a Java Project

To use H2 database in your Java project, a compatible JDBC driver is required at runtime. The h2-version.jar file contains H2 JDBC driver, so you just need to make this jar file available in the project’s classpath.

Add H2 JDBC Driver from local Jar file:

If your Java project doesn’t use any build tool, you should copy the h2-version.jar file to the project’s directory. And then configure Java Build Path (in Eclipse) like this:

Add H2 JDBC Driver from Maven dependency:

In case your project uses Maven, just put the following dependency declaration in the pom.xml file:

<dependency>
	<groupId>com.h2database</groupId>
	<artifactId>h2</artifactId>
	<version>2.1.210</version>
</dependency>

To know the latest version of H2, check this page.

5. Java Connect to H2 Database in In-Memory Mode

You can use H2 database in in-memory mode, which means the database is created in memory when the first connection is established, and is removed when all connections are closed. The database engine runs in the same process of the application.

Using H2 in-memory database is preferred for quick testing in which data is not need to be stored permanently on disk.

To connect to a H2 in-memory database, you need to use JDBC URLs look like follows:

jdbc:h2:mem:test multiple connections in one process

jdbc:h2:mem: unnamed private; one connection.

The following example code demonstrates a Java console program that connects to a H2 in-memory database, creates a table and inserts a row into the table:

package net.codejava;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class ConnectH2Example {

	public static void main(String[] args) throws SQLException {
		String jdbcURL = "jdbc:h2:mem:test";

		Connection connection = DriverManager.getConnection(jdbcURL);

		System.out.println("Connected to H2 in-memory database.");

		String sql = "Create table students (ID int primary key, name varchar(50))";
		
		Statement statement = connection.createStatement();
		
		statement.execute(sql);
		
		System.out.println("Created table students.");
		
		sql = "Insert into students (ID, name) values (1, 'Nam Ha Minh')";
		
		int rows = statement.executeUpdate(sql);
		
		if (rows > 0) {
			System.out.println("Inserted a new row.");
		}

		connection.close();

	}
}

As you can see, you can use JDBC API to work with H2 database just like any other databases.

6. Java Connect to H2 Database in Embedded Mode

In embedded mode, a H2 database is stored permanently on disk and the database engine runs in the same process that runs the application.

To connect to an H2 embedded database, you need to use one of the following JDBC URLs:

jdbc:h2:~/testthe ‘test’ database in the user home directory

jdbc:h2:./test the ‘test’ database in the current directory

jdbc:h2:/data/test the ‘test’ database in the /data directory (Unix)

jdbc:h2:D:/data/test the ‘test’ database in the D:/data directory (Windows)

The following example program connects to a H2 database in embedded mode, executes a SQL Select statement that gets all rows from students table:

package net.codejava;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class H2EmbeddedExample {

	public static void main(String[] args) throws SQLException {
		String jdbcURL = "jdbc:h2:~/test";
		String username = "sa";
		String password = "1234";

		Connection connection = DriverManager.getConnection(jdbcURL, username, password);
		System.out.println("Connected to H2 embedded database.");

		String sql = "SELECT * FROM students";

		Statement statement = connection.createStatement();
		ResultSet resultSet = statement.executeQuery(sql);

		int count = 0;

		while (resultSet.next()) {
			count++;

			int ID = resultSet.getInt("ID");
			String name = resultSet.getString("name");
			System.out.println("Student #" + count + ": " + ID + ", " + name);
		}

		connection.close();
	}
}

Note that in this program, it connects to the H2 database test in the user home directory with username and password. Suppose that you created the database beforehand using the H2 Console webapp.

7. Java Connect to H2 Database in Server Mode

In client/server mode, the H2 database engine runs as the server in a separate process, and your Java application is the client that connects to the server via network (locally or remotely). In server mode, you need to use one of the following JDBC URLs:

jdbc:h2:tcp://localhost/~/test connect to the ‘test’ database in the user home directory on the server (local computer).

jdbc:h2:tcp://192.168.1.3:9092 //data/test connect to the ‘test’ database in the /data directory on the remote server.

Run H2 server in command line:

In command prompt or terminal window, type the following command to run H2 server:

java -cp h2-version.jar org.h2.tools.Server -tcp

This will start H2 server program, listening for TCP connections. To know more options, type the following command:

java -cp h2-version.jar org.h2.tools.Server -help

Run H2 server in a Java program:

You can also write a simple Java program that starts H2 database server, as shown below:

package net.codejava;

import java.sql.SQLException;

import org.h2.tools.Server;

public class H2Server {

	public static void main(String[] args) throws SQLException {
		Server server = Server.createTcpServer(args).start();
	}
}

And the following example program connects to H2 database server (localhost) with the test database in the user home directory. Then it executes a SQL Select statement that retrieves all rows from the students table. Below is the code:

package net.codejava;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class H2ServerModeExample {

	public static void main(String[] args) throws SQLException {
		String jdbcURL = "jdbc:h2:tcp://localhost/~/test";
		String username = "sa";
		String password = "1234";

		Connection connection = DriverManager.getConnection(jdbcURL, username, password);
		System.out.println("Connected to H2 in server mode.");

		String sql = "SELECT * FROM students";

		Statement statement = connection.createStatement();

		ResultSet resultSet = statement.executeQuery(sql);

		int count = 0;

		while (resultSet.next()) {
			count++;

			int ID = resultSet.getInt("ID");
			String name = resultSet.getString("name");
			System.out.println("Student #" + count + ": " + ID + ", " + name);
		}

		connection.close();
	}
}

To stop H2 server, you can press Ctrl + C in command line or kill the Java process that launched H2 server.

That’s my tutorial about Java connect to H2 database. I hope you found it helpful. To see the coding action, I recommend you watch the following video:

Video:

Related JDBC Tutorials:

• JDBC CRUD Tutorial
• JDBC Transaction Tutorial
• How to Read Database Meta Data in JDBC
• How to call stored procedure with JDBC

About the Author:

Nam Ha Minh is certified Java programmer (SCJP and SCWCD). He began programming with Java back in the days of Java 1.4 and has been passionate about it ever since. You can connect with him on Facebook and watch his Java videos on YouTube.