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="" xmlns:db="" xmlns:http="" xmlns="" xmlns:doc=""
    <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="" 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>
        <json:object-to-json-transformer doc:name="Object to JSON"/>

© 2020

Share on :

Mule 4:

XML to JSON in mule 4 Web service consumer VM Validation in Mule 4 Until Successful
Sub flow Set Variable & Remove Variable Set Transaction ID Scatter Gather Round Robin
Consume Restful Service CRUD in Mule 4 Parse Template Object to JSON Load Static Resource
JSON to XML Invoke Idempotent Filter ForEach Flat file to JSON
Fixwidth to JSON First Successful File Execute Error Handling
Email Dynamic Evaluate Custom Business Event CSV to JSON Copybook to JSON
Choice Router Async RabbitMQ

Mule 3:

Database Connector Async CXF Attachement XSLT
Mongo DB Cache scope Custom Business Event Gzip Compress Expression
SMTP For Each Expression Javascript Filter Reference
Salesforce Message Enricher Groovy Parse Template Idempotent
VM Poll Invoke Ruby Message
Webservice consumer Python Transformer reference Message Property Not
Collection Aggregator First Sucessful Catch Exception Strategy Reference Exception Strategy Or
Choice Message Chunk Aggregator Choice Exception Strategy Global Exception Strategy Schema Validation
Custom Aggregator Scatter Gather Custom Exception Strategy Batch Processing MEL
M Unit RAML Map Operator API Mocking Dataweave

Converter Tools :

JSON to XML Converter XML to JSON Converter Decimal to Binary Decimal to Octal Binary to Decimal
Binary to Hexadecimal Binary to Octal Hexadecimal to Decimal Hexadecimal to Binary Hexadecimal to Octal
Octal to Decimal Octal to Binary Octal to Hexadecimal Number to Words Converter Text to Hex Converter
Hex to Text String to Binary Converter Binary to String Converter Word to HTML Converter Word to PDF Converter
JSON to YAML Converter CSV to JSON Converter XML to XSLT Converter XML to YAML Converter
JSON to CSV Converter JSON to TSV Converter CSV to TSV Converter CSV to XLS Converter

Image Tools :

PNG to JPG Converter PNG to BMP Converter PNG to GIF Converter JPG to PNG Converter JPG to BMP Converter
JPG to GIF Converter GIF to PNG Converter GIF to JPG Converter GIF to BMP Converter PNG to PDF Converter
JPEG to PDF Converter JPG to PDF Converter BMP to PDF Converter GIF to PDF Converter

String Utilities :

Reverse String Random String Generator Base64 Encoder Base64 Decoder Words Counter
Remove Accents Remove Duplicate Lines Remove Empty Lines Remove Line Breaks Remove Similar Lines
Sort Text Lines Reverse Text Lines Random Words Generator

Number Utilities :

Square Root Calculator Random Number Generator Area of Circle Calculator Date Calculator

Other Tools :

Find IP of Website Password Generator URL Encoder URL Decoder lorem ipsum Generator
IP to Hostname Port Checker JSON Escape JSON Unescape

Free Rest APIs for Testing :

GET Sample Rest APIs POST Sample Rest API PUT Sample Rest API PATCH Sample Rest API DELETE Sample Rest API
Login Sample Rest API User Registration Sample API User Registration Unsuccessful Sample Rest API Delay Response