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.
The database we’ve selected is H2. Why H2?
It’s a simple but robust, embeddable database created by the original developer of the HSQL database.
Has configurable database compatibility modes to emulate non-ansi SQL grammar of other databases
Feel free to use whatever embedded database meets your needs (derby, hsql, etc.). This is just what we’ve chosen for this example.
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.
The properties are externalized and read from the config.properties.In our src/test/resources/config.properties, we have settings for the embedded data source.
Put your non-embedded config.properties 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).
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 config.properties (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: