Maven, dbdeploy, HSQLDB without Ant

Believe me, integrating Maven and dbdeploy is a pain using Ant. For some weird reasons, dbdeploy ant task was causing maven build to crash.

So tried to integrate dbdeploy with maven without ant on my Snow Leopard –

You will need following jars –

dbdeploy-cli-3.0M2.jar
hsqldb-1.8.0.7.jar

keep above jars in dir “db” in same dir of pom.xml and keep your dbdeploy sql scripts in dir “db/scripts”. This will look like –

|-db
|----lib
|-------dbdeploy-cli-3.0M2.jar
|-------hsqldb-1.8.0.7.jar
|----scripts
|-------createSchemaVersionTable.sql
|-pom.xml

Next configure following Maven plugins –

            <plugin>
                <groupId>org.codehaus.mojo</groupId>
                <artifactId>sql-maven-plugin</artifactId>
                <version>1.1</version>
                <dependencies>
                    <dependency>
                        <groupId>hsqldb</groupId>
                        <artifactId>hsqldb</artifactId>
                        <version>1.8.0.7</version>
                    </dependency>
                </dependencies>
                <configuration>
                    <driver>${test.jdbc.driverClassName}</driver>
                    <url>${test.jdbc.url}</url>
                    <username>${test.jdbc.username}</username>
                    <password>${test.jdbc.password}</password>
                </configuration>
                <executions>
                    <execution>
                        <id>create-change-log</id>
                        <phase>process-test-sources</phase>
                        <goals>
                            <goal>execute</goal>
                        </goals>
                        <configuration>
                            <srcFiles>
                                <srcFile>${basedir}/db/scripts/createSchemaVersionTable.sql</srcFile>
                            </srcFiles>
                        </configuration>
                    </execution>
                    <execution>
                        <id>apply-db-changes</id>
                        <phase>process-test-classes</phase>
                        <goals>
                            <goal>execute</goal>
                        </goals>
                        <configuration>
                            <srcFiles>
                                <srcFile>${basedir}/target/consolidated_script.sql</srcFile>
                            </srcFiles>
                        </configuration>
                    </execution>
                </executions>
            </plugin>

createSchemaVersionTable.sql is part of dbdeploy distribution. Copy HSQLDB version.

And the following

            <plugin>
                <groupId>org.codehaus.mojo</groupId>
                <artifactId>exec-maven-plugin</artifactId>
                <version>1.1</version>
                <executions>
                    <execution>
                        <id>dbdeploy</id>
                        <phase>test-compile</phase>
                        <goals>
                            <goal>exec</goal>
                        </goals>
                        <configuration>
                            <executable>java</executable>
                            <workingDirectory>${basedir}/db</workingDirectory>
                            <commandlineArgs>
                                -cp ${test.hsql.jar}:lib/dbdeploy-cli-3.0M2.jar com.dbdeploy.CommandLineTarget -U ${test.jdbc.username} -D ${test.jdbc.driverClassName} -u ${test.jdbc.url.escaped} -d ${test.dbdeploy.dbms.type} -o ${basedir}/target/consolidated_script.sql -s scripts/
                            </commandlineArgs>
                        </configuration>
                    </execution>
                </executions>
            </plugin>

Following are the important maven properties that are to be set –

        <test.jdbc.driverClassName>org.hsqldb.jdbcDriver</test.jdbc.driverClassName>
        <test.jdbc.url>jdbc:hsqldb:file:${basedir}/target/testdb;shutdown=true</test.jdbc.url>
        <test.jdbc.url.escaped>jdbc:hsqldb:file:${basedir}/target/testdb\\;shutdown=true</test.jdbc.url.escaped>
        <test.jdbc.username>sa</test.jdbc.username>
        <test.jdbc.password></test.jdbc.password>
        <test.dbdeploy.dbms.type>hsql</test.dbdeploy.dbms.type>
        <test.hsql.jar>lib/hsqldb-1.8.0.7.jar</test.hsql.jar>

How it works –

We are using dbdeploy command line interface to achieve integration. The HSQLDB is used in a file mode which is evident by test.jdbc.url, since maven exec task executes by using bash semi-colon used in jdbc url needs to be escaped, thats the reason for defining test.jdbc.url.escaped property.

The sql-maven-plugin’s execution “create-change-log” is configured to run after phase “process-test-sources”, which creates change log table which is required by dbdeploy.

The exec-maven-plugin’s execution “dbdeploy” is configured to run after phase “test-compile”, executes dbdeploy command line interface, creates “${basedir}/target/consolidated_script.sql” which contains sql changes to be applied.

The sql-maven-plugin’s execution “apply-db-changes” is configured to run after phase “process-test-classes”, which applies consolidated change log script.

Hope it helps somebody..




Comments

  1. BibsNo Gravatar March 30th

    Comment Arrow

    Thanks , for this wonderful insight , but I hope you could further explain in a bit more detailed what ‘ to achieve by this Blog. Thanks again


  2. Jim C.No Gravatar July 18th

    Comment Arrow

    Well, by reading it, one can achieve tests that don’t require access to a live database other than hypersonic (hsqldb). Hypersonic is a small flexible and project-embeddable database. It can be used to achieve persistence to a temporary database since hibernate abstracts away the specific database type. It has other uses too. For example, one might use hypersonic to persist for any small desktop application and if you use hibernate, you get scalability to something larger like MySQL just by changing the dialect.


  3. Jim C.No Gravatar July 18th

    Comment Arrow

    Apparently dbdeploy executes sql scripts so he is using that to actually generate the database. and then probably later on, he will use some other widget to extract the hibernate POJOs.


  4. roshanNo Gravatar November 18th

    Comment Arrow

    Hi,
    I need your help on two things

    1) I get the following error
    [ERROR] Failed to execute goal org.codehaus.mojo:exec-maven-plugin:1.1:exec (dbdeploy) on project dbdeploy: Result of cmd.exe /X /C “java -cp lib/mysql-connector-java-5.1.8-bin.jar:lib/dbdeploy-cli-3.
    0M2.jar com.dbdeploy.CommandLineTarget -U root -D com.mysql.jdbc.Driver -u jdbc:mysql://localhost/test -d mysql -o target/consolidated_script.sql -s scripts/” execution is: ‘1’. -> [Help 1]
    org.apache.maven.lifecycle.LifecycleExecutionException: Failed to execute goal org.codehaus.mojo:exec-maven-plugin:1.1:exec (dbdeploy) on project dbdeploy: Result of cmd.exe /X /C “java -cp lib/mysql-
    connector-java-5.1.8-bin.jar:lib/dbdeploy-cli-3.0M2.jar com.dbdeploy.CommandLineTarget -U root -D com.mysql.jdbc.Driver -u jdbc:mysql://localhost/test -d mysql -o target/consolidated_script.sql -s scr
    ipts/” execution is: ‘1’.

    I have the jars in the right place, the target and scripts directory exist

    2) Where should my sql scripts be – in the scripts directory?


  5. neoNo Gravatar January 10th

    Comment Arrow

    yeah


Add Yours

  • Author Avatar

    YOU


Comment Arrow




About Author

shiv

This author has not yet written a description. Please give them some time to get acquainted with the site and surely they will write their masterpiece.