Testing Database Code Using JUnit

This post outlines the basic setup that you may need to use HSQL in JUnit, Spring, Maven environment to test database code when you are using JdbcTemplate directly instead of Hibernate.

Step 1: Include the maven dependencies in your pom:

<dependency>
    <groupId>hsqldb</groupId>
    <artifactId>hsqldb</artifactId>
    <version>1.8.0.7</version>
    <scope>test</scope>
</dependency>
<dependency>
    <groupId>commons-collections</groupId>
    <artifactId>commons-collections</artifactId>
    <version>3.2</version>
</dependency>
<dependency>
    <groupId>commons-dbcp</groupId>
    <artifactId>commons-dbcp</artifactId>
    <version>1.2.2</version>
</dependency>

Step 2: Setup bean config that will be used by our test (save as database.xml and put it in the resources folder):

<bean id="dataSource"
        class="org.apache.commons.dbcp.BasicDataSource">
    <property name="driverClassName" 
        value="org.hsqldb.jdbcDriver"/>
    <property name="url" 
        value="jdbc:hsqldb:mem:aname"/>
    <property name="username" value="sa"/>
    <property name="password" value=""/>
    <property name="defaultAutoCommit" value="false"/>
</bean>
<bean id="jdbcTemplate" 
        class="org.springframework.jdbc.core.JdbcTemplate">
    <property name="dataSource" ref="dataSource"/>
</bean>

Step 3: Write the test case(s)

You may find the HSQL documentation (supported data types in chapter 9) and the api doc on JdbcTemplate useful as well.

Of course things will not be that simple due to one simple reason: HSQL will most probably not support all the data types used by your DB (e.g. "bytea" , an array of bytes, is supported by PostGreSQL DB but not by HSQL). But it definitely a headstart and a good way to test your DB.

Cutting Corners

When you are making systems that need to be very fast, every small bit helps. Some tips (thanks to Andrew) to fine tune performance:

• If you know that a value will not change in its lifetime, mark it as final (even those inside a method call). This will ensure that compiler accesses them faster. eg:
  

  final int someValueToRefer;
  
  public void aMethod(final Object obj, final int funcValToRefer){
      final int aMethodValueThatWontChange = 1234;
  }

• Use message queues between systems you want to truly decouple. This makes sure that one part doesn't hold the other to ransom


• Use System.currentTimeMillis() if all you need is current time instead of new Date()


• Save dates in DB as long values instead of date objects


• When using a cache, save minimal information (saving a unique ID instead of the whole object, for example)


• Use connection pooling: for databases, thread pools, messaging queues


• Use synchronization blocks instead of full method synchronization


• When there is too much information to process, it may be desirable to first "group" similar chunks and process them in batches (for example, when you are sending stock alerts to you star trading team, you may want to group all the alerts by the time they are expected to go off)


• When there is a boolean to return, one can generally avoid creating one explicitly. eg:
  

  boolean wasFound = false;
  
  for(MyObject currObj:objectList){
       if(currObj.getProperty().equals(valueLookingFor)){
          wasFound=true;
          break;
      }
  }
  
  return wasFound;

  can be replaced by:
  

  for(MyObject currObj:objectList){
       if(currObj.getProperty().equals(valueLookingFor)){
          return true;
      }
  }
  
  return false;



• Similarly, when we have a function that finds something and returns it, when you find it in a loop return right there instead of bothering to assign it to some var and break. eg:
  

  MyObject retVal= null;
  
  for(MyObject currObj:objectList){
       if(currObj.getProperty().equals(valueLookingFor)){
          retVal=currObj;
          break;
      }
  }
  
  return retVal;

  can be replaced by:
  

  for(MyObject currObj:objectList){
       if(currObj.getProperty().equals(valueLookingFor)){
          return currObj;
      }
  }
  
  return null;