Allen Chee

About Allen Chee

Allen is a software developer working in the banking domain. Apart from hacking code and tinkering with technology, he reads a lot about history, so that mistakes of the past need not be repeated if they are remembered.

Embedding HSQLDB server instance in Spring

I was using XAMPP happily for development until I had to host it somewhere accessible via the Internet for the client to test and use. I have a VPS that only has 384 RAM, and needing to find a way fast, I decided to install XAMPP into the VPS. Because of the low RAM, when MySQL was running, Tomcat failed to start, even though the initial Java heap size was set to 64m. I managed to host the site temporarily in Jelastic, before moving to OpenShift.

I toyed at the idea of combining the database and application server instances in 1 JVM, to reduce RAM usage (compared to running MySQL + Tomcat). After searching the Internet, I came across several articles on running HSQL server instances together with Tomcat. No doubt I have to update my site to be compatible with HSQL first, but as a POC (proof-of-concept) attempt, I decided to explore the feasibility of running the HSQL server instance in a Spring container.

There are several reasons to run the HSQL server just like a bean in Spring:

1. All-in-one configuration. Everything that is needed to be configured is done in Spring. There are examples in the Net to run the HSQL instance alongside Tomcat, but this requires adding stuff to Tomcat (see links below).

2. Application server independence. ‘Theoretically’ (in quotes as I successfully tested this in Tomcat only), since everything is done in Spring, there’s no or little that needs to be configured in the appserver.

The HSQL server ‘bean’ is also meant to launch an instance in network mode (not in-process e.g. mem or file). Some reasons for this:

1. ‘mem’ in-process access is the fastest, but is not persistent. There are other means to initiate a ‘mem’ data source using Spring’s spring-jdbc tags, which is a better approach.

2. ‘file’ in-process access is persistent, but like ‘mem’, it can only be accessed within the Java process.

3. Network mode (hsql) is both persistent and accessible using external JDBC client tools. This is useful for troubleshooting and verification.

After reading HSQLDB’s documentation, here’s the code that does the HSQL server instance bean lifecycle management:

package org.gizmo.hsql.spring;

import java.io.IOException;
import java.util.Properties;

import org.hsqldb.Server;
import org.hsqldb.persist.HsqlProperties;
import org.hsqldb.server.ServerAcl.AclFormatException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.context.SmartLifecycle;

public class HyperSqlDbServer implements SmartLifecycle
{
 private final Logger logger = LoggerFactory.getLogger(HyperSqlDbServer.class);
 private HsqlProperties properties;
 private Server server;
 private boolean running = false;

 public HyperSqlDbServer(Properties props)
 {
  properties = new HsqlProperties(props);
 }

 @Override
 public boolean isRunning()
 {
  if(server != null)
   server.checkRunning(running);
  return running;
 }

 @Override
 public void start()
 {
  if(server == null)
  {
   logger.info('Starting HSQL server...');
   server = new Server();
   try
   {
    server.setProperties(properties);
    server.start();
    running = true;
   }
   catch(AclFormatException afe)
   {
    logger.error('Error starting HSQL server.', afe);
   }
   catch (IOException e)
   {
    logger.error('Error starting HSQL server.', e);
   }
  }
 }

 @Override
 public void stop()
 {
  logger.info('Stopping HSQL server...');
  if(server != null)
  {
   server.stop();
   running = false;
  }
 }

 @Override
 public int getPhase()
 {
  return 0;
 }

 @Override
 public boolean isAutoStartup()
 {
  return true;
 }

 @Override
 public void stop(Runnable runnable)
 {
  stop();
  runnable.run();
 }
}

The abridged Spring configuration:

    server.database.0=file:d:/hsqldb/demobase
    server.dbname.0=demobase
    server.remote_open=true
    hsqldb.reconfig_logging=false

 
Sample output when starting Spring in Tomcat:

[Server@1e893ae]: [Thread[pool-2-thread-1,5,main]]: checkRunning(false) entered
[Server@1e893ae]: [Thread[pool-2-thread-1,5,main]]: checkRunning(false) exited
[Server@1e893ae]: Initiating startup sequence...
[Server@1e893ae]: Server socket opened successfully in 7 ms.
Sep 27, 2012 9:26:23 AM org.hsqldb.persist.Logger logInfoEvent
INFO: checkpointClose start
Sep 27, 2012 9:26:23 AM org.hsqldb.persist.Logger logInfoEvent
INFO: checkpointClose end
[Server@1e893ae]: Database [index=0, id=0, db=file:d:/hsqldb/demo
base, alias=demobase] opened sucessfully in 442 ms.
[Server@1e893ae]: Startup sequence completed in 451 ms.
[Server@1e893ae]: 2012-09-27 09:26:23.395 HSQLDB server 2.2.8 is online on port
9001
[Server@1e893ae]: To close normally, connect and execute SHUTDOWN SQL
[Server@1e893ae]: From command line, use [Ctrl]+[C] to abort abruptly

 
Related Links:

 
Reference: Embedding HSQLDB server instance in Spring from our JCG partner Allen Julia at the YK’s Workshop blog.

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you two of our best selling eBooks for FREE!

JPA Mini Book

Learn how to leverage the power of JPA in order to create robust and flexible Java applications. With this Mini Book, you will get introduced to JPA and smoothly transition to more advanced concepts.

JVM Troubleshooting Guide

The Java virtual machine is really the foundation of any Java EE platform. Learn how to master it with this advanced guide!

Given email address is already subscribed, thank you!
Oops. Something went wrong. Please try again later.
Please provide a valid email address.
Thank you, your sign-up request was successful! Please check your e-mail inbox.
Please complete the CAPTCHA.
Please fill in the required fields.

One Response to "Embedding HSQLDB server instance in Spring"

  1. Marcelo says:

    did you post de source code? the spring configuration.

Leave a Reply


+ 9 = eleven



Java Code Geeks and all content copyright © 2010-2014, Exelixis Media Ltd | Terms of Use | Privacy Policy | Contact
All trademarks and registered trademarks appearing on Java Code Geeks are the property of their respective owners.
Java is a trademark or registered trademark of Oracle Corporation in the United States and other countries.
Java Code Geeks is not connected to Oracle Corporation and is not sponsored by Oracle Corporation.
Do you want to know how to develop your skillset and become a ...
Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you two of our best selling eBooks for FREE!

Get ready to Rock!
You can download the complementary eBooks using the links below:
Close