How to write junit test cases for DAO layer using namedParameterJdbcTemplate for parameterized query?

Question

I have to write test cases for DAO layer and we use namedParameterJdbcTemplate.queryForObject to fetch details from database. We use BeanPropertyRowMapper and parameterrized queries.

DAO layer code as below

@Repository
public class GeneratorRepository {


    @Value("${sql.existingUtQuery}")
    private String existingUtQuery;

    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Autowired
    private BeanPropertyRowMapper<Generator> generatorRowMapper;


    public generator findExistingUt(Request request) {
        Generator ut = new Generator();
        try {
            SqlParameterSource namedParameters = new MapSqlParameterSource()
                    .addValue("no", request.getId(), Types.VARCHAR)
                    .addValue("sub", request.getSub(), Types.VARCHAR)
                    .addValue("rt", request.getRt(), Types.INTEGER)
                    .addValue("sta", request.getSta(), Types.VARCHAR);
            ut = namedParameterJdbcTemplate.queryForObject(existingUtQuery, namedParameters, generatorRowMapper);
        } catch (EmptyResultDataAccessException e) {
            ut = null;
        } catch (Exception e) {
            throw e;
        }
        return ut;
    }

}

I am trying to write test cases as below

@Test
    public void findExistingUt() {
        Request request = new Request("12345", "F", 0, "XC");
        Generator expectedResponse = new Generator("12345", "F", 0, "XC","12345.FCST","345TDGHDF45G","OS01",null);
        String existingUtiQuery = "select Top 1 * from existingUT where NO= :no AND SUB= :sub AND rt= :rt and STA= :sta order by LAST_UPDATE desc";
        Mockito.when(namedParameterJdbcTemplate.queryForObject(existingUtQuery, namedParameterJdbcTemplate, generatorRowMapper)).thenReturn(expectedResponse);
        Generator actualResponse = generatorRepo.findExistingUt(request);
        assertEquals(expectedResponse, actualResponse);
    }

How to write test cases for this DAO layer using Mockito ?

Answer 1

The code is not reproducible. So it should look this way. InjectMocks on a class that you are testing. Mock dependencies. Set up environment variables. and the in-test use argument matches. Hope it work.

@RunWith(MockitoJUnitRunner.class)
public class GeneratorRepositoryTest {

    @Mock
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @InjectMocks
    private GeneratorRepository generatorRepository;
    
    @Before
    public void setUp() {
        ReflectionTestUtils.setField(generatorRepository, "existingUtQuery", "select Top 1 * from existingUT where NO= :no AND SUB= :sub AND rt= :rt and STA= :sta order by LAST_UPDATE desc");
    }

   @Test
    public void testFindExistingUt() {
    Request request = new Request("12345", "F", 0, "XC");
    Generator expectedResponse = new Generator("12345", "F", 0, "XC", "12345.FCST", "345TDGHDF45G", "OS01", null);

    Mockito.when(namedParameterJdbcTemplate.queryForObject(Mockito.anyString(), Mockito.any(SqlParameterSource.class), Mockito.any(BeanPropertyRowMapper.class)))
            .thenReturn(expectedResponse);

    Generator actualResponse = generatorRepository.findExistingUt(request);

    assertEquals(expectedResponse, actualResponse);
    Mockito.verify(namedParameterJdbcTemplate).queryForObject(Mockito.anyString(), Mockito.any(SqlParameterSource.class), Mockito.any(BeanPropertyRowMapper.class));
}

}