Unit testing repository/DAO classes

In an enterprise application we have many layers. As part of testing a layer, we would Mock the underlying layer objects and test it. But to test the DAO layer, we need to write integration test to perform DB operations.

1) Declare Spring context aware Abstract Test class:

@ContextConfiguration(locations = {"classpath:data-applicationContext.xml"})  
 @TransactionConfiguration(transactionManager = "transactionManager", defaultRollback = true)  
 @Transactional  
 public abstract class SpringTransactionTestContext extends AbstractTransactionalJUnit4SpringContextTests {  
 }  

2) Extend this class in all DAOTest and Autowire DAO:

 public class StudentDaoImplTest extends SpringTransactionTestContext {  
   @Autowired  
   private StudentDaoImpl personDao;  
 }  

3) Setup Test data:
If Student class is dependent on some School table, to test this class we need to insert School record. 
Options:
i) Using Session factory:
Use SessionFactory instance and save School object as shown below:

@Autowired  
 private SessionFactory sessionFactory;  
 School school= new School("School Name");  
 sessionFactory.getCurrentSession().save(school);  

Also, builders can be used to create objects with required test data. Look at this post on how to use builder pattern in Unit tests.

ii) Using SimpleJDBCTemplate - Less preferred:
SimpleJDBCTemplate object is found in . This can be directly used to run SQL scripts to insert into tables.

String schoolInsertQuery = "Insert into SCHOOL(ID,NAME) values (?,?)";  
 this.simpleJdbcTemplate.update(schoolInsertQuery, schoolId, schoolName);  

Once the Test execution is completed, it would automatically rollback the transaction (As defaultRollback is set true).