Database Connector in Mule | Select Operation in MYSQL

  Database Connector in Mule | Select Operation in MYSQL





Database Connector :

The Database connector can connect to almost any Java Database Connectivity (JDBC) relational database and run SQL operations. You can specify MEL expressions in connector fields and configure attributes dynamically, depending on the database configuration you use. An application can support multi-tenant scenarios using the same configuration element, changing the connection attributes based on, for example, information coming from each request.

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 is available with Mule Community and Mule Enterprise runtimes.

Prerequisites :

To complete the examples presented here, we assume that you have access to the database that you are to connect to, and that you have downloaded the proper database driver for your database. To use a Database connector in your Mule application, check whether your database engine is supported out-of-the-box. The database connector currently includes out of the box support for Oracle, MySQL and Derby engines. The Generic Database Configuration option supports all other database engines, including MS SQL and PostgreSQL.

Here we have example of database connector in Mule 3 and Anypoint studio 6.2


Database which we are using is MySQL and we are making Mule APIs to perform CRUD(Create,Read,Update,delete) operations.


First create a table in MySQL database. with attributes ID,NAME & AGE.

ID is the primary key.
I have created the schema with name demodata and table with name info


Make a new mule project testdbconnector and make flows in it.

1) We are performing select operation here means it will fetch all rows from table.

Http method : GET

And below is the flow for fetching all rows from database table.Here three mule components are used :

1) HTTP Connector
2) Database Connector
3) Object to JSON transformer




Below is HTTP connector Configuration where port is 8085 and base uri is /api




Below is path mapping in HTTP Connector which is : /getinfo




press ctrl+s to save.



Now in database connector go in connector configuration click on plus symbol + for new configuration.there select mysql database. When you select mysql db ,below screen appears.

Fill all required details as shown below and don't forget to add mysql connector jar.
I have used mysql-connector-java-5.0.8.jar



In DB connector choose operation as select as shown below and write parameterized query in space





save it by ctrl+s.


Now output that will come in object form so better if we convert to json form so we have used object to json convertor.


Finally, run the project and once it get deployed.


Please select GET method in postman and hit : http://localhost:8085/api/getinfo


Output :




Note : You can use any Rest client like Postman/SoapUI,etc


XML code for this project :


<?xml version="1.0" encoding="UTF-8"?>

<mule xmlns:json="http://www.mulesoft.org/schema/mule/json" xmlns:db="http://www.mulesoft.org/schema/mule/db" xmlns:http="http://www.mulesoft.org/schema/mule/http" xmlns="http://www.mulesoft.org/schema/mule/core" xmlns:doc="http://www.mulesoft.org/schema/mule/documentation"
xmlns:spring="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
http://www.mulesoft.org/schema/mule/core http://www.mulesoft.org/schema/mule/core/current/mule.xsd
http://www.mulesoft.org/schema/mule/db http://www.mulesoft.org/schema/mule/db/current/mule-db.xsd
http://www.mulesoft.org/schema/mule/http http://www.mulesoft.org/schema/mule/http/current/mule-http.xsd
http://www.mulesoft.org/schema/mule/json http://www.mulesoft.org/schema/mule/json/current/mule-json.xsd">
    <db:mysql-config name="MySQL_Configuration" host="localhost" port="3306" user="root" password="********" database="demodata" doc:name="MySQL Configuration"/>
    <http:listener-config name="HTTP_Listener_Configuration" host="0.0.0.0" port="8085" basePath="/api" doc:name="HTTP Listener Configuration"/>
    <flow name="testdbconnectorFlow">
        <http:listener config-ref="HTTP_Listener_Configuration" path="/getinfo" doc:name="HTTP"/>
        <db:select config-ref="MySQL_Configuration" doc:name="Database">
            <db:parameterized-query><![CDATA[select * from info;]]></db:parameterized-query>
        </db:select>
        <json:object-to-json-transformer doc:name="Object to JSON"/>
    </flow>
</mule>





Please go through below tutorials:


Mule 4 Tutorials

DEPLOY TO CLOUDHUB C4E CLIENT ID ENFORCEMENT CUSTOM POLICY RABBIT MQ INTEGRATION
XML TO JSON WEBSERVICE CONSUMER VM CONNECTOR VALIDATION UNTIL SUCCESSFUL
SUB FLOW SET & REMOVE VARIABLE TRANSACTION ID SCATTER GATHER ROUND ROBIN
CONSUME REST WEBSERVICE CRUD OPERATIONS PARSE TEMPLATE OBJECT TO JSON LOAD STATIC RESOURCE
JSON TO XML INVOKE IDEMPOTENT FILTER FOR EACH FLAT TO JSON
FIXWIDTH TO JSON FIRST SUCCESSFUL FILE OPERATIONS EXECUTE ERROR HANDLING
EMAIL FUNCTIONALITY DYNAMIC EVALUATE CUSTOM BUSINESS EVENT CSV TO JSON COPYBOOK TO JSON
CHOICE ASYNC

Widely used Connectors in Mule 3

CMIS JETTY VM CONNECTOR SALESFORCE POP3
JMS TCP/IP WEBSERVICE CONSUMER QUARTZ MONGO DB
FILE CONNECTOR DATABASE CONNECTOR


Widely used Scopes in Mule 3

SUB FLOW REQUEST REPLY PROCESSOR CHAIN FOR EACH CACHE
ASYNC TCP/IP COMPOSITE SOURCE POLL UNTIL SUCCESSFUL
TRANSACTIONAL FLOW

Widely used Components in Mule 3

EXPRESSION CXF SCRIPT RUBY PYTHON
JAVASCRIPT JAVA INVOKE CUSTOM BUSINESS EVENT GROOVY
ECHO LOGGER


Widely used Transformers in Mule 3

MONGO DB XSLT TRANSFORMER REFERENCE SCRIPT RUBY
PYTHON MESSAGE PROPERTIES JAVA TRANSFORMER GZIP COMPRESS/UNCOMPRESS GROOVY
EXPRESSION DOM TO XML STRING VALIDATION COMBINE COLLECTIONS BYTE ARRAY TO STRING
ATTACHMENT TRANSFORMER FILE TO STRING XML TO DOM APPEND STRING JAVASCRIPT
JSON TO JAVA COPYBOOK TO JSON MAP TO JSON JSON TO XML FLATFILE TO JSON
FIXWIDTH TO JSON CSV TO JSON


Widely used Filters in Mule 3

WILDCARD SCHEMA VALIDATION REGEX PAYLOAD OR
NOT MESSAGE PROPERTY MESSAGE IDEMPOTENT FILTER REFERNCE
EXPRESSION EXCEPTION CUSTOM AND


Exception Strategy in Mule 3

REFERENCE EXCEPTION STRATEGY CUSTOM EXCEPTION STRATEGY CHOICE EXCEPTION STRATEGY CATCH EXCEPTION STRATEGY GLOBAL EXCEPTION STRATEGY


Flow Control in Mule 3

CHOICE COLLECTION AGGREGATOR COLLECTION SPLITTER CUSTOM AGGREGATOR FIRST SUCCESSFUL
MESSAGE CHUNK AGGREGATOR MESSAGE CHUNK SPLITTER RESEQUENCER ROUND ROBIN SOAP ROUTER