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.

Does this answer your question? [Inspect in memory hsqldb while debugging](https://stackoverflow.com/questions/2697341/inspect-in-memory-hsqldb-while-debugging) — rogerdpack, Oct 21 '20 at 18:46

score 60 · Accepted Answer · edited May 23 '17 at 12:18

60

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.

edited May 23 '17 at 12:18

Community

1
1

answered Sep 05 '11 at 14:27

Tomasz Nurkiewicz

311,858
65
665
652

2

I got error that "true" is invalid option. Did -webAllowOthers used to take parameter? With the latest H2 code, it does not take any parameter. Look at "main" method here: http://www.h2database.com/javadoc/org/h2/tools/Server.html – hshib Nov 13 '13 at 18:10
1

Like hman mentioned, latest version does not accept "true" parameter so just remove it: `` – Mike R Dec 31 '14 at 20:46
2

New versions follow separate argument convention of `Server.createTcpServer("-tcpPort" ,"9092", "-tcpAllowOthers")` `Server.createWebServer("-webPort", "8082", "-tcpAllowOthers")` – Jor Jan 16 '15 at 09:24
9

In pure Java, you have to remove the "true" parameter, and separate the parameters like this : `Server.createWebServer("-web", "-webAllowOthers", "-webPort", "8082").start();` – xnopre Feb 26 '15 at 10:50
2

With Spring Boot you can simply set "h2.console.enabled: true" and "h2.console.path: h2-console" in your application.properties file. – user2754985 Jun 21 '16 at 00:09
@Tomasz Nurkiewicz, from H2 docs I understood that both Web and Tcp servers are normally used for server mode. And, indeed, you start a TcpServer with a port specified. How can you access it with jdbc:h2:mem then? – yuranos Aug 02 '16 at 15:26
1

Was not working for me `Server webServer = Server.createWebServer("-web,-webAllowOthers,true,-webPort,8082").start();` – Oleksandr Papchenko Oct 18 '17 at 07:38

score 33 · Answer 2 · answered Sep 06 '11 at 05:09

33

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.

answered Sep 06 '11 at 05:09

Thomas Mueller

44,960
12
100
124

5

Comment for those who are using Spring Data - you can get connection from ApplicationContext in this way: ((DataSource)context.getBean("dataSource")).getConnection() – Odysseus Feb 15 '16 at 09:29
this is also great in a JUnit TestWatcher @Rule – weberjn Feb 28 '17 at 22:06
3

It even works if you lauch it with "evaluateExpression" in intelliJ while debugging. (locks the rest of watchs until stopped) – borjab Jul 18 '17 at 17:39
I auto-wired the `DataSource` instance into my test case and added a method with body `org.h2.tools.Server.startWebServer(dataSource.getConnection());`. Now evaluating that method opens browser. Note that this causes execution to sleep until you disconnect (top-left icon in browser)! – Tom Oct 29 '20 at 10:15

score 9 · Answer 3 · edited May 03 '13 at 17:43

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

score 4 · Answer 4 · edited Nov 03 '20 at 16:30

4

For HSQLDB, The following worked for me:

DatabaseManager.threadedDBM();

And this brought up the GUI with my tables and data once I pointed it to the right named in-mem database.

It is basically the equivalent of newing up a DatabaseManager (the non Swing variety), which prompts for connection details, and is set to --noexit)

I also tried the Swing version, but it only had a main, and I was unsure of the arguments to pass. If anyone knows, please post here.

Just because I searched for hours for the right database name: The name of the database is the name of your datasource. So try with URL jdbc:hsqldb:mem:dataSource if you have a data source bean with id=dataSource. If this does not work, try testdb which is the default.

edited Nov 03 '20 at 16:30

rogerdpack

50,731
31
212
332

answered Dec 05 '12 at 21:07

knownasilya

5,421
4
32
58

4

org.hsqldb.util.DatabaseManagerSwing.main(new String[] { "--url", URL, "--user", USERNAME, "--password", PASSWORD}); – Petro Semeniuk Feb 23 '13 at 02:28
Here's some explanation of the parameters: https://stackoverflow.com/a/3076005/32453 – rogerdpack Oct 27 '20 at 16:39

score 4 · Answer 5 · answered Sep 05 '11 at 16:08

With HSQLDB, you have several built-in options.

There are two GUI database managers and a command line interface to the database. The classes for these are:

org.hsqldb.util.DatabaseManager
org.hsqldb.util.DatabaseManagerSwing
org.hsqldb.cmdline.SqlTool

You can start one of the above from your application and access the in-memory databases.

An example with JBoss is given here:

http://docs.jboss.org/jbpm/v3.2/userguide/html/ch07s03.html

You can also start a server with your application, pointing it to an in-memory database.

org.hsqldb.Server

According to the docs, you can use a "manager" with any JDBC connection, so this would work for H2 as well :) http://hsqldb.org/doc/2.0/util-guide/dbm-chapt.html — rogerdpack, Nov 03 '20 at 16:26

score 3 · Answer 6 · answered Oct 29 '13 at 17:58

You can expose it as a JMX feature, startable via JConsole:

@ManagedResource
@Named
public class DbManager {

    @ManagedOperation(description = "Start HSQL DatabaseManagerSwing.")
    public void dbManager() {
        String[] args = {"--url", "jdbc:hsqldb:mem:embeddedDataSource", "--noexit"};
        DatabaseManagerSwing.main(args);
    }
}

XML context:

<context:component-scan base-package="your.package.root" scoped-proxy="targetClass"/>
<context:annotation-config />
<context:mbean-server />
<context:mbean-export />

score 3 · Answer 7 · answered May 19 '15 at 12:50

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 <mrsarm@gmail.com>
 */
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");
        }
    }
}

score 1 · Answer 8 · edited May 23 '17 at 11:33

1

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

edited May 23 '17 at 11:33

Community

1
1

answered Nov 15 '15 at 11:37

Grigory Kislin

12,805
7
98
154

score 1 · Answer 9 · answered Feb 23 '19 at 16:30

This is more a comment to previous Thomas Mueller's post rather than an answer, but haven't got enough reputation for it. Another way of getting the connection if you are Spring JDBC Template is using the following:

jdbcTemplate.getDataSource().getConnection();

So on debug mode if you add to the "Expressions" view in Eclipse it will open the browser showing you the H2 Console:

org.h2.tools.Server.startWebServer(jdbcTemplate.getDataSource().getConnection());

Eclipse Expressions View

H2 Console

score 0 · Answer 10 · answered Mar 09 '18 at 09:22

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>

score 0 · Answer 11 · answered Mar 27 '20 at 09:28

What about comfortably viewing (and also editing) the content over ODBC & MS-Access, Excel? Softwareversions::

H2 Version:1.4.196
Win 10 Postgres ODBC Driver Version: psqlodbc_09_03_0210
For Win7 ODBC Client: win7_psqlodbc_09_00_0101-x64.msi

H2 Server:

/*
For JDBC Clients to connect:
jdbc:h2:tcp://localhost:9092/trader;CIPHER=AES;IFEXISTS=TRUE;MVCC=true;LOCK_TIMEOUT=60000;CACHE_SIZE=131072;CACHE_TYPE=TQ
*/
public class DBStarter {
    public static final String BASEDIR = "/C:/Trader/db/";
    public static final String DB_URL = BASEDIR + "trader;CIPHER=AES;IFEXISTS=TRUE;MVCC=true;LOCK_TIMEOUT=10000;CACHE_SIZE=131072;CACHE_TYPE=TQ";

  static void startServer() throws SQLException {
        Server tcpServer = Server.createTcpServer(
                "-tcpPort", "9092",
                "-tcpAllowOthers",
                "-ifExists",
//                "-trace",
                "-baseDir", BASEDIR
        );
        tcpServer.start();
        System.out.println("H2 JDBC Server started:  " + tcpServer.getStatus());

        Server pgServer = Server.createPgServer(
                "-pgPort", "10022",
                "-pgAllowOthers",
                "-key", "traderdb", DB_URL
        );
        pgServer.start();
        System.out.println("H2 ODBC PGServer started: " + pgServer.getStatus());

    }
}

Windows10 ODBC Datasource Configuration which can be used by any ODBC client: In Databse field the name given in '-key' parameter has to be used.

View content of H2 or HSQLDB in-memory database

11 Answers11

Linked