How to connect to an H2 Database from Java

In this article we will show an example to to connect to an H2 Database from Java. At first, we will use create a Java example for the Embedded H2 Database. Then, we will modify the example to connect to a remote H2 Database.

Firstly, if you are new to H2 Database, we recommend the following article for an overview of the Basics of this Java Database: H2 Database Tutorial and expert Tips

Then,download the H2 Database from this link: http://www.h2database.com/html/download.html

Let’s start!

Java Example to Connect to an Embedded H2 Database

In this first example, we will start an Embedded H2 Database directly from a Java Application, We will use the Connection to create a Table and add some data in it.

Here is the Java example:

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

public class EmbeddedH2Example {

    public static void main(String[] args) {
        EmbeddedH2Example example = new EmbeddedH2Example();
        example.createTable();
        example.insertData();
        example.queryData();
    }

    public void createTable() {
        try (Connection connection = DriverManager.getConnection("jdbc:h2:~/test", "sa", "sa")) {
            String createTableSQL = "CREATE TABLE IF NOT EXISTS person (id INT PRIMARY KEY, name VARCHAR(255))";
            try (PreparedStatement preparedStatement = connection.prepareStatement(createTableSQL)) {
                preparedStatement.executeUpdate();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void insertData() {
        try (Connection connection = DriverManager.getConnection("jdbc:h2:~/test", "sa", "sa")) {
            String insertDataSQL = "INSERT INTO person (id, name) VALUES (?, ?)";
            try (PreparedStatement preparedStatement = connection.prepareStatement(insertDataSQL)) {
                preparedStatement.setInt(1, 1);
                preparedStatement.setString(2, "John Doe");
                preparedStatement.executeUpdate();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void queryData() {
        try (Connection connection = DriverManager.getConnection("jdbc:h2:~/test", "sa", "sa")) {
            String queryDataSQL = "SELECT * FROM person";
            try (PreparedStatement preparedStatement = connection.prepareStatement(queryDataSQL);
                 ResultSet resultSet = preparedStatement.executeQuery()) {

                while (resultSet.next()) {
                    int id = resultSet.getInt("id");
                    String name = resultSet.getString("name");
                    System.out.println("User ID: " + id + ", Name: " + name);
                }

            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

As you can see from the code, the JDBC Connection String for the Embedded H2 Database uses a local file system in the User’s directory: jdbc:h2:~/test . Also, we use the default username and password for H2 Database which is “sa” / “sa”.

Compile the Source and run it with.

java -classpath bin/h2-2.2.224.jar:. EmbeddedH2Example 

As you can see, the application terminates with the output of a Database row:

Finally, here is an hint. If you have installed JBang on your machine, then you can add on top of your Java script the reference to H2 Database dependency:

//usr/bin/env jbang "$0" "$@" ; exit $?
//DEPS com.h2database:h2:2.2.224

Then, you can run the Java H2 Example just as follows:

jbang EmbeddedH2Example.java

For more info on JBang, check this article: JBang: Create Java scripts like a pro

Java Example to Connect to a Remote H2 Database

In the second example, we will show how to connect to an H2 Database through a TCP Connection. This time, we need to start the H2 Database as a separate process:

$ java -cp h2*.jar org.h2.tools.Server -webAllowOthers -tcpAllowOthers -ifNotExists
TCP server running at tcp://127.0.1.1:9092 (others can connect)
PG server running at pg://127.0.1.1:5435 (only local connections)
Web Console server running at http://127.0.1.1:8082 (others can connect)

Please note that you need to add the -tcpAllowOthers -ifNotExists options to allow the connection and database creation from a Remote Host. Then, you need to acquire the JDBC Connection from the TCP Host. Therefore, modify as follows the JDBC Connection part for every method:

try (Connection connection = DriverManager.getConnection("jdbc:h2:tcp://localhost/~/test", "sa", "sa")) {
            String createTableSQL = "CREATE TABLE IF NOT EXISTS person (id INT PRIMARY KEY, name VARCHAR(255))";
            try (PreparedStatement preparedStatement = connection.prepareStatement(createTableSQL)) {
                preparedStatement.executeUpdate();
            }
        } catch (SQLException e) {
            e.printStackTrace();
}

With these changes in your Java application you will be able to connect to the H2 Database also via TCP.

Conclusion

In this tutorial, you have learned how to connect to an H2 database from a Java application. Ensure you have the H2 server running, create a database, add the H2 dependency in your classpath, and use JDBC to establish a connection. From here, you can build upon this foundation to create Java applications that interact with the H2 database. Happy coding!

Found the article helpful? if so please follow us on Socials