Home » Core Java » junit » JUnit HSQLDB Example

About Vinod Kumar Kashyap

Vinod is Sun Certified and love to work in Java and related technologies. Having more than 13 years of experience, he had developed software's including technologies like Java, Hibernate, Struts, Spring, HTML 5, jQuery, CSS, Web Services, MongoDB, AngularJS, AWS. He is also a JUG Leader of Chandigarh Java User Group.

JUnit HSQLDB Example

Here is the new JUnit example, but with a difference. In this example we shall show users how they can use JUnit with HSQLDB for testing. In JUnit HSQLDB example, we will try to explain the usage of HSQLDB. Why we are using HSQLDB and not any other DB?

We will try to explain the answer to this question in this example. Let’s start by a little introduction of the HSqlDB.

1. Introduction

HSQLDB is a 100% Java database. HSQLDB (HyperSQL DataBase) is the leading SQL relational database software written in Java. Latest version 2.3.4 is fully multi-threaded and supports high performance 2PL and MVCC (multi version concurrency control) transaction control models.

We can use this database as a in memory database also. This answers our question that why we are using HSQLDB for our example. We will create in memory database, create a table, insert data into tables and after test cases are executed we will drop table. So all in all we will use database that will work in memory. We will not start any server to run DB nor we stop it.

2. Technologies Used

We will use the following technologies in this example.

  • Java: Coding Language
  • JUnit 4.12: Testing Framework
  • Maven: Build and Dependency Tool
  • HSQLDB: In Memory 100% Java database
  • Eclipse: IDE for coding

3. Project Setup

Tip
You may skip project creation and jump directly to the beginning of the example below.

We will start by creating a Maven project. Open Eclipse. Select File -> New -> Maven Project. Fill in the details and click on the Next button.

JUnit HSqlDB Example Setup 1

Figure 1: JUnit HSqlDB Example Setup 1

On this screen, fill in the details as mentioned below and click on Finish button.

JUnit HSqlDB Example Setup 2

Figure 2: JUnit HSqlDB Example Setup 2

With this, we are ready with the blank Maven project. Let’s start filling up the details.

4. JUnit HSQLDB Example

Starting by writing the below line in the pom.xml file. This will fetch all dependencies for our example to work.

pom.xml

    <dependencies>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>

        <dependency>
            <groupId>org.hsqldb</groupId>
            <artifactId>hsqldb</artifactId>
            <version>2.3.4</version>
        </dependency>
    </dependencies>

    <properties>
        <maven.compiler.source>1.8</maven.compiler.source>
        <maven.compiler.target>1.8</maven.compiler.target>
    </properties>

Now this will fetch JUnit jar(line 3), HSLDB jar(line 9) and also tell maven to use Java 1.8 for compiling of this example(line 16,17).

4.1 JUnit Test Class

We will create a test class

JUnitHSqlDBTest

package junithsqldb;

import static org.hamcrest.CoreMatchers.is;
import static org.junit.Assert.assertThat;

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

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

public class JUnitHSqlDBTest {

	@BeforeClass
	public static void init() throws SQLException, ClassNotFoundException, IOException {
		Class.forName("org.hsqldb.jdbc.JDBCDriver");

		// initialize database
		initDatabase();
	}
	

	@AfterClass
	public static void destroy() throws SQLException, ClassNotFoundException, IOException {
		try (Connection connection = getConnection(); Statement statement = connection.createStatement();) {
			statement.executeUpdate("DROP TABLE employee");
			connection.commit();
		}
	}

	/**
	 * Database initialization for testing i.e.
	 * <ul>
	 * <li>Creating Table</li>
	 * <li>Inserting record</li>
	 * </ul>
	 * 
	 * @throws SQLException
	 */
	private static void initDatabase() throws SQLException {
		try (Connection connection = getConnection(); Statement statement = connection.createStatement();) {
			statement.execute("CREATE TABLE employee (id INT NOT NULL, name VARCHAR(50) NOT NULL,"
					+ "email VARCHAR(50) NOT NULL, PRIMARY KEY (id))");
			connection.commit();
			statement.executeUpdate(
					"INSERT INTO employee VALUES (1001,'Vinod Kumar Kashyap', 'vinod@javacodegeeks.com')");
			statement.executeUpdate("INSERT INTO employee VALUES (1002,'Dhwani Kashyap', 'dhwani@javacodegeeks.com')");
			statement.executeUpdate("INSERT INTO employee VALUES (1003,'Asmi Kashyap', 'asmi@javacodegeeks.com')");
			connection.commit();
		}
	}

	/**
	 * Create a connection
	 * 
	 * @return connection object
	 * @throws SQLException
	 */
	private static Connection getConnection() throws SQLException {
		return DriverManager.getConnection("jdbc:hsqldb:mem:employees", "vinod", "vinod");
	}

	/**
	 * Get total records in table
	 * 
	 * @return total number of records. In case of exception 0 is returned
	 */
	private int getTotalRecords() {
		try (Connection connection = getConnection(); Statement statement = connection.createStatement();) {
			ResultSet result = statement.executeQuery("SELECT count(*) as total FROM employee");
			if (result.next()) {
				return result.getInt("total");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}

	@Test
	public void getTotalRecordsTest() {
		assertThat(3, is(getTotalRecords()));
	}

	@Test
	public void checkNameExistsTest() {
		try (Connection connection = getConnection();
				Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_READ_ONLY);) {

			ResultSet result = statement.executeQuery("SELECT name FROM employee");

			if (result.first()) {
				assertThat("Vinod Kumar Kashyap", is(result.getString("name")));
			}

			if (result.last()) {
				assertThat("Asmi Kashyap", is(result.getString("name")));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

Now let’s see each step in this class.
Line 19: This method will execute before all @Test cases. It will initialize our DB that will be used to test.
Line 28: This method will execute after all @Test cases are executed. We will drop table in this method
Line 45: Initialize DB with table creation and insertion of records.
Line 64: Creating a connection.
Line 73: Return total number of records in DB.
Line 86: @Test method to test case for total number of records.
Line 91: @Test method to test for fetch records.

Output

JUnit HSqlDB Example Output

Figure 3: JUnit HSqlDB Example Output

5. Conclusion

In this example, users have learnt that how we can test out methods by using in memory database HSQLDB. We have used this 100% Java database HSQLDB.

6. Download the Eclipse Project

This is a JUnit HSQLDB example.

Download
You can download the full source code of this example here: JunitHSqlDB.zip
(No Ratings Yet)
1 Comment Views Tweet it!

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

 

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

 

and many more ....

 

Receive Java & Developer job alerts in your Area

 

1
Leave a Reply

avatar
1 Comment threads
0 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
1 Comment authors
shree bhat Recent comment authors
  Subscribe  
newest oldest most voted
Notify of
shree bhat
Guest
shree bhat

So how exactly to set the username and password of the db as vinod and vinod? where is the properties file?