Unit Testing with HSQLDB

The latest release (2.0.0) of Yank - the Ultra-Light JDBC Persistance Layer for Java, finally contains unit-tested code. This blog is about how HSQLDB was used for performing in-memory unit tests using JUnit. Just like most things, once you know the few tricks, it's really easy.

Once nice feature of HSQLDB is that you can set up 100% in-memory tables, which makes unit testing a snap because you don't need to worry about having a database setup on the machine running the database. The following code snippets show how easy it was to setup a unit test for testing the core JDBC persistance layer code in Yank. While this is specific to Yank, this example should help you unit test any of your JDBC code using HSQLDB. After all, the main trick is to have your database properties setup correctly, as shown in HSQL_DB.properties below.

TestBooksTable.java

package com.xeiam.yank.unit;

import static org.hamcrest.CoreMatchers.equalTo;
import static org.hamcrest.MatcherAssert.assertThat;

import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import org.junit.AfterClass;
import org.junit.BeforeClass;
import org.junit.Test;

import com.xeiam.yank.DBConnectionManager;
import com.xeiam.yank.PropertiesUtils;
import com.xeiam.yank.demo.Book;
import com.xeiam.yank.demo.BooksDAO;

/**
 * @author timmolter
 */
public class TestBooksTable {

  @BeforeClass
  public static void setUpDB() {

    Properties dbProps = PropertiesUtils.getPropertiesFromClasspath("HSQL_DB.properties");
    Properties sqlProps = PropertiesUtils.getPropertiesFromClasspath("HSQL_SQL.properties");

    DBConnectionManager.INSTANCE.init(dbProps, sqlProps);
  }

  @AfterClass
  public static void tearDownDB() {

    DBConnectionManager.INSTANCE.release();
  }

  @Test
  public void testBooksTable() {

    BooksDAO.createBooksTable();

    Book book = new Book();
    book.setTitle("Cryptonomicon");
    book.setAuthor("Neal Stephenson");
    book.setPrice(23.99);
    int i = BooksDAO.insertBook(book);
    assertThat(i, equalTo(1));

    List<Book> books = new ArrayList<Book>();

    book = new Book();
    book.setTitle("Cryptonomicon");
    book.setAuthor("Neal Stephenson");
    book.setPrice(23.99);
    books.add(book);

    book = new Book();
    book.setTitle("Harry Potter");
    book.setAuthor("Joanne K. Rowling");
    book.setPrice(11.99);
    books.add(book);

    book = new Book();
    book.setTitle("Don Quijote");
    book.setAuthor("Cervantes");
    book.setPrice(21.99);
    books.add(book);

    int[] returnValue = BooksDAO.insertBatch(books);
    assertThat(returnValue.length, equalTo(3));

    List<Book> allBooks = BooksDAO.selectAllBooks();
    assertThat(allBooks.size(), equalTo(4));

    book = BooksDAO.selectBook("Cryptonomicon");
    assertThat(book.getPrice(), equalTo(23.99));

  }
}

HSQL_DB.properties

driverclassname=org.hsqldb.jdbcDriver

# 100% in memory DB
myconnectionpoolname.url=jdbc:hsqldb:mem:aname;shutdown=true
myconnectionpoolname.user=sa
myconnectionpoolname.password=
myconnectionpoolname.maxconn=10

HSQL_SQL.properties

BOOKS_CREATE_TABLE=CREATE TABLE Books (TITLE VARCHAR(42) NULL, AUTHOR VARCHAR(42) NULL, PRICE DECIMAL(10,2) NOT NULL)
BOOKS_SELECT_BY_TITLE=SELECT * FROM BOOKS WHERE TITLE = ?

Ultra-Light JDBC Persistance Layer

Yank is a very easy-to-use yet flexible Java persistence layer for JDBC-compatible databases build on top of org.apache.DBUtils. Yank wraps DBUtils, hiding the nitty-gritty Connection and ResultSet details behind a straight-forward proxy class: DBProxy. "Query" methods execute SELECT statements and return a List of POJOs. "Execute" methods execute INSERT, UPDATE, and DELETE (and more) statements.

Usage is very simple: define DB connectivity properties, create a DAO and POJO class, and execute queries.

Features

• Depends on light-weight and robust DBUtils library
• ~13KB Jar
• Apache 2.0 license
• Batch execute
• Automatic POJO and POJO List querying
• Works with any JDBC-compliant database
• Write your own SQL statements
• Optionally store SQL statements in a Properties file
• Built-in Connection pool

What's Next?

Now go ahead and study some examples, download the thing and provide feedback.

Piece of Cake!!!