How to shutdown HSQLDB from Ant

Martin Fowler is of the opinion that Ant is not good enough.

And now Spring has a Java configuration option in the works. So the anti-XML movement is in full swing :)

I’ve personally been happy with extending Ant with Beanshell scripts. Here’s one example. It is easy to start HSQLDB from the command line, but as far as I could figure, the only way to stop the database cleanly is to connect to it and issue the following pseudo-sql: “SHUTDOWN”

So I have this Ant snippet:

<target name="hsqldb-stop">
    <java classname="bsh.Interpreter" fork="true">
        <classpath refid="master-classpath"/>
        <arg line="hsqldb-stop.bsh"/>
    </java>
</target>

And this is how “hsqldb-stop.bsh” looks like:

import java.sql.*;

Class.forName("org.hsqldb.jdbcDriver");
String url = "jdbc:hsqldb:hsql://127.0.0.1/mydb";
Connection con = DriverManager.getConnection(url, "sa", "");
String sql = "SHUTDOWN";
Statement stmt = con.createStatement();
stmt.executeUpdate(sql);
stmt.close();

There. No main method, no compilation, no mess. In true scripting language fashion, even the variable type declarations can be skipped (I haven’t). In this case I had started HSQLDB in “network” mode (not embedded). Both the HSQLDB and Beanshell jars need to be in the “master-classpath”.

And yes, I know there is an Ant task for sql. Consider this just an example. But if you need to conditionally do stuff, Beanshell is perfect.

Update 2008-09-08: For those who are looking for the “right way” to shut down HSQLDB from Ant, here you go:

<target name="hsqldb-stop">
  <sql
    classpathref="master-classpath"
    driver="org.hsqldb.jdbcDriver"
    url="jdbc:hsqldb:hsql://127.0.0.1/mydb"
    userid="sa" password=""
    autocommit="true">SHUTDOWN</sql>
</target>

Update 2009-10-12: Also see this blog post by Frederic Daoud – it looks like HSQDB comes with a class called “org.hsqldb.util.ShutdownServer” that can be used instead.

Speaking for myself personally – I’m much more productive in “normal” Java rather than Groovy, Ruby etc. (yes maybe there is something wrong with me :) And I’ve found myself using Beanshell in all kinds of interesting ways – such as for example a batch file that will connect to a series of Oracle databases and check if they are alive.

Previous blog posts on how to do cool stuff with Ant: Jetty, Maven Integration, Tomcat.

Update 2006-12-20: Just to make a point in response to comment # 1 and discussion on TheServerside.com., here’s one way to get the script into the Ant file itself (and the db name also is passed as a parameter):

<target name="hsqldb-stop">
    <concat destfile="temp.bsh">
        <![CDATA[
            import java.sql.*;
            
            Class.forName("org.hsqldb.jdbcDriver");
            String url = "jdbc:hsqldb:hsql://127.0.0.1/" + bsh.args[0];
            Connection con = DriverManager.getConnection(url, "sa", "");
            String sql = "SHUTDOWN";
            Statement stmt = con.createStatement();
            stmt.executeUpdate(sql);
            stmt.close();
        ]]>
    </concat>
    <java classname="bsh.Interpreter" fork="true">
        <classpath refid="master-classpath"/>
        <arg line="temp.bsh ${db.name}"/>
    </java>
</target>

Not very pretty but it works. I guess I really don’t want to learn a new language :)