<!-- Local Database Configuration -->
<db:config name="Database_Config">
<db:my-sql-connection host="localhost" port="1234" />
</db:config>
<!-- Properties according to the environment -->
<configuration-properties file="db.properties" />
<flow name="selectFlow" >
<!-- Perform a query based on a value passed by a variable -->
<db:select config-ref="${db.config}">
<db:sql >SELECT * FROM jobtitlelookup WHERE jobtitleid = :id</db:sql>
<db:input-parameters ><![CDATA[#[id : vars.jobid]]]></db:input-parameters>
</db:select>
<!-- Set two conditions depending on the query result -->
<choice>
<!-- If there is one or more values resulting from the query set those values as the payload -->
<when expression="#[sizeOf(payload) > 0]">
<set-payload value="#[payload[0].jobtitle]"/>
</when>
<!-- If the query throws no results, inform it in the payload -->
<otherwise>
<set-payload value="#['No job title for $(vars.jobid) was found']" />
</otherwise>
</choice>
<!-- Finally, pass the payload content as a Flow variable -->
<set-variable value="#[payload]" variableName="job" />
</flow>
Configuring the Database Server Utility in an MUnit Test
If you want to test the following Mule application:
The file db.properties
in src/main/resources
has the following content:
db.config=Database_Config
You must create a test that:
-
Contains a database server with a
jobtitlelookup
table. -
Provides a valid database structure for the database connector to perform its query.
-
Passes a value for the
jobid
variable that Database Connector uses.
Install the MUnit DB Server Module
-
Look for the MUnit Utils Database Server module in Exchange.
-
Add the module to your project:
<!-- dbserver Dependency --> <dependency> <groupId>com.mulesoft.munit.utils</groupId> <artifactId>munit-dbserver-module</artifactId> <version>2.0.2</version> <classifier>mule-plugin</classifier> <scope>test</scope> </dependency>
The MUnit DB server artifact in your POM file must have the
test
scope. -
Add the
h2
dependency to your POM file and list it assharedLibrary
inmule-maven-plugin
:<!--Third party libs--> <dependency> <groupId>com.h2database</groupId> <artifactId>h2</artifactId> <version>1.3.166</version> </dependency>
<plugin> <groupId>org.mule.tools.maven</groupId> <artifactId>mule-maven-plugin</artifactId> <version>${mule.maven.plugin.version}</version> <extensions>true</extensions> <configuration> <sharedLibraries> ... <sharedLibrary> <groupId>com.h2database</groupId> <artifactId>h2</artifactId> </sharedLibrary> </sharedLibraries> </configuration> </plugin>
The h2 dependency enhances MUnit tests by allowing the simulation of database interactions using an in-memory, lightweight, and fast database. This setup ensures tests are both isolated and repeatable.
Define the MUnit DB Server
Define the database server by creating the global-configs.xml file and defining the MUnit DB Server Config element.
Define the MUnit DB Server Config element as follows:
-
Go to the
src/test/resources
directory in your project. -
Create a file named
jobtitlelookup.csv
containing the following values:JOBTITLE,EECSALARYORHOURLY,JOBTITLEID Developer,10,DEV
-
Create the global-configs.xml file and define the MUnit DB Server Config element as follows:
<dbserver:config name="MUnit_DB_Server_Config" > <dbserver:connection csv="jobtitlelookup.csv" database="DATABASE_NAME" connectionStringParameters="MODE=MySQL" /> </dbserver:config>
-
Define the DB configuration to connect to your DB server:
<db:config name="Test_Database_Config"> <db:generic-connection url="jdbc:h2:tcp://localhost/mem:DATABASE_NAME" driverClassName="org.h2.Driver" /> </db:config>
-
Define the
db.properties
file in yoursrc/test/resources
folder that picks up your test database configuration:db.config=Test_Database_Config
Run the Test
After installing and configuring the DB server you can run the test:
<munit:test name="selectFlowTest" description="Test selectFlow" >
<munit:behavior>
<!-- Passes a variable to value to run in the main flow. -->
<set-variable variableName="jobid" value="DEV" />
</munit:behavior>
<munit:execution>
<!-- Run the production code. -->
<flow-ref name="db-server-docsFlow"/>
</munit:execution>
<munit:validation>
<munit-tools:assert-equals actual="#[vars.job]" expected="Developer" />
</munit:validation>
</munit:test>
This test validates that the query run in your production code is correct and that the DB server returns the payload as expected.