Configure a Database Connection

Anypoint Connector for Database (Database Connector) can connect to any database for which a JDBC driver is available. The following examples show how to connect to the most popular databases such as Derby, Microsoft SQL Server, MySQL, Oracle, and a generic database. Additionally, for advanced uses cases, there are examples showing how to connect to a global data source, configure a JDBC driver, configure connection pooling, and connect to an Oracle database with TNS, both in Anypoint Studio and the XML editor.

Configure a Data Source Reference Connection

Database Connector generates a JDBC data source by using the parameter values you provide when you configure the Generic Connection field. If you want to create your own data source and use it to connect to a database, configure the Data Source Reference Connection field.

The following example shows how to use Anypoint Studio to configure a data source reference connection to a Derby database:

In Studio, in the src/main/resources folder, create a datasource-config.xml bean file to define a custom enhydra data source that connects to a Derby database:

datasource-config.xml File

<bean id="jdbcDataSource" class="org.enhydra.jdbc.standard.StandardDataSource"
 destroy-method="shutdown">
   <property name="driverName" value="org.apache.derby.jdbc.EmbeddedDriver"/>
   <property name="url" value="jdbc:derby:muleEmbeddedDB;create=true"/>
</bean>

Return to your Mule flow, go to the Global Elements view, and click Create to open the Choose Global Type window.
In the Filter field, type spring, select Spring Config, and click OK.
In the Spring Config window Files field, add datasource-config.xml to reference the spring configuration file with the Spring module.
Click OK.

The following screenshot shows the configuration:

Figure 1. Spring Configuration Data Source

In the Global Elements view, click Create to open the Choose Global Type window.
In the Filter field, type database, select Database Config, and click OK.
In the Database Config window, for the Connection field, select Data Source Reference Connection.
Click Configure to add the required JDBC driver and select either:
- Use local file
  Install the library using a local file.
- Add Maven dependency
  Install a Maven dependency to add to the project.
For Data source ref, add the bean ID jdbcDataSource created in the datasource-config.xml bean file.
Click Test Connection to confirm that Mule can connect to the database.
Click OK.

The following screenshot shows the configuration:

The Data Source Reference Connection global configuration

Figure 2. Data Source Reference Connection

In the XML editor, both the spring and database configurations look like this:

<spring:config name="datasource" files="datasource-config.xml" />

<db:config name="Database_Config">
   <db:data-source-connection dataSourceRef="jdbcDataSource" />
</db:config>

Configure a Data Source Reference Connection When in XA Transaction Mode

If your Mule app requires operations bound to XA transactions, and connection pooling is enabled for the database configuration in use, the default connection pool implementation for Data Source Reference Connection is Enhydra XAPool.

Enhydra XAPool does not support prepared statements. To execute these operations use the Bitronix connection pool. Refer to Configuring the Bitronix Transaction Manager documentation for details.

When using XA transactions, you must provide an XADataSource object in the JDBC data source spring bean configuration file of your app.

The following example shows how to configure a Data Source Reference Connection for a Derby database when in XA transaction mode:

In Studio, in the src/main/resources folder, create a datasource-config.xml spring bean file.

Add the XADataSource object to the end of the class org.enhydra.jdbc.standard.Standard, for example, class="org.enhydra.jdbc.standard.StandardXaDataSource:

datasource-config.xml File

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="
               http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-current.xsd">

    <bean id="jdbcDataSource" class="org.enhydra.jdbc.standard.StandardXaDataSource" destroy-method="shutdown">
        <property name="driverName" value="org.apache.derby.jdbc.EmbeddedDriver"/>
        <property name="url" value="jdbc:derby:muleEmbeddedDB;create=true"/>
    </bean>

</beans>

Return to your Mule flow and navigate to Global Elements > Create to open the Choose Global Type window.
In Filter, type spring, select Spring Config, and click OK.
In Spring Config > Files, add datasource-config.xml to reference the Spring configuration file with the Spring module.
Click OK.

Spring Configuration window with datasource-config.xml configure in Files field

In Global Elements, click Create to open the Choose Global Type window.
In Filter, type database, select Database Config, and click OK.
In Database Config > Connection, select Data Source Reference Connection.
Click Configure to add the required JDBC driver and select either:
- Use local file
  Install the library using a local file.
- Add Maven dependency
  Install a Maven dependency to add to the project.
For Data source ref, add the bean ID jdbcDataSource created in the datasource-config.xml bean file.
In the Advanced tab, configure the Pooling profile, Column types, and Reconnection fields.
Click Test Connection to confirm that Mule can connect to the database.
Click OK.

In the XML editor, both the Spring and database configurations look like this:

<spring:config name="spring-beans" files="datasource-spring-beans.xml"/>

<db:config name="db-datasource">
    <db:data-source-connection dataSourceRef="jdbcDataSource">
        <reconnection failsDeployment="true">
            <reconnect frequency="4000" count="4"/>
        </reconnection>
        <db:column-types>
            <db:column-type id="124"
                            typeName="column_type"
                            className="class_name"/>
        </db:column-types>
        <db:pooling-profile maxPoolSize="6" minPoolSize="1"
                            acquireIncrement="2"
                            maxWait="1" maxWaitUnit="MINUTES"/>
    </db:data-source-connection>
</db:config>

Configure a Derby Connection

Use the Derby Connection field type to connect to a Derby database.

The following example shows how to configure the connection in Anypoint Studio:

In Studio, navigate to the Global Elements view.
Click Create to open the Choose Global Type view.
In the Filter field, type database, select Database Config, and click OK.
In the Database Config window, for the Connection field, select Derby Connection.
Click Configure to add the required Derby JDBC driver and select one of:
- Add recommended library
  Install the recommended library.
- Use local file
  Install the library using a local file.
- Add Maven dependency
  Install a Maven dependency to add to the project.
Set Database to derbyDB and Subsub protocol to directory.
On the Transactions tab, optionally specify the transaction isolation, or XA transactions when connecting to the database.
On the Advanced tab, optionally specify connection pooling and reconnection information, including a reconnection strategy.
Click Test Connection to confirm that Mule can connect to the database.
Click OK.

The following screenshot shows the configuration in Studio:

The Derby Connection global configuration

Figure 3. Derby Connection configuration

In the XML editor, the configuration looks like this:

<db:config name="Database_Config" >
		<db:derby-connection database="derbyDB" />
	</db:config>

Configure a Generic Connection

Use the Generic Connection field type to connect to:

A database for which MuleSoft does not provide a specific connection type
A supported database in which you are using custom options that are not included in the connection types

The following example shows how to connect to an H2 database in Anypoint Studio:

In Studio, navigate to the Global Elements view.
Click Create to open the Choose Global Type view.
In the Filter field, type database, select Database Config,and click OK.
In the Database Config window, for the Connection field select Generic Connection.
Click Configure to add the required JDBC driver and select either:
- Use local file
  Install the library using a local file.
- Add Maven dependency
  Install a Maven dependency to add to the project.
Set URL to jdbc:h2:file:/tmp/datasenseDBand and Driver class name to org.h2.Driver.
Click Test Connection to validate the connection with the database.
Click OK.

The following screenshot shows the configuration in Studio:

The Generic Connection global configuration

Figure 4. Generic Connection configuration

In the XML editor, the configuration looks like this:

<db:config name="Database_Config">
    <db:generic-connection driverClassName="org.h2.Driver"
    url="jdbc:h2:file:/tmp/datasenseDB"/>
</db:config>

Configure a Microsoft SQL Server Connection

Use the Microsoft SQL Server Connection field type to connect to a Microsoft SQL Server database.

The following example shows how to configure the connection in Anypoint Studio:

In Studio, navigate to the Global Elements view.
Click Create to open the Choose Global Type view.
In the Filter field, type database, select Database Config, and click OK.
In the Database Config window, for the Connection field, select Microsoft SQL Server Connection.
Click Configure to add the required Microsoft SQL Server driver and select one of:
- Add recommended library
  Install the recommended library.
- Use local file
  Install the library using a local file.
- Add Maven dependency
  Install a Maven dependency to add to the project.
Set Host to 0.0.0.0, Port to 553, User to Max, Password to password and Database name to microsoftDB.
On the Transactions tab, optionally specify the transaction isolation, or XA transactions when connecting to the database.
On the Advanced tab, optionally specify connection pooling and reconnection information, including a reconnection strategy.
Click Test Connection to confirm that Mule can connect to the database.
Click OK.

The following screenshot shows the configuration in Studio:

The Microsoft SQL Server Connection global configuration

Figure 5. Microsoft SQL Server Connection configuration

In the XML editor, the configuration looks like this:

<db:config name="Database_Config">
    <db:mssql-connection
    host="0.0.0.0"
    port="553"
    user="Max"
    password="password"
    databaseName="microsoftDB" />
</db:config>

Configure a MySQL Database Connection

Use the MySQL Connection field type to connect to a MySQL database.

The following example shows how to configure the connection in Anypoint Studio:

In Studio, navigate to the Global Elements view.
Click Create to open the Choose Global Type view.
In the Filter field, type database, select Database Config, and click OK.
In the Database Config window, for the Connection field, select MySQL Connection.
Click Configure to add the required MySQL JDBC driver and select one of:
- Add recommended library
  Install the recommended library.
- Use local file
  Install the library using a local file.
- Add Maven dependency
  Install a Maven dependency to add to the project.
Set Host to 0.0.0.0, Port to 3306, User to Max, Password to password, and Database to someSchema.
On the Transactions tab, optionally specify the transaction isolation, or XA transactions when connecting to the database.
On the Advanced tab, optionally specify connection pooling and reconnection information, including a reconnection strategy.
Click Test Connection to confirm that Mule can connect to the database.
Click OK.

The following screenshot shows the configuration in Studio:

The MySQL Connection global configuration

Figure 6. MySQL Connection configuration

In the XML editor, the configuration looks like this:

<db:config name="Database_Config">
  <db:my-sql-connection
  host="0.0.0.0"
  port="3306"
  user="Max"
  password="POWERS"
  database="mysqlDB"/>
</db:config>

Configure an Oracle Connection

Use the Oracle Connection field type to connect to an Oracle database.

The following example shows how to configure the connection in Anypoint Studio:

In Studio, navigate to the Global Elements view.
Click Create to open the Choose Global Type view.
In the Filter field, type database, select Database Config and click OK.
In the Database Config window, for the Connection field, select Oracle Connection.
Click Configure to add the required Oracle JDBC driver and select either:
- Use local file
  Install the library using a local file.
- Add Maven dependency
  Install a Maven dependency to add to the project.
Set Host to 192.168.99.100, Port to 1521, User to system, Password to oracle, and Instance to xe.
On the Transactions tab, optionally specify the transaction isolation, or XA transactions when connecting to the database.
On the Advanced tab, optionally specify connection pooling and reconnection information, including a reconnection strategy.
Click Test Connection to confirm that Mule can connect to the database.
Click OK.

The following screenshot shows the configuration in Studio:

The Oracle Connection global configuration

Figure 7. Oracle Connection configuration

In the XML editor, the configuration looks like this:

<db:config name="Database_Config">
  <db:oracle-connection
  host="192.168.99.100"
  port="1521" instance="xe"
  user="system"
  password="oracle" />
</db:config>

Configure the JDBC Driver

After you configure the connections, add the JDBC driver. The following example shows how to add a driver for the MySQL database:

In Studio, add the driver using either of the following options:
- In the Database Config window, click Configure, select Add Maven dependency, and pick the dependency:
  
  Figure 8. Pick Maven dependency
- Add the dependency directly to your project’s pom.xml file:
  <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.48</version> </dependency>

Configure your pom.xml file so that Mule exposes the driver dependency to Database Connector:

<build>
    <plugins>
        <!-- Only used to declare the shared libraries-->
        <plugin>
            <groupId>org.mule.tools.maven</groupId>
            <artifactId>mule-maven-plugin</artifactId>
            <version>1.0.0</version>
            <configuration>
                <sharedLibraries>
                    <sharedLibrary>
                        <groupId>mysql</groupId>
                        <artifactId>mysql-connector-java</artifactId>
                    </sharedLibrary>
                </sharedLibraries>
            </configuration>
        </plugin>
    </plugins>
</build>

Configure Connection Pools

Establishing connections is costly in terms of connectors, the relational database management system (RDBMS) network, and processing overhead. Database Connector supports the use of pooling profiles that enable you to build a connection pool. This is a group of established connections that the connector can use to perform required operations without having to establish new connections every time.

When you configure the Pooling profile field in the global element of your application, deselect the Test connection on checkout field to improve the connector performance with connection pooling. If you leave the field selected as default (TRUE), every time a connection is checked out of the pool, the connection pool performs connectivity checks against the database, which increases the time required for each operation to complete.

All connection type configurations accept the Pooling profile field configuration. In JDBC, pooling occurs at the data source level, so to enable pooling, you have to configure the global data source.

The following example shows how to configure a pooling profile for an Oracle database connection with the Test connection on checkout field set to FALSE:

In the Database Config window, in the Advanced tab, for Pooling profile select Edit inline.
Set the following fields:
- Max pool size: 30
- Min pool size: 4
- Acquire increment: 1
- Prepared statement cache size 5
- Max wait: 0
- Max wait unit: SECONDS (Default)
- Max idle time 0
- Max statements 0

Deselect Test connection on checkout.
This sets the field as FALSE.
For Column types select Edit inline.
Click the (+) plus sign to add a column type.
In the Column type window, set Id to 2003, Type name to 1000, and click Finish.
Repeat step 5.
To add a new column type, set Id to 2002, Type name to PHONE_NUMBER_ARRAY, and click Finish.

The following screenshot shows the configuration in Studio:

Figure 9. Pooling Profile Configuration

In the XML editor, the <db:pooling-profile> and testConnectionOnCheckout="false configurations look like these:

<db:config name="dbConfig">
		<db:oracle-connection host="localhost" user="myuser" password="mypass" serviceName="orclpdb1">
			<db:pooling-profile minPoolSize="4" maxPoolSize="30" testConnectionOnCheckout="false" acquireIncrement="1" maxWait="0" maxWaitUnit="SECONDS" preparedStatementCacheSize="5"/>
			<db:column-types>
				<db:column-type id="2003" typeName="PHONE_NUMBER" />
				<db:column-type id="2002" typeName="PHONE_NUMBER_ARRAY" />
			</db:column-types>
		</db:oracle-connection>
	</db:config>

Configure an Oracle Connection with TNS Names

The following example shows how to configure the Generic Connection field to connect to an Oracle database that relies on the tnsnames.ora file. If a firewall separates your Mule runtime engine from the database, use the ENABLE=BROKEN parameter to enable a keep-alive connection at the TNS connection. If a keep-alive connection is not set, the connection can drop traffic due to expiring inactivity timers.

In Studio, navigate to the Global Elements view.
Click Create to open the Choose Global Type view.
In the Filter field, type database, select Database Config and click OK.
In the Database Config window, for the Connection field, select Generic Connection.
Click Configure to add the required JDBC driver and select either:
- Use local file
  Install the library using a local file.
- Add Maven dependency
  Install a Maven dependency to add to the project.
Set the connection URL to jdbc:oracle:thin:${oracle.user}/${oracle.password}@(DESCRIPTION=(ENABLE=BROKEN)(FAILOVER=ON)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=${oracle.host1})(PORT=${oracle.port}))(ADDRESS=(PROTOCOL=TCP)(HOST=${oracle.host2})(PORT=${oracle.port})))(CONNECT_DATA=(SERVICE_NAME=${oracle.service}))).
Set Driver class name to oracle.jdbc.driver.OracleDriver.
Click Test Connection to validate the connection with the database.
Click OK.

The following screenshot shows the configuration in Studio:

The Oracle database with TNS configuration

Figure 10. Oracle with TNS configuration

In the XML editor, the configuration looks like this:

<db:config name="Database_Config">
    <db:generic-connection driverClassName="oracle.jdbc.driver.OracleDriver"
    url="jdbc:oracle:thin:${oracle.user}/${oracle.password}@(DESCRIPTION=(ENABLE=BROKEN)(FAILOVER=ON)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=${oracle.host1})(PORT=${oracle.port}))(ADDRESS=(PROTOCOL=TCP)(HOST=${oracle.host2})(PORT=${oracle.port}))(CONNECT_DATA=(SERVICE_NAME=${oracle.service})))` and *Driver class name* to `oracle.jdbc.driver.OracleDriver"/>
</db:config>