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>