how to mock resultset and populate it using Mockito in Java

I have code where I populate Resultset with CallableStatement.executeQuery(). I have mocked ResultSet and CallableStatement but in order to test the method i have to populate ResultSet.

Here is the code from the method I am testing

ResultSet rset = cs.executeQuery();
while (rset.next()) {
IndexVolatilityImpl tsImpl = new IndexVolatilityImpl();
tsImpl.setTradeDate(rset.getString("trade_date"));
tsImpl.setTradeTime(rset.getString("trade_time"));
tsImpl.setExprDate(rset.getString("expr_date"));
tsImpl.setSymbol(rset.getString("symbol"));
tsImpl.setTradePrice(rset.getDouble("trade_price"));
tsImpl.setContractMonth(rset.getString("contract_month"));
tsImpl.setMilliSecs(rset.getString("trade_time_thou"));
colIndexVolatilityImpl.add(tsImpl);

I have mocked the CallableStatement and ResultSet now since they are mocked my rset comes up empty. I would like to populate Resultset and doing it as below

resultSetMock = Mockito.mock(ResultSet.class);
Mockito.when(resultSetMock.getString("trade_date")).thenReturn("03/10/2011");
Mockito.when(resultSetMock.getString(开发者_开发百科"trade_time")).thenReturn("12:24:56");
Mockito.when(resultSetMock.getString("expr_date")).thenReturn("03/19/2011");
Mockito.when(resultSetMock.getString("symbol")).thenReturn("VIX1");
Mockito.when(resultSetMock.getDouble("trade_price")).thenReturn(Double.valueOf("20.96"));
Mockito.when(resultSetMock.getString("contract_month")).thenReturn("1");
Mockito.when(resultSetMock.getString("trade_time_thou")).thenReturn("165");

Mockito.doReturn(resultSetMock).when(callableStatementMock).executeQuery();

But rset is null.

You should also mock the next() method to have it return true the first time it's called, as mockito will return false by default.

Mockito.when(resultSetMock.next()).thenReturn(true).thenReturn(false);

I have written something for this same case. You can mock the resultset using Mockito. You can as well loop over the mock rows of resultset by mocking the resultset.next() with this piece of code.

// two dimensional array mocking the rows of database.
String[][] result = { { "column1", "column2" }, { "column1", "column2" } };

@InjectMocks
@Spy
private TestableClass testableClass;

@Mock
private Connection connection;

@Mock
private Statement statement;

@Mock
private ResultSet resultSet;

@BeforeTest
public void beforeTest() {
    MockitoAnnotations.initMocks(this);
}

@BeforeMethod
public void beforeMethod() throws SQLException {
    doAnswer(new Answer<Connection>() {
        public Connection answer(InvocationOnMock invocation)
                throws Throwable {
            return connection;

        }
    }).when(testableClass).getConnection();

    when(connection.createStatement()).thenReturn(statement);
    when(statement.executeQuery(anyString())).thenReturn(resultSet);
    final AtomicInteger idx = new AtomicInteger(0);
    final MockRow row = new MockRow();

    doAnswer(new Answer<Boolean>() {

        @Override
        public Boolean answer(InvocationOnMock invocation) throws Throwable {
            int index = idx.getAndIncrement();
            if (result.length <= index) {
                return false;
            } 
            String[] current = result[index];
            row.setCurrentRowData(current);
            return true;

        }

        ;
    }).when(resultSet).next();

    doAnswer(new Answer<String>() {

        @Override
        public String answer(InvocationOnMock invocation) throws Throwable {
            Object[] args = invocation.getArguments();
            int idx = ((Integer) args[0]).intValue();
            return row.getColumn(idx);
        }

        ;
    }).when(resultSet).getString(anyInt());
}

static class MockRow {
    String[] rowData;

    public void setCurrentRowData(String[] rowData) {
        this.rowData = rowData;
    }

    public String getColumn(int idx) {
        return rowData[idx - 1];
    }
}

I rewrote @karthik m's answer a bit to make the ResultSet mocker standalone:

By using the below class I can easily export the result from a query as csv and write a test around that.

Not all methods from the ResultSet are mocked, as I didn't need them, but those should be fairly trivial to use.

import no.di.common.util.StringUtil;
import org.apache.commons.io.FileUtils;
import org.apache.commons.io.LineIterator;
import org.mockito.invocation.InvocationOnMock;
import org.mockito.stubbing.Answer;

import java.io.File;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.atomic.AtomicInteger;

import static org.mockito.Matchers.anyInt;
import static org.mockito.Matchers.anyString;
import static org.mockito.Mockito.doAnswer;
import static org.mockito.Mockito.mock;

/**
 * Creates a Mock of a ResultSet
 */
public class ResultSetMocker {

    private Map<String, Integer> columnNames = new HashMap<>();

    private Object[][] result;

    public ResultSetMocker(String filename) throws IOException {
        loadData(filename);
    }

    private void loadData(String filename) throws IOException {
        List<Object[]> toRet = new ArrayList<>();

        int numberOfParts = 0;
        LineIterator it = FileUtils.lineIterator(new File(filename), "ISO8859-1");
        try {
            String names = it.nextLine();
            String[] name = names.split(";");
            for(int i = 0; i < name.length; i++) {
                columnNames.put(name[i], i + 1);
            }

            while (it.hasNext()) {
                String line = it.nextLine();

                String[] parts = line.split(";");
                numberOfParts = parts.length;
                Object[] result = new Object[parts.length];
                for(int i = 0; i < parts.length; i++) {
                    if(parts[i].equals("(null)"))
                        result[i] = null;
                    else if(StringUtil.isAllNumeric(parts[i]))
                        result[i] = Integer.parseInt(parts[i]);
                    else
                        result[i] = parts[i];
                }

                toRet.add(result);
            }
        } finally {
            it.close();
        }

        result = toRet.toArray(new Object[toRet.size()][numberOfParts]);
    }

    public ResultSet getResultSet() throws SQLException, IOException {
        ResultSet resultSet = mock(ResultSet.class);

        final AtomicInteger idx = new AtomicInteger(0);
        final MockRow row = new MockRow(columnNames);

        doAnswer(new Answer<Boolean>() {
            @Override
            public Boolean answer(InvocationOnMock invocation) throws Throwable {
                int index = idx.getAndIncrement();
                if (result.length > index) {
                    row.setCurrentRowData(result[index]);
                    return true;
                } else
                    return false;
            }
        }).when(resultSet).next();

        doAnswer(new Answer<String>() {
            @Override
            public String answer(InvocationOnMock invocation) throws Throwable {
                Object[] args = invocation.getArguments();
                int idx = (Integer) args[0];
                return row.getString(idx);
            }
        }).when(resultSet).getString(anyInt());

        doAnswer(new Answer<String>() {
            @Override
            public String answer(InvocationOnMock invocation) throws Throwable {
                Object[] args = invocation.getArguments();
                String name = (String) args[0];
                return row.getString(name);
            }
        }).when(resultSet).getString(anyString());

        doAnswer(new Answer<Object>() {
            @Override
            public Object answer(InvocationOnMock invocation) throws Throwable {
                Object[] args = invocation.getArguments();
                String name = (String) args[0];
                return row.getObject(name);
            }
        }).when(resultSet).getObject(anyString());

        doAnswer(new Answer<Integer>() {
            @Override
            public Integer answer(InvocationOnMock invocation) throws Throwable {
                Object[] args = invocation.getArguments();
                String name = (String) args[0];
                return row.getInt(name);
            }
        }).when(resultSet).getInt(anyString());

        return resultSet;
    }

    static class MockRow {
        Object[] rowData;
        private Map<String, Integer> columnNames;

        public MockRow(Map<String, Integer> columnNames) {

            this.columnNames = columnNames;
        }

        public void setCurrentRowData(Object[] rowData) {
            this.rowData = rowData;
        }

        public String getString(int idx) {
            return (String)rowData[idx - 1];
        }

        public String getString(String name) {
            return (String)rowData[columnNames.get(name) - 1];
        }

        public Object getObject(String name) {
            return rowData[columnNames.get(name) - 1];
        }

        public Integer getInt(String name) {
            return (Integer)rowData[columnNames.get(name) - 1];
        }
    }
}

10 years on from when this question was asked there's a good chance you would be using Spring Boot 2+ with h2 as an in-memory DB for testing. If that's the case then you can make use of the org.h2.tools.Csv class to create a ResultSet implementation from CSV data:

import org.h2.tools.Csv;

// mock resultset

String csvResults =
    "0001, John Doe\n" +
    "0002, Bob Smith\n" +
    "0003, Alice Doe\n";

ResultSet rs = new Csv().read(new StringReader(csvResults), new String[] {"id", "name"});

Then use that ResultSet any way that you want. Here's a Mockito example that supplies it as a RowCallbackHandler to a JdbcTemplate query() call.

JdbcTemplate mockTemplate = mock(JdbcTemplate.class);

doAnswer(ia -> {
  while (rs.next()) {
    ia.getArgument(2, RowCallbackHandler.class).processRow(rs);
  }
  return null;
}).when(mockTemplate).query(any(String.class), any(SqlParameterSource.class), any(RowCallbackHandler.class));

// change the above when() args to match how you are using JdbcTemplate

A little late to the game here, but it looks like all you needed on your original Mock was this...

Mockito.when(resultSetMock.next()).thenReturn(true);

This is necessary so that the mapper - whatever you are using to map the resultset will know that there is data in the result set.