Thedwick

A Technologist Who Speaks Business

Thedwick

4 years ago
ResultSet Mocking with JMock

I found myself recently wanting to mock out a whole mess of database interaction on a legacy system. This system didn’t have a strict data access layer, so direct calls to the database were strewn throughout the business logic.

Because JDBC is such a verbose library, mocking it out can be a challenge. For this task, I found myself with horrific-looking mock methods like this:

	
       private void mockSpecificPeopleQuery() throws SQLException {
		final PreparedStatement stmt = context.mock(PreparedStatement.class, "specificpeoplePreparedStatement");
		final ResultSet rs = context.mock(ResultSet.class, "specficpeopleResultSet");
		final Sequence rsSequence = context.sequence("specificpeople");
		context.checking(new Expectations() {{
			one(this.dbConnection).prepareStatement("SELECT mbid, people_id, name FROM specific_people"); will(returnValue(stmt));
			one(stmt).executeQuery(); will(returnValue(rs));
			one(rs).next(); inSequence(rsSequence); will(returnValue(true));
			one(rs).getString(1); will(returnValue("mbidCher")); 
			one(rs).getInt(2); will(returnValue(2)); //"people_id" column
			one(rs).getString(3); will(returnValue("Cher")); //"name" column
			one(rs).next(); inSequence(rsSequence); will(returnValue(false));
			one(rs).close(); inSequence(rsSequence);
			one(stmt).close(); inSequence(rsSequence);
		}});
		
	}

I thought there had to be a better way. I remembered and was inspired by a colleague of mine (Denis) who had once nicely encapsulated all this in a helper class. So I wrote myself a simple extension to the JMock Expectations class that makes mocked-out ResultSets a whole lot easier to read, more like this:

	private void mockSpecificPeopleQuery() throws SQLException {
		ResultSetExpectations mmsSpecificPeopleQuery = new ResultSetExpectations(this.dbConnection, this.context, "specificPeople");
		mmsSpecificPeopleQuery.expectQuery("SELECT mbid, people_id, name FROM specific_people");
		mmsSpecificPeopleQuery.newRow();
		mmsSpecificPeopleQuery.willReturnString(1,MBID_CHER);//"mbid" column
		mmsSpecificPeopleQuery.willReturnInt(2, CHER_ID);//"people_id" column
		mmsSpecificPeopleQuery.willReturnString(3, CHER);//"name" column
		mmsSpecificPeopleQuery.finishRows();
		this.context.checking(mmsSpecificArtistsQuery);
	}

Here’s the class. Feel free to pilfer it:

public class ResultSetExpectations extends Expectations {
	private final Sequence rowsSequence;
	private final Connection connectionMock;
	private final PreparedStatement stmt;
	private final ResultSet rs;
	private final Mockery context;
	
	public ResultSetExpectations(Connection conn, Mockery context, String disambiguation){
		this.context = context;
		this.connectionMock = conn;
		this.rowsSequence = context.sequence(disambiguation+"Sequence");
		this.stmt = context.mock(PreparedStatement.class, disambiguation+"PreparedStatement");
		this.rs = context.mock(ResultSet.class, disambiguation+"ResultSet");
	}
	
	public void expectQuery(String sql) throws SQLException{
		one(this.connectionMock).prepareStatement(sql); 
		will(returnValue(stmt));
		one(stmt).executeQuery(); 
		will(returnValue(rs));

	}

	public void newRow() throws SQLException{
		super.one(rs).next();
		super.inSequence(this.rowsSequence);
		super.will(returnValue(true));
	}
	
	public void willReturnInt(int column, int returnValue) throws SQLException{
		super.one(rs).getInt(column);
		super.will(returnValue(returnValue));
	}
	
	public void willReturnString(int column, String returnValue) throws SQLException {
		super.one(rs).getString(column);
		super.will(returnValue(returnValue));
	}
	
	public void finishRows() throws SQLException{
		super.one(rs).next(); super.inSequence(this.rowsSequence); super.will(returnValue(false));
		super.one(stmt).close(); 
		super.one(rs).close(); 
	}
	
}
	
}
♦ End

4 thoughts on “ResultSet Mocking with JMock

  1. Interesting solution to a tricky problem. I think some of the constraints could be relaxed. For example, clauses like one(rs).getInt(column) could use the allowing() clause since they’re effectively queries. On the other hand, you might also want to use States to enforce that the close calls are made last.

UA-16297310-1

Run a SaaS app and want to know how to improve customer retention?

Sign up for our free 5-week email course on combating churn.