HSQL DBUnit Testing Sample Programs

HSQLDB (Hyper Structured Query Language Database) is a relational database management system written in Java. It has a JDBC driver and supports a large subset of SQL-92 and SQL:2008 standards. It offers a fast, small (around 1300 kilobytes in version 2.2) database engine which offers both in-memory and disk-based tables. Embedded and server modes are available. And also we can use HSQLDB to test the classes like DBUnit with JDBC and Hibernate. HSQLDB unit testing also same like DBUnit but more advanced...
You do not want the data generated by your tests to interfere with data stored in this database, and would like to avoid the additional risk of database problems complicating, or causing failure, in your tests. A good solution is to use an in memory database for testing. HSQLDB is a 100% Java database which can be setup to run in memory. It's great for testing. So, how do we setup a simple test case which runs a test against HSQLDB?
Below is my class, which is connecting to database. Generally everybody will use HSQLDB for only hibernate with spring to test.
As per my knowledge we can test any kind of DB classes. For that I am going to show how to write test case for JDBC class with MySQL.
Special Advantage with HSQLDB is that for our project db is not implemented, but code is completed. In that case I want to test my class.
First, let's write a class for test case:

package org.javaVillage.DBUnit;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 *
 * @author Munna
 * Salary Calculation
 *
 */
public class SalaryCalculation {

	private Connection connection;
	public void setConnection(Connection connection) {
	  this.connection = connection;
	} 
	/**
	* Creating Connection
	* @return Connection
	* @throws SQLException
	*/
	public Connection getSQLConnection() throws SQLException {
		connection =  DriverManager.getConnection("jdbc:mysql://localhost:3306/Company", 
											"root",""); 
		return connection;
	}

	/**
	 * Salary Calculation
	 * @param EmpID string
	 * @return salary
	 * @throws SQLException
	 */
	public int calculator(String EmpID) throws SQLException{
		Statement stmt=connection.createStatement();
		ResultSet rs=stmt.executeQuery("select * from SCHOOL where 
										emptitle='"+EmpID+"'");
		int salary = 0;
		int bonus = 0;
		int increment = 0;      
		while (rs.next()) {
			salary = rs.getInt("Salary");
			bonus = rs.getInt("Bonus");
			increment = rs.getInt("Increment");  
		} 
		return (salary+bonus+increment);
	} 

}

Test case for above program, here I want to tell you database is not ready. So for that, see the below test case using HSQLDB.

package org.javaVillage.test;

import static org.junit.Assert.assertEquals;
import java.io.StringReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import org.dbunit.IDatabaseTester;
import org.dbunit.JdbcDatabaseTester;
import org.dbunit.dataset.IDataSet;
import org.dbunit.dataset.xml.FlatXmlDataSetBuilder;
import org.dbunit.operation.DatabaseOperation;
import org.javaVillage.BO.SalaryCalculation;
import org.junit.AfterClass;
import org.junit.BeforeClass;
import org.junit.Test;

public class SalaryCalculationTest {

	private static IDatabaseTester databaseTester;
	private static SalaryCalculation salCal;
	@BeforeClass
	public static void init() throws Exception {
		databaseTester = new JdbcDatabaseTester(org.hsqldb.jdbcDriver.class
		.getName(), "jdbc:hsqldb:file:/opt/db/testdb", "sa", "");       
		createTablesSinceDbUnitDoesNot(databaseTester.getConnection().getConnection());
		String inputXml = "<dataset>" + "    <SCHOOL emptitle=\"54601B\" "
							+ "       Salary=\"25000\""
							+ "       Bonus=\"5000\""
							+ "       Increment=\"0\""
							+ "       subject=\"mathametics\"/>"       
							+ "</dataset>";
		IDataSet dataSet = new FlatXmlDataSetBuilder().build(new StringReader(inputXml));
		databaseTester.setDataSet(dataSet);
		databaseTester.setSetUpOperation(DatabaseOperation.CLEAN_INSERT);
		databaseTester.setTearDownOperation(DatabaseOperation.DELETE_ALL);
		databaseTester.onSetup();
		salCal = new SalaryCalculation();
		salCal.setConnection(databaseTester.getConnection().getConnection());    
	}

	private static void createTablesSinceDbUnitDoesNot(Connection connection)
	  throws SQLException {
		PreparedStatement statement = connection.prepareStatement("CREATE TABLE SCHOOL"+
		"(EMPID VARCHAR(20),SALARY VARCHAR(10), BONUS VARCHAR(20),INCREMENT VARCHAR(10),"+
		"SUBJECT VARCHAR(30))");
		statement.execute();
		statement.close();
	}

	@Test
	public void testCalculator() throws SQLException{
	 assertEquals(30000,salCal.calculator("54601B"));
	}
	@Test
	public void testCalculatorInvalidEmpID() throws SQLException{
	 assertEquals(0,salCal.calculator("54501A"));
	}

	@AfterClass
	public static void cleanUp() throws Exception {
	 salCal=null;
	 databaseTester.onTearDown();
	 databaseTester = null;
	}
}

As per my requirement for me database is not ready, So I should create table also in test class. Based on upon class I have written two test methods for calculation method with positive and negative scenarios.
In HSQLDB I have used file system catalog "jdbc:hsqldb:file:/opt/db/testdb". All related to HSQLDB files like property files, log files and scripts files will be moved to file:/opt/db/testdb.
To test these classes below are the required jars:

HSQL DB unit required jars for sample program

HSQL DB Testing Sample Programs