hsql for unit tests maven

Question

I am using sql-maven-plugin to setup a in memory hsql database for unit tests

        <plugin>
            <groupId>org.codehaus.mojo</groupId>
            <artifactId>sql-maven-plugin</artifactId>
            <version>1.5</version>
            <dependencies>
                <dependency>
                    <groupId>org.hsqldb</groupId>
                    <artifactId>hsqldb</artifactId>
                    <version>2.2.8</version>
                </dependency>
            </dependencies>
            <executions>
                <execution>
                    <id>create-db</id>
                    <phase>process-test-resources</phase>
                    <goals>
                        <goal>execute</goal>
                    </goals>
                    <configuration>
                        <driver>org.hsqldb.jdbcDriver</driver>
                        <url>jdbc:hsqldb:mem:test;shutdown=false</url>
                        <username>SA</username>
                        <password></password>
                        <autocommit>true</autocommit>
                        <srcFiles>
                            <srcFile>src/test/sql/test_db/test.sql</srcFile>
                        </srcFiles>
                    </configuration>
                </execution>
            </executions>
        </plugin>

in unit tests that run in maven:test phase, I instantiated a datasource with that url

            org.hsqldb.jdbc.JDBCDataSource ds = new JDBCDataSource();
            ds.setUrl(URL);
            ds.setUser("sa");
            ds.setPassword("");

but this does not have the tables that i had initialized via the scripts. it turns out surefire forks a new jvm and the original hsql instance started is not accessible from there. Is there a solution without introducing a file backed hsqldb ?

Thanks

Steve Goodman · Answer 1 · 2013-10-07T18:26:39.750

You could try turning off forking in surefire.

      <plugin>
        <groupId>org.apache.maven.plugins</groupId>
        <artifactId>maven-surefire-plugin</artifactId>
        <version>2.16</version>
        <configuration>
          <forkCount>0</forkCount>
        </configuration>
      </plugin>

Or, you could instantiate the HSQL server directly in your unit test environment, so that HSQL is spawned within the forked Surefire JVM:

@BeforeClass
public static void oneTime() throws Exception {
    org.hsqldb.Server.main(new String[]{});
}

@AfterClass
public static void oneTime() throws Exception {
    BasicDataSource dataSource = ... // get your data source
    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
    jdbcTemplate.execute("SHUTDOWN");
}

If you don't shutdown HSQL after your test class, you'll run into "Port already in use" type errors from trying to spawn a new db server for each test class.

Or, you could use a framework like Hibernate or DbUnit.

score 0 · Answer 2 · answered Aug 05 '13 at 08:45

0

Have you considered using dbunit? That will also make it a lot easier to run the unit-tests from your IDE without requiring Maven or executing all its pre test phases.

Also, for a unittest is it required to use a database? You shouldn't be testing if a db connection could be made. Instead I'd prefer to mock such objects with something like mockito. It is about specifying what result you want for a certain sql statement.

answered Aug 05 '13 at 08:45

Robert Scholte

11,889
2
35
44

thanks, for your suggestion but what i needed is sort of sql validity check. i have a lot of sqls which is dynamically built (lots of StringBuilder.appends) and wanted to check the generated sqls for validity by running against a db. i thought dbunit would ease table/resultset comparision but would it help in this case as well ? – mzzzzb Aug 07 '13 at 11:13
Be very, VERY careful with StringBuilder.appends, you might introduce the option for SQL Injection (https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet). DBUnit is often used to bring a DB in a certain state (prefilled tables), so you can easily test as if you were using the production DB. http://dbunit.sourceforge.net/howto.html – Robert Scholte Aug 08 '13 at 08:17

hsql for unit tests maven

2 Answers2