JDBC Service Virtualisation
What does it do?
This library provides JDBC DataSource wrapper (similar to P6Spy) in order to spy on or mock database operations. It redirects JDBC operations to an HTTP server, so you can use WireMock or similar HTTP Mock Servers to define the actual Mock behaviour. The major advantage of this approach is that we can reuse existing technologies to remotely stub a JDBC endpoint.
Under the hood it uses P6Spy and MockRunner-JDBC to spy on the JDBC connection and hooks into PreparedStatement#executeQuery() and PreparedStatement#executeUpdate() to redirect the call to an HTTP Server as shown below:
The following example using Spring’s JdbcTemplate to avoid boilerplate JDBC and demonstrates how to use the technique to just spy on a real database and intercept/mock only selected jdbc queries.
public class UseWireMockToMockJdbcResultSetsTest {
@Rule
public WireMockRule wireMockRule = new WireMockRule(0);
JdbcTemplate jdbcTemplate;
@Before
public void before() {
JdbcServiceVirtualizationFactory myJdbcMockFactory = new JdbcServiceVirtualizationFactory();
myJdbcMockFactory.setTargetUrl("http://localhost:" + wireMockRule.port() + "/sqlstub");
DataSource dataSource = myJdbcMockFactory.createMockDataSource();
jdbcTemplate = new JdbcTemplate(dataSource);
}
@Test
public void intercepts_matching_query_and_responds_with_multi_column_mockresultset() {
final String NAME_ERICH_EICHINGER = "Erich Eichinger";
final String PLACE_OF_BIRTH = "Vienna";
// setup mock resultsets
WireMock.stubFor(WireMock
.post(WireMock.urlPathEqualTo("/sqlstub"))
// SQL Statement is posted in the body, use any available matchers to match
.withRequestBody(WireMock.equalTo("SELECT birthday, placeofbirth FROM PEOPLE WHERE name = ?"))
// Parameters are sent with index has headername and value as headervalue
.withHeader("1", WireMock.equalTo(NAME_ERICH_EICHINGER))
// return a recordset
.willReturn(WireMock
.aResponse()
.withBody(""
+ "<resultset>"
+ " <cols><col>birthday</col><col>placeofbirth</col></cols>"
+ " <row>"
+ " <birthday>1980-01-01</birthday>"
+ " <placeofbirth>" + PLACE_OF_BIRTH + "</placeofbirth>"
+ " </row>"
+ "</resultset>"
)
)
)
;
String[] result = jdbcTemplate.queryForObject(
"SELECT birthday, placeofbirth FROM PEOPLE WHERE name = ?"
, new Object[] { NAME_ERICH_EICHINGER }
, (rs, rowNum) -> {
return new String[] { rs.getString(1), rs.getString(2) };
}
);
assertThat(result[0], equalTo("1980-01-01"));
assertThat(result[1], equalTo(PLACE_OF_BIRTH));
}
}
Operation Modes
JdbcServiceVirtualizationFactory supports two modes, Spy Mode and Mock Mode
Spy Mode
In Spy Mode, the service virtualizer spies on a real datasource. In this case, only configured statements will be handled by WireMock, all other statements are routed through to the actual database.
Spy Mode is configured by wrapping the original DataSource using spyOnDataSource():
@Before
public void before() {
// wiremock is listening on port WireMockRule#port(), point our Jdbc-Spy to it
JdbcServiceVirtualizationFactory myP6MockFactory = new JdbcServiceVirtualizationFactory();
myP6MockFactory.setTargetUrl("http://localhost:" + wireMockRule.port() + "/sqlstub");
// wrap the real datasource so we can spy/intercept it
DataSource dataSource = ... // grab real DataSource
wrappedDataSource = myP6MockFactory.spyOnDataSource( dataSource );
// use wrapped dataSource
}
Full Example
Mock Mode
In Mock Mode you don’t need a real database. Any statement must be configured in WireMock. If WireMock returns 404 (i.e. no match was found), an {@link AssertionError} is thrown.
Mock Mode is configured by creating the datasource using createMockDataSource():
@Before
public void before() {
// wiremock is listening on port WireMockRule#port(), point our Jdbc-Spy to it
JdbcServiceVirtualizationFactory myP6MockFactory = new JdbcServiceVirtualizationFactory();
myP6MockFactory.setTargetUrl("http://localhost:" + wireMockRule.port() + "/sqlstub");
DataSource dataSource = myP6MockFactory.createMockDataSource();
// use dataSource as usual
}
Full Example
Specifying ResultSets
Using "named" column elements
XML ResultSets are specified in the same form as Sybase XML (http://dcx.sybase.com/1200/en/dbusage/xmldraftchapter-s-3468454.html), Element-names are interpreted as column-names. Only the first row is parsed to determine column names to be used for the resultset:
<resultset xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'>
<row><name>Erich Eichinger</name><birthday>1990-01-02</birthday><placeofbirth>London</placeofbirth></row>
<row><name>Matthias Bernlöhr</name><birthday>1995-03-04</birthday><placeofbirth>Stuttgart</placeofbirth></row>
...
</resultset>
If your column-name can’t be used as an XML element name, you can use the 'name' attribute instead. The element name is ignored in this case:
<resultset xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'>
<row><name>Erich Eichinger</name><birthday>1990-01-02</birthday><val name='Place of Birth'>London</val></row>
...
</resultset>
Using "positional" column elements
Instead of named xml elements, you can specify a special <cols> Element on top of the result set in order to specify the columns to be used for the resultset. In this case the order of value elements is obviously relevant:
<resultset>
<cols><col>Name</col><col>Birthday</col><col>Place of Birth</col></cols>
<row><val>James Bond</val><val>1900-04-01</val><val>Philadelphia</val></row>
</resultset>
NULL values
For "named" column formats, simply omit the element to emulate NULL values. Alternatively you can use the "xsi:nil='true'" attribute. For positional column formats it MUST be specified using xsi:nil
<resultset xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'>
<cols><col>name</col><col>birthday</col><col>placeofbirth</col></cols>
<row><col>James Bond</col><col xsi:nil='true'/><col>Philadelphia</col></row>
<row><name>Erich Eichinger</name><!-- birthday omitted --><placeofbirth>Philadelphia</placeofbirth></row>
</resultset>
Getting the Binaries
the library is available from Maven Central via
<dependency>
<groupId>com.github.eeichinger.service-virtualisation</groupId>
<artifactId>jdbc-service-virtualisation</artifactId>
<version>0.0.4.RELEASE</version>
</dependency>
Contributing
For bugs, feature requests or questions and discussions please use GitHub issues on https://github.com/eeichinger/jdbc-service-virtualisation/issues.
Building
To build the project simply run
mvn clean install
CI
Travis is used to build and release this project https://travis-ci.org/eeichinger/jdbc-service-virtualisation
Nightly Builds
nightly builds are triggered via https://nightli.es/
Build Reports
reports (Javadoc etc.) are published on https://eeichinger.github.com/jdbc-service-virtualisation
Change Log
The project will follow semantic versioning as soon as a stable 1.x.x line is released. For 0.x.x releases please expect binary incompatible changes.
0.0.4.RELEASE (2016-07-25)
-
#11 Connections returned from MockDataSource are not threadsafe