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
AWS Cloud HSM, Docker and NGINX
There is quite a bit of easily searchable content on the security benefits of leveraging a Hardware Security Module to manage cryptographic keys, so I will leave that to the scope of another article. The […]
Google Professional Machine Learning Engineer Exam 2021
Exam Description A Professional Machine Learning Engineer designs, builds, and productionizes ML models to solve business challenges using Google Cloud technologies and knowledge of proven ML models and techniques. The ML Engineer is proficient in all aspects […]
Designing Kubernetes Controllers
There has been some excellent online discussion lately around Kubernetes controllers, highlighted by an excellent Speakerdeck presentation assembled by Tim Hockin. What I’d like to do in this post is explore some of the implications […]
React Server Components
The React Team recently announced new work they are doing on React Server Components, a new way of rendering React components. The goal is to create smaller bundle sizes, speed up render time, and prevent […]