Mule Soft: Database Connectors

Mule provides database connectors to interact multiple databases. You can perform predefined queries, dynamically constructed queries, and template queries that are self-sufficient and customizable. You can perform multiple SQL requests in a single bulk update and make Data Definition Language (DDL) requests that alter the data structure rather than the data itself.

The database connector supports the following operations:

·       Select

·       Insert

·       Update 

·       Delete

·       Stored Procedure

·       Bulk Execute

·       DDL operations such as CREATE, ALTER, etc.

Connection Pooling:

You can use connection pooling facility provided by Mule to reuse database connections. Default database connector configuration does not use database connection pooling. e.g.

 <db:mysql-config name="dbConfig" host="localhost" port="3306" user="root"

    password="my_password" database="my_database" doc:name="MySQL Configuration" />

Here in above example database connection will not be polled and would not be reused.

But with a small effort we can achieve it. e.g.

<db:mysql-config name="dbConfig" host="localhost" port="3306" user="root"

    password="my_password" database="my_database" doc:name="MySQL Configuration">

    <db:pooling-profile maxPoolSize="17" minPoolSize="13" />

</db:mysql-config>

In above configuration we have provided pooling-profile, which enabled database connection pooling.

We can also use third party database pooling libraries like dbcp, c3p0. For using these 3^rd party libraries, we need to use Spring beans configuration. We have to define data source for database connection, and generic-config connector can be used to establish connection to the database using defined data sources.

DataSource Configuration:

<spring:beans>

            <spring:bean id="dbcpDataSource"class="org.apache.commons.dbcp.BasicDataSource"

                  destroy-method="close">

                  <spring:property name="driverClassName"value="${datasource.driverClassName}" />

                  <spring:property name="url"value="${datasource.url}" />

                  <spring:property name="username"value="${datasource.username}" />

                  <spring:property name="password"value="${datasource.password}" />

                  <spring:property name="initialSize"value="${datasource.initialSize}" />

                  <spring:property name="maxActive"value="${datasource.maxActive}" />

            </spring:bean>

      </spring:beans>

      <spring:bean id="C3P0DataSource"class="com.mchange.v2.c3p0.ComboPooledDataSource"

            destroy-method="close">

            <spring:property name="driverClass"value="${datasource.driverClassName}" />

            <spring:property name="jdbcUrl"value="${datasource.url}" />

            <spring:property name="user"value="${datasource.username}" />

            <spring:property name="password"value="${datasource.password}" />

            <spring:property name="maxPoolSize"value="${datasource.maxPoolSize}" />

            <spring:property name="minPoolSize"value="${datasource.minPoolSize}" />

      </spring:bean>

Connector Configuration:

<db:generic-config name="Database_DBCP_Configuration"

            dataSource-ref="dbcpDataSource" doc:name="DBCP Generic Database Configuration"/>

      <db:generic-config name="Database_C3P0_Configuration"

            dataSource-ref="C3P0DataSource" doc:name="C3P0 Generic Database Configuration"/>