Unit Testing Data Access Logic in Java

Most interviewers like Java candidates those who are passionate and experienced about writing unit tests. Any non trivial Java application will be making calls to database tables. So, here are a few questions and answers testing your ability to write unit tests to test data access layer.

Q1. How will you go about unit testing the data access logic?
A1. The main challenge with using a ‘real’ database” for unit testing is the setup, take down, and isolation of the tests. You don’t want to have to spin up an entirely new Oracle database and create tables and data just for unit testing.

Mocking the database is one option when testing the service layer, however for testing the DAO itself you need something behind the DAO has the data and the queries to run properly.

Solution: The solution is to use an in memory database. HyperSQL (i.e. HSQLDB) is an excellent choice for this because it has the ability to emulate the dialect of another database.

Q2. Can you describe the basic steps involved in setting up an in memory database in Java apps?

Step 1: The pom.xml file that brings in the libraries required for testing — the hsqldb and spring-test jars.

Step 2: The Spring configuration file to bootstrap HSQLDB. One of the new features introduced in Spring 3 is the support for embedded Java database engines. Embedded databases like HSQL, H2, or Derby are very useful during the development phase of the project as they are fast, have small memory footprints and are opensource. “EmbeddedDatabaseBuilder” will make things easier.

Step 3: The default schema.sql and data.sql along with the hsqldb-oracle.sql need to be in the classpath. Say in src/test/resources



data.sql dummy data for testing

The above steps would have created the schema and data for the unit testing purpose in memory.

It is also flexible enough to specifically configure the scripts like

if you are doing it via XML based spring config, it will be something like

The above approach can help you identify and fix any object-to-relational mapping issues, spring-jpa-hibernate wiring issues, and other coding issues.

Q3. How the heck to unit test business logic with a database hanging around?
A3. One of the best ways to get the database out of your way is to hide data access behind abstracted interfaces that can be mocked in business logic testing.

There are a few libraries that help you mock database logic.

1) MockRunner: has some JDBC-specific extensions that allow for simulating JDBC ResultSets, as well as for checking whether actual queries are executed.

2) An ordinary Java mocking libraries like jMock, EasyMock, Mockito etc mock the DAO layer. This approach was explained in Q2. Mocking DAO Layer

Q4. What are some of the things you don’t have to unit test?
A4. You should never write unit test for the sake of writing one. There are scenarios where you don’t have to write unit tests. For example,

1) Third-party frameworks and libraries. You should assume that they work as expected.

2) Code that gives non deterministic results.

3) Very complex database logic invoking stored procedures, etc. It is much easier to test basic CRUD operations.

4) Trivial code like getters and setters.

Java Developer & Architect Q&As

Big Data Engineer & Architect Q&As

16+ Key Areas & 13+ Techs to fast-track