Databases 17 min read

Using H2 Database in Embedded, Server, and Mixed Modes with Java

This article demonstrates how to use the H2 database with Java 1.8, covering embedded (file and memory) modes, server modes (Web, TCP, PG), mixed mode configurations, required dependencies, and provides complete JUnit test code examples for creating, accessing, and managing tables.

Java Captain

May 9, 2025

Using H2 Database in Embedded, Server, and Mixed Modes with Java

The article introduces the basic usage of the H2 database, using Java 1.8.0_341, H2 2.2.224 and the PostgreSQL driver 42.5.5.

1. Embedded (Local) Mode

In embedded mode the database is created automatically when the JDBC URL points to a file or memory location.

1.1 Persistent Database

@Test
public void localFile() throws SQLException {
    String dbName = "test";
    //用户名密码为第一次连接设置的密码
    Connection con = JdbcUtil.getConnection("org.h2.Driver", "jdbc:h2:file:d:/temp/" + dbName, "admin", "123456");
    business(con, dbName);
    con.close();
}

private void business(Connection con, String dbName) throws SQLException {
    String tableName = "a_student";
    Statement st = con.createStatement();
    ResultSet rs = st.executeQuery("select * from INFORMATION_SCHEMA.TABLES");
    while (rs.next()) {
        log.info("table_catalog={},table_schema={},table_name={}", rs.getString("table_catalog"), rs.getString("table_schema"), rs.getString("table_name"));
    }
    String sql = "select 1 from INFORMATION_SCHEMA.TABLES where upper(table_catalog)=? and upper(table_schema)=? and upper(table_name)=?";
    PreparedStatement pst = con.prepareStatement(sql);
    pst.setString(1, dbName.toUpperCase());
    pst.setString(2, "PUBLIC");
    pst.setString(3, tableName.toUpperCase());
    rs = pst.executeQuery();
    if (!rs.next()) { //表不存在则创建并初始化数据，这里根据业务需要进行操作
        st.executeUpdate("create table " + tableName + "(id int, name varchar(32))");
        st.executeUpdate("insert into " + tableName + "(id,name) values (1,'李白')");
        st.executeUpdate("insert into " + tableName + "(id,name) values (2,'杜甫')");
    }
    rs = st.executeQuery("select * from " + tableName);
    while (rs.next()) {
        log.info("id={},name={}", rs.getInt("id"), rs.getString("name"));
    }
}

1.2 Memory Database

@Test
public void localMem() throws SQLException {
    String dbName = "test";
    //用户名密码为第一次连接设置的密码
    Connection con = JdbcUtil.getConnection("org.h2.Driver", "jdbc:h2:mem:" + dbName, "admin", "123456");
    business(con, dbName);
    con.close();
}

2. Server Mode

The H2 console can be started with bin/h2.bat (or bin/h2.sh ), which also launches the Web and PG servers, or it can be started manually from the command line.

Generic command to start a server:

java -cp h2*.jar org.h2.tools.Server [param1] [param2] [...]

Use -? to list all parameters. Important parameters include:

[ -web ]               Start the web server with the H2 Console
[ -webAllowOthers ]      Allow other computers to connect
[ -webExternalNames ]   Comma‑separated list of external names and IP addresses
[ -webDaemon ]          Use a daemon thread
[ -webPort
]      The port (default: 8082)
[ -webSSL ]             Use encrypted (HTTPS) connections
[ -webAdminPassword ]   Password of DB Console administrator
[ -browser ]            Start a browser connecting to the web server
[ -tcp ]                Start the TCP server
[ -tcpAllowOthers ]     Allow other computers to connect
[ -tcpDaemon ]          Use a daemon thread
[ -tcpPort
]      The port (default: 9092)
[ -tcpSSL ]             Use encrypted (SSL) connections
[ -tcpPassword
]  Password for shutting down a TCP server
[ -tcpShutdown "
"]  Stop the TCP server; example: tcp://localhost
[ -tcpShutdownForce ]   Do not wait until all connections are closed
[ -pg ]                 Start the PG server
[ -pgAllowOthers ]      Allow other computers to connect
[ -pgDaemon ]           Use a daemon thread
[ -pgPort
]      The port (default: 5435)
[ -properties "
"] Server properties (default: ~, disable: null)
[ -baseDir
]      The base directory for H2 databases (all servers)
[ -ifExists ]           Only existing databases may be opened (all servers)
[ -ifNotExists ]        Databases are created when accessed
[ -trace ]              Print additional trace information (all servers)
[ -key
]    Allows to map a database name to another (all servers)

2.1 Start Web Server

java -cp h2-2.2.224.jar org.h2.tools.Server -web -webAllowOthers

Access the console at http://localhost:8082 .

2.2 Start TCP Server

java -cp h2-2.2.224.jar org.h2.tools.Server -tcp -tcpAllowOthers -ifNotExists

Applications can connect via JDBC.

2.2.1 Persistent Database

@Test
public void tcpFile() throws SQLException {
    String dbName = "test";
    //用户名密码为第一次连接设置的密码
    Connection con = JdbcUtil.getConnection("org.h2.Driver", "jdbc:h2:tcp://localhost:9092/file:d:/temp/" + dbName, "admin", "123456");
    business(con, dbName);
    con.close();
}

2.2.2 Memory Database

@Test
public void tcpMem() throws Exception {
    String dbName = "test";
    Connection con = JdbcUtil.getConnection("org.h2.Driver", "jdbc:h2:tcp://localhost:9092/mem:" + dbName, "admin", "123456");
    business(con, dbName);
    con.close();
}

2.3 Start PG Server

java -cp h2-2.2.224.jar org.h2.tools.Server -pg -pgAllowOthers -baseDir d:/temp -ifNotExists

Maven dependency for the PostgreSQL driver:

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.5.5</version>
</dependency>

@Test
public void pg() throws Exception {
    String dbName = "test";
    //用户名密码为第一次连接设置的密码
    Connection con = JdbcUtil.getConnection("org.postgresql.Driver", "jdbc:postgresql://localhost:5435/" + dbName, "admin", "123456");
    business(con, dbName);
    con.close();
}

Note: When accessing H2 databases via the PG client, the default schema is lower‑case public , while H2’s embedded or TCP modes use upper‑case PUBLIC . Mixing these can cause "Schema \"PUBLIC\" not found" errors.

3. Mixed Mode

Mixed mode lets a local application use the embedded database while remote applications connect through a server.

3.1 Start Web Server

@Test
public void web() throws Exception {
    Server server = Server.createWebServer().start();
    Thread.sleep(1000 * 100);
    server.stop();
}

Access the console at http://localhost:8082 .

3.2 Start TCP Server

Example that starts a TCP server and simulates another application accessing the database.

@Test
public void tcpFile2() throws Exception {
    //如果数据库不存在，tcp方式默认不允许创建数据库，使用 -ifNotExists 参数允许创建数据库
    Server server = Server.createTcpServer("-ifNotExists").start();
    CountDownLatch countDownLatch = new CountDownLatch(1);
    new Thread(() -> {
        try {
            //模拟其他应用访问
            tcpFile();
        } catch (Exception e) {
            e.printStackTrace();
        }
        countDownLatch.countDown();
    }).start();
    countDownLatch.await();
    server.stop();
}

@Test
public void tcpMem2() throws Exception {
    //如果数据库不存在，tcp方式默认不允许创建数据库，使用 -ifNotExists 参数允许创建数据库
    Server server = Server.createTcpServer("-ifNotExists").start();
    CountDownLatch countDownLatch = new CountDownLatch(1);
    new Thread(() -> {
        try {
            //模拟其他应用访问
            tcpMem();
        } catch (Exception e) {
            e.printStackTrace();
        }
        countDownLatch.countDown();
    }).start();
    countDownLatch.await();
    server.stop();
}

3.3 Start PG Server

@Test
public void pg2() throws Exception {
    //如果数据库不存在，该方式默认不允许创建数据库，使用 -ifNotExists 参数允许创建数据库；该方式还需要指定数据库文件目录
    Server server = Server.createPgServer("-baseDir", "d:/temp", "-ifNotExists").start();
    CountDownLatch countDownLatch = new CountDownLatch(1);
    new Thread(() -> {
        try {
            //模拟其他应用访问
            pg();
        } catch (Exception e) {
            e.printStackTrace();
        }
        countDownLatch.countDown();
    }).start();
    countDownLatch.await();
    server.stop();
}

Full Source Code

package com.abc.demo.db;

import lombok.extern.slf4j.Slf4j;
import org.h2.tools.Server;
import org.junit.Test;

import java.sql.*;
import java.util.concurrent.CountDownLatch;

@Slf4j
public class H2Case {
    @Test
    public void localFile() throws SQLException {
        String dbName = "test";
        //用户名密码为第一次连接设置的密码
        Connection con = JdbcUtil.getConnection("org.h2.Driver", "jdbc:h2:file:d:/temp/" + dbName, "admin", "123456");
        log.info("con={}", con);
        business(con, dbName);
        con.close();
    }

    @Test
    public void localMem() throws SQLException {
        String dbName = "test";
        //用户名密码为第一次连接设置的密码
        Connection con = JdbcUtil.getConnection("org.h2.Driver", "jdbc:h2:mem:" + dbName, "admin", "123456");
        business(con, dbName);
        con.close();
    }

    @Test
    public void tcpFile() throws SQLException {
        String dbName = "test";
        //用户名密码为第一次连接设置的密码
        Connection con = JdbcUtil.getConnection("org.h2.Driver", "jdbc:h2:tcp://localhost:9092/file:d:/temp/" + dbName, "admin", "123456");
        business(con, dbName);
        con.close();
    }

    @Test
    public void tcpMem() throws Exception {
        String dbName = "test";
        Connection con = JdbcUtil.getConnection("org.h2.Driver", "jdbc:h2:tcp://localhost:9092/mem:" + dbName, "admin", "123456");
        business(con, dbName);
        con.close();
    }

    @Test
    public void pg() throws Exception {
        String dbName = "test";
        //用户名密码为第一次连接设置的密码
        Connection con = JdbcUtil.getConnection("org.postgresql.Driver", "jdbc:postgresql://localhost:5435/" + dbName, "admin", "123456");
        business(con, dbName);
        con.close();
    }

    @Test
    public void web() throws Exception {
        Server server = Server.createWebServer().start();
        Thread.sleep(1000 * 10);
        server.stop();
    }

    @Test
    public void tcpFile2() throws Exception {
        //如果数据库不存在，tcp方式默认不允许创建数据库，使用 -ifNotExists 参数允许创建数据库
        Server server = Server.createTcpServer("-ifNotExists").start();
        CountDownLatch countDownLatch = new CountDownLatch(1);
        new Thread(() -> {
            try {
                //模拟其他应用访问
                tcpFile();
            } catch (Exception e) {
                e.printStackTrace();
            }
            countDownLatch.countDown();
        }).start();
        countDownLatch.await();
        server.stop();
    }

    @Test
    public void tcpMem2() throws Exception {
        //如果数据库不存在，tcp方式默认不允许创建数据库，使用 -ifNotExists 参数允许创建数据库
        Server server = Server.createTcpServer("-ifNotExists").start();
        CountDownLatch countDownLatch = new CountDownLatch(1);
        new Thread(() -> {
            try {
                //模拟其他应用访问
                tcpMem();
            } catch (Exception e) {
                e.printStackTrace();
            }
            countDownLatch.countDown();
        }).start();
        countDownLatch.await();
        server.stop();
    }

    @Test
    public void pg2() throws Exception {
        //如果数据库不存在，该方式默认不允许创建数据库，使用 -ifNotExists 参数允许创建数据库；该方式还需要指定数据库文件目录
        Server server = Server.createPgServer("-baseDir", "d:/temp", "-ifNotExists").start();
        CountDownLatch countDownLatch = new CountDownLatch(1);
        new Thread(() -> {
            try {
                //模拟其他应用访问
                pg();
            } catch (Exception e) {
                e.printStackTrace();
            }
            countDownLatch.countDown();
        }).start();
        countDownLatch.await();
        server.stop();
    }

    private void business(Connection con, String dbName) throws SQLException {
        String tableName = "a_student";
        Statement st = con.createStatement();
        String sql = "select 1 from INFORMATION_SCHEMA.TABLES where upper(table_catalog)=? and upper(table_schema)=? and upper(table_name)=?";
        PreparedStatement pst = con.prepareStatement(sql);
        pst.setString(1, dbName.toUpperCase());
        pst.setString(2, "PUBLIC");
        pst.setString(3, tableName.toUpperCase());
        ResultSet rs = pst.executeQuery();
        if (!rs.next()) { //表不存在则创建并初始化数据，这里根据业务需要进行操作
            st.executeUpdate("create table " + tableName + "(id int, name varchar(32))");
            st.executeUpdate("insert into " + tableName + "(id,name) values (1,'李白')");
            st.executeUpdate("insert into " + tableName + "(id,name) values (2,'杜甫')");
        }
        rs = st.executeQuery("select * from " + tableName);
        while (rs.next()) {
            log.info("id={},name={}", rs.getInt("id"), rs.getString("name"));
        }
    }
}

package com.abc.demo.db;

import lombok.extern.slf4j.Slf4j;

import java.sql.*;

@Slf4j
public class JdbcUtil {
    private JdbcUtil() {}

    public static Connection getConnection(String driver, String url, String username, String password) {
        Connection con = null;
        try {
            Class.forName(driver);
            con = DriverManager.getConnection(url, username, password);
        } catch (ClassNotFoundException | SQLException e) {
            log.warn("url={},username={},password={}", url, username, password);
            e.printStackTrace();
        }
        return con;
    }
}

Java Database JDBC Junit embedded Server H2

Written by

Java Captain

Focused on Java technologies: SSM, the Spring ecosystem, microservices, MySQL, MyCat, clustering, distributed systems, middleware, Linux, networking, multithreading; occasionally covers DevOps tools like Jenkins, Nexus, Docker, ELK; shares practical tech insights and is dedicated to full‑stack Java development.

0 followers

Reader feedback

How this landed with the community

Rate this article

Was this worth your time?

Discussion

0 Comments

Thoughtful readers leave field notes, pushback, and hard-won operational detail here.