Mule ESB Concepts

Database Connectivity in Mule

The Database connector establishes communication between your Mule app and a relational database

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.

How to Configure Database Connectivity in Mule:

Drag and drop the below DB connector from Component pallet to Canvas

I choose MySql as database

There are 3 ways to configure Database connectivity in Mule

a)       By Providing hostname, port, username, password and DB schema details on UI

b)      By providing the details through Spring bean

c)       By providing the URL.

:

Providing details through Spring bean:  

Create a java class under source folder src/main/java

And override the method getConnection()

The Java should implement java.sql.DataSource

import java.io.PrintWriter;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import java.sql.SQLFeatureNotSupportedException;

import java.util.logging.Logger;

import javax.sql.DataSource;

public class DBConfigDetails implements DataSource{

// Keep the rest of methods as it is, I modified the below 2 methods

              @Override

              public Connection getConnection() throws SQLException {

                             Connection con = getConnection("root", "");

                             return con;

              }

              @Override

              public Connection getConnection(String username, String password) throws SQLException {

                             Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/mule_support", username, password);

                             return con;

              }

}

Configure the DB Connector as below

3) By providing the URL.:

jdbc:mysql://localhost:3306/mule_support?password=root&user=root

Querying the Database:

From the Mule we can query database in 3 ways

1)      Parameterized queries

2)      Dynamic Queries

3)      Template Based Queries

Parameterized Query

Parameterized (Recommended) - Mule replaces all Mule Expression Language (MEL) expressions inside a query with "?" to create a prepared statement, then evaluates the MEL expressions using the current event so as to obtain the value for each parameter.

Here only parameters replaced by MEL expressions

Dynamic - Mule replaces all MEL expressions in the query with the result of the expression evaluation, then sends the result to the database

flexibility - you have ultimate flexibility over the SQL statement. For example, all of the following are valid dynamic query statements:

·       select * from #[tablename] where id = 1;

·       insert into #[message.payload.restOfInsertStatement];

·       #[flowVars[‘deleteStatement’]]

From Template - Enables you to define a query statement once, in a global element in your application (global Template Query Reference element),

then reuse the query multiple times within the same application, dynamically varying specific values as needed.

Relative to parameterized and dynamic queries, from template queries offer the advantage of enabling you to reuse your query statements.