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!!!
