Contact Us 1-800-596-4880
  1. Homepage
  2. MUnit (2.2)
  3. MUnit Processors
  4. Utilities

  5. Database

Database

To test a piece of code that connects with a database server, you must install a database server in your local environment to run the tests. You could also have a dedicated external database server for testing, but your Maven project would not be portable — a third party would need to install the testing database server to compile your project.

To make it easier for you to test database connections, MUnit allows you to implement a database server in your local environment.

Installing the MUnit DB Server Module

  1. From Anypoint Studio, go to the Mule Palette and Search in Exchange…​.

  2. In the search bar look for MUnit Utils Database Server, and 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.

  3. The h2 dependency used for the database is a shared library.
    You must add the dependency to your pom.xml file and list it as a sharedLibrary in the mule-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>

Attributes Reference

Attribute Name Description

name

Defines the configuration name of this DB server. This value must be unique.

database

Defines the name of the in-memory DB.

sqlFile

Specifies the path of the SQL file that defines the DB structure/contents. This parameter is used to create the DB at startup. See the Defining the DB Structure from an SQL File section for more information.

csv

Specifies the path of the-comma separated file used to define the DB structure/contents. This parameter is used to create the DB at startup. See Defining the DB Structure from a CSV File section for more information.

connectionStringParameters

Defines the connection string for your database. You can concatenate the parameters with a semicolon.

Defining the DB Structure

There are two different ways to define the structure and content of your database:

  • SQL

  • CSV

The MUnit DB server is based on H2. Complex structures, index and join commands may not work properly.

Defining the DB Structure from an SQL File

To define the DB structure and content from an SQL file, you must provide a valid set of ANSI SQL DDL (Data Definition Language) instructions.

<dbserver:config name="MUnit_DB_Server_Config" >
    <dbserver:connection sqlFile="FILE_NAME.sql" database="DATABASE_NAME" connectionStringParameters="MODE=MySQL" />
</dbserver:config>

Defining the DB Structure From a CSV File

To define your DB structure based on a CSV file:

  • The name of the table is the name of the file (in the example below, customers).

  • The name of the columns are the headers of your CSV file.

<dbserver:config name="MUnit_DB_Server_Config" >
    <dbserver:connection csv="FILE_NAME.csv" database="DATABASE_NAME" connectionStringParameters="MODE=MySQL" />
</dbserver:config>

You can also split your DB structure among several CSV files. To do this, include the file names in a list, with the names separated by semicolons:

<dbserver:config name="MUnit_DB_Server_Config" >
    <dbserver:connection csv="FILE_NAME.csv;FILE_NAME_1.csv" database="DATABASE_NAME" connectionStringParameters="MODE=MySQL" />
</dbserver:config>

This creates two tables: FILE_NAME and FILE_NAME_1.

MUnit DB Server Processors

The DB server utility has two processors to interact with your DB server:

Validate-That

The MUnit DB Server allows you to validate that the results of a query are as expected.

To do this, you use the validate-that operation. Set the results property to CSV with rows separated by a newline character (\n), as shown below.

<dbserver:validate-that config-ref="DB_Server"
query="SELECT * FROM jobtitlelookup WHERE JOBTITLE='Developer';" returns="&quot;JOBTITLE&quot;,&quot;EECSALARYORHOURLY&quot;,&quot;JOBTITLEID&quot;\n&quot;Developer&quot;,&quot;10&quot;,&quot;DEV&quot;" />

Execute

MUnit DB Server enables you to execute instructions to the in-memory databases so that you can add or remove registries before a test and verify that your data was stored correctly.

Use a DB Server connector with the execute operation to insert the values new values to the table (Culinary Team Member,10,HIR), then use another DB Server connector with an execute query operation to retrieve all values from the table (including the newly added ones) and check the payload using a logger component to store the payload:

<!-- Execute a SQL instruction using the execute operation -->
<dbserver:execute config-ref="MUnit_DB_Server_Config" sql="INSERT INTO jobtitlelookup VALUES ('Culinary Team Member','10','HIR');"/>

<!-- Check the update using execute-query operation -->
<dbserver:execute-query config-ref="MUnit_DB_Server_Config" sql="SELECT * FROM jobtitlelookup"/>

<!-- log the resulting payload -->
<logger level="INFO" message="#[payload]"/>

The logger in your console reads:

org.mule.runtime.core.internal.processor.LoggerMessageProcessor: [{JOBTITLEID=DEV, EECSALARYORHOURLY=10, JOBTITLE=Developer}, {JOBTITLEID=HIR, EECSALARYORHOURLY=10, JOBTITLE=Culinary Team Member}]

See Also