Using HSQLDB with the SQL Maven Plugin and jOOQ

This is a similar question like Using embedded database with Flyway and jOOQ in Maven for continuous integration, although not exactly the same as we're using the sql-maven-plugin, not Flyway.

The following Maven plugin configuration fails:

sql-maven-plugin

<plugin>
    <groupId>org.codehaus.mojo</groupId>
    <artifactId>sql-maven-plugin</artifactId>
    <version>1.5</version>

    <executions>
        <execution>
            <id>create-database</id>
            <phase>generate-sources</phase>
            <goals>
                <goal>execute</goal>
            </goals>
            <configuration>
                <driver>${db.driver}</driver>
                <url>${db.url}</url>
                <username>${db.username}</username>
                <password>${db.password}</password>
                <srcFiles>
                    <srcFile>src/main/resources/sql/create-db.sql</srcFile>
                </srcFiles>
            </configuration>
        </execution>
    </executions>
    <dependencies>
        <dependency>
            <groupId>org.hsqldb</groupId>
            <artifactId>hsqldb</artifactId>
            <version>${hsqldb.version}</version>
        </dependency>
    </dependencies>
</plugin>

jooq-codegen-maven

<plugin>
    <groupId>org.jooq</groupId>
    <artifactId>jooq-codegen-maven</artifactId>
    <version>${jooq.version}</version>

    <executions>
        <execution>
            <id>generate-hsqldb</id>
            <phase>generate-sources</phase>
            <goals>
                <goal>generate</goal>
            </goals>
            <configuration>
                <jdbc>
                    <driver>${db.driver}</driver>
                    <url>${db.url}</url>
                    <user>${db.username}</user>
                    <password>${db.password}</password>
                </jdbc>
                <generator>
                    <database>
                        <inputSchema>PUBLIC</inputSchema>
                    </database>
                    <target>
                        <packageName>com.example.db</packageName>
                        <directory>target/generated-sources/jooq-hsqldb</directory>
                    </target>
                </generator>
            </configuration>
        </execution>
    </executions>
</plugin>

The error is:

java.sql.SQLException: Database lock acquisition failure: lockFile: org.hsqldb.persist.LockFile@cc76d81d[file =C:\Users\Lukas\example.db.lck, exists=true, locked=false, valid=false, ] method: checkHeartbeat read: 2015-10-14 10:49:15 heartbeat - read: -9975 ms.
        at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
        at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
        at org.hsqldb.jdbc.JDBCConnection.<init>(Unknown Source)
        at org.hsqldb.jdbc.JDBCDriver.getConnection(Unknown Source)
        at org.hsqldb.jdbc.JDBCDriver.connect(Unknown Source)
        at org.jooq.util.GenerationTool.run(GenerationTool.java:208)
        at org.jooq.util.GenerationTool.generate(GenerationTool.java:180)
        at org.jooq.util.maven.Plugin.execute(Plugin.java:131)
        at org.apache.maven.plugin.DefaultBuildPluginManager.executeMojo(DefaultBuildPluginManager.java:132)
        at org.apache.maven.lifecycle.internal.MojoExecutor.execute(MojoExecutor.java:208)
        at org.apache.maven.lifecycle.internal.MojoExecutor.execute(MojoExecutor.java:153)
        at org.apache.maven.lifecycle.internal.MojoExecutor.execute(MojoExecutor.java:145)
        at org.apache.maven.lifecycle.internal.LifecycleModuleBuilder.buildProject(LifecycleModuleBuilder.java:116)
        at org.apache.maven.lifecycle.internal.LifecycleModuleBuilder.buildProject(LifecycleModuleBuilder.java:80)
        at org.apache.maven.lifecycle.internal.builder.singlethreaded.SingleThreadedBuilder.build(SingleThreadedBuilder.java:51)
        at org.apache.maven.lifecycle.internal.LifecycleStarter.execute(LifecycleStarter.java:120)
        at org.apache.maven.DefaultMaven.doExecute(DefaultMaven.java:347)
        at org.apache.maven.DefaultMaven.execute(DefaultMaven.java:154)
        at org.apache.maven.cli.MavenCli.execute(MavenCli.java:582)
        at org.apache.maven.cli.MavenCli.doMain(MavenCli.java:214)
        at org.apache.maven.cli.MavenCli.main(MavenCli.java:158)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:497)
        at org.codehaus.plexus.classworlds.launcher.Launcher.launchEnhanced(Launcher.java:289)
        at org.codehaus.plexus.classworlds.launcher.Launcher.launch(Launcher.java:229)
        at org.codehaus.plexus.classworlds.launcher.Launcher.mainWithExitCode(Launcher.java:415)
        at org.codehaus.plexus.classworlds.launcher.Launcher.main(Launcher.java:356)

How can one work around this issue?

As explained in that other question, where jOOQ is used with Flyway, there's a problem with HSQLDB locking the database on a class loader basis. The two maven plugins involved here do not share the same class loaders, so the second plugin cannot access the database that is embedded by the first class loader, unless:

SHUTDOWN IMMEDIATELY is called in the first script (but that is a bit ugly, as the script might be needed in other contexts as well)
The JVM is shut down (but that is very ugly in the context of Maven)

A workaround is to specify a second execution to the sql-maven-plugin, which explicitly calls the SHUTDOWN IMMEDIATELY command:

<execution>
    <id>create-database</id>
    <phase>generate-sources</phase>
    ...
</execution>

<execution>
    <id>shutdown-database</id>
    <phase>generate-sources</phase>
    <goals>
        <goal>execute</goal>
    </goals>
    <configuration>
        <driver>${db.driver}</driver>
        <url>${db.url}</url>
        <username>${db.username}</username>
        <password>${db.password}</password>

        <sqlCommand>SHUTDOWN IMMEDIATELY</sqlCommand>
    </configuration>
</execution>