Blog Post: Mocking JDBC Endpoints in Mule Function Tests

config.properties

jdbc.url=jdbc:h2:mem:mule;INIT=RUNSCRIPT FROM 'classpath:/jdbc/ddl.sql';MODE=MYSQL
jdbc.username=sa
jdbc.password=
jdbc.driver=org.h2.Driver
jdbc.validationQuery=select 1

ddl.sql

CREATE TABLE IF NOT EXISTS user (
  user_name  VARCHAR(16) PRIMARY KEY,
  last_name  VARCHAR(32) NOT NULL,
  first_name VARCHAR(32) NOT NULL
)
AS SELECT
     user_name,
     last_name,
     first_name
   FROM CSVREAD('classpath:/jdbc/users.csv');

dependencies.xml

<!-- mock: JDBC Server -->
<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version>1.3.168</version>
    <scope>test</scope>
</dependency>

EmbeddedDatabaseServerFunctionalTest.groovy

package com.confluex.cookbook.jdbc

import org.junit.BeforeClass
import org.junit.Test
import org.mule.tck.junit4.FunctionalTestCase


class EmbeddedDatabaseServerFunctionalTest extends FunctionalTestCase {

    @BeforeClass
    static void enableVerboseErrors() {
        System.setProperty("mule.verbose.exceptions", "true")
    }

    @Override
    protected String getConfigResources() {
        return "jdbc-config.xml"
    }

    @Test
    void shouldLookupUsersFromInClause() {
        def msg = muleContext.client.send("getUserInfo", "bmurray", [:], 10000)
        assert msg.payload == [[USER_NAME:'bmurray', LAST_NAME:'Murray', FIRST_NAME:'Bill']]
    }
}

jdbc-confg.xml

<mule xmlns="http://www.mulesoft.org/schema/mule/core"
      xmlns:vm="http://www.mulesoft.org/schema/mule/vm"
      xmlns:jdbc="http://www.mulesoft.org/schema/mule/jdbc"
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      xmlns:context="http://www.springframework.org/schema/context"
      xmlns:spring="http://www.springframework.org/schema/beans"
      xsi:schemaLocation="
http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd
http://www.mulesoft.org/schema/mule/vm http://www.mulesoft.org/schema/mule/vm/current/mule-vm.xsd
http://www.mulesoft.org/schema/mule/jdbc http://www.mulesoft.org/schema/mule/jdbc/current/mule-jdbc.xsd
http://www.mulesoft.org/schema/mule/core http://www.mulesoft.org/schema/mule/core/current/mule.xsd">

    <context:property-placeholder location="classpath:config.properties"/>

    <spring:beans>
        <spring:bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
            <!-- 1) externalize your properties -->
            <spring:property name="url" value="${jdbc.url}"/>
            <spring:property name="username" value="${jdbc.username}"/>
            <spring:property name="password" value="${jdbc.password}"/>
            <spring:property name="driverClassName" value="${jdbc.driver}"/>
            <spring:property name="initialSize" value="5"/>
            <spring:property name="testOnBorrow" value="true"/>
            <spring:property name="validationQuery" value="${jdbc.validationQuery}"/>
            <spring:property name="maxWait" value="60000"/>
            <spring:property name="maxActive" value="50"/>
            <spring:property name="removeAbandoned" value="true"/>
            <spring:property name="removeAbandonedTimeout" value="300"/>
            <spring:property name="logAbandoned" value="true"/>
        </spring:bean>

        <!-- 2) named queries externalized into files from the classpath -->
        <spring:bean id="userByUserNameQuery" class="org.apache.commons.io.IOUtils" factory-method="toString">
            <spring:constructor-arg type="java.io.InputStream">
                <spring:bean class="java.io.FileInputStream" destroy-method="close">
                    <spring:constructor-arg type="java.io.File" value="classpath:/jdbc/userByName.sql"/>
                </spring:bean>
            </spring:constructor-arg>
        </spring:bean>
    </spring:beans>

    <jdbc:connector name="defaultJdbcConnector" dataSource-ref="dataSource">
        <jdbc:query key="userByUserName" value-ref="userByUserNameQuery"/>
    </jdbc:connector>

    <vm:endpoint name="getUserInfo" path="user.info" exchange-pattern="request-response"/>
    <flow name="jdbc-test-flow">
        <inbound-endpoint ref="getUserInfo"/>
        <logger level="INFO" category="jdbc-test-flow" message="JDBC Parameters: #[payload]"/>
        <jdbc:outbound-endpoint queryKey="userByUserName" exchange-pattern="request-response"/>
        <logger level="INFO" category="jdbc-test-flow" message="JDBC Query Results: #[payload]"/>
    </flow>
</mule>

users.csv