View content of H2 or HSQLDB in-memory database
Solution 1
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.
Solution 2
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.
Solution 3
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
Solution 4
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
Solution 5
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.
jplandrain
Updated on July 08, 2022Comments
-
jplandrain almost 2 years
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.
-
Petro Semeniuk about 11 yearsorg.hsqldb.util.DatabaseManagerSwing.main(new String[] { "--url", URL, "--user", USERNAME, "--password", PASSWORD});
-
hshib over 10 yearsI 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: h2database.com/javadoc/org/h2/tools/Server.html
-
Mike R over 9 yearsLike hman mentioned, latest version does not accept "true" parameter so just remove it:
<constructor-arg value="-web,-webAllowOthers,-webPort,8082"/>
-
Jor over 9 yearsNew versions follow separate argument convention of
Server.createTcpServer("-tcpPort" ,"9092", "-tcpAllowOthers")
Server.createWebServer("-webPort", "8082", "-tcpAllowOthers")
-
xnopre about 9 yearsIn pure Java, you have to remove the "true" parameter, and separate the parameters like this :
Server.createWebServer("-web", "-webAllowOthers", "-webPort", "8082").start();
-
Odysseus over 8 yearsComment for those who are using Spring Data - you can get connection from ApplicationContext in this way: ((DataSource)context.getBean("dataSource")).getConnection()
-
user2754985 almost 8 yearsWith Spring Boot you can simply set "h2.console.enabled: true" and "h2.console.path: h2-console" in your application.properties file.
-
yuranos almost 8 years@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?
-
weberjn about 7 yearsthis is also great in a JUnit TestWatcher @Rule
-
borjab almost 7 yearsIt even works if you lauch it with "evaluateExpression" in intelliJ while debugging. (locks the rest of watchs until stopped)
-
Oleksandr Papchenko over 6 yearsWas not working for me
Server webServer = Server.createWebServer("-web,-webAllowOthers,true,-webPort,8082").start();
-
rogerdpack over 3 yearsHere's some explanation of the parameters: stackoverflow.com/a/3076005/32453
-
Tom over 3 yearsI auto-wired the
DataSource
instance into my test case and added a method with bodyorg.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)! -
rogerdpack over 3 yearsAccording to the docs, you can use a "manager" with any JDBC connection, so this would work for H2 as well :) hsqldb.org/doc/2.0/util-guide/dbm-chapt.html