Using HSQLDB + DBUnit for Unit Testing Java Database Code

Foreword

It is believed that unit tests should not use real objects (i.e. database connections, network sockets and similar resources). A lot of holivars have developed on this basis - you need to test the code that works with databases, or it's a bad tone. If testing, can this be called Unit testing or is it functional testing (or integration testing, because we are testing the joint work of two software environments / modules). Disputes and battles do not cease. I’ll ask readers not to be distracted by the holy wars, but to accept this material as food for thought. Let's not forget that the tool I described is just a tool, its applicability is determined by the task.

Tool selection

Perhaps the most difficult thing in Unit testing is checking the code that works with database connections (by and large, checking the code that works with external objects). Yes, you can use mocks instead of connections, but if you have more than one operation with a JDBC provider, then you are more likely to make a mistake in the mock object than catch it in the code using the latter. What remains? Using real databases is also bad, because you can’t put the database server in the repository ... And if I say that you can put it very much, and it is already there? The solution to our problem is HSQLDB .

HSQLDB is a relational database entirely written in Java. At the same time, which is very remarkable, the database server can be raised as a separate instance, or created inside a Java application. The small size and the ability to completely store the entire database in memory (by default) make HSQLDB an ideal database server for Unit testing. Given that from the point of view of JDBC and ORM, the implementation of the DBMS does not matter (if you adhere to the SQL standard and do not abuse the extensions of the DBMS engines), then we can easily replace the connection to PostgreSQL or Oracle with the connection to HSQLDB during Unit testing.

Well, suppose we have a database that is completely in memory and consumes a minimal amount of resources. Before conducting tests, it needs to be filled with data, and it is advisable to do this with a more universal method than writing SQL queries. We also need to check the state of the database after operations on it. To get data from it and compare it with the reference manually, you see, the idea is extremely bad. Therefore, to solve the problem of initialization and checking the results of the operation, the DBUnit library was created , which is ideally suited for automating the initialization of the database and subsequent verification of data sets.

Usage example

To demonstrate the capabilities of HSQLDB and DBUnit, we will create a class whose constructor accepts a database connector as parameters. The class will have a method that takes a string of text as parameters, breaks it into separate words and adds statistics about the frequency of occurrence of words in the database table. Our class will look like this:

publicclassUser{
	private Connection sqlConnection;
	publicUser(Connection sqlConnectopn){
		this.sqlConnection = sqlConnectopn;
	}
	privateintinsertOriginalString(String originalString)throws SQLException {
		int originalStringId = 0;
		PreparedStatement psInsert = sqlConnection.
			prepareStatement(
				"INSERT INTO original_strings (strings, date) VALUES (?, now())",
				PreparedStatement.RETURN_GENERATED_KEYS
			);
		psInsert.setString(1, originalString);
		psInsert.execute();
		ResultSet rsInsert = psInsert.getGeneratedKeys();
		if(rsInsert.next()) {
			originalStringId = rsInsert.getInt(1);
		}
		else {
			thrownew RuntimeException();
		}
		rsInsert.close();
		psInsert.close();
		return originalStringId;
	}
	privateintinsertToken(int originalStringId, String token)throws SQLException {
		int tokenId = 0;
		PreparedStatement psTokenId = sqlConnection.
				prepareStatement("SELECT id FROM tokens WHERE word = ?");
		psTokenId.setString(1, token);
		ResultSet rsToken = psTokenId.executeQuery();
		if(rsToken.next()) {
			tokenId = rsToken.getInt(1);
		}
		else {
			PreparedStatement psInsertToken = sqlConnection.
					prepareStatement(
							"INSERT INTO tokens (word) VALUES (?)", 
							PreparedStatement.RETURN_GENERATED_KEYS
					);
			psInsertToken.setString(1, token);
			psInsertToken.execute();
			ResultSet rsInserToken = psInsertToken.getGeneratedKeys();
			if(rsInserToken.next()) {
				tokenId = rsInserToken.getInt(1);
			}
			else {
				thrownew RuntimeException();
			}
			rsInserToken.close();
			psInsertToken.close();
		}
		rsToken.close();
		psTokenId.close();
		return tokenId;
	}
	privatevoidlinkTokenToString(int originalStringId, int tokenId)throws SQLException {
		PreparedStatement psCreateLink = sqlConnection.
			prepareStatement("INSERT INTO links (original_string_id, token_id) VALUES(?,?)");
		psCreateLink.setInt(1, originalStringId);
		psCreateLink.setInt(2, tokenId);
		psCreateLink.execute();
	}
	publicvoidlogRequestString(String requestString)throws SQLException {
		String preParsed = requestString.replaceAll("\\W+", " ");
		String[] tokens = preParsed.split(" ");
		if(tokens.length > 0) {
			int originalStringId = insertOriginalString(requestString);
			for(String token: tokens) {
				linkTokenToString(
						originalStringId, 
						insertToken(originalStringId, token)
				);
			}
		}
	}
}

Now write a unit test for it.

publicclass UserTest {
	private IDatabaseTester tester = null;
	@Beforepublicvoid instantiate() throws Exception {
		//Creating databse server instance
		tester = new JdbcDatabaseTester("org.hsqldb.jdbcDriver", "jdbc:hsqldb:mem:" + UUID.randomUUID().toString(), "sa", "");
		//Creating tables
		tester.getConnection().getConnection().prepareStatement("CREATE SEQUENCE SEQU AS INTEGER START WITH 0").execute();
		tester.getConnection().getConnection().prepareStatement("CREATE SEQUENCE SEQU2 AS INTEGER START WITH 0").execute();
		tester.getConnection().getConnection().prepareStatement("CREATE SEQUENCE SEQU3 AS INTEGER START WITH 0").execute();
		tester.getConnection().getConnection().prepareStatement("CREATE TABLE TOKENS(ID INT GENERATED BY DEFAULT AS SEQUENCE SEQU NOT NULL PRIMARY KEY, WORD LONGVARCHAR NOT NULL)").execute();
		tester.getConnection().getConnection().prepareStatement("CREATE TABLE ORIGINAL_STRINGS(ID INT GENERATED BY DEFAULT AS SEQUENCE SEQU2 NOT NULL PRIMARY KEY, STRINGS LONGVARCHAR NOT NULL,DATE TIMESTAMP NOT NULL)").execute();
		tester.getConnection().getConnection().prepareStatement("CREATE TABLE LINKS(ID INT GENERATED BY DEFAULT AS SEQUENCE SEQU3 NOT NULL PRIMARY KEY,TOKEN_ID INT NOT NULL,ORIGINAL_STRING_ID INT NOT NULL)").execute();
		//Setting DATA_FACTORY, so DBUnit will know how toworkwith specific HSQLDB data types
		tester.getConnection().getConfig().setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY, new HsqldbDataTypeFactory());
		//Getting dataset fordatabase initialization 
		IDataSet dataSet = new FlatXmlDataSetBuilder().build(this.getClass().getClassLoader().getResourceAsStream("template_set.xml"));
		//Initializing database
		tester.setDataSet(dataSet);
		tester.onSetup();
	}
	@Test
	publicvoid logRequestStringTest() throws SQLException, Exception {
		User man = newUser(tester.getConnection().getConnection());
		man.logRequestString("Hello, world!");
		ITable template = new FlatXmlDataSetBuilder().build(this.getClass().getClassLoader().
				getResourceAsStream("check_set.xml")).getTable("tokens");
		ITable actual = DefaultColumnFilter.includedColumnsTable(tester.getConnection().createDataSet().getTable("tokens"),
				template.getTableMetaData().getColumns());
		Assertion.assertEquals(template, actual);
	}
}

Dataset files look like this:

template_set.xml

<dataset></dataset>

check_set.xml

<tokensWORD="Hello" /><tokensWORD="world" />

When viewing a unit test, the question may immediately arise: “Why is the code for creating tables in the database included in unit-test ?! They promised to download data sets from files? ” Yes, that's right, we load the sets from files, but to describe the database structure using xml and make it work with all database drivers is not an easy process, due to the different syntax of DDL queries for each DBMS. Therefore, such functionality is not available in DBUnit.
I want to focus your attention on the following design:

ITable actual = DefaultColumnFilter.includedColumnsTable(tester.getConnection().createDataSet().getTable("tokens"),
				template.getTableMetaData().getColumns());

The function DefaultColumnFilterfilters the columns to compare the data set without considering the id of the records.

Conclusion

In the article, I analyzed the simplest example of working with a database. The reader can easily imagine that such an approach to testing is applicable not only for the "bare" use of ODBC connections, but also for ORM frameworks. Stable code for you!