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

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

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

.