How to mock jdbc connection and resultSet using Mockito in TestNG

Question

I have to write some unit tests but I have problem with mocking ResultSet and jdbc Connection.

I have this method:

@Test
public void test3() throws SQLException, IOException {

    Connection jdbcConnection = Mockito.mock(Connection.class);
    ResultSet resultSet = Mockito.mock(ResultSet.class);

    Mockito.when(resultSet.next()).thenReturn(true).thenReturn(true).thenReturn(true).thenReturn(false);
    Mockito.when(resultSet.getString(1)).thenReturn("table_r3").thenReturn("table_r1").thenReturn("table_r2");
    Mockito.when(jdbcConnection
            .createStatement()
            .executeQuery("SELECT name FROM tables"))
            .thenReturn(resultSet);

    //when
    List<String> nameOfTablesList = null;
    try {
        nameOfTablesList = Helper.getTablesName(jdbcConnection);
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

    //then
    Assert.assertEquals(nameOfTablesList.size(), 3);
}

And error is showing in line executeQuery("SELECT name FROM tables") and it sounds like this:

java.lang.NullPointerException HelperTest.test3(HelperTest.java:71)

Any ideas whats going wrong?

Answer 1

You need to create an expectation on jdbcConnection.createStatement().

By default, I believe a null is returned.

Should read something like:

ResultSet resultSet = Mockito.mock(ResultSet.class);
Mockito.when(resultSet.next()).thenReturn(true).thenReturn(true).thenReturn(true).thenReturn(false);
Mockito.when(resultSet.getString(1)).thenReturn("table_r3").thenReturn("table_r1").thenReturn("table_r2");

Statement statement = Mockito.mock(Statement.class);
Mockito.when(statement.executeQuery("SELECT name FROM tables")).thenReturn(resultSet);

Connection jdbcConnection = Mockito.mock(Connection.class);
Mockito.when(jdbcConnection.createStatement()).thenReturn(statement);

Answer 2

Mocking the JDBC API on this low level is rather tedious as you should really look into mocking the entirety of the JDBC API. Just a few examples:

• What would happen if anyone called ResultSet.previous()?
• What would happen if anyone called ResultSet.getObject() rather than getString()?
• What would happen if the ResultSet was obtained through Statement.getResultSet()?

To your client code, it shouldn't matter much if you're calling JDBC one way or another, the result should always be the same. If you really have to mock the database (rather than use e.g. a test database, or better, a testcontainers based approach), then using something like jOOQ's MockDataProvider or MockFileDatabase would certainly make things much simpler. In your case:

MockDataProvider db = new MockFileDatabase(
    "SELECT name FROM tables;\n"
  + "> name\n"
  + "> --------\n"
  + "> table_r3\n"
  + "> table_r1\n"
  + "> table_r2\n"
  + "> @rows: 3\n");

//when
List<String> nameOfTablesList = null;
try {
    nameOfTablesList = Helper.getTablesName(new MockConnection(db));
} catch (SQLException e) {
    e.printStackTrace();
}

//then
Assert.assertEquals(nameOfTablesList.size(), 3);

The above approach will work regardless what the Helper.getTablesName() method does with the passed JDBC Connection.

Note, I'm working for the company behind jOOQ, so this answer is biased.