how to mock resultset and populate it using Mockito in Java

Question

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.

score 56 · Answer 1 · edited Jun 26 '18 at 08:35

56

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);

edited Jun 26 '18 at 08:35

Ahmed Ashour

5,179
10
35
56

answered Jun 07 '11 at 15:55

proactif

11,331
1
17
11

2

I am doing that but I would like to Populate ResultSet and I am doing as described above but it comes out to null. Can you please shed light what am i doing wrong? – Tejas Shah Jun 07 '11 at 16:50
Just tried a similar test code and it worked. So your rset is null ? I guess the cs object is of course the callableStatementMock, in which case it should work. I thought your problem was just that your RS was empty, but I dont see how it can be null. – proactif Jun 07 '11 at 17:40

score 13 · Answer 2 · edited Nov 21 '16 at 21:48

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];
    }
}

@UmerHayat - it is a clean encapsulation over an array representation. — karthik m, Dec 12 '14 at 01:00
@UmerHayat Hi, it should be good like this? Do I have to write a specific test? — spikeTJ, Sep 02 '21 at 20:39

score 10 · Answer 3 · answered Nov 25 '21 at 12:34

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

This is the best way and it's so easy! Thank you. – Mateusz Sobala May 13 '22 at 10:27 — Mateusz Sobala, May 13 '22 at 10:27

score 7 · Answer 4 · answered Oct 09 '14 at 08:48

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];
        }
    }
}

score -1 · Answer 5 · edited Jun 26 '18 at 08:35

-1

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.

edited Jun 26 '18 at 08:35

Ahmed Ashour

5,179
10
35
56

answered May 04 '18 at 14:39

Dave

1

This will make the loop go on forever. – Dalton Jan 07 '19 at 12:09
try this it will break Mockito.when(resultSetMock.next()).thenReturn(true).thenReturn(false); – Rohit Chaurasiya Jul 15 '20 at 13:28

how to mock resultset and populate it using Mockito in Java

5 Answers5