Mocking Result Set using mockito

Question

I have a static class which has this method:

    public static Connection getDbConnection(String tenant, String product) {
    Connection connection = null;

    try {
        Map<String,Map<String,String >> databaseConnectionTable = PropertyUtil.getInstance().getDatabaseConnectionTable();
        Map<String,String> properties = getHighestPrecedenceMap(databaseConnectionTable,tenant,product);

        if (properties!=null) {

            Class.forName(properties.get("db.driver"));
            connection = DriverManager.getConnection(
                properties.get("db.url"),
                properties.get("db.user"),
                properties.get("db.password"));
        }
    } catch (ClassNotFoundException e) {
            LOGGER.error("Message",e);
        } catch (SQLException e) {
            LOGGER.error("Message:",e);
        }
    return connection;

}

Then I have another class which has a method for fetching the resultset given a SQL Query String, this method calls the above method, below is the source:

   public static ResultSet getResultSetFromSql(String sql,String tenant,String product) {
    Connection connection = null;
    PreparedStatement statement = null;
    ResultSet rs = null;
    try {
        if(product!=null)
        connection = SqlConnectionUtil.getDbConnection(tenant,product);
        RccSqlParameterMap parameterMap = RccSqlParameterMap.getParameterMap();

        if(connection!=null) {
            if (parameterMap.getSqlParameters().entrySet().size() > 0)
                sql = parameterMap.SqlMessageFormat(sql);
            else
                LOGGER.error("Parameter map isn't set please initialize it");

            LOGGER.info("Executing SQL: " + sql);
            statement = connection.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY,
                ResultSet.CONCUR_READ_ONLY);
            if (!statement.execute()) {
                LOGGER.error("No results found for statement!");
                return null;
            }
            rs = statement.getResultSet();
        }else{
            LOGGER.error("Coudn't create Connection Object");
        }
    } catch (SQLException e) {
        LOGGER.error("Message", e);
    }
    return rs;
}

I need to write unit tests for testing these, to have an in memory implementation I am able to mock the result set, by reading the rows from files, so when I instantiate the result set mocker and do getResultSet() I get the result set object, the problem I am facing is integrating this mocker with the above methods. Please suggest a way to do this.

Answer 1

With your current implementation it's impossible to mock connection object, since Mockito unable to mock static calls, that is possible with PowerMockito. There is possible solution (feel free to change test logic, it's just a worked skeleton with mocks for you)

import static org.junit.Assert.assertEquals;
import static org.mockito.Matchers.anyInt;
import static org.mockito.Matchers.anyString;
import static org.mockito.Matchers.eq;
import static org.mockito.Mockito.mock;
import static org.mockito.Mockito.when;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.mockito.Mock;
import org.mockito.MockitoAnnotations;
import org.powermock.modules.junit4.PowerMockRunner;
import org.powermock.api.mockito.PowerMockito;
import org.powermock.core.classloader.annotations.PrepareForTest;
import java.util.Map;
import java.util.HashMap;

@RunWith(PowerMockRunner.class)
@PrepareForTest({ SqlConnectionUtil.class, RccSqlParameterMap.class })
public class TestQueryRunner {

    @Test
    public void testGetResultSetFromSql() throws SQLException {
        ResultSet rs = mock(ResultSet.class);
        when(rs.getString(eq("foo"))).thenReturn("This is mocked value");

        PreparedStatement stmt = mock(PreparedStatement.class);
        when(stmt.getResultSet()).thenReturn(rs);
        when(stmt.execute()).thenReturn(true);

        Connection connection = mock(Connection.class);
        when(connection.prepareStatement(anyString(), anyInt(), anyInt()))
                .thenReturn(stmt);

        PowerMockito.mockStatic(SqlConnectionUtil.class);
        PowerMockito.when(SqlConnectionUtil.getDbConnection(anyString(), anyString()))
                .thenReturn(connection);

        Map<String, String> sqlParams = new HashMap<>();
        sqlParams.put("param1", "value1");
        RccSqlParameterMap paramMap = mock(RccSqlParameterMap.class);
        when(paramMap.getSqlParameters()).thenReturn(sqlParams);

        PowerMockito.mockStatic(RccSqlParameterMap.class);
        PowerMockito.when(RccSqlParameterMap.getParameterMap()).thenReturn(paramMap);       

        ResultSet actual = QueryRunner.getResultSetFromSql("SELECT ...",
                "tenant", "product");
        assertEquals(rs, actual);
        assertEquals("This is mocked value", actual.getString("foo"));
    }

}

Also, some general advices:

1. Always use {} in each if-else statements even if they are one-lined. This will be much more convenient to merge and support your code in the future.
2. Override your code to manage database connections properly. They should be closed! Use some third-party connection pooling mechanism like Apache DBCP

Hope it helps!

Answer 2

You can specify mock data right in the code of test case, there's no need to read something from the file system.

With Mockito you can make methods of the objects to return whatever you want:

// Initialize the object to be returned
ResultSet desiredResultSet = ...;

// After doing this you can mock methods of statement object
statement = Mockito.mock(PreparedStatement.class);

// Whenever you call statement.getResultSet(), it will return desiredResultSet
Mockito.doReturn(desiredResultSet).when(statement).getResultSet();

The only thing you need to change in your code to use this mechanism is to make Connection available to your test code. So that you can mock it's method that returns PreparedStatement the same way like I've demonstrated above.

In overall, I'd recommend you to split your methods to a bunch of smaller ones - right now they have too many things going on for just one method. This will also make your code much easier to unit test and mock.