Loading test BLOB data into HSQLDB with a Groovy Ant Task

At my current client we use an in-memory HSQLDB for unit testing. An in-memory DB provides us a mechanism to test DAO’s and services against a known dataset. Overall this has worked out very well for us and our unit testing.

Until recently we only used the HSQL jar file to create the schema and DBUnit Ant task to load the data from .csv files.  The Ant tasks looked something like the following.

Ant INIT target

<target name="init">
  <property name="hsql.url" value="jdbc:hsqldb:hsql://localhost:9001/testSchema" />
  <property name="hsql.username" value="testUser" />
  <property name="hsql.password" value="testPass" />
  <property name="hsql.dir" value="./hsqldb" />
  <property name="hsql.jar" value="${hsql.dir}/lib/hsqldb-1.8.0.7.jar" />
  <property name="ddl.dir" value="./ddl" />
  <property name="data.dir" value="./data" />
</target>

Ant Target to load the DDL

<target name="hsql-load-schema"
           depends="init"
      description="Loads schema into hsql.">

  <!-- load ddl -->
  <java jar="${hsql.jar}" fork="true" failonerror="true">
  <arg value="--rcFile" />
  <arg value="${hsql.dir}/sqltool.rc" />
  <arg value="testSchema" />
  <arg value="${ddl.dir}/base-schema.ddl" />
  </java>
</target>

This solution worked great for the data types we normally use. Recently however, we needed to add support for logos stored as jpg’s. In the DB the jpg files are stored as BLOB’s. From what I could find, DBUnit wasn’t an option for loading BLOB datatypes from image files in the same manner we were familiar with.

Our solution for loading images into a BLOB was to use the Ant Groovy task . This plugin allowed us to write Groovy code inside the same build.xml file we were already using to create the tables and load the data.

Updated Ant INIT task

<target name="init">
  <property name="hsql.url" value="jdbc:hsqldb:hsql://localhost:9001/testSchema" />
  <property name="hsql.username" value="testUser" />
  <property name="hsql.password" value="testPass" />

  <property name="hsql.dir" value="./hsqldb" />
  <property name="hsql.jar" value="${hsql.dir}/lib/hsqldb-1.8.0.7.jar" />
  <property name="ddl.dir" value="./ddl" />
  <property name="data.dir" value="./data" />

<!-- Setup the Groovy Path to define the tasks  -->
  <path id="groovy.path">
    <fileset dir="./lib">
      <include name="groovy-all-1.7.5.jar" />
    </fileset>
    <fileset dir="${hsql.dir}/lib">
      <include name="hsqldb-1.8.0.7.jar" />
    </fileset>
  </path>

<!-- Define the Groovy Task  -->
  <taskdef name="groovy"
             classname="org.codehaus.groovy.ant.Groovy"
          classpathref="groovy.path" />
  </target>

Groovy Ant task to load BLOB’s

<target name="hsql-load-logos"
           depends="init"
      description="Loads Logos into the DB.">
<!-- Below is an Groovy-Ant task that allows Groovy code to be executed.
The script uses Groovy to load an image into a blob DB Column.   -->

    <groovy>
      import groovy.sql.Sql
      import org.hsqldb.jdbcDriver

      def sql = Sql.newInstance("jdbc:hsqldb:hsql://localhost:9001/testSchema", "testUser", "testPass", "org.hsqldb.jdbcDriver")

// Read the directory containing the images.  Image files are prefixed with a 4 digit school identifier "

     def dir = new File('../finance-student-test/blobs/logos')
     dir.eachFile {
     if (it.isFile()) {
        println "----  processing file ---- " + it.name

        def schoolId = it.name.substring(0,4)

//  The table was not setup with a sequence to generate next value so we need to get the current max_id value for the school

        def firstRow = sql.firstRow("select LOGO_ID
                                                      from LOGO_CNTRL
                                                    where SCHOOL_ID = ${schoolId}
                                                     order by LOGO_ID desc")

        def maxId = 0
        if(firstRow != null) {
          maxId = firstRow.get("LOGO_ID")
        }

// get the file as a FileInputStream
       InputStream fis = new FileInputStream(it);

// Create a PreparedStatement to do the insert.  Some values are hardcoded in the insert statement.
       def ps = sql.connection.prepareStatement(
       "insert into LOGO_CNTRL
            (SCHOOL_ID, LOGO_ID, LOGO_TYPE_ID, LOGO_FILE_TYPE_ID, LOGO_BEGIN_DATE, LOGO_END_DATE, LOGO_FILE)
            values(?, ?, 101001, 42, ?, ?, ?)")

        ps.setObject(1, rcId)
        ps.setObject(2, maxId + 1)
        ps.setObject(3, beginDate)
        ps.setObject(4, endDate)

// Set the BLOB with the FileInputStream

        ps.setBinaryStream(5, fis, (int)it.length());

        ps.execute()
        sql.commit()
        ps.close()

      }
    }
    sql.close()

  </groovy>
</target>

If you have a better way to solve this problem I would encourage you to share it in the comments below.

About the Author

Object Partners profile.
Leave a Reply

Your email address will not be published. Required fields are marked *

Related Blog Posts
An Exploration in Rust: Musings From a Java/C++ Developer
Why Rust? It’s fast (runtime performance) It’s small (binary size) It’s safe (no memory leaks) It’s modern (build system, language features, etc) When Is It Worth It? Embedded systems (where it is implied that interpreted […]
Getting Started with CSS Container Queries
For as long as I’ve been working full-time on the front-end, I’ve heard about the promise of container queries and their potential to solve the majority of our responsive web design needs. And, for as […]
Simple improvements to making decisions in teams
Software development teams need to make a lot of decisions. Functional requirements, non-functional requirements, user experience, API contracts, tech stack, architecture, database schemas, cloud providers, deployment strategy, test strategy, security, and the list goes on. […]
JavaScript Bundle Optimization – Polyfills
If you are lucky enough to only support a small subset of browsers (for example, you are targeting a controlled set of users), feel free to move along. However, if your website is open to […]