Hibernate Interview Questions and Answers: integration testing the DAO layer

Before this, please refer to Hibernate Interview Questions and Answers: with annotations and Spring framework , as the examples below are extension of this blog.


Q. How would you go about integration testing your DAO (i.e. Data Access Objects) layer or your Hibernate Repository classes?
A. It is a bit tricky to write integration tests because any changes to the underlying data can make your tests to fail. For example, addition of new records, modification to existing data , etc. The key is to keep the data as static as possible. There are two possible strategies.


1. Use a separate in memory database like HSQL (i.e. Hyper Structured Query Language) Database. The data can be stored in flat text files -- say in pipe delimited format and loaded into the in memory database during test set up phase, and deleted during the test tear down phase.

2. The second alternative is to use a framework like DBUnit to extract the relevant data from a given database and convert it into XML based data sets that can be inserted into your test database during the test setUp phase and deleted in the test tear-down phase. The DBUnit takes care of the data extraction and data load.

Both the above approaches maintain static data in either xml or text based flat files and load the data during the test setup.

Q. How would you go about using an in memory database like HSQL DB?
A. It involves the following steps.

• Define the data in flat files. Say in pipe delimited format.
• Write the parser classes to read the data and convert them into SQL insert statements to be loaded into the HSQL DB. This is known as ETL (i.e. Extract Transform Load) operation.
• Override the default hibernate datasource proprties with the HSQL DB related configuration.
• Wire up the datasource override file via override-daoContext.xml, which uses the HSQL DB, and overrides the hibernate proprties from the file daoContext.xml.
• Write a mock HSQL DB JNDI bootstrap class and wire it up using a Spring context file (e.g. hsqlBootstrapContext.xml).
• Write a database populator class that makes use of a number of parser classes to populate the database. Also, wire this up via a Spring context file (e.g. databasePopulatorContext.xml)
• Finally, the test class that bootstraps all the relevant classes via the Spring context files using dependency injection.

Define the data in a flat file say employeeSearch.txt

#employee_id, emp_code, manager_code, type, base_salary
A342|XSMITH|A456|IM|Permanent|43,500.00
A342|YSMITH|A678|IM|Contract|57,700.00 

Define the parser that loads the data by reading from the above flat file. For e.g. EmployeeParser.java. This class can be further improved by moving out the methods that will be shared by other parsers to a parent class or a helper class.

package com.myapp.database;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;

public class EmployeeParser {

 private static final String DROP_SQL = "drop table tbl_employee";
    private static final String CREATE_SQL =
            "create table tbl_employee(employee_id varchar(12), emp_code varchar(12), " + 
            "manager_code  varchar(12), type  varchar(12), base_salary decimal";


    private static final String INSERT_SQL =
            "insert into tbl_employee (employee_id, emp_code, manager_code, type, base_salary) values (";

   
    public void parseEmployee(Session session) throws ParserException, IOException {
        createDatabaseTable(session);
        BufferedReader file = findFile(getFileName());
        String[] data = readLine(file);
        while (data != null) {
            Query query =
                    session.createSQLQuery(INSERT_SQL + "'" + data[0] + "','" + data[1] + "','"
                            + data[2] + "'," + data[3] +  "','" + data[4] + ")");
            query.executeUpdate();
            data = readLine(file); // read next line from the file
        }
    }
    
    
    protected String[] readLine(BufferedReader file) throws IOException {
     String[] data = null;
        String line = file.readLine();
        while (line != null && line.startsWith("#")) {
            line = file.readLine();
        }
        if (line != null) {
         data =  line.split("\\|"); //split by "|" 
        }
        return data;
    }


    private void createDatabaseTable(Session session) {
        Query query = session.createSQLQuery(DROP_SQL);
        try {
            query.executeUpdate();
        } catch (HibernateException e) {
        }
        query =  session.createSQLQuery(CREATE_SQL);
        query.executeUpdate();
    }
    
    protected BufferedReader findFile(String fileName) {
        final InputStreamReader file =
                new InputStreamReader(getClass().getClassLoader().getResourceAsStream(fileName));
        BufferedReader stream = new BufferedReader(file);
        return stream;
    }

  

    public String getFileName() {
        return "employeeSearch.txt";
    }

}
 

Define an override spring context file to override the actual datasource properies. For example, the actual database could be Sybase or Oracle. The override-daoContext.xml is shown below.

<?xml version="1.0" encoding="UTF-8"?>
<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"
 xsi:schemaLocation="http://www.springframework.org/schema/beans
   http://www.springframework.org/schema/beans/spring-beans-2.5.xsd
   http://www.springframework.org/schema/context
   http://www.springframework.org/schema/context/spring-context-2.5.xsd"
 default-autowire="byName">

    <bean id="hibernateProperties" class="org.springframework.beans.factory.config.PropertiesFactoryBean">
        <property name="properties">
            <props>
                <prop key="hibernate.dialect">org.hibernate.dialect.HSQLDialect</prop>
                <prop key="hibernate.generate_statistics">true</prop>
                <prop key="hibernate.hbm2ddl.auto">create-drop</prop>
                <prop key="hibernate.jdbc.batch_size">1000</prop>
                <prop key="hibernate.show_sql">true</prop>
                <prop key="hibernate.use_sql_comments">true</prop>
                <prop key="hibernate.cache.use_query_cache">false</prop>
                <prop key="hibernate.cache.use_second_level_cache">false</prop>
                <prop key="hibernate.query.factory_class">org.hibernate.hql.classic.ClassicQueryTranslatorFactory</prop>
            </props>
        </property>
        <property name="location">
            <value>classpath:/hibernate.properties</value>
        </property>
    </bean>


</beans>

Write a JNDI based bootstrapper class HsqlDevBootstrapper.java that emulates JNDI boot strapping for your test classes. This is achieved via the Spring mock class SimpleNamingContextBuilder.

package com.myapp.test.db;
 
import javax.naming.NamingException;
 
import org.springframework.beans.factory.BeanCreationException;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.mock.jndi.SimpleNamingContextBuilder;
 
/**
 * helper class to bootstrap the sybase database datasources
 */
public class HsqlDevBootstrapper  {
     
 public static final String JNDI_BINDING_DB = "java:comp/env/jdbc/dataSource/mydb";
    public static final String DRIVER_CLASS = "org.hsqldb.jdbcDriver";
 
 private SimpleNamingContextBuilder builder;  //Spring JNDI mock class
 
 /**
  * setup HSQL DB, and bind it to jndi tree
  */
 public void start() {
        try {
            builder = SimpleNamingContextBuilder.emptyActivatedContextBuilder();
 
            DriverManagerDataSource ds = new DriverManagerDataSource();
            ds.setDriverClassName(DRIVER_CLASS);
            ds.setUrl("jdbc:hsqldb:mem:my_db"); //in memory HSQL DB URL
            ds.setUsername("user");
            ds.setPassword("pwd");
            builder.bind(JNDI_BINDING_DB, ds);
             
             
        } catch (NamingException e) {
            throw new BeanCreationException(e.getExplanation());
        }
    }
  
 public void stop() {
    builder.deactivate();
    builder.clear();
 }
 
}

Wire up the JNDI bootstrap class via Spring the config file hsqlBootstrapContext.xml.

<?xml version="1.0" encoding="UTF-8"?>
<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"
 xsi:schemaLocation="http://www.springframework.org/schema/beans
   http://www.springframework.org/schema/beans/spring-beans-2.5.xsd
   http://www.springframework.org/schema/context
   http://www.springframework.org/schema/context/spring-context-2.5.xsd"
 default-autowire="byName">

 <bean id="hsqlBootstrapper" class="com.myapp.test.db.HsqlBootstrapper" init-method="start" destroy-method="stop"/>

</beans>

Define a DatabasePopulator.java class that populates all the relevant (i.e. associated) database tables incling tbl_employee.

package com.myapp.database;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.SQLException;
import java.text.ParseException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.springframework.orm.hibernate3.HibernateTemplate;


public class DatabasePopulator {

    private final HibernateTemplate daoTemplate;

   

    public DatabasePopulator(HibernateTemplate daoTemplate) throws Exception {
        this.daoTemplate = daoTemplate;
        try {
            createDB();
        } catch (Exception e) {
            throw e;
        }
    }

 /**
   * This is where all the loading happens
   */
    public void createDB() throws HibernateException, SQLException, IOException {
        Session session = daoTemplate.getSessionFactory().openSession();
  
  Transaction tran = session.beginTransaction();
  
  //make use of the parser to read the data from a file and load it (i.e. ETL operation - Extract, Transaform, and Load)
        EmployeeParser empParser = new EmployeeParser();
  empParser.parseEmployee(session);
        
  //load other relevant data
  
        session.flush();
        tran.commit();   
    }
 
}

Wire the DatabasePopulator via Spring config file databasePopulatorContext.xml.

<?xml version="1.0" encoding="UTF-8"?>
<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"
 xsi:schemaLocation="http://www.springframework.org/schema/beans
   http://www.springframework.org/schema/beans/spring-beans-2.5.xsd
   http://www.springframework.org/schema/context
   http://www.springframework.org/schema/context/spring-context-2.5.xsd"
 default-autowire="byName">

    <bean name="databasePopulator" class="com.myapp.database.DatabasePopulator">
        <constructor-arg ref="daoTemplate" />
    </bean>
 
</beans>

Finally the test class. Some of the missing Spring context files and classes were defined in a different blog entry mentioned at the beginning of this blog. The context files need to be loaded in the right order.

package com.myapp.repository;

import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertFalse;
import static org.junit.Assert.assertTrue;
import static org.junit.Assert.fail;

import java.util.ArrayList;
import java.util.List;

import javax.annotation.Resource;

import org.joda.time.DateTime;
import org.junit.Assert;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.TestExecutionListeners;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.test.context.support.DependencyInjectionTestExecutionListener;



@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations={
  "classpath:hsqlBootstrapContext.xml",
  "classpath:transactionContext.xml",
  "classpath:daoContext.xml",
        "classpath:override-daoContext.xml",
        "classpath:databasePopulatorContext.xml"
})
@TestExecutionListeners(value = { 
  DependencyInjectionTestExecutionListener.class,
  SessionBindingHibernateListener.class
})

public class EmployeeRepositoryTest {

 @Resource
 EmployeeTableRepository  tableRepository;
    
    @Test
    public void testLoadEmployee() {
 
     Assert.assertTrue(tableRepository != null);
        Emplyee employee = null; 
        try {
            employee = tableRepository.loadEmployee("A342");
        } catch (RepositoryException e) {
            fail("Load employee threw an exception " + e);
        }
  
  assertTrue(employee != null);
        assertEquals(employee.getType(), "Permanent");
  //...more assertions
       
    }
 
 
 //....test other repository methods like saveEmployee, etc.
}