How you should unit test DAO layer

April 17, 2015 by Lokesh Gupta

If you are working in a project built on Spring, hibernate or JPA, and you want to unit test it’s data access layer (DAO) then information given in this tutorial may help you. As we are going to test DAO layer, we will need access to a database as well. But you may not be allowed to use any existing database for few reasons that it may corrupt the test data, which is primarily prepared for integration tests, or simply some other team members need access of that data too. To solve this issue, I am using in-memory database. IM (in-memory) database is good option because it does not leave any trace back and you are sure that you will get empty tables before each test (generally a good practice).

A good unit test should leave the database state same as it was before test case execution. It should remove all added data; and roll back all updates.

Table of Contents

1) Always create unit test specific configuration file
2) Writing unit tests for DAO layer
3) Package Structure

1) Always create unit test specific configuration file

This may be the first step for creating unit tests for your DAO layer. Ideally, you should be using same configuration for tests as you are using for application. But there may be some changes which are only unit test specific. To solve this issue, you should create another test-specific configuration file and add/override the test specific configuration changes.

E.g. in main application, if configuration file is application-context.xml then you should create another file application-context-test.xml and import the original configuration into this file on top. Then override bean definitions which you may want (e.g. use in-memory database instead of regular database).

application-context-test.xml

 
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:context="http://www.springframework.org/schema/context"
    xmlns:tx="http://www.springframework.org/schema/tx"
    xmlns:mvc="http://www.springframework.org/schema/mvc"
    xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
    http://www.springframework.org/schema/context/ http://www.springframework.org/schema/context/spring-context-3.0.xsd
    http://www.springframework.org/schema/tx/ http://www.springframework.org/schema/tx/spring-tx-3.2.xsd
    http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-3.2.xsd">
  
   <import resource="application-context.xml"/>

   <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
      <property name="driverClassName" value="org.hsqldb.jdbcDriver" />
      <property name="url" value="jdbc:hsqldb:mem:howtodoinjava" />
      <property name="username" value="sa" />
      <property name="password" value="" />
   </bean>
    
</beans>

application-context.xml

<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:context="http://www.springframework.org/schema/context"
    xmlns:tx="http://www.springframework.org/schema/tx"
    xmlns:mvc="http://www.springframework.org/schema/mvc"
    xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
    http://www.springframework.org/schema/context/ http://www.springframework.org/schema/context/spring-context-3.0.xsd
    http://www.springframework.org/schema/tx/ http://www.springframework.org/schema/tx/spring-tx-3.2.xsd
    http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-3.2.xsd">
  
    <context:component-scan base-package="com.howtodoinjava.jpa.demo" />

    <bean id="entityManagerFactoryBean" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
      <property name="dataSource" ref="dataSource" />

      <property name="packagesToScan" value="com.howtodoinjava.jpa.demo.entity" />

      <property name="jpaVendorAdapter">
         <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter" />
      </property>
      <property name="jpaProperties">
         <props>
            <prop key="hibernate.archive.autodetection">class,hbm</prop>
            <prop key="hibernate.hbm2ddl.auto">create</prop>
            <prop key="hibernate.show_sql">true</prop>
            <prop key="hibernate.dialect">org.hibernate.dialect.HSQLDialect</prop>
         </props>
      </property>
   </bean>

  <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
      <property name="driverClassName" value="com.mysql.jdbc.Driver" />
      <property name="url" value="jdbc:mysql://localhost:3306/test" />
      <property name="username" value="root" />
      <property name="password" value="password" />
   </bean>
     
    <bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager">
      <property name="entityManagerFactory" ref="entityManagerFactoryBean" />
   </bean>

   <tx:annotation-driven />
    
</beans>

In above example, I have override the regular datasource with in-memory datasource implementation.

2) Writing unit tests for DAO layer

The next part comes writing junit (or any other framework) testcases. I am using spring-test module. You may write test case as below manner.

package com.jpa.demo.test;

import java.util.List;

import org.junit.Assert;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.annotation.Rollback;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.transaction.annotation.Transactional;

import com.howtodoinjava.jpa.demo.dao.DepartmentDAO;
import com.howtodoinjava.jpa.demo.dao.EmployeeDAO;
import com.howtodoinjava.jpa.demo.entity.DepartmentEntity;
import com.howtodoinjava.jpa.demo.entity.EmployeeEntity;

@ContextConfiguration(locations = "classpath:application-context-test.xml")
@RunWith(SpringJUnit4ClassRunner.class)
public class TestEmployeeDAO 
{
	
	@Autowired
	private EmployeeDAO employeeDAO;
	
	@Autowired
	private DepartmentDAO departmentDAO;
	
	@Test
	@Transactional
	@Rollback(true)
	public void testAddDepartment()
	{
		DepartmentEntity department = new DepartmentEntity("Information Technology");
		departmentDAO.addDepartment(department);
		
		List<DepartmentEntity> departments = departmentDAO.getAllDepartments();
		Assert.assertEquals(department.getName(), departments.get(0).getName());
	}
	
	@Test
	@Transactional
	@Rollback(true)
	public void testAddEmployee()
	{
		DepartmentEntity department = new DepartmentEntity("Human Resource");
		departmentDAO.addDepartment(department);
		
		EmployeeEntity employee = new EmployeeEntity();
		employee.setFirstName("Lokesh");
		employee.setLastName("Gupta");
		employee.setEmail("howtodoinjava@gmail.com");
		employee.setDepartment(department);
		
		employeeDAO.addEmployee(employee);
		
		List<DepartmentEntity> departments = departmentDAO.getAllDepartments();
		List<EmployeeEntity> employees = employeeDAO.getAllEmployees();
		
		Assert.assertEquals(1, departments.size());
		Assert.assertEquals(1, employees.size());
		
		Assert.assertEquals(department.getName(), departments.get(0).getName());
		Assert.assertEquals(employee.getEmail(), employees.get(0).getEmail());
	}
}

Now notice few things here.

Load the test configuration file before starting test-suite.

@ContextConfiguration(locations = "classpath:application-context-test.xml");

As you have loaded the main configuration as well, you will be easily able to inject DAO references directly into testcase.
```
@Autowired
private EmployeeDAO employeeDAO;

@Autowired
private DepartmentDAO departmentDAO;
```

Use @Rollback(true) annotation to bring back original database state.

@Test
@Transactional
@Rollback(true)
public void testAddDepartment()
{
	//other code
}

Always create some data in a testcase, and verify that data in same testcase. Never make one testcase depedent on other testcase. You may want to read some unit testing best practices and guidelines in below posts.
Read More : Unit testing best practices

3) Package Structure

Finally, take a look at project structure used for this example. Notice, that application-context-test.xml is inside test/resources folder.

Now look at other files which I have written for this tutorial.

log4j.properties

log4j.logger.org.hibernate=INFO, hb
log4j.logger.org.hibernate.type=TRACE
log4j.appender.hb=org.apache.log4j.ConsoleAppender
log4j.appender.hb.layout=org.apache.log4j.PatternLayout

DepartmentEntity.java

package com.howtodoinjava.jpa.demo.entity;

import java.io.Serializable;
import java.util.ArrayList;
import java.util.List;
import javax.persistence.CascadeType;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.Table;

@Entity(name="DepartmentEntity")
@Table (name="department")
public class DepartmentEntity implements Serializable {
 
    private static final long serialVersionUID = 1L;
     
    @Id
    @GeneratedValue
    private Integer id;
    private String name;
     
    public DepartmentEntity(){
    }
 
    public DepartmentEntity(String name) {
        super();
        this.name = name;
    }
     
    @OneToMany(mappedBy="department",cascade=CascadeType.PERSIST)
    private List<EmployeeEntity> employees = new ArrayList<EmployeeEntity>();
     
    //Setters and Getters
     
	@Override
	public String toString() {
		return "DepartmentVO [id=" + id + ", name=" + name + "]";
	}
}

EmployeeEntity.java

package com.howtodoinjava.jpa.demo.entity;

import java.io.Serializable;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.ManyToOne;
import javax.persistence.Table;
 
@Entity(name="EmployeeEntity")
@Table (name="employee")
public class EmployeeEntity implements Serializable
{
    private static final long serialVersionUID = 1L;
 
    @Id
    @GeneratedValue
    private Integer id;
     
    private String firstName;
    private String lastName;
    private String email;
     
    @ManyToOne
    private DepartmentEntity department;
     
    public EmployeeEntity() {}
      
    public EmployeeEntity(String name, DepartmentEntity department) {
        this.firstName = name;
        this.department = department;
    }
      
    public EmployeeEntity(String name) {
        this.firstName = name;
    }
 
    //Setters and Getters
	
    @Override
    public String toString() {
        return "EmployeeVO [id=" + id + ", firstName=" + firstName
                + ", lastName=" + lastName + ", email=" + email
                + ", department=" + department + "]";
    }
}

DepartmentDAO.java

package com.howtodoinjava.jpa.demo.dao;

import java.util.List;
import com.howtodoinjava.jpa.demo.entity.DepartmentEntity;

public interface DepartmentDAO 
{
	public List<DepartmentEntity> getAllDepartments();
	public DepartmentEntity getDepartmentById(Integer id);
	public boolean addDepartment(DepartmentEntity dept);
	public boolean removeDepartment(DepartmentEntity dept);
	public boolean removeAllDepartments();
}

DepartmentDAOImpl.java

package com.howtodoinjava.jpa.demo.dao;

import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import com.howtodoinjava.jpa.demo.entity.DepartmentEntity;

@Repository
@Transactional
public class DepartmentDAOImpl implements DepartmentDAO {
	
	@PersistenceContext
    private EntityManager manager;

	@Override
	public List<DepartmentEntity> getAllDepartments() {
		List<DepartmentEntity> depts = manager.createQuery("Select a From DepartmentEntity a", DepartmentEntity.class).getResultList();
        return depts;
	}

	@Override
	public DepartmentEntity getDepartmentById(Integer id) {
		return manager.find(DepartmentEntity.class, id);
	}

	@Override
	public boolean addDepartment(DepartmentEntity dept) {
		try{
			manager.persist(dept);
		}catch(Exception e){
			e.printStackTrace();
			return false;
		}
		return true;
	}

	@Override
	public boolean removeDepartment(DepartmentEntity dept) {
		try{
			manager.remove(dept);
		}catch(Exception e){
			e.printStackTrace();
			return false;
		}
		return true;
	}

	@Override
	public boolean removeAllDepartments() {
		try{
			Query query = manager.createNativeQuery("DELETE FROM DEPARTMENT");
			query.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
			return false;
		}
		return true;
	}
}

EmployeeDAO.java

package com.howtodoinjava.jpa.demo.dao;

import java.util.List;
import com.howtodoinjava.jpa.demo.entity.EmployeeEntity;

public interface EmployeeDAO 
{
	public List<EmployeeEntity> getAllEmployees();
	public List<EmployeeEntity> getAllEmployeesByDeptId(Integer id);
	public EmployeeEntity getEmployeeById(Integer id);
	public boolean addEmployee(EmployeeEntity employee);
	public boolean removeEmployee(EmployeeEntity employee);
	public boolean removeAllEmployees();
}

EmployeeDAOImpl.java

package com.howtodoinjava.jpa.demo.dao;

import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import com.howtodoinjava.jpa.demo.entity.EmployeeEntity;

@Repository
@Transactional
public class EmployeeDAOImpl implements EmployeeDAO {
	
	@PersistenceContext
    private EntityManager manager;

	@Override
	public List<EmployeeEntity> getAllEmployees() {
		List<EmployeeEntity> employees = manager.createQuery("Select a From EmployeeEntity a", EmployeeEntity.class).getResultList();
        return employees;
	}

	@Override
	public List<EmployeeEntity> getAllEmployeesByDeptId(Integer id) {
		List<EmployeeEntity> employees = manager.createQuery("Select a From EmployeeEntity a", EmployeeEntity.class).getResultList();
        return employees;
	}

	@Override
	public EmployeeEntity getEmployeeById(Integer id) {
		return manager.find(EmployeeEntity.class, id);
	}

	@Override
	public boolean addEmployee(EmployeeEntity employee) {
		try{
			manager.persist(employee);
		}catch(Exception e){
			e.printStackTrace();
			return false;
		}
		return true;
	}

	@Override
	public boolean removeEmployee(EmployeeEntity employee) {
		try{
			manager.remove(employee);
		}catch(Exception e){
			e.printStackTrace();
			return false;
		}
		return true;
	}

	@Override
	public boolean removeAllEmployees() {
		try{
			Query query = manager.createNativeQuery("DELETE FROM EMPLOYEE");
			query.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
			return false;
		}
		return true;
	}
}

pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>com.howtodoinjava.jpa.demo</groupId>
	<artifactId>JPAExamples</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>jar</packaging>
	<build>
		<sourceDirectory>src</sourceDirectory>
		<plugins>
			<plugin>
				<artifactId>maven-compiler-plugin</artifactId>
				<version>2.3.2</version>
				<configuration>
					<source>1.7</source>
					<target>1.7</target>
				</configuration>
			</plugin>
		</plugins>
	</build>
	<dependencies>

		<dependency>
			<groupId>junit</groupId>
			<artifactId>junit</artifactId>
			<version>4.12</version>
			<scope>test</scope>
		</dependency>

		<!-- Spring Support -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-core</artifactId>
			<version>4.1.4.RELEASE</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-context</artifactId>
			<version>4.1.4.RELEASE</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-orm</artifactId>
			<version>4.1.4.RELEASE</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-test</artifactId>
			<version>4.1.4.RELEASE</version>
			<scope>test</scope>
		</dependency>

		<dependency>
			<groupId>org.hibernate</groupId>
			<artifactId>hibernate-core</artifactId>
			<version>4.0.1.Final</version>
		</dependency>
		<dependency>
			<groupId>org.hibernate</groupId>
			<artifactId>hibernate-validator</artifactId>
			<version>4.2.0.Final</version>
		</dependency>
		<dependency>
			<groupId>org.hibernate.common</groupId>
			<artifactId>hibernate-commons-annotations</artifactId>
			<version>4.0.1.Final</version>
		</dependency>
		<dependency>
			<groupId>org.hibernate.javax.persistence</groupId>
			<artifactId>hibernate-jpa-2.0-api</artifactId>
			<version>1.0.1.Final</version>
		</dependency>
		<dependency>
			<groupId>org.hibernate</groupId>
			<artifactId>hibernate-entitymanager</artifactId>
			<version>4.0.1.Final</version>
		</dependency>
		<dependency>
			<groupId>javax.validation</groupId>
			<artifactId>validation-api</artifactId>
			<version>1.0.0.GA</version>
		</dependency>
		<dependency>
			<groupId>org.slf4j</groupId>
			<artifactId>slf4j-api</artifactId>
			<version>1.7.5</version>
		</dependency>
		<dependency>
			<groupId>org.slf4j</groupId>
			<artifactId>slf4j-log4j12</artifactId>
			<version>1.5.6</version>
		</dependency>
		<dependency>
			<groupId>org.jboss.logging</groupId>
			<artifactId>jboss-logging</artifactId>
			<version>3.1.0.CR2</version>
		</dependency>

		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>5.1.10</version>
		</dependency>

		<dependency>
			<groupId>commons-dbcp</groupId>
			<artifactId>commons-dbcp</artifactId>
			<version>1.4</version>
		</dependency>

		<dependency>
			<groupId>org.hsqldb</groupId>
			<artifactId>hsqldb</artifactId>
			<version>2.2.8</version>
		</dependency>
		<dependency>
			<groupId>antlr</groupId>
			<artifactId>antlr</artifactId>
			<version>2.7.6</version>
		</dependency>
		<dependency>
			<groupId>commons-collections</groupId>
			<artifactId>commons-collections</artifactId>
			<version>3.1</version>
		</dependency>
		<dependency>
			<groupId>dom4j</groupId>
			<artifactId>dom4j</artifactId>
			<version>1.6.1</version>
		</dependency>
		<dependency>
			<groupId>javassist</groupId>
			<artifactId>javassist</artifactId>
			<version>3.4.GA</version>
		</dependency>
		<dependency>
			<groupId>javax.transaction</groupId>
			<artifactId>jta</artifactId>
			<version>1.1</version>
		</dependency>
		<dependency>
			<groupId>org.slf4j</groupId>
			<artifactId>slf4j-api</artifactId>
			<version>1.5.6</version>
		</dependency>

	</dependencies>
</project>

JPAExamples

Feel free to drop me your queries and suggestions.

Happy Learning !!

About Lokesh Gupta

Founded HowToDoInJava.com in late 2012. I love computers, programming and solving problems everyday. A family guy with fun loving nature. You can find me on Facebook, Twitter and Google Plus.

Feedback, Discussion and Comments

Billy Baldisser
November 17, 2017
Unit test should not connect to a database, does not matter if it is a in-memory db, this is why exist integration tests.
Reply
Prashant
November 17, 2017
A unit test connecting to database?????? Never heard this before and doesn’t sound correct
Reply
- Lokesh Gupta
  November 17, 2017
  These unit tests are connecting to embedded database, created and destroyed during execution of tests. Its not connecting to regular/production DB.
  Reply
Ufuk Altin
November 23, 2016
Hey,
I got a similar test case where I do test the DAO Layer.
Considering you example:
```
    public void testAddDepartment()
    {
        DepartmentEntity department = new DepartmentEntity("Information Technology");
        departmentDAO.addDepartment(department);
         
        List<DepartmentEntity> departments = departmentDAO.getAllDepartments();
        Assert.assertEquals(department.getName(), departments.get(0).getName());
    }
```
When I debug the test code (my one), set a breakpoint at the last line in (the method above), and compare the reference of
```
department
```
and
```
departments.get(0)
```
I see that they are both the same.
So my concern or question is if the test is useful at all, as the loaded
```
departments.get(0)
```
is the same as
```
department
```
.
I think the tests are useful but just realized that the references are the same (probably as the session/transaction) does not load it newly from the db and provides the already existent entity.
Reply
bd
July 31, 2016
Thanks Lokesh, This was helpful. Do you think there is a setting to use different hibernate.cfg.xml when unit tests are run as compared to actual hibernate configuration file? I have a separate hibernate utility class which creates a sessionFactory. The sessions are actually created and closed in the Dao layer after accessing the database. So when I use the inmemory database for dao layer testing I want hibernate configuration to connect to a different database.
Thanks in advance!
Reply
Uma Maheswara Rao Marripudi
April 19, 2016
Hi Lokesh, Thanks for this article.
We have Dbunit to test database logic(ie, DAO layer) in unit testing.
My doubt is, dbunit is like a mockito or not?
Mockito is to create mock objects for java classes and Dbunit is to create mock database (in the form of xml).
Am I correct? Anyone help me in mockito and dbunit . I am working spring webservices and hibernate.
Reply
Vikas Singh
June 2, 2015
Hi,
Thank you for useful tutorial.
I am implementing it on Spring+Hibernate project. I am using HSQLDB, While implementing I am facing below problem.
I have created some objects in before method, saved them. But inside DAO method select query(using Hibernate Query API) returns nothing, however Hibernate’s load, get, find API method returns proper data.
Apart from this, could you please explain little bit, how does HSQL db (in memory version) work.
Reply
- Lokesh
  June 2, 2015
  Then there must be something wrong with SELECT query itself, perhaps. You can try printing query in log, and then analyze it by running against some table in any physical DB such as MySQL. An interesting SO thread.
  Reply
Tomek
May 5, 2015
c) why do you repeat the logic from testAddDeparment() in testAddUser()? DRY principle please!
Reply
- Lokesh
  May 6, 2015
  I do not call them logic. They are steps. In unit test, each test should be complete in itself, and should never rely on any other tests. If if needs some code to be written multiple times, so be it.
  Reply
Tomek
May 5, 2015
Hi, thanks for interesting tutorial. My 3 cents:
a) if it talks to database, it is not a unit test anymore – it is an integration test which verifies how your code (DAO layer) integrates with 3rd party code (DB driver etc.)
b) I wonder if it makes sens to write such tests at DAO level. DAOs are very thin wrappers over some external API, so there is no logic involved. Wouldn’t it be better to write such tests using rather services layer as entry point? Could you please comment on this?
Regards,
Tomek Kaczanowski
Reply
- Lokesh
  May 6, 2015
  a) Disagree. There is no such rule like this.
  b) It depends from application to application. If ORM is a framework like hibernate, DAO is essentially very thin (still I would like to test entity relationships). And if ORM is like iBatis, which rely on user written SQL, then testing the code becomes very very essential.
  Reply
  - Guillaume
    October 21, 2015
    Lokesh, I suggest you to read again the definition of unit test.
    A test that is talking to a database if definitely not a unit test.
    For b) it makes sense to write a test at the DAO layer to ensure that your queries and mappings work, but you don’t want to acceptance test your DAO.
    Reply
    - Lokesh Gupta
      October 22, 2015
      Thanks for sharing your views. I appreciate it.
      Reply
Rahul
April 17, 2015
Is it necessary for an entity class to implement Serializable interface?
I wanted to know the benefits of implementing one, apart from that the entity could be serialised for later use.
Reply

Ask Questions & Share Feedback Cancel reply

Your email address will not be published. Required fields are marked *

Comment

*Want to Post Code Snippets or XML content? Please use [java] ... [/java] tags otherwise code may not appear partially or even fully. e.g.

[java] 
public static void main (String[] args) {
...
} 
[/java]

Name *

Email *

Website

Help me fight spammers. Solve this simple math. * 4 − = 0

How you should unit test DAO layer

1) Always create unit test specific configuration file

2) Writing unit tests for DAO layer

3) Package Structure

Stay Updated with Awesome Weekly Newsletter

About Lokesh Gupta

Feedback, Discussion and Comments

Ask Questions & Share Feedback Cancel reply

Developer Tools

Meta Links

References