View content of H2 or HSQLDB in-memory database

hsqldb h2 in-memory-database
jplandrain picture jplandrain · Sep 5, 2011 · Viewed 106.2k times · Source

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

Tomasz Nurkiewicz picture Tomasz Nurkiewicz · Sep 5, 2011

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.

Related questions

Create an in-memory database structure from an Oracle instance

I have an application where many "unit" tests use a real connection to an Oracle database during their execution. As you can imagine, these tests take too much time to be executed, as they need to initialize some Spring contexts, …

Read More
Which is better H2 or HSQLDB?

HSQLDB 2.0 is soon to be released. I wonder if it will outperform H2 since, as far as I know, most users prefer H2 than HSQLDB. I am interested in the MVCC support of HSQLDB 2.0. I have learned that MVCC on …

Read More
File based h2 persisted but not loaded in Spring Boot

I made a small application based on Spring Boot: spring-boot-starter-web spring-boot-starter-data-jpa The application has simply one domain class Post.java. Accordingly there is a RestController and a DAO. The data is supposed to be persisted in a file based hsql …

Read More