Free MuleSoft CONNECT Keynote & Expo Pass Available!

Register now+
Nav

Configuring a Database Connection

The Database connector can connect to any database for which a JDBC driver is available. To make it easier to connect to the most popular databases, the connector includes a series of different connection types.

Examples in this section show configurations for the connection types. They also illustrate more advanced use cases, such as connecting to a global data source, providing a configuration for a JDBC driver, and setting up connection pooling.

Connecting to MySQL


         
      
1
2
3
<db:config name= "dbConfig">
  <db:mysql-connection host="0.0.0.0" port="3306" user="Max" password="POWERS" database="someSchema"/>
</db:config>

Connecting to Oracle


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

Connecting to Derby


         
      
1
2
3
<db:config name= "dbConfig" >
  <db:derby-connection url=" jdbc:derby:muleEmbeddedDB;create=true" />
</db:config >

Connecting to a Generic Database

The generic connection type is useful in these cases:

  • When you want to connect to a database for which MuleSoft does not provide a specific connection type.

  • When you want to connect to one of the supported databases, but you are using custom options that not included in the connection types.

A generic connection simply requires:

  • Driver class name

  • Connection URL

The following example shows how to connect to an H2 database:


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

Connecting to a Global DataSource

When you set up a generic connection, the connector will use the information you provide to generate a JDBC DataSource. In some cases, you might want to create your own DataSource. To do this, the database configuration supports a data-source-connection element that allows you to reference a DataSource that is defined through the Spring module.

To connect to a global DataSource:

  1. Create a Spring configuration file containing a Bean such as this one:

    
                
             
    1
    2
    3
    4
    
    <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>

    The example above creates a custom enhydra DataSource that connects to a Derby database.

  2. Reference your Spring configuration file using the Spring module, and use the DataSource in a connection:

    
                
             
    1
    2
    3
    4
    5
    
    <spring:config name="datasource" files="datasource-config.xml" />
    
    <db:config name="dbConfig">
       <db:data-source-connection dataSourceRef="jdbcDataSource" />
    </db:config>

Setting the JDBC Driver

Now that you know how to configure the connections, you can supply the JDBC driver. The example below shows how to supply a driver for the MySQL database.

Note that the steps are the same for any database. The only difference is the need for the driver dependency.

  1. Add the driver as a dependency in your project’s pom.xml file:

    
                
             
    1
    2
    3
    4
    5
    
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.6</version>
    </dependency>
  2. Through your pom.xml, make Mule Runtime expose the driver dependency to the Database connector, for example:

    
                
             
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    
    <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>

Configuring Connection Pools

Establishing connections is costly in terms of connector and RDBMS network and processing overhead. The solution is to build a connection pool that provides a number of available connections. You use the <db:pooling-profile> element, rather than the standard Mule <pooling-profile> element, for this purpose.

This example shows how to use the db:pooling-profile element:


         
      
1
2
3
4
5
<db:config name="dbConfig">
    <db:my-sql-connection database="mozart_test" host="${host}" password="${password}" port="${port}" user="${user}">
        <db:pooling-profile acquireIncrement="1" maxPoolSize="5" maxWait="0" maxWaitUnit="SECONDS" minPoolSize="0" preparedStatementCacheSize="5"/>
    </db:my-sql-connection>
</db:config>

All connection configuration elements, except the global data source reference, accept the pooling profile. In JDBC, pooling occurs at the data source level. To do pooling, you have to configure the global data source. The software cannot add it on the fly.

Notice that unlike other connectors such as FTP or SFTP, the <db:pooling-profile> element is used instead of the standard <pooling-profile>. This is because databases have special pooling attributes, such as preparedStatementCacheSize, which do not make sense on the generic element.

For more details on parameters and capabilities of the connection types, see the Database Connector Technical Reference.