22 January 2015

Tags: java |

blog_post_image

There are several Java SQL databases which supports both server and embedded modes: H2, HSQLDB and Derby. The most interesting for me is H2 with own web Console.

The aim of this post is to show how to connect from H2 Console to embedded in-memory H2.

H2 currently supports three servers: a web server (for the H2 Console), a TCP server (for client/server connections) and an PG server (for PostgreSQL clients). Please note that only the web server supports browser connections. The servers can be started in different ways, one is using the Server tool. Starting the server doesn’t open a database - databases are opened as soon as a client connects.

The next steps have to be performed to connect to embedded DB and populate initial data:

  1. Load DB driver
  2. Get Connection
  3. Create Statement and executeUpate queries
  4. Create TCP Server. We should start serve to make in-memory DB available for another process

Let’s try to implement this:


public class App { private static final String DBNAME = "mytest"; public static void main(String[] args) throws ClassNotFoundException, SQLException, IOException { // open the in-memory database within a VM Class.forName("org.h2.Driver"); // (1) Connection conn = DriverManager.getConnection("jdbc:h2:mem:" + DBNAME, "sa", "sa"); // (2) // username:password are very important and must be used for connecting via H2 Console Statement stat = conn.createStatement(); // (3) stat.executeUpdate("create table mytbl(id int primary key, name varchar(255))"); stat.executeUpdate("insert into mytbl values(1, 'Hello')"); stat.executeUpdate("insert into mytbl values(2, 'World')"); // Verify that sample data was really inserted ResultSet rs = stat.executeQuery("select * from mytbl"); System.out.println("ResultSet output:"); while (rs.next()) { System.out.println("> " + rs.getString("name")); } // start a TCP server Server server = Server.createTcpServer().start(); // (4) // .. use in embedded mode .. // or use it from another process: System.out.println("Server started and connection is open."); System.out.println("URL: jdbc:h2:" + server.getURL() + "/mem:" + DBNAME); System.out.println("\n"); System.out.println( "now start the H2 Console in another process using:\n" + "$ cd h2/bin; java -cp h2-1.4.185.jar org.h2.tools.Console -web -browser"); System.out.println("Press [Enter] to stop."); System.in.read(); System.out.println("Stopping server and closing the connection"); rs.close(); server.stop(); conn.close(); System.out.println("Server is STOPPED"); } }
ResultSet output:
> Hello
> World
Server started and connection is open.
URL: jdbc:h2:tcp://192.168.1.100:9092/mem:mytest


now start the H2 Console in another process using:
$ cd h2/bin; java -cp h2-1.4.185.jar org.h2.tools.Console -web -browser
Press [Enter] to stop.

Verify that our custom table contains just inserted values
blog_post_image_inline

Add Embedded TCP and Web Servers

Now, it’s time to add small improvements.
The idea is to switch on H2 Console Web Server and TCP Server to have external access.
We should add/update item (4) in previous code snippets like this

public class App {
    private static final String DBNAME = "mytest";

    public static void main(String[] args) throws ClassNotFoundException, SQLException, IOException {
...

        Server webServer = Server.createWebServer("-webAllowOthers","-webPort","8082").start(); // (4a)
        Server server = Server.createTcpServer("-tcpAllowOthers","-tcpPort","9092").start();    // (4b)
        // .. use in embedded mode ..
...
        server.stop();
        webServer.stop();
        conn.close();
        System.out.println("Server is STOPPED");
    }
}

blog_post_image_inline

References

  1. H2 database in memory mode cannot be accessed by Console
  2. Official H2 Tutorial
  3. H2 Server methods
  4. GitHub Sources


comments powered by Disqus