JUnit: mocking jdbcTemplate's query() method with a RowCallbackHandler

Question

I have the following code in my Java class in a Spring Boot (v. 2.2.1.RELEASE) application:

@Inject
private JdbcTemplate jdbcTemplate;

@Inject
private MyRowCallbackHandler myRowCallbackHandler;

public void myMethod() {
    jdbcTemplate.query(MY_QUERY, myRowCallbackHandler);
}

The JDBC template object is an implementation of org.springframework.jdbc.core.JdbcTemplate and the handler is an implementation of org.springframework.jdbc.core.RowCallbackHandler.

With JUnit version 4 and Mockito, can I mimic the retrieval of one or more rows from a database by the query method, thus calling the handler's processRow() method?

Thanks for any assistance.

Answer 1

I was struggling with the same problem. First, you have to keep in mind that you can mock almost anything in spring boot using Junit tests.

I am writing the solution in a generalized pattern so that everyone can get an idea how it gets implemented and used.

Suppose, we have a function implemented in our BookService.java class:

@Service
public class BookService {

    @Inject
    private NamedParameterJdbcOperations jdbcTemplate;
    
    @Inject
    private FileHelper fileHelper;
    
    public List<BookDTO> getBooks(Long libraryId){
        String sql = fileHelper.getFileContents("SQL_FILE_PATH");
        
        Map<String, Object> parameterMap = new HashMap<>();
        if(libraryId!=null){
            parameterMap.put("libraryId", libraryId);
        }
        
        List<BookDTO> books = new ArrayList<>();
        
        jdbcTemplate.query(sql, parameterMap, rs -> {
            BookDTO book = new BookDTO();
            book.setId(rs.getLong("id"));
            book.setName(rs.getString("name"));
            
            if(rs.getObject("librarian") != null){
                book.setLibrarian(rs.getString("librarian"));
            }
            books.add(book);
        });
        
        return books;
    }
}

Now, we want to mock the functionality of the jdbcTemplate.query() method for the above service class.

Our test should be written in this pattern:

public class BookServiceMockTest {
    @Mock
    private NamedParameterJdbcOperations jdbcTemplate;
    
    @Mock
    private FileHelper fileHelper;
    
    private BookService mockBookService;
    
    @Before
    public void setup(){
        mockBookService = new BookService();
        // using reflectionUtils setFields of the fileHelper and jdbcTemplate.
        // ....
    }
    
    @Test
    public void getBooksTest(){
        
        when(fileHelper.getFileContents("SQL_FILE_PATH")).thenReturn("SOME SQL");
        doAnswer(invocation -> {
            // we are going to mock ResultSet class.
            ResultSet rs = Mockito.mock(ResultSet.class);
            when(rs.getLong("id")).thenReturn(1L);
            when(rs.getString("name")).thenReturn("Game of Thrones");
            
            // this will mock the if() statement
            when(rs.getObject("librarian")).thenReturn("John Doe");
            
            // this will mock the actual get statement call on getString() inside the if statement
            when(rs.getString("librarian")).thenReturn("John Doe");
            
            // the argument index is important here.
            // we are doing getArgument(2).
            // This means the third parameter passed in the jdbcTemplate.query(Param0, Param1, Param2) in the BookService.getBooks() function.
            RowCallbackHandler rch = (RowCallbackHandler) invocation.getArgument(2);
            // as we are mocking only one row..
            rch.processRow(rs);
            
            /* // if we wanter two or more results:
                when(rs.getLong("id")).thenReturn(1L).thenReturn(2L);
                when(rs.getString("name")).thenReturn("Game of Thrones").thenReturn("Dance of the Dragon");
                int n = 2; // no of rows..
                for(int i=0; i<n; i++){
                    rch.processRow(rs);
                }
            */
            return null;
        })
        // the parameters used here are important. Any mismatch will result in unsuccessful.
        .when(jdbcTemplate).query(eq("SOME SQL"), anyMap(), any(RowCallbackHandler.class));
        
        List<BookDTO> books = mockBookService.getBooks(anyLong());
        
        verify(jdbcTemplate, times(1)).query(eq("SOME SQL"), anyMap(), any(RowCallbackHandler.class));
        
        assertThat(books).hasSize(1);
    }
}

I hope this answered what you were looking for!

Answer 2

I ran into this problem in my own code, thought I'd share the solution here, even though it's slightly different than the situation above as I mock the jdbcTemplate as well.

@InjectMocks
private JdbcOperationRepository jdbcOperationRepository;

@Mock
private NamedParameterJdbcTemplate mockJdbcTemplate;

@Test
public void testMyResults() {
  final ResultSet mockResult1 = mock(ResultSet.class);
  when(mockResult1.getString(MY_COLUMN)).thenReturn(value);
  // ... other when statements to mock the returned data

  doAnswer(invocation -> {
      RowCallbackHandler callbackHandler = invocation.getArgument(2);
      callbackHandler.processRow(mockResult1);
      callbackHandler.processRow(mockResult2);
      return null;
  }).when(mockJdbcTemplate).query(any(), any(), any(RowCallbackHandler.class));
}