View content of H2 or HSQLDB in-memory database

Question

Is there a way to browse the content of an H2 or an HSQLDB in-memory database for viewing? For example, during a debugging session with Hibernate in order to check when the flush is executed; or to make sure the script that instantiates the DB gives the expected result.

Does it exist an addon or a library that you can embed with your code in order to allow this?

Please, mention which one you're talking about (H2 or HSQLDB) in case you have an answer specific to one of them.

Answer 1

I don't know why is it working fine at yours machines, but I had to spend a day in order to get it is working.

The server works with Intellij Idea U via url "jdbc:h2:tcp://localhost:9092/~/default".

"localhost:8082" in the browser alse works fine.

I added this into the mvc-dispatcher-servlet.xml

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close" depends-on="h2Server">
    <property name="driverClassName" value="org.h2.Driver"/>
    <property name="url" value="jdbc:h2:tcp://localhost:9092/~/default"/>
    <property name="username" value="sa"/>
    <property name="password" value=""/>
</bean>

<bean id="h2Server" class="org.h2.tools.Server" factory-method="createTcpServer" init-method="start" destroy-method="stop" depends-on="h2WebServer">
    <constructor-arg>
        <array>
            <value>-tcp</value>
            <value>-tcpAllowOthers</value>
            <value>-tcpPort</value>
            <value>9092</value>
        </array>
    </constructor-arg>
</bean>

<bean id="h2WebServer" class="org.h2.tools.Server" factory-method="createWebServer" init-method="start" destroy-method="stop">
    <constructor-arg>
        <array>
            <value>-web</value>
            <value>-webAllowOthers</value>
            <value>-webPort</value>
            <value>8082</value>
        </array>
    </constructor-arg>
</bean>

Answer 2

You can run H2 web server within your application that will access the same in-memory database. You can also access the H2 running in server mode using any generic JDBC client like SquirrelSQL.

UPDATE:

Server webServer = Server.createWebServer("-web,-webAllowOthers,true,-webPort,8082").start();
Server server = Server.createTcpServer("-tcp,-tcpAllowOthers,true,-tcpPort,9092").start();

Now you can connect to your database via jdbc:h2:mem:foo_db URL within the same process or browse the foo_db database using localhost:8082. Remember to close both servers. See also: H2 database in memory mode cannot be accessed by Console.

You can also use Spring:

<bean id="h2Server" class="org.h2.tools.Server" factory-method="createTcpServer" init-method="start" destroy-method="stop" depends-on="h2WebServer">
    <constructor-arg value="-tcp,-tcpAllowOthers,true,-tcpPort,9092"/>
</bean>
<bean id="h2WebServer" class="org.h2.tools.Server" factory-method="createWebServer" init-method="start" destroy-method="stop">
    <constructor-arg value="-web,-webAllowOthers,true,-webPort,8082"/>
</bean>

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close" depends-on="h2Server">
    <property name="driverClass" value="org.h2.Driver"/>
    <property name="jdbcUrl" value="jdbc:h2:mem:foo_db"/>
</bean>

BTW you should only depend on assertions and not on manual peeking the database contents. Use this only for troubleshooting.

N.B. if you use Spring test framework you won't see changes made by a running transaction and this transaction will be rolled back immediately after the test.

Answer 3

This is a Play 2 controller to initialize the H2 TCP and Web servers:

package controllers;

import org.h2.tools.Server;
import play.mvc.Controller;
import play.mvc.Result;

import java.sql.SQLException;

/**
 * Play 2 controller to initialize H2 TCP Server and H2 Web Console Server.
 *
 * Once it's initialized, you can connect with a JDBC client with
 * the URL `jdbc:h2:tcp://127.0.1.1:9092/mem:DBNAME`,
 * or can be accessed with the web console at `http://localhost:8082`,
 * and the URL JDBC `jdbc:h2:mem:DBNAME`.
 *
 * @author Mariano Ruiz <[e-mail]>
 */
public class H2ServerController extends Controller {

    private static Server h2Server = null;
    private static Server h2WebServer = null;

    public static synchronized Result debugH2() throws SQLException {
        if (h2Server == null) {
            h2Server = Server.createTcpServer("-tcp", "-tcpAllowOthers", "-tcpPort", "9092");
            h2Server.start();
            h2WebServer = Server.createWebServer("-web","-webAllowOthers","-webPort","8082");
            h2WebServer.start();
            return ok("H2 TCP/Web servers initialized");
        } else {
            return ok("H2 TCP/Web servers already initialized");
        }
    }
}

Answer 4

For H2, you can start a web server within your code during a debugging session if you have a database connection object. You could add this line to your code, or as a 'watch expression' (dynamically):

org.h2.tools.Server.startWebServer(conn);

The server tool will start a web browser locally that allows you to access the database.

Answer 5

I've a problem with H2 version 1.4.190 remote connection to inMemory (as well as in file) with Connection is broken: "unexpected status 16843008" until do not downgrade to 1.3.176. See Grails accessing H2 TCP server hangs

Answer 6

In H2, what works for me is:

I code, starting the server like:

server = Server.createTcpServer().start();

That starts the server on localhost port 9092.

Then, in code, establish a DB connection on the following JDBC URL:

jdbc:h2:tcp://localhost:9092/mem:test;DB_CLOSE_DELAY=-1;MODE=MySQL

While debugging, as a client to inspect the DB I use the one provided by H2, which is good enough, to launch it you just need to launch the following java main separately

org.h2.tools.Console

This will start a web server with an app on 8082, launch a browser on localhost:8082

And then you can enter the previous URL to see the DB