Query the In-memory HSQLDB When Integrated With Hibernate

HSQLDB in-memory database is often used with Hibernate for testing, prototyping and even in production environment. With Hibernate, you can perform powerful database operations either using the ORM based APIs, HSQL or native SQL. As a developer, sometimes you want to peek the database itself to ensure the data model in the database instance matches the object model in your Java world. This post emphasizes on how you can execute native SQL query from HSQLDB Database Manager in this scenario, assuming Eclipse is the IDE in use.

Step#1: Set up
With the following maven and hibernate configuration (the full source can be downloaded here), you will be able to run the Hibernate against an in-memory version of HSQLDB instance.

Maven:

<dependency>
	   	<groupId>org.hibernate</groupId>
		<artifactId>hibernate-core</artifactId>
		<version>4.3.8.Final</version>
	</dependency>
<dependency>
		<groupId>org.hsqldb</groupId>
		<artifactId>hsqldb</artifactId>
		<version>2.3.2</version>
	</dependency>

Hibernate

 <session-factory>
    <property name="connection.url">jdbc:hsqldb:mem:playground</property>
    <property name="connection.username">sa</property>
    <property name="connection.password"></property>
    <property name="connection.driver_class">org.hsqldb.jdbcDriver</property>
    <property name="dialect">org.hibernate.dialect.HSQLDialect</property>
</session-factory>

Step#2: Access the in-memory database
It’s time to run and debug the application with Eclipse. When you run the App.java, the Hibernate console output tells that the Computer table has been created and populated. But is the data truly held by HSQLDB? To find out, let’s do a debug in the following steps:

  • Add a breakpoint on System.out.println in the App.init()
  • Start a debug session
  • When the breakpoint is hit, switch to the Debug perspective->Display view
  • Open and Display view and enter
    org.hsqldb.util.DatabaseManagerSwing.main(new String[] {
    “–url”, “jdbc:hsqldb:mem:playground”, “–noexit”
    });
    Note the url value should match the property value for connection.url in the hibernate.cfg.xml
  • Select the above entered code and execute it (right click)
  • This will bring up the HSQLDB Database Manager and you can do any SQL query from there.

    Note
    (1) To be able to access the same in-memory database instance, the HSQLDB Database Manager has to be started by the process that started the database instance. Launching the HSQLDB Database Manager from the Display view in fact starts the manager UI by the same process that debugs the main App (you should be able to see the HSQLDB Database Manager is running in a new thread in JConsole).
    (2) You can also put the above snippet into your code (especially if it is test code) to pragmatically open the manager.
    (3)Starting the manager from command line (java -cp hsqldb-2.3.2.jar org.hsqldb.util.DatabaseManagerSwing -url jdbc:hsqldb:mem:playground -noexit) will run it in a different process and hence you will fail to view the same database instance.

    Advertisements
    This entry was posted in Database, Java, Programming, Tool and Debug and tagged , , , . Bookmark the permalink.

    One Response to Query the In-memory HSQLDB When Integrated With Hibernate

    1. Pingback: Open H2 UI When Debugging in Eclipse | SW Notes

    Leave a Reply

    Fill in your details below or click an icon to log in:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out / Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out / Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out / Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out / Change )

    Connecting to %s