Junit Mockito test everything

Question

I am searching for more hours now with no result. Please help...

This is my class to test:

public class DBSelectSchema extends Database {

    private static final Logger LOG = Logger
            .getLogger(DBSelectSchema.class.getName());
    private Connection conn = null;

    public DBSelectSchema() {
        super();
    }

    /**
     * This method will return the version of the database.
     * 
     * @return version
     * @throws Exception
     */
    public JSONObject getVersionFromDB() throws SQLException {
        ResultSet rs = null;
        JSONObject version = new JSONObject();
        PreparedStatement query = null;

        try {
            conn = mensaDB();
            query = conn.prepareStatement("SELECT number FROM version");

            rs = query.executeQuery();

            if (rs.isBeforeFirst()) {
                rs.next();
                version.put(HTTP.HTTP, HTTP.OK);
                version.put("version", rs.getString("number"));
            } else {
                version.put(HTTP.HTTP, HTTP.NO_CONTENT);
                version.put(HTTP.ERROR, "Die SQL Abfrage lieferte kein Result!");
            }

            rs.close();
            query.close();
            conn.close();

        } catch (SQLException sqlError) {
            String message = ERROR.SQL_EXCEPTION;
            LOG.log(Level.SEVERE, message, sqlError);
            return version;

        } catch (JSONException jsonError) {
            String message = ERROR.JSON_EXCEPTION;
            LOG.log(Level.SEVERE, message, jsonError);
            return version;
        }

        return version;
    }

I am trying to get in each branch for 100% code coverage. How can I mock ResultSet rs, JSONObject version and PreparedStatement query to do/return what I want:

Currently I am testing like that:

@Test
    public void getVersionFromDB_RS_FALSE() throws SQLException, JSONException {
        MockitoAnnotations.initMocks(this);

        Mockito.when(dbSelMocked.mensaDB()).thenReturn(conn);
        Mockito.when(conn.prepareStatement(Mockito.anyString())).thenReturn(query);
        Mockito.when(query.executeQuery()).thenReturn(rs);
        Mockito.when(rs.isBeforeFirst()).thenReturn(false);

        JSONObject returnObj = dbSelMocked.getVersionFromDB();

        assert(...);
    }

But this just works when the 3 variables are class variables (like Connection conn) and not local variables. But I dont want them (even Connection) not to be global.

=== EDIT 1 ===

It works like that if all variables are local:

@Test
    public void getVersionFromDB_RS_FALSE() throws SQLException, JSONException {
        System.out.println("####################");
        System.out.println("started test: getVersionFromDB_RS_FALSE");
        System.out.println("####################");

        Connection conn = Mockito.mock(Connection.class);
        PreparedStatement query = Mockito.mock(PreparedStatement.class);
        ResultSet rs = Mockito.mock(ResultSet.class);

        MockitoAnnotations.initMocks(this);


        Mockito.when(dbSelMocked.mensaDB()).thenReturn(conn);
        Mockito.when(conn.prepareStatement(Mockito.anyString())).thenReturn(query);
        Mockito.when(query.executeQuery()).thenReturn(rs);
        Mockito.when(rs.isBeforeFirst()).thenReturn(false);

        JSONObject returnObj = dbSelMocked.getVersionFromDB();

        assertTrue(returnObj.has("error"));
    }

But I am not able to mock JSONObject version in another test anymore :( How can I do that?

@Test
    public void getVersionFromDB_JSON_EXCEPTION() throws SQLException, JSONException {
        System.out.println("####################");
        System.out.println("started test: getVersionFromDB_JSON_EXCEPTION");
        System.out.println("####################");
        JSONObject version = Mockito.mock(JSONObject.class);

        MockitoAnnotations.initMocks(this);

        doThrow(new JSONException("DBSelectSchemaIT THROWS JSONException")).when(version).put(anyString(), any());

        JSONObject returnObj = dbSelMocked.getVersionFromDB();

        System.out.println(returnObj.toString());

        assertTrue(returnObj.equals(null));
    }

I think its overwritten in the real method... because it does not throw an exception and the method does not fail.

Solution 1

Your test code has multiple issues.

• The test is verbose and fragile
• The same (verbose) setup is required for multiple tests
• You don't test real object, instead you are using mock of your class for testing

The first 2 issues can be solved by extracting repeated code to a setup method (I added static import for Mockito to reduce the noise):

@Before
public void setUp() throws Exception {
    Connection conn = mock(Connection.class);
    PreparedStatement query = mock(PreparedStatement.class);
    when(dbSelMocked.mensaDB()).thenReturn(conn);
    when(conn.prepareStatement(anyString())).thenReturn(query);
    when(query.executeQuery()).thenReturn(rs);

    rs = mock(ResultSet.class); // rs is field
}

Now in each of your tests you can configure rs to return whatever you need:

@Test
public void getVersionFromDB_RS_FALSE() throws Exception {
    // Given
    when(rs.isBeforeFirst()).thenReturn(false);

    // When
    JSONObject returnObj = dbSelMocked.getVersionFromDB();

    // Then
    assertTrue(returnObj.has("error"));
}

Now the most important issue: you are mocking class DBSelectSchema to return connection mock. Mocking class under test can cause different hard-to-spot problems.

To solve this issue you have 3 options:

1. Refactor your code and inject some connection factory. So you'll be able to mock it in your test.

2. Extend class DBSelectSchema in your test and override method mensaDB() so it will return mocked connection

3. Use embedded database like H2 and put test data in 'number' table before calling getVersionFromDB()

Option #1

Extract creation of connection to a separate class and use it in your DBSelectSchema:

public class ConnectionFactory {
    public Connection getConnection() {
       // here goes implementation of mensaDB()
    }
}

Then inject it to your DBSelectSchema:

public DBSelectSchema(ConnectionFactory connFactory) {
    this.connFactory = connFactory;
}

Now your test you can use real DBSelectSchema class with mocked ConnectionFactory

    ConnectionFactory connFactory = mock(ConnectionFactory.class);
    dbSel = new DBSelectSchema(connFactory); 

Option #2

You can make almost real class under test:

    final Connection conn = mock(Connection.class);
    dbSel = new DBSelectSchema() {
        @Override
        public Connection mensaDB() {
            return conn;
        }
    }; 

Option #3

This option is most preferable, because you will call real SQL commands and you mock the whole database instead of classes. It requires some effort to use plain JDBC here, but it worth that. Keep in mind that SQL dialect can differ from the DB used in production.

@Before
public void setUp() throws Exception {
    Class.forName("org.h2.Driver");
    conn = DriverManager.getConnection("jdbc:h2:mem:test;INIT=RUNSCRIPT FROM 'classpath:schema.sql'");
}

@After
public void tearDown() throws Exception {
    conn.close();
}

Then in your test you simply add required records to DB:

 @Test
 public void getVersionFromDB() throws Exception {
    // Given
    conn.prepareStatement("INSERT INTO version(number) VALUES (1)").execute();

    // When
    JSONObject returnObj = dbSel.getVersionFromDB();

    // Then
    assert(...);
}

Obviously, DBSelectSchema must use the same connection, so you can use in combination with options #1 and #2,

Solution 2

You are unit testing data access layer by mocking all ADO calls. By doing so, you will end up with a unit test that does not really test any logic.

Taking an example from your code: assume that you are using the following sql to retrieve a version number : SELECT number FROM version. Now assume that the column name changed and you should retrieve 2 additional column from your sql. You will eventually end up with an sql like SELECT number, newColumn1, newColumn2 FROM version. With the test you would have written (using mock), it would still pass even though its not really testing whether the 2 new column is being retrieved. You get my point?

I would advise you to have a look at this thread for some possible alternative to test your data access layer. Using mock for your data access layer will end up with brittle test that does not really test anything