Using HSQLDB + DBUnit for Unit-testing Java code that works with databases

Foreword

It is believed that unit-tests should not use real objects (ie, connections to databases, network sockets, and similar resources). On this basis, a lot of holivars have developed - it is necessary to test the code that works with databases, or this is a bad form. If we test, can we call it Unit testing or is it functional testing (or integration testing, since we are testing the joint operation of two software environments / modules). Disputes and battles do not subside. I will ask readers not to be distracted by sacred wars, but to accept this material as food for thought. Let us not forget that the instrument I have described is only a tool, its applicability is determined by the task.

Selection of tools

Perhaps the most difficult part of unit testing is checking code that works with connections to databases (by and large, checking code that works with external objects). Yes, you can use mock'i ​​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 a mock object than catch it in code using the latter. What remains? It’s not good to use real databases either, because you don’t put a database server in the repository ... And if I say that you’ll put it very well, and it is already there? The solution to our problem is HSQLDB .

HSQLDB is a relational database written entirely in Java. At the same time, which is very remarkable, the database server can be raised as a separate instance, or it can be created inside a Java application. The small size and the ability to fully store the entire database in memory (by default) make HSQLDB an ideal database server for Unit-testing. Taking into account the fact that from the point of view of JDBC and ORM, the implementation of the DBMS does not matter (if you follow the SQL standard and do not abuse the extensions of the DBMS engines), we can easily replace the connection to PostgreSQL or Oracle with the connection to HSQLDB during Unit testing.
')
Well, let's assume that we have a database that is fully located in memory and consumes a minimal amount of resources. Before conducting tests, it must be filled with data, and it is desirable to make this method more universal than writing SQL queries. We also need to check the status of the database after operations on it. Get data from it and compare it with the standard manually, agree that the idea is extremely bad. Therefore, to solve the problem of initialization and validation of the results of the operation, a DBUnit library was created, ideally suited for automating the initialization of the database and the subsequent verification of data sets.

Usage example

To demonstrate the capabilities of the HSQLDB and DBUnit connectives, we will create a class whose constructor takes a connector to the database 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 the words in the database table. Our class will look like this:

public class User { private Connection sqlConnection; public User(Connection sqlConnectopn) { this.sqlConnection = sqlConnectopn; } private int insertOriginalString(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 { throw new RuntimeException(); } rsInsert.close(); psInsert.close(); return originalStringId; } private int insertToken(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 { throw new RuntimeException(); } rsInserToken.close(); psInsertToken.close(); } rsToken.close(); psTokenId.close(); return tokenId; } private void linkTokenToString(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(); } public void logRequestString(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 let's write a unit test for it.

 public class UserTest { private IDatabaseTester tester = null; @Before public void 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 to work with specific HSQLDB data types tester.getConnection().getConfig().setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY, new HsqldbDataTypeFactory()); //Getting dataset for database initialization IDataSet dataSet = new FlatXmlDataSetBuilder().build(this.getClass().getClassLoader().getResourceAsStream("template_set.xml")); //Initializing database tester.setDataSet(dataSet); tester.onSetup(); } @Test public void logRequestStringTest() throws SQLException, Exception { User man = new User(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

 <tokens WORD="Hello" /> <tokens WORD="world" /> 

When viewing a unit-test, the question may immediately arise: “Why is the code for creating tables in the database included in the unit-test ?! Did they promise to download datasets from files? ” Yes, that's right, we load sets from files, but describing the database structure using xml and making it work with all database drivers is not an easy process, due to the different DDL query syntax for each DBMS. Therefore, this functionality is missing from DBUnit.
I want to draw your attention to the following design:

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

The DefaultColumnFilter function filters columns, for comparison of a data set, disregarding id records.

Conclusion

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