Creating a custom h2 database server

Sometimes when you are writing a small application you want to have a persistence layer but using a big database engine often carries dependencies and makes the software difficult to move.

one option is using a portable database, some of the most popular are

  • SQLite
  • Derby
  • H2DB
  • BerkeleyDB

When it comes to Java, my favorite is H2DB due to its simplicity and portability, it gives you the ability to create an embedded server or running it as a stand-alone server.

The prefer the server mode over the embedded because it works faster and you can use it from another application, the below code shows how to create a database server plus a web server that can be accessed from the browser

public void startServer() throws Exception {
    new Thread(() -> {
        try {
            webServer = Server.createWebServer("-webAllowOthers", "-webPort", "8082");
            webServer.start();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }).start();
    new Thread(() -> {
        try {
            tcpServer = Server.createTcpServer("-tcpAllowOthers", "-tcpPort", "9094");
            tcpServer.start();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }).start();
    //wait until the servers are created
    while (tcpServer == null || webServer == null) {
        Thread.sleep(40);
    }
}

below a method to stop gracefully the server

public void stopServer() {
    if (tcpServer != null) {
        System.out.println("stopping tcpServer");
        System.out.println("tcpServer: " + tcpServer.getStatus());
        tcpServer.stop();
        System.out.println("tcpServer: " + tcpServer.getStatus());
    }

    if (webServer != null) {
        System.out.println("stopping webServer");
        System.out.println("webServer: " + webServer.getStatus());
        webServer.stop();
        System.out.println("webServer: " + webServer.getStatus());
    }

    if (conn != null) {
        try {
            conn.close();
        } catch (SQLException e) {
            //ignored
        }
    }

}

then a sample about how to use it

public static void main(String[] args0) throws Exception {
    DatabaseServer server = new DatabaseServer("myuser", "mypass", "mydb");
    server.startServer();
    Connection conn = server.getConnection();

    Statement stmt = conn.createStatement();
    stmt.execute(Utils.resource2txt("schema.sql"));

    String sql1 = "insert into mytable (name) values (?)";
    PreparedStatement ps1 = conn.prepareStatement(sql1);
    ps1.setString(1, "fred");
    ps1.executeUpdate();

    String sql2 = "select name from mytable";
    PreparedStatement ps2 = conn.prepareStatement(sql2);
    ResultSet rs = ps2.executeQuery();
    while (rs.next()) {
        System.out.println(rs.getString("name"));
    }

    server.stopServer();
}

output

url: jdbc:h2:tcp://169.254.31.143:9094/~/mydb
fred
fred
stopping tcpServer
tcpServer: TCP server running at tcp://169.254.31.143:9094 (others can connect)
tcpServer: Not started
stopping webServer
webServer: Web Console server running at http://169.254.31.143:8082 (others can connect)
webServer: Not started

the web console looks like

1

the user and the pass is the one that we defined, make sure the you use the correct JDBC URL

2

the whole code is available here

finally a soundtrack for codding this stuff, I published this example because when I was learning how to do this, I could not find any good example on the internet, almost everybody uses H2 as an embedded database with the default user and password sa/” or just start the server with the default setting :v

.