Hey guys, Let us do a deep dive into the Database Connector. This article focuses on Datasource connection, the de-facto standard for Spring Applications to connect to the database.

The DataSource connection provides us with various features

Connection Pooling
Robust Connection Framework
Use External Connection Pooling libraries such as DBCP2.

Prerequisites:

The following things are required:

Anypoint Studio IDE
Database JDBC Driver - PostgreSQL (or any JDBC-Compliant database java driver)
Spring Module / MuleSoft Connector - Needed, if Data Source Configuration is selected
DBCP2 Connection Pooling Library - Needed, if Data Source Configuration is selected

Scenario:

Assume that you have a requirement where you need to interface with the PostgreSQL database. Requirement is to create, update, delete, and get data from PostgreSQL. You decided to develop a System API and wanted to use MuleSoft to achieve this.

Solution:

MuleSoft provides developers with a fully-featured database connector. It allows us to communicate with JDBC Compliant Relational Databases. The following are the configurations it supports.

Data Source Configuration
MySQL Configuration
JDBC URL Configuration
SQL Server Configuration
Derby Configuration

In this tutorial, I’m going to explain two types of configuration rest are similar in configuration.

Whatever connection configuration you choose, the operation of the database remains similar, though with some performance improvement.

Data Source Configuration:

To briefly explain this, Java uses a Data Source connection to communicate with databases via JDBC drivers. This data source provides us with a way to set the database credentials. JDBC driver contains a Driver class, which has all the abstractions needed for us to connect to the database. A few reasons to choose a data source is, it has good support for connection pooling and tuning performance.

Configuration:

Here we just need to provide a data source ref, as the Data Source ref is provided by spring beans. For that purpose, we must add spring module to our application.

Once this is added, notice here I have entered, a spring-context.xml in Files text box. This file will have spring beans. The configuration file is stored in src/main/resources path. The contents of the file look as below:

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

    <bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource"
        destroy-method="close">
        <property name="driverClassName" value="org.postgresql.Driver" />
        <property name="url" value="${db.url}" />
        <property name="username" value="${db.username}" />
        <property name="password" value="${db.password}" />
    </bean>
</beans>

Observations from above config file:

org.apache.commons.dbcp2.BasicDataSource is the class from the connection pooling library.
org.postgresql.Driver is the JDBC driver class name for PostgreSQL.
${db.url} contains the JDBC URL. Format: jdbc:postgres://HOST:PORT/DATABASE_NAME.
${db.username} is the username for the database.
${db.password} is the password for the database.

Here, ${key} are called Spring Properties, which can be loaded using configuration-properties connection configuration

Now let’s add the JDBC driver for PostgreSQL and a data source library which provides good connection pooling capabilities.

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.4.0</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
    <artifactId>commons-dbcp2</artifactId>
    <version>2.9.0</version>
</dependency>

For the spring module to get access to these libraries, add them to sharedLibrary configuration.

This is required for mule to provide the classes needed for Spring Module. The plugin bootstraps the needed classes and provides them.

Since properties should not be exposed, we use configuration properties file. It will contains above properties. config.yaml should be placed in src/main/resources folder

Add configuration property global element and provide the file name as below

Once above configuration is done, check if the connection is made.

Connector Usage:

Assume, we have a Users table in database with some data. Let’s create a flow as shown below.

The flow contains:

Http Listener: It starts a HTTP server which can be accessed on port 8081
Select (Database Connector): This enables use to write sql to get data from database.
Transform Message: Transforms response to JSON

Second flow is to insert data to database:

The database connector:

The source code for this can be found at: Repository

MuleSoft Database Connector