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

  5. Database

Installing a Database Server

logo acb active Anypoint Code Builder

logo studio active Anypoint Studio

To test a piece of code that connects with a database server, install a database server in your local environment to run the tests. You can also use a dedicated external database server for testing, but your Maven project can’t be portable. If so, a third party must 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.

Install the MUnit DB Server Module

To install the MUnit DB Server Module:

  1. Look for the MUnit Utils Database Server module in Exchange.

  2. 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.
    Add the dependency to your pom.xml file and list it as a sharedLibrary in 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. Use this parameter to create the DB at startup. See the Defining the DB Structure from an SQL File section for additional information.

csv

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

connectionStringParameters

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

Define the DB Structure

You can define the structure and content of your database in two different ways:

  • SQL

  • CSV

The MUnit DB server is based on H2. Complex structures, index and join commands can present issues.

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>

Define 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 following example, customers).

  • The names 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

To validate that the results of a query are as expected, use the validate-that operation. Set the results property to CSV with rows separated by a newline character (\n), as follows:

<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

Execute instructions for the in-memory databases to add or remove registries before a test and verify that your data is correctly stored using MUnit DB Server.

Use a DB Server connector with the execute operation to insert the 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). Finally, 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