Inspect in memory hsqldb while debugging
Solution 1
HSQL is in memory, so when you say that you're connecting with SQLDB Database Manager, you're not - you are instead connecting to another database in the memory space of the SQLDB Database Manager, not the one in the memory space of the unit test. This is why the database in the SQLDB Database Manager is empty.
You can run HSQL as a server using org.hsqldb.Server
as described here.
Although the org.hsqldb.Server
class is typically used to start-up a seperate process, you could instantiate and configure it in your unit test, which should allow a remote process to connect and query the database.
Alternatively, you'll have to write some sort of dump functionality that is called from within your unit test as need be.
As an aside, using HSQL in unit tests is just proving your code works against HSQL, which is different to the actual database. This means you can get false positives and vice versa. The same thing can be achieved with a mocking API or better, save the database testing for some decent integration tests that works with the real database.
Solution 2
In your unit test or in the @Before
/ setUp()
method, you can add the following line to launch the HSQL Database Manager:
org.hsqldb.util.DatabaseManager.main(new String[] {
"--url", "jdbc:hsqldb:mem:testdb", "--noexit"
});
or for the improved Swing version with "more refinements" (but about same functionality)
org.hsqldb.util.DatabaseManagerSwing.main(new String[] {
"--url", "jdbc:hsqldb:mem:testdb", "--noexit"
});
The DB manager lets you inspect your schema and run SQL queries on the live in-memory database while the application is running.
Make sure to set a beakpoint or pause the execution one way or another if you want to check the state of your database at a specific line.
Solution 3
Run your unit test to a breakpoint, then in the Debug perspective of Eclipse, open the Display view (Window, Show View, Display) and enter
org.hsqldb.util.DatabaseManagerSwing.main(new String[] {
"--url", "jdbc:hsqldb:mem:testdb", "--noexit"
});
(as per dimdm's post), highlight it, right-click and choose Execute.
Solution 4
You could also use the DatabaseManagerSwing class included in [HSQLDB][1] passing to it an open connection, that allows you to see the state of the database in the transaction the connection is in.
DatabaseManagerSwing manager = new DatabaseManagerSwing();
manager.main();
manager.connect(connection);
manager.start();
Solution 5
The above accepted answer works perfectly only if the database name, username and password is untouched (testdb, SA, blank password).
For custom database name, username and password you will get the following exception
java.sql.SQLInvalidAuthroizationSpecException: invalid authorization specification - not found: SA
Then, you have to connect manually.
To connect directly to a non "default" DB use the following snippet
org.hsqldb.util.DatabaseManager.main(new String[] {
"--url", "jdbc:hsqldb:mem:yourdbname", "--noexit",
"--user", "dbusername", "--password", "dbpassword"
});
or for the improved Swing version
org.hsqldb.util.DatabaseManagerSwing.main(new String[] {
"--url", "jdbc:hsqldb:mem:yourdbname", "--noexit",
"--user", "dbusername", "--password", "dbpassword"
});
Before executing the above snippet update the following
- yourdbname - Update yourdbname with real database name
- dbusername - Update dbusername with your database username
- dbpassword - Update dbpassword with your database password
Comments
-
Albert almost 2 years
We're using hdsqldb in memory to run junit tests which operate against a database. The db is setup before running each test via a spring configuration. All works fine. Now when a tests fails it can be convinient to be able to inspect the values in the in memory database. Is this possible? If so how? Our url is:
jdbc.url=jdbc:hsqldb:mem:testdb;sql.enforce_strict_size=true
The database is destroyed after each tests. But when the debugger is running the database should also still be alive. I've tried connecting with the sqldb databaseManager. That works, but I don't see any tables or data. Any help is highly appreciated!
-
Albert about 14 yearsThanks for your response! I've figured it out and it works if you have hsqldb write to file and use that url to connect to it. I was using an older driver which was in my way. I agree that you should also test it against a real database which we also do, but having the sql queries beeing validated is more valuable then mocking it away I'd say.
-
dimdm almost 14 yearsNote that I omitted the "sql.enforce_strict_size=true" part of the JDBC URL, I'm unsure what the effect would be here...
-
Paul D. Eden almost 12 yearsWhen I do as you suggested the DatabaseManager starts, but freezes. I cannot do anything with it. If I force quit it, the debug session dies as well. I'm using Intellij Idea 11.1.2.
-
Paul D. Eden almost 12 yearsSee here (stackoverflow.com/questions/11435654/…) for a discussion on how to keep DatabaseManager from freezing.
-
jricher over 11 yearsNote that this doesn't work when running a remote debugging session on Tomcat.
-
dimdm over 11 years@jricher Tomcat doesn't prevent the GUI from launching. You will have to VNC into the remote host to use it though. But you are right, this doesn't allow the database manager and the in-memory DB to run on different VMs.
-
jricher over 11 yearsMore to the point, most tomcat instances that I use (and that probably many others use) are launched in a headless environment, which precludes the use of this technique.
-
yellavon over 10 yearsThe HSQL Database Manager freezes when executing SQL just like @dimdms' post.
-
kumetix about 8 yearsNote that I had to add to my
@Before
method the lineSystem.setProperty("java.awt.headless", "false")
in order to preventjava.awt.HeadlessException
from being thrown (even when on windows dev machine) -
deFreitas almost 8 years@yellavon configure on your IDE breakpoint to not freezes all threads
-
Dherik over 6 yearsThis tool is from 2010 and have a lot of limitations. Is there newer alternatives?
-
Sridhar Sarnobat about 6 yearsIn 2018, I don't have any freezing issues in HSQL version
2.4.0
via EclipseVersion: Oxygen Release (4.7.0) Build id: 20170620-1800
. So don't give up on this excellent solution without trying it out. -
Saurabh Oza over 5 yearsThe website mentioned in the answer is giving 404.
-
Nick Holt over 5 years@SaurabhOza fixed :-)
-
rogerdpack over 3 years@kumetix was that from within Tomcat, I presume yes?
-
rogerdpack over 3 yearstestdb isn't a special name. But user SA with blank password is, good hint!
-
rogerdpack over 3 years@Dherik not built-in with HSQLDB but you could use some other in memory manager that can use a JDBC connection I suppose...
-
rogerdpack over 3 yearsYou don't need a new thread for the DatabaseManagerSwing. And then you'll have your teat frozen at that sleep :| I do like the
sleep
call to keep it from freezing though, without having to modify the debugger :) -
rogerdpack over 3 yearsMine hangs when doing a watch of this from IntelliJ...
-
Wallace Howery almost 3 yearsIt's 2021, the tool still works and is helpful.
-
Wallace Howery almost 3 yearsWorks fine in Eclipse