How to receive external requests with HSQLDB instance
In HSQLDB 1.8.1 and above, it is possible to deploy HSQLDB in production AND run an instance of HSQLDB server to serve external requests.
This makes it easy to maintain your production data and make updates when necessary.
- Assumption: Before we begin, I am assuming you already have HSQLDB setup and deployed to production.
- ie. If you use spring for datasource configuration, then I expect something like this in your application context:
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="org.hsqldb.jdbcDriver"/>
<property name="url" value="jdbc:hsqldb:file:/cust/<your account name>/home/<your data file>"/>
<property name="username" value="<your hsqldb username>"/>
<property name="password" value="<your hsqldb password>"/>
</bean>
- Instruction: The goal is just to instantiate/configure the HSQLDB external server. Here is an example of how to start an external server using Spring.
- In the spring configuration file:
<bean id="systemDao" class="<your package>.SystemJdbcDao">
<property name="dataSource" ref="dataSource"/>
</bean>
<bean id="hsqldbServer" class="<your package>.HsqldbManager">
<constructor-arg value="<url to your datasource>"/>
<constructor-arg value="<your database name>"/>
<constructor-arg value="3456"/> <!-- the port you want to listen to external requests on -->
<property name="systemDao" ref="systemDao"/>
</bean>
- And the java code for HsqldbManager & SystemDao defined above:
import org.hsqldb.Server;
import <your package>.SystemDao;
public class HsqldbManager {
private Server _server;
private SystemDao _systemDao;
public void setSystemDao(SystemDao dao) {
_systemDao = dao;
}
public HsqldbManager(String path, String name, int port) {
_server = new Server();
_server.setSilent(true); // VERY IMPORTANT, see explanation
_server.setPort(port);
_server.setDatabasePath(0, path);
_server.setDatabaseName(0, name);
_server.start();
}
public void exit() {
_systemDao.shutdown(); // VERY IMPORTANT, see explanation
_server.stop();
_server.shutdown();
}
}
import org.springframework.jdbc.core.support.JdbcDaoSupport;
public class SystemJdbcDao extends JdbcDaoSupport implements SystemDao {
public void shutdown() {
getJdbcTemplate().execute("shutdown");
}
}
- Finally, get your application to call the exit method for cleanup on stop/undeploy by including the following code in your servlet's destroy() method:
public void destroy() {
// close off database on destroy
HsqldbManager dbManager = (HsqldbManager) getWebApplicationContext()
.getBean("hsqldbServer");
if (dbManager != null) {
dbManager.exit();
}
super.destroy();
}
- Explanation: The above changes will start an external server that you can connect to remotely on port 3456. Here are several comments on how it hangs together.
- When HSQLDB detects that the external server instance is requesting from the same url as the in-process HSQLDB server, it will serve both servers from the same datasource. So it is very important the url is identical in both cases, if your url is different, then you may get a "database is already in use".
- Setting the server to run in silent mode prevent the server from logging massively to the server log. Otherwise, the log will grow very large very fast, to the extent where attempts to view the log using SiteWinder will freeze your browser. (and everyone's browser if you are on a shared plan)
- It is very important that you execute the "Shutdown" command (from SystemDao) when the application is destroyed. Otherwise, your database will still retain locks from previous deployment (which you can clean up by physically removing the .lck file) and the socket your external server was using will still be bound (which can only be freed with a Tomcat bounce).
If you have any problems following this guide, just post a question and we can compile a complete Q&A section.
