July 11, 2013

Mocking JDBC Endpoints in Mule Functional Tests

Mocking Endpoints Series


Continuing on our last post about Mocking HTTP Endpoints, we’re going to provide an example of how to use an embedded database to mock out JDBC endpoints in Mule using H2.



Many Mule projects need to interact with an RDBMS at some point. Mule provides the JDBC transport for easy access but many times, you’ll want to avoid actually using a shared database. Sharing a database in a development environment can cause several problems:

  • Contention with other people (developers) or processes (CI servers)
  • Ensuring the database is in the correct state for your tests (predictable data)
  • Cleaning up your changes for the next set of tests

There are various ways of handling these problems. For instance, by using spring and its transaction managed test case, you can rollback any changes between tests but that’s not an option we have in a Mule flow (not easily at least). Another option would be to create a unique database for everyone who wishes to run the tests. This isn’t really realistic to manage long term.

A locally embedded database which loads up fresh, predictable data upon test startup is the approach we’re using here.


H2 Database


The database we’ve selected is H2. Why H2?

Feel free to use whatever embedded database meets your needs (derby, hsql, etc.). This is just what we’ve chosen for this example.


Mule Configuration


Our Mule flow declares some Spring beans which will help us manage our data source:


This is pretty normal configuration. There are a few interesting points to call out.

  1. The properties are externalized and read from the our src/test/resources/, we have settings for the embedded data source.

    Put your non-embedded somewhere on the classpath for the server
    (e.g. MULE_HOME/conf or in src/main/resources).

    Notice that we’ve made use of the h2 features to run a script: ddl.sql and set the compatibility mode to MYSQL (to help us emulate our production environment better).

  2. We’ve externalized our SQL query templates into a file. This isn’t necessary but it’s nicer than writing SQL inside your XML file.


DDL and Mock Data


In our (above), we told it to run a script: src/test/resources/jdbc/ddl.sql

Again, we’ve made use of another nifty feature of h2: CSV Bulk Loading. This enables us to cleanly load a fresh set of tables and data upon each startup (since it’s in-memory). If you have more complex setup requirements, I’d take a look at:




The only other thing we need is the database library itself. Since we’re using a Maven build, just add it to the pom with a scope of test:


The Test


Now that we have our embedded database in place, we can run our functional test case to prove the flow functions correctly


Sample Project


You can checkout the complete project as part of our Github Mule Cookbook Project

The project will be updated to include other examples, updated documentation, etc.

One thought on “Mocking JDBC Endpoints in Mule Functional Tests

  1. applicius says:

    Acolyte framework can also be used to mock connection for tests, with required data cases but no need of any running DB outside testing env (there H2): .

Leave a Reply

Your email address will not be published. Required fields are marked *