Skip to content

Support JDBC spying and mocking via HTTP using a potentially remote WireMock or similar

Notifications You must be signed in to change notification settings

eeichinger/jdbc-service-virtualisation

Repository files navigation

JDBC Service Virtualisation Build Status

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:

flow spy

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():

flow spy

@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.

flow mock

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)

Bugs
  • #11 Connections returned from MockDataSource are not threadsafe

0.0.3.RELEASE (2016-07-12)

Enhancements
  • #9 Default xml result-set response parsing to UTF-8 instead ISO-8859-1

  • #8 Support NULL values in Mock ResultSets

Bugs
  • #4 pull request builds on travis fail b/c they can’t be signed

About

Support JDBC spying and mocking via HTTP using a potentially remote WireMock or similar

Resources

Stars

Watchers

Forks

Packages

No packages published