[FIXED] Mock ResultSet with Mockito

Issue

How to mock the resultset?

In the test class trying to mock the resultset as below but, when trying to test getting error as UnnecessaryStubbingException at statements :

voObj.setDept(rs.getString(2)); 

and

voObj.setDeptDesc(rs.getString(3));

Any suggessions on how to mock the resultset?

public class Example { 
    public static void main(String[] s) {
        method1();
        method2();
        ..........
    }

    private Employee method1(String str) {
                Connection conn           =  getConnection();   
        PreparedStatement pstmt   = null;
        .........
        pstmt = conn.prepareStatement(strQuery.toString());
        rs  = pstmt.executeQuery();
        int ilCounter   = 0; 
        int maxId   = method2(loc); //some DB calls here with select

        if(null != rs) {            
            while(rs.next()) { 
                ilCounter++;
                ObjVoBean voObj = new ObjVoBean();
                voObj.setLoc(rs.getString(1));
                voObj.setDept(rs.getString(2));
                voObj.setDeptDesc(rs.getString(3));
            }
            .................
        }
       }

   private Employee method2(String str1) {
                Connection connOHM      = getConnection();  
        PreparedStatement pstmt     = null;
        .........
       //some DB call with select ...
   }
}

public class ExampleTest { 
   @InjectMocks
   Example example;

   @Mock
   private Connection c;

   @Mock
   private PreparedStatement preStmt;
    .....

   @Before
   public void setUp() {
        ........
   }

   @Test
   public void testMethod1() throws SQLException {
        ResultSet resultSetMock = Mockito.mock(ResultSet.class);
        when(resultSetMock.getString(1)).thenReturn("1111");
        when(resultSetMock.getString(2)).thenReturn("2222");
        when(resultSetMock.getString(3)).thenReturn("dept desc");

        when(c.prepareStatement(any(String.class))).thenReturn(preStmt);
        when(resultSetMock.next()).thenReturn(true).thenReturn(false); 
        doReturn(resultSetMock).when(preStmt).executeQuery();

        example.method1("1111");            
        assertTrue(true);
   }
}

Solution

To be able to mock the ResultSet you should mock all objects that allow to create it that is Connection that creates the PreparedStatement, that itself creates the ResultSet. Mocking the Connection will work in the tested code only if you provide a way to set the connection from the client code.

Here conn that is the Connection should be first injected as a dependency in your test fixture :

pstmt = conn.prepareStatement(strQuery.toString());

Generally you create a Connection such as :

conn = DriverManager.getConnection(DB_URL,USER,PASS);

or via a DataSource such as :

conn = ds.getConnection();

So you should abstract this part into an interface or a not final class and define an implementation that do this processing. In this way you can mock the part that creates a Connection. And so you can mock the whole chain : Connection-PreparedStatement-ResultSet.

---

Personally I would avoid this way because mocking too many things is often not the right choice.
In your case, what you need is mocking the ResultSet to test the post processing after loading the ResultSet :

while(rs.next()) { 
     ilCounter++;
     ObjVoBean voObj = new ObjVoBean();
     voObj.setLoc(rs.getString(1));
     voObj.setDept(rs.getString(2));
     voObj.setDeptDesc(rs.getString(3));
}

So as alternative you could move all code performed before in a method of specific class handling the persistence part. In this way you just need to mock this dependency and this method. You don't need to worry about Connection and any JDBC detail.

EmployeeDAO employeeDAO; // dependency to mock

// constructor with dependency
public Example(EmployeeDAO employeeDAO){
  this.employeeDAO = employeeDAO;
}

private Employee method1(String str) {
   ResultSet resultSet = employeeDAO.load(str);

    if(null != rs) {            
        while(rs.next()) { 
            ilCounter++;
            ObjVoBean voObj = new ObjVoBean();
            voObj.setLoc(rs.getString(1));
            voObj.setDept(rs.getString(2));
            voObj.setDeptDesc(rs.getString(3));
        }
        .................
    }
   }

Of couse the DAO components also have to be unitary tested.
Bu as said earlier, asserting that a Connection is created or that it returns a PreparedStatement does not bring value. While testing that your queries perform what you expect from them is much more interesting in terms of functional coverage.
In this case, you want to test it against an in-memory DB such as H2 because unit tests are not integration tests and unit tests have to be fast executed.
To write DAO/Repository tests, Dbunit and DbSetup are good candidates because they provide facilities to setup the DB before each test (mainly injecting data and clearing data).

Answered By - davidxxx
Answer Checked By - Mildred Charles (JavaFixing Admin)