Integration testing using H2Database

Reading Time: 3 minutes

Need of Integration Testing

For doing unit tests, We use to do mocking. Consider we call getUser(), it calls the DAO that’s been mocked and returns “John” for the name and “21” for the age, assembles them and everything is perfect. No need to actually unit test a database there.What if there is any bug in database operations. If you mocked the database you can verify that a given method has been called once with a certain parameters. Integration testing is to test how the different parts behave together.It’s purpose is to verify the integration of your specific components with  a Database, a Framework, a 3rd party external system, etc. In integration testing we can test database operations also. 

In-memory Database

In memory database is a very good idea for integration testing.You can make sure your database operations are working correctly as expected. For the purpose of testing, you might not want to open and hold connections to the actual database because it could be resource intensive and it can be quite tedious to set up so we can go through In memory database. As its name suggests, the database is in-memory so would be embedded into the application during runtime. And on application shutdown, the database would be dropped completely. Each test has its own in memory database so you don’t have to worry if two tests are running simultaneously. In memory database is a very good idea for integration testing.

To write integration test cases in scala standard hierarchy used-

  • src/it/scala for Scala sources and
  • src/it/resources for resources that should go on the integration test classpath

In this blog we will discuss about H2 in memory database. H2 is an open-source lightweight And also it supports read-only database and temporary tables. Following dependency is needed to use H2.

libraryDependencies += "com.h2database" % "h2" % "1.4.196"

Steps to connect h2 in memory database

Step 1 − Registering the JDBC database driver.

Class.forName ("org.h2.Driver");

Step 2 − Opening the connection.

Connection conn =DriverManager.getConnection ("jdbc:h2:~/test", "sa","");

Step 3 − Creating a statement.

Statement st = conn.createStatement();

Step 4 − Executing a statement and receiving Resultset.

Stmt.executeUpdate("sql statement");

Step 5 − Closing a connection.


Let’s take one example for better understanding

def addUser: String = {
 val result = dbOperations.add
 if (result > 0) {
   "User Added"
 else {
   "User Not Added"

In above code there is a method addUsers() which calls another method add() which is in DbOperations and it adds user in database now if we want to test this method then we can write unit test cases we can mock the add() method but if we want to test add() method also then we can perform integration testing. H2 is a database which creates temporary tables.

So to test above method lets write integration test case. Following is the H2 database setup

val conn: Connection = DriverManager.getConnection("jdbc:h2:./userinfo","sa", "")
val dbOperations = new DbOperations(conn)
val userOperations = new UserOperations(dbOperations)
val stmt = conn.createStatement()
stmt.execute(“create table if not exists userinfo.myusers(
 id int auto_increment,
 name varchar(255) not null,
 age varchar(255) not null,);“)

We can write test cases for the above method as follows,

"UserOperations#addUser" should {

 "return message user added" in {
   val result = userOperations.addUser()
   result shouldBe "User Added"

To find code click here

Advantages of H2-

  • It is an extremely fast database engine.
  • Scrollable and updatable result set support, large result set, external result sorting, functions can return a result set 
  • Supports Inner and outer joins, subqueries, read only views and inline views 
  • It is used for running integration test cases for which you want to use temporary data instead of using actual data in your DB.

References –