Category Archives: Code

OpenOffice 3.3 with HSQLDB via JDBC

This blog has moved to!

I had developed an application that stored some metrics in a HyperSQL database for offline processing and reporting with OpenOffice.

It seemed simple enough – OpenOffice would connect to the database via JDBC, with the JDBC URL and driver class..trouble was, no matter what I did, the driver class would not be recognised!

After endlessly searching on the web for a solution, I realised the piece of the puzzle I was missing – the version of the hsqldb jar file. The version 2.0.0 jar appears to be incompatible with OpenOffice 3.3 – Reverting back to solves the problem, and the JDBC driver class is recognised.

So I’ll publish the process in full here, just in case anyone else runs into the same problem!


  • OpenOffice 3.3.0 (The latest stable at the time of writing)
  • hsqldb-
  • An existing hsql database (created using hsqldb jars)

OpenOffice Configuration

Despite OpenOffice using hsqldb for it’s internal databases, the driver is not natively available for JDBC.

Under Preferences > > Java, ensure that a JRE is registered. Open up the classpath dialog, and add an archive entry for the hsqldb jar

OpenOffice Java Config

OpenOffice Java and Classpath Configuration

This is basically where I had been running into trouble. My application originally used hsqldb version 2.0.0 with Hibernate. Despite the driver class being the same (org.hsqldb.jdbcDriver), it seems OpenOffice would not recognise this version of hsqldb.

Connect OpenOffice to Database

Using OpenOffice’s Database, select Connect to an existing database (using JDBC).

Enter the JDBC Connection details:

  • URL: The JDBC URL minus the ‘jdbc:’ – should be of the form hsqldb:/path/to/database – also append ;default_schema=true to the end.
  • JDBC driver class is: org.hsqldb.jdbcDriver

Note that ‘database’ is the collective database name for the files making up the hsqldb instance (e.g. database.log,, database.script).

OpenOffice JDBC Connection

HSQLDB JDBC Connection

Click ‘Next, enter the default username of sa (No password required).

Accept the defaults for registering the database, choose a location to save and you’re done!

If anyone has an explanation of the OpenOffice incompatibility, please leave a comment!


Continuous Deployment: Deploying to Glassfish with Maven and TeamCity

This blog has moved to!

A later stage of the continuous integration process – continuous deployment. The sooner we can deploy a tested and verified piece of software, the better!

Here I’m describing an automated deployment process that uses Maven to deploy to a Glassfish application server. TeamCity facilitates the build and test stages, with an additional deployment of the packaged web application. Using the glassfish plugin for maven 2, we can integrate the application server deployment into the continuous integration cycle, and provide a constantly up-to-date development/test environment.


I’ll create a new domain from scratch for the maven apps, using the default port values (i.e. admin port 4848, http port 8080), but setting the admin password and master password.

In setting up the glassfish domain we generate a password file so that we are not storing any passwords in plain text – such as in the pom or settings.xml

cd ${glassfish.home}/bin
./asadmin create-domain --savemasterpassword=true my-apps

the –savemasterpassword switch generates an encrypted ‘master-password’ file in the domains/my-apps directory.


Maven profiles makes it easy to have machine-specific variables so that moving to other platforms in the future is straightforward.

On the on the host machine I’ve created the file ~/.m2/settings.xml.

<?xml version="1.0" encoding="UTF-8"?>


This gives us the parameters for the glassfish instance that we will use in our pom.

Update: I found that while the above works for v3.1, my dev machine’s glassfish v3.0.1 needed to reference glassfish home one directory deeper:


In the project pom.xml, we define a profile for glassfish deployment:

        <name> Maven2 Repository</name>

The repository for the glassfish plugin repository is specified within the profile since its not part of the larger project in this case.

As you see the variables from the local settings.xml are used for the glassfish config.


The TeamCity setup needs to include two things in the maven2 runner config:

  • Glassfish goals
  • Profile parameters

TeamCity - maven runner configuration

The glassfish goals that are used should be able to start the domain if it isn’t running, and replace the application.

The ‘redeploy’ goal would allow a hot-swap deployment, if for example we were running other applications on the domain.

See the plugin page ( for more info.


So now this process provides us with continuous deployment – a commit will be built, tested and deployed automatically, allowing changes to the software to be seen and used almost immediately!

How to programmatically ‘ping’ a port using Telnet

As part of a larger application, I needed to determine if particular services were running on remote servers – i.e. if a particular port on the server was accessible.

I wrote this utility class to perform this function, using the telent client supplied by Apache Commons-net.

import org.apache.log4j.Logger;


 * Execute a telnet connection to determine if the server and port are accessible.
 * @author will
public final class TelnetExecutor implements Executor {
    private static final Logger LOG = Logger.getLogger(TelnetExecutor.class);
    private static final int PORT_MIN = 0;
    private static final int PORT_MAX = 65535;

    private final String _server;
    private final int _port;

    public TelnetExecutor(final String server, final int port) {
        if (server == null || server.trim().length() == 0) {
            LOG.warn("Server name has a length of zero. Status result will fail.");
            _server = null;
        } else {
            _server = server;

        if (port < PORT_MIN || port > PORT_MAX) {
            LOG.warn("Server port is out of bounds. Status result will fail.");
            _port = -1;
        } else {
            _port = port;

     * Determine the result of the port request.
     * <p/>
     * A return value of <code>Success</code> indicates successful connection, <code>Error</code> indicates a 
     * configuration problem, <code>Fail</code> indicates a failed connection, and <code>Unknown</code> indicates an 
     * unexpected problem.
     * @return A {@link Status} representing the result.
    public Status getResult() {
        if (_server == null || _port < 0) {
            return Status.ERROR;

        Status status;
        final TelnetClient telnetClient = new TelnetClient();
        try {
            telnetClient.connect(_server, _port);
            status = Status.SUCCESS;
        } catch (ConnectException ce) {
  "Could not connect to server '" + _server + "' _port " + _port);
            status = Status.FAIL;
        } catch (UnknownHostException e) {
            LOG.error("Unknown host: " + _server);
            status = Status.ERROR;
        } catch (IOException e) {
            LOG.error("Error connecting to server: " + _server + " - " + e.getMessage(), e);
            status = Status.UNKNOWN;

        return status;

The Status objects are an enumeration.

I can test this using variations on this [integration] JUnit testcase:

public void shouldConnectToExamplePort80() {
    Assert.assertSame(Status.SUCCESS, new TelnetExecutor("", 80).getResult());

Whoops! IntelliJ broke my JAAS config…

One of IntelliJ’s features is maintenance of copyright headers in source and text files. The update of the header can be selected to run upon commit (so that your commited code always has the latest copyright!).

* - 11 Nov 2010
* Copyright (c) 2010 - My Organisation

Kind of cool and useful depending on your coding standards.

I’m updating a module of ours that uses Java Authentication and Authorization service (JAAS) to hook into LDAP.

The JAAS configuration file, unlike most properties and config files, uses Java-style block comments (such as the example above), and not hash comments. IntelliJ has recognized my jaas.config as a properties file, and upon committing to source control, inserts the copyright as a hash-comment block:

# jaas.test.config - 11 Nov 2010
# Copyright (c) 2010 - My Organisation

The result – My tests fail because JAAS config loader throws an exception!


I hoped that I could somehow indicate that a JAAS config file should be matched to the same style used in Java files.

I can set up a file type for the file easily enough:

But I can’t find a way to apply a copyright template to the file type…damn! The copyright templates seem static.

We could, in theory, apply one of those static file types to out JAAS config file; Add “jaas*.config” to Java or Javascript file types – but you’d have to be willing to put up with the errors and warnings!

For now, using the custom file type will do – IntelliJ doesn’t modify the copyright since it doesn’t know what the file is anymore. At least it won’t update to the wrong format by accident this way..!

Unit Testing XML – Evaluating Diffs

I am trying to test code that merges two XML files. In the unit test that I am attempting to implement, I want to compare the difference between the merge result and one of the XML files (the larger of the two).

This is a description of the file contents:

  • The right-hand XML file has 13 elements underneath the root, while the left-hand file has 4.
  • Two elements in both files are equivalent, so the left-hand version is discarded.

What I’m expecting is that the two remaining elements in the left hand file are merged into the resultant XML, so that in reference to the right-hand file, the merged content has two additional elements underneath the root.


I’ve used XMLUnit previously to compare generated XML with expected output. In this case however, I am more concerned with evaluating the differences between the source and resultant XML.

XMLUnit has a Diff object – org.custommonkey.xmlunit.Diff – which I realised, after some investigation, doesn’t quite offer a diff in the traditional UNIX diff/patch command sense. It evaluates a document in terms of being identical, similar or different, and holds a message describing the first difference encountered.

This has some use of course, my testcase could look something like this:

Diff myDiff = new Diff(originalXml, mergedXml);
assertFalse("Expected differences in XML", myDiff.identical());

The message contained in myDiff here is:

[different] Expected number of child nodes '13' but was '17' - comparing  at /Group[1] to  at /Group[1]

I’m not quite contented with that as a robust unit test. I want to ensure that the two files have specific differences. When I think of comparing two files with a diff, I’m picturing a visual diff:

..and the concept of a patch – that the set of +/- lines differences are collected and made available for inspection/verification.

XMLUnit does have some alternatives that, while not exactly what I’m looking for, I could use and are worth discussing:


DetailedDiff is an extension of Diff, which will give me a list of all the Differences in the comparison.

final DetailedDiff diff = new DetailedDiff(myDiff);
assertTrue("Expected a difference in child nodes", 

..will assert that the comparison has resulted in a mismatch in the number of children between the two XMLs (Javadoc). A few of those assertions could describe the expected differences between the XMLs.

Counting Nodes

CountingNodeTester is another alternative that allows us to assert the total number of elements contained in the XML:

CountingNodeTester countingNodeTester = 
    new CountingNodeTester(38);
    countingNodeTester, Node.ELEMENT_NODE);

Or alternatively, comparing the counts of the two XMLs (7 additional nodes):

final int countOriginal = 31;
final int countMerged = countOriginal + 7;
CountingNodeTester countingNodeOriginal = 
    new CountingNodeTester(countOriginal);
CountingNodeTester countingNodeMerged = 
    new CountingNodeTester(countMerged);
    countingNodeOriginal, Node.ELEMENT_NODE);
    countingNodeMerged, Node.ELEMENT_NODE);


Finally we could also use XPath evaluations to assert the existence or lack of certain structures:

    "/Group/PageContainer/External-Group/File/@Location", mergedXml);

So while XMLUnit gives us a pretty good toolset for XML comparisons, I’m still wondering if there’s a more diff-oriented tool I could use.


I found java-diff-utils, which looks like it could be a good option for handling diffs the way I’m imagining. Lets have a go!

The sample code on the website shows us the basic usage:

// Compute diff. Get the Patch object. Patch is the container for computed deltas.
Patch patch = DiffUtils.diff(original, revised);

..where original and revised are List objects.

The Patch object gives us a list of Deltas, containing the ‘original’ and ‘revised’ segments. Perfect!

This matches what we have in the image (disregarding the empty elements formatted differently)

Assert.assertEquals(1, patch.getDeltas().size());

The Delta itself contains a list of text lines, so we could potentially verify the list of strings manually.


..outputs something like:

, <External-Group>
, <File Location="/Sites/centre/dcita/site.xml">, </File>
, </External-Group>
, </PageContainer>
, <PageContainer>
, <Page Title="">
, <File Location="/Content/centre/dcita/index.xml">, </File>
, <Description>Migrated from previous CMS1 Homepage, </Description>
, </Page>
, </PageContainer>

A cleaner scenario could involve saving the expected delta text to a file (/src/test/resources/merged_xml.diff), and comparing the file contents to the lines in the actual patch delta

final String target = resourceToString("/merged_xml.diff");
final String actual = 
Assert.assertEquals(target, actual);

This needs a couple of helper functions to load the diff file into a String, and convert the delta List into a String also.

    private String resourceToString(String filename) {
        StringBuffer lines = new StringBuffer();
        String line = "";
        try {
            BufferedReader in = new BufferedReader(
                new FileReader(getClass().getResource(filename).getPath()));
            while ((line = in.readLine()) != null) {
        } catch (IOException e) {
        return lines.toString();

    private String listToString(List list) {
        StringBuffer buff = new StringBuffer(list.size());
        for (Object o : list) {
            buff.append(((String) o).trim());
        return buff.toString();


So in the end, my test case ends with combining the above diff utils snippets:

        Patch patch = DiffUtils.diff(original, revised);
        Assert.assertEquals(1, patch.getDeltas().size());

        final String target = resourceToString("/merged_xml.diff");
        final String actual = 
        Assert.assertEquals(target, actual);

Unit testing JAXB marshalling and XJC-generated classes

JAXB – the Java Architecture for Xml Binding provides a simple way of mapping XML to POJOs. It give the ability of painlessly marshalling and unmarshalling objects to and from XML.

JAXB’s usefulness is enhanced by the ‘xjc’ tool that is included in the SDK, which converts an XML schema to a set of Java classes.

A portion of the XML schema (which itself is generated from an XML file):

  <xs:complexType name="MetaType">
      <xs:attribute type="xs:string" name="Name" use="optional"/>
      <xs:attribute type="xs:string" name="Scheme" use="optional"/>
      <xs:attribute type="xs:string" name="Value" use="optional"/>

Because the XML schema I’m working with has been auto-generated from sample XMLs and not hand-written (and fairly complex!), I’d like to ensure that the XML coming out of the marshalling is what I expect.

The following JUnit 4 test creates and populates the object, then verifies that the object is marshalled to XML properly:

public class MetaTypeTest {
    private final String _xmlHeader = "<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?>";

    public void shouldMarshalAllAttributes() throws Exception {
        final MetaType type = new MetaType();

        // Can't be certain @XmlRootElement annotation has been generated, so wrap obj in JAXBElement
        final JAXBElement element = new JAXBElement(new QName("Meta"), MetaType.class, type);

        // Marshal to output stream
        JAXBContext context = JAXBContext.newInstance(MetaType.class);
        final ByteArrayOutputStream outStream = new ByteArrayOutputStream();
        context.createMarshaller().marshal(element, outStream);

        final String xmlContent = "";
        Assert.assertEquals(_xmlHeader + xmlContent, outStream.toString());


Monitoring OSCache statistics with JMX

Given an existing web application that uses OSCache, we can pretty easily add support for JMX monitoring.

A couple of things first:

  • We are using Sun’s Glassfish application server
  • The application is not yet spring-enabled
  • The project uses maven for dependancy management

Since we are using Glassfish, we already have an MBean server and connector, so we don’t have to worry about configuring those parts of the stack. (An MBean is a Managed JavaBean)

As for Spring support, we are restricting its introduction to a small aspect of the application (for now!), so we are minimizing the impact on the rest of the application.

What we need to do:

  • Modify and add an event listener
  • Add a dependancy on spring-web
  • Add a spring context for the JMX exporter
  • Modify web.xml to load the spring context

OSCache event listener

Firstly, we need to set a listener for OSCache that will publish the statistics we are interested in.

In, there is a section for event listeners. We want to implement a statistical listener:


Spring dependancies

If you’re using maven, add a dependency on spring-web. This has dependancies on spring-core, spring-context and spring-beans (At the time of writing, 3.0.2.RELEASE was the latest version in the maven repositories).

Otherwise download these manually and chuck them in your WEB-INF/lib.

Spring Context

I made a separate spring context file (cacheContext.xml) for the cache MBean exporter:

    <beans xmlns=""

        <!-- OSCache stats listener bean -->
        <bean id="statisticListener" class="com.opensymphony.oscache.extra.StatisticListenerImpl"/>

        <!-- Export the OSCache stats beans -->
        <bean id="exporter" class="org.springframework.jmx.export.MBeanExporter">
            <property name="beans">
                    <entry key="oscache-bean:name=Statistics" value="statisticListener"/>

The map in the exporter links the statistics bean to a key that will be visible in JConsole.

Note: The OpenSymphony link in the references below gives you code to expose an arbitrary JMX connector and port. This is unnecessary since we’re accessing via the application server.

Adding the Spring context loader

The web.xml then needs to be updated to be told to load the new cacheContext.xml.




JMX Clients

JDK 1.5 includes two JMX clients that you can use.

jconsole is the legacy monitoring application, which has been superseded by the fancy UI of jvisualvm.

Either application will allow you to monitor the statistics bean, however in jvisualvm you will need to install the plugin VisualVM-MBeans under Tools > Plugins. Meanwhile jconsole has an MBeans tab by default.

When your application is running, use one of these utilities to connect to the application server (either directly or via localhost:8686 for glassfish). Under the oscache-bean > Statistics, you should be able to see counts generated by cache attributes. Double-clicking the numbers will pop up a graph, and right-clicking will allow you to export the recorded values.

Graph of OSCache statistics