Database Connector in Mule | Insert Data in MYSQL
One of the most common requirements in the integration realm is to be able to work with databases. Being able to perform CRUD operations in relational database tables is perhaps the most common features across programming languages and perhaps the most utilized when it comes to physically persisting data.
MuleSoft provides the Database Connector which utilizes JDBC (Java Database Connector) to enable APIs to perform database operations. Although database operations are enabled by the MuleSoft Database Connector, it relies on the JDBC drivers provided by each database provider.
MuleSoft supports MySQL, Oracle and Derby out of the box, but it also provides a Generic Driver for every other kind of relational database engine. Personally, I have been able to use it with Netezza and SQLServer. It is worth mentioning that although MuleSoft supports these databases out of the box, the developer is still required to provide the JDBC package (jar file) so that MuleSoft is able to communicate with the target database.
Here we are performing insert operation in MySQL database.
Http Method : POST
Below is the insert flow where we used 'Byte Array to String' & 'JSON to Object' for converting incoming request from byte form to database object.
Here in HTTP Configuration rest of the settings are same as previous except path is set to : /insertinfo
In DB Connector, choose operation as Insert and write parameterized query as shown below
save it by ctrl+s
We have used set payload component to display a user friendly message as : Data is saved successfully.
Finally, run the project and once it is deployed please select POST method in postman and hit URL :
http://localhost:8085/api/saveinfo
Output :
Data is saved successfully
Below is XML code for above flow :
<?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="testdbconnectorFlow1">
<http:listener config-ref="HTTP_Listener_Configuration" path="/saveinfo" doc:name="HTTP"/>
<byte-array-to-string-transformer doc:name="Byte Array to String"/>
<json:json-to-object-transformer returnClass="java.util.Map" doc:name="JSON to Object"/>
<db:insert config-ref="MySQL_Configuration" doc:name="Database">
<db:parameterized-query><![CDATA[insert into info (ID,NAME,AGE) values (#[payload.id],#[payload.name],#[payload.age]);]]></db:parameterized-query>
</db:insert>
<set-payload value="Data is saved successfully" doc:name="Set Payload"/>
</flow>
</mule>
No comments:
Post a Comment