Data insert into DB using Mule esb

I want to do some POC, in this I taken data from the Rest api. I am getting data in Json format, then put in one text file. Now I try to that file data in DB. Below find the XML format of Mule flow.

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

<mule xmlns:file="http://www.mulesoft.org/schema/mule/file" xmlns:metadata="http://www.mulesoft.org/schema/mule/metadata" xmlns:json="http://www.mulesoft.org/schema/mule/json" xmlns:dw="http://www.mulesoft.org/schema/mule/ee/dw" 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/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/ee/dw http://www.mulesoft.org/schema/mule/ee/dw/current/dw.xsd
http://www.mulesoft.org/schema/mule/core http://www.mulesoft.org/schema/mule/core/current/mule.xsd
http://www.mulesoft.org/schema/mule/json http://www.mulesoft.org/schema/mule/json/current/mule-json.xsd
http://www.mulesoft.org/schema/mule/file http://www.mulesoft.org/schema/mule/file/current/mule-file.xsd">
    <http:listener-config name="HTTP_Listener_Configuration" host="0.0.0.0" port="8082" doc:name="HTTP Listener Configuration"/>
    <http:request-config name="HTTP_Request_Configuration" host="$host" port="$port" doc:name="HTTP Request Configuration"/>
    <db:generic-config name="Generic_Database_Configuration" url="localDB Connection" driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver" doc:name="Generic Database Configuration"/>
    <file:connector name="File" autoDelete="true" streaming="true" validateConnections="true" doc:name="File"/>

    <flow name="Flowname">
        <http:listener config-ref="HTTP_Listener_Configuration" path="/Customer" doc:name="HTTP" />
        <http:request config-ref="HTTP_Request_Configuration" path="/Services/Customers/api/2.0/search/{a}" method="GET" doc:name="HTTP">
            <http:request-builder>
                <http:uri-param paramName="a" value="s"/>
            </http:request-builder>
        </http:request>
        <file:outbound-endpoint path="D:\Docs" outputPattern="test.txt" responseTimeout="10000" doc:name="File"/>
        <json:json-to-object-transformer returnClass="java.util.HashMap" mimeType="text/plain" doc:name="JSON to Object"/>
        <logger message="#[message.payload]" level="INFO" doc:name="Logger"/>
        <set-payload value="#[message.payload.CustomerID],#[message.payload.Address],#[message.payload.DOB],#[message.payload.FirstName],#[message.payload.LastName],#[message.payload.MiddleName],#[message.payload.PhoneNo]" doc:name="Set Payload"/>
        <db:insert config-ref="Generic_Database_Configuration" doc:name="Database">
            <db:parameterized-query><![CDATA[INSERT INTO dbo.tblCustomer (Customerid,Address,Dob,Firstname,LastName,Middlename,Phoneno) VALUES (#[Message.payload.CustomerID],#[Message.payload.Address],#[Message.payload.DOB],#[Message.payload.FirstName],#[Message.payload.LastName],#[Message.payload.MiddleName],#[Message.payload.PhoneNo])]]></db:parameterized-query>
        </db:insert>
    </flow>
</mule>

Getting data from Service like this

[{
    "Address": "372 Willene Drive",
    "CustomerID": 1010007031,
    "DOB": "1981-09-19",
    "FirstName": "Aaliyah",
    "LastName": "Gonzalez",
    "MiddleName": "R",
    "PhoneNumber": "7775271592"
}, {
    "Address": null,
    "CustomerID": 1010007743,
    "DOB": "1937-05-28",
    "FirstName": "Aaron",
    "LastName": "Green",
    "MiddleName": "T",
    "PhoneNumber": "0924758727"
}, {
    "Address": "7 Country Lake Drive",
    "CustomerID": 1010004653,
    "DOB": "1936-03-07",
    "FirstName": "Aaron",
    "LastName": "Gutierrez",
    "MiddleName": "Q",
    "PhoneNumber": "9919500942"
}, {
    "Address": "157 Tamir Avenue",
    "CustomerID": 1010005851,
    "DOB": "1955-12-19",
    "FirstName": "Abigail",
    "LastName": "Garcia",
    "MiddleName": "G",
    "PhoneNumber": "4695049914"
}, {
    "Address": "5 Cross Road",
    "CustomerID": 1010007962,
    "DOB": "1939-07-23",
    "FirstName": "Abigail",
    "LastName": "Gomez",
    "MiddleName": "R",
    "PhoneNumber": "6267010014"
}]

Could you please help on this.

Solution

I'm just learning Mule too and have thousands of questions but, I think, I can answer yours.

There are some flows in your flow. You put too much questions at once.

Here is your flow (I added 1/2 for HTTP for comments)

First issue is that we have 3 entry points. 2 HTTPs and 1 File. HTTP1 waits for one http call. HTTP2 waits for another and both do nothing since we have File Component which actually suppose to get data.

Let's get rid of unnecessary HTTPs and reconfigure File to grab some file from local directory. Yo can add another part of the flow to grab file from HTTP (store in file which is not necessary) and then use it. I skip this because it doesn't fit question topic which is inserting data to the DB.

Second issue in your configuration is that you map JSON to a Map. You have array. So, Let's map it to the Array. See the code.

Third issue is that you have Database Conector which works with one record but you have a lot of them in the Arry. So, let's use ForEach loop for Array and process records one by one.

To show that it works I had added few more Loggers.

Here is self explanatory flow

and here is code

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

<mule xmlns:file="http://www.mulesoft.org/schema/mule/file" xmlns:metadata="http://www.mulesoft.org/schema/mule/metadata" xmlns:json="http://www.mulesoft.org/schema/mule/json" xmlns:dw="http://www.mulesoft.org/schema/mule/ee/dw" 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/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/ee/dw http://www.mulesoft.org/schema/mule/ee/dw/current/dw.xsd
http://www.mulesoft.org/schema/mule/core http://www.mulesoft.org/schema/mule/core/current/mule.xsd
http://www.mulesoft.org/schema/mule/json http://www.mulesoft.org/schema/mule/json/current/mule-json.xsd
http://www.mulesoft.org/schema/mule/file http://www.mulesoft.org/schema/mule/file/current/mule-file.xsd">
    <db:generic-config name="Generic_Database_Configuration" url="localDB Connection" driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver" doc:name="Generic Database Configuration"/>
    <file:connector name="File" autoDelete="true" streaming="true" validateConnections="true" doc:name="File"/>

    <flow name="Flowname">
        <file:inbound-endpoint path="C:\tmp" responseTimeout="10000" doc:name="FileIn">
            <file:filename-regex-filter pattern="text.txt" caseSensitive="true"/>
        </file:inbound-endpoint>

        <file:file-to-string-transformer doc:name="File to String"/>

        <json:json-to-object-transformer returnClass="java.util.ArrayList" mimeType="text/plain" doc:name="JSON to Object"/>
        <logger message="#[payload]" level="INFO" doc:name="Log Whole Payload"/>
        <foreach doc:name="For Each">
            <logger message="#[payload]" level="INFO" doc:name="Log one Record"/>
            <logger message="#[payload.Address] #[payload.CustomerID]" level="INFO" doc:name="Log two fields"/>
            <db:insert config-ref="Generic_Database_Configuration" doc:name="Database">
            <db:parameterized-query><![CDATA[INSERT INTO dbo.tblCustomer (Customerid,Address,Dob,Firstname,LastName,Middlename,Phoneno) VALUES (#[Message.payload.CustomerID],#[Message.payload.Address],#[Message.payload.DOB],#[Message.payload.FirstName],#[Message.payload.LastName],#[Message.payload.MiddleName],#[Message.payload.PhoneNo])]]></db:parameterized-query>

        </db:insert>
            <logger message="Record succesfull" level="INFO" doc:name="Log Success"/>

        </foreach>

    </flow>
</mule>

Change localDB Connection to real url like

jdbc:sqlserver://myserver:1433; databaseName=myDB; user=myid; password=mypassword;

and you are good to go.

Run application.

Copy your data file to c:\tmp\text.txt

Application will grab the file, convert to String (delete the file to be ready for next one), convert String to Array of objects and for each object (which is your row) log it, log two fields, store record and make log message of success.

Then Application will wait for another text.txt to proceed.

Of course all these steps are not necessary but they help you feel what's going on, when and what is happening.