Testing with... Oracle XE

This is rather "infrastructure" than "testing" post.
Sometimes it happens that developer needs to tie his business logic with fixed database provider, just to obtain required functionality. In my case it was Oracle >= 10g and functionality was regular expression support (REGEX_LIKE, REGEXP_INSTR, REGEXP_SUBSTR, REGEX_REPLACE functions). Hence testing code has to refrain from using quick, agile DB backends (as HSQL) and setup something more heavyweight.
Those who happened to install/configure full-blown Oracle know how hard can this be. Fortunately Oracle came up with solution by releasing Oracle XE database. In Linux with dpkg installation is as easy as:

• get DEB package
  
• run dpkg -i package
  
• /etc/init.d/oracle-xe configure
  

And that's it! We have fully-functional Oracle DB in our own host :) Web UI, typically available under http://127.0.0.1:8100/apex/f let user log in as SYS and manage database just as using sqlplus.
There is however one caveat - when connecting via JDBC, user will very probably get stranfge exceptions with error code ORA-12519, meaning "TNS:no appropriate service handler found". Typical SQL exception is:

oracle xe Io exception:
Connection refused
(DESCRIPTION=(TMP=)(VSNNUM=169869568)(ERR=12519)
(ERROR_STACK=(ERROR=(CODE=12519)(EMFI=4))))

This is caused by a bug in how Oracle XE handles monitoring processes, Fix is pretty straightforward:

• execute: ALTER SYSTEM SET PROCESSES=150 SCOPE=SPFILE;
  
• stop db: /etc/init.d/oracle-xe stop
  
• start it again: /etc/init.d/oracle-xe start
  

Voila! Enjoy your integration testing with brand-new Oracle Express Edition.

Simple & fast DB testing

Yes, you're right. This short post is about HSQL - powerful yet surprisingly simple to use, pure-Java RDBMS.
Developing db-related functionalities in Java has never been easier. Using Hibernate's features as on-the-fly schema generation developer can easily stick to changing requirements. But what about testing? Transferring data to and from DB could take considerable amount of time and usage of one, shared, remote db can change test conditions and therefore make results unpredictable.
Solution is straight forward - configure own, isolated DB for all the testing! In Java world there could be only one answer - Hypersonic SQL (HSQL). In simplest example, one could use embedded HSQL running in-memory database.
Only two things are needed to enjoy separated DB test environment:

• add HSQL to the test class path - using Maven2 it's as simple as adding dependency to project's POM:
  

  
        <dependency>
          <groupid>hsqldb</groupid>
          <artifactid>hsqldb</artifactid>
          <version>1.8.0.7</version>
          <scope>test</scope>
        </dependency>
  

  
  
• configure and start org.hsqldb.Server class.

The latter can be done by writing simple wrapping class:

public class HsqlBootstrap
  extends Thread{

  private Server server;
 
  public HsqlBootstrap() {
    super();
    server = new Server();
    server.setNoSystemExit(true);
  
    server.setDatabasePath(0, "mem");
    server.setDatabaseName(0, "testDb");
  }
 
  public void run() {
   
    server.start();
  }

  public void stopServer() {
    server.stop();
  }
}

There is of course need for configuring Hibernate to use our new, freshly created DB. Adequate properties could be found on various Hibernate-related sites and include using jdbc:hsqldb:mem:testDb connection string ;-)