How to Use Embedded H2 With Web Console?

| Comments

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:

  • Run sample application
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
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");
    }
}
  • Output for previous run:
1
2
3
4
5
6
7
8
9
10
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.
  • We assume that h2 installation bundle (zip file) was downloaded (from http://www.h2database.com/html/download.html) beforehand and unpacked in some folder
  • Go into H2 “install/unpack” folder and run the next command cd h2/bin; java -cp h2-1.4.185.jar org.h2.tools.Console -web -browser
  • Now we should copy URL (from sample App output) and credentials sa/sa to login into Console
    • URL: jdbc:h2:tcp://192.168.1.100:9092/mem:mytest
    • Username: sa
    • Password: sa
  • Login window (opened from shell by org.h2.tools.Console)

Verify that our custom table contains just inserted values

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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");
    }
}
  • After this we can access to Web Console without running external service
  • Use any JDBC client (see IntelliJ IDEA as a sample below)
    • access to TCP Server

References

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

How to Fix Textmate OSX Yosemite Ruby 1.8 Issues?

| Comments

Textmate 2.0 is using Ruby 1.8 in own bundles. OSX Yosemite dropped Ruby 1.8 as default Ruby implementation, now it’s Ruby 2.0. Based on this all Textmate ruby-based (legacy) bundles do not work. It can be fixed in several steps:

  • Install Ruby 1.8.x via some Ruby version manager (e.g. rvm)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
$ rvm install 1.8
Warning! Requested ruby installation which requires another ruby available - installing ruby-1.8.7-p374 first.

Checking requirements for osx.
Installing requirements for osx.
Updating system...........
Installing required custom packages: homebrew/versions homebrew/versions.
Installing required packages: libtool, gcc48, libyaml, libksba, openssl098.....-Certificates in '/usr/local/etc/openssl/cert.pem' are already up to date.
Requirements installation successful.
Installing Ruby from source to: /Users/halyph/.rvm/rubies/ruby-1.8.7-p374, this may take a while depending on your cpu(s)...
ruby-1.8.7-p374 - #downloading ruby-1.8.7-p374, this may take a while depending on your connection...
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100 4150k  100 4150k    0     0   279k      0  0:00:14  0:00:14 --:--:--  326k
ruby-1.8.7-p374 - #extracting ruby-1.8.7-p374 to /Users/halyph/.rvm/src/ruby-1.8.7-p374....
ruby-1.8.7-p374 - #applying patch /Users/halyph/.rvm/patches/ruby/1.8.7/stdout-rouge-fix.patch.
ruby-1.8.7-p374 - #applying patch /Users/halyph/.rvm/patches/ruby/1.8.7/no_sslv2.diff.
ruby-1.8.7-p374 - #applying patch /Users/halyph/.rvm/patches/ruby/GH-488.patch.
ruby-1.8.7-p374 - #applying patch /Users/halyph/.rvm/patches/ruby/ssl_no_ec2m.patch.
ruby-1.8.7-p374 - #configuring...............................
ruby-1.8.7-p374 - #post-configuration.
ruby-1.8.7-p374 - #compiling....................................................
ruby-1.8.7-p374 - #installing.
ruby-1.8.7-p374 - #making binaries executable..
ruby-1.8.7-p374 - #downloading rubygems-2.0.14
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100  329k  100  329k    0     0   108k      0  0:00:03  0:00:03 --:--:--  108k
ruby-1.8.7-p374 - #extracting rubygems-2.0.14....
ruby-1.8.7-p374 - #removing old rubygems.........
ruby-1.8.7-p374 - #installing rubygems-2.0.14..................................|
ruby-1.8.7-p374 - #gemset created /Users/halyph/.rvm/gems/ruby-1.8.7-p374@global
ruby-1.8.7-p374 - #importing gemset /Users/halyph/.rvm/gemsets/ruby/1.8.7/global.gems..............................................................
ruby-1.8.7-p374 - #generating global wrappers........
ruby-1.8.7-p374 - #gemset created /Users/halyph/.rvm/gems/ruby-1.8.7-p374
ruby-1.8.7-p374 - #importing gemsetfile /Users/halyph/.rvm/gemsets/default.gems evaluated to empty gem list
ruby-1.8.7-p374 - #generating default wrappers........
ruby-1.8.7-p374 - #adjusting #shebangs for (gem irb erb ri rdoc testrb rake).
Install of ruby-1.8.7-p374 - #complete
WARNING: Please be aware that you just installed a ruby that is no longer maintained (2014-02-23), for a list of maintained rubies visit:

    http://bugs.ruby-lang.org/projects/ruby/wiki/ReleaseEngineering

Please consider upgrading to ruby-2.1.4 which will have all of the latest security patches.
Ruby was built without documentation, to build it run: rvm docs generate-ri
Checking requirements for osx.
Certificates in '/usr/local/etc/openssl/cert.pem' are already up to date.
Requirements installation successful.
Installing Ruby from source to: /Users/halyph/.rvm/rubies/ruby-1.8.7-head, this may take a while depending on your cpu(s)...
Cloning from git://github.com/ruby/ruby.git, this may take a while depending on your connection.
HEAD is now at 2a9ea11 * lib/webrick/server.rb (initialize): Initialize shutdown pipe here
Branch ruby_1_8_7 set up to track remote branch ruby_1_8_7 from origin.
Switched to a new branch 'ruby_1_8_7'
Copying from repo to src path...
ruby-1.8.7-head - #applying patch /Users/halyph/.rvm/patches/ruby/1.8.7/stdout-rouge-fix.patch.
ruby-1.8.7-head - #applying patch /Users/halyph/.rvm/patches/ruby/1.8.7/no_sslv2.diff.
ruby-1.8.7-head - #applying patch /Users/halyph/.rvm/patches/ruby/ssl_no_ec2m.patch.
ruby-1.8.7-head - #autoreconf.
ruby-1.8.7-head - #configuring...............................
ruby-1.8.7-head - #post-configuration.
ruby-1.8.7-head - #compiling...................................................|
ruby-1.8.7-head - #installing.
ruby-1.8.7-head - #making binaries executable..
ruby-1.8.7-head - #downloading rubygems-2.0.14
ruby-1.8.7-head - #extracting rubygems-2.0.14.....
ruby-1.8.7-head - #removing old rubygems.........
ruby-1.8.7-head - #installing rubygems-2.0.14..................................|
ruby-1.8.7-head - #gemset created /Users/halyph/.rvm/gems/ruby-1.8.7-head@global
ruby-1.8.7-head - #importing gemset /Users/halyph/.rvm/gemsets/ruby/1.8.7/global.gems..............................................................
ruby-1.8.7-head - #generating global wrappers........
ruby-1.8.7-head - #gemset created /Users/halyph/.rvm/gems/ruby-1.8.7-head
ruby-1.8.7-head - #importing gemsetfile /Users/halyph/.rvm/gemsets/default.gems evaluated to empty gem list
ruby-1.8.7-head - #generating default wrappers........
ruby-1.8.7-head - #adjusting #shebangs for (gem irb erb ri rdoc testrb rake).
Install of ruby-1.8.7-head - #complete
Please be aware that you just installed a ruby that requires        3 patches just to be compiled on an up to date linux system.
This may have known and unaccounted for security vulnerabilities.
Please consider upgrading to ruby-2.1.4 which will have all of the latest security patches.
Ruby was built without documentation, to build it run: rvm docs generate-ri
  • Update Textmate preferences

  • Verify “updated” Textmate settings via some ruby-based bundles, e.g. Bundles->Text->Duplicate Line