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!


A Quick Spring Security Lock-Down

This blog has moved to!

My new shiny web application is fantastically useful, but only to a certain group of people (i.e. my team), and should only be accessible by them.

So, before being able to put it into real production, I needed a security framework around it.

A legacy JAAS component of ours exists, but given my application was making use of the Spring framework, I compared Spring’s offering to the JAAS infrastructure.

Popular opinion seems to be that JAAS was build for J2SE, not J2EE, and is designed for things at a much ‘lower level’ than web applications, such as client-side applets rather than server-side applications.


First things first: Maven dependencies.

I’m using spring-webmvc 2.5.6, so I’d like to get security working with the application as it stands now – the latest pre-3.0 release of spring-security is 2.0.6-RELEASE:



The web context requires two things:

1. Context location


(we’ll create the security context in the next step)

2. Filter definition



The url-pattern will mean all requests pass through the filter (which will have more explicit criteria).

Security Context

Now we get to the real meat of the security layer!

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

<beans:beans xmlns=""

    <http auto-config="true">
        <intercept-url pattern="/**" access="ROLE_USER" />
        <http-basic />

        <password-encoder hash="md5"/>
            <user name="user" password="aabbccddeeff001122334455667788ff" authorities="ROLE_USER" />

Here we can see the configuration for http requests. The ‘auto-config’ sets the defaults (refer to the doco in the references), which are overridden by the contents of the tag. We’ll let in one user for now with the role ‘ROLE_USER’, defined in the authentication-provider section.

Including http-basic just puts the preference on using the basic HTTP prompt, but removing that line would use Spring’s default login page (with user/pass and ‘remember me’ checkbox).

And its done! Deploying the application and loading the page demands a login before progressing.


Future improvements might involve setting up a styled login page, hooking up an LDAP connection (but with restrictions).
Oh, and Selenium tests..


Spring Source, Spring Security Reference Documentation <>
Peter Mularien, 5 Minute Guide to Spring Security <>

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());