Using Mule Studio to read Data from PostgreSQL(Inbound) and write it to File (Outbound) - Step by Step approach

Introduction

We have already covered up the basics of Mule in one of my previous article.In this article we will look into as how we can use JDBC driver to connect to PostgreSQL,read the data from PostgreSQL and write it to some files.

What we need to have for doing the experiment?

We need

1. PostgreSQL
2. PostgreSQL JDBC driver
3. Download Mule ESB

4. A database to be created in PostgreSQL say "TestDB" where atleast one Table say "Players" should be created with some record populated as under

   -- Table Creation Script
   CREATE TABLE "Players"
   (
     "PlayerName" text,
     "PlayerId" integer
   )
   
   -- Record Insertion Script
   INSERT INTO "Players"("PlayerName", "PlayerId") VALUES ('Niladri', 1);
   INSERT INTO "Players"("PlayerName", "PlayerId") VALUES ('Arina', 2);
   INSERT INTO "Players"("PlayerName", "PlayerId") VALUES ('Rajlakshmi', 3);
   		

5. Everything should be up and running properly

Let us start the experiment

Step1:Open Mule Studio

So now we are all set to do our first example using Mule Studio.Execute the "MuleStudio.exe" and enter a location for creating the workspace. Workspace is the location where we need to work for the project.

Click on "OK" button to receive the Mule Studio window.Click on "Go to Mule Studio".We will receive the Mule studio editor.

Click File > New > Mule Project.From the "New Mule Project" window that appears, populate the "Name" field with "JDBC-PostgreSQL-Mule-Experiment".

Click "Next" button and "New Mule Flow" window appears.Click "Next" button and "New Mule Flow" window appears.Leave it as it is.Click "Next" button and "Create New Java Project" window appears. Use proper JRE version that you are using.

Click "Next" button and "Java Settings" window appears.Leave it as it is.Click "Finish" button.We will be presented with the following

Step2:Import the Postgre SQL JDBC Driver

(a)In the Package Explorer, right-click over the Project folder ( in this case "jdbc-postgresql-mule-experiment)->BuildPath->Add External Archives…

(b)Choose the location where the "postgresql-9.2-1002.jdbc4.jar" jar file is located in the hard drive and then click on the "Open" button.

(c)Now we can find the jar file being present in the "Referenced Libraries" of the package explorer.

This will allow us to create an instance of the Object driver

Step3:Now the time for creating DataSource

(a)Switch to the "Global Elements" tab

(b)Click on the "Create" button.Look for Data Sources > PostgreSQL Data Source and click the OK button.

(c)The PostgreSQL Data Source Window appears

(d)In that, we need to specify the Database name in the URL section, PostgreSQL User Name in the "User" field and Password in "Password" field and click OK.

Step4:Let us create the Connector now

So we have successfully created a Data-source with it's proper driver.Now we need a Connector.

(a)Click on the "Create" button.Look for Connector > Database.Then click OK.

(b)From the Database Window that appears, click on the "Database Specific" dropdown and choose "PostgreSQL_Data_Source".It is the Data-Source that we created in the previous step

Finally, we will be presented with the below screen if everything has gone right so far

Step5:Let us create flow now

Let us now switch back to "Message Flow" and from the "EndPoints" pallete on the left, drag and drop a "Database".This is the Inbound Endpoint.That will be followed by a "Object-ToXML" from the "Transformer"'s section.And finally the Outbound "File" from the "Endpoint" section

Step6:Time to configure the components.

Double click on the "Database" component and visit the "Reference" tab.From there, from the "Connector Reference" drop-down, choose the "Connector Name" which is "PostgreSQL-JDBC-Connector" here.

Now visit the General tab and select the [+] button of the "Query" section.In the "Query" popup the appears, enter "PlayerList" in the Query Key section and enter

Select * From "Players";

in the Query editor.Lastly click on "Finish" button

Query selected in the Query key drop down list.Finally click on the "OK" button.

We need to configure the output path of the "File" component.So double click on that and specify the "Path" found the general tab.Finally click "OK" button.

The final flow is

The configuration XML should look like

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

<mule xmlns:file="http://www.mulesoft.org/schema/mule/file" xmlns:mulexml="http://www.mulesoft.org/schema/mule/xml" xmlns:jdbc="http://www.mulesoft.org/schema/mule/jdbc" 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" version="CE-3.3.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="
http://www.mulesoft.org/schema/mule/xml http://www.mulesoft.org/schema/mule/xml/current/mule-xml.xsd 
http://www.mulesoft.org/schema/mule/file http://www.mulesoft.org/schema/mule/file/current/mule-file.xsd 
http://www.mulesoft.org/schema/mule/jdbc http://www.mulesoft.org/schema/mule/jdbc/current/mule-jdbc.xsd 
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 ">
    <jdbc:postgresql-data-source name="PostgreSQL_Data_Source" user="postgres" password="niladri_123" url="jdbc:postgresql://localhost:5432/TestDB" transactionIsolation="UNSPECIFIED" doc:name="PostgreSQL Data Source"/>
    <jdbc:connector name="PostgreSQL_JDBC_Connector" dataSource-ref="PostgreSQL_Data_Source" validateConnections="true" queryTimeout="-1" pollingFrequency="0" doc:name="Database"/>
    <flow name="JDBC-PostgreSQL-Mule-ExperimentFlow1" doc:name="JDBC-PostgreSQL-Mule-ExperimentFlow1">
        <jdbc:inbound-endpoint queryKey="MyPlayers" queryTimeout="-1" pollingFrequency="1000" connector-ref="PostgreSQL_JDBC_Connector" doc:name="Source Data">
            <jdbc:query key="MyPlayers" value="Select * From "Players";"/>
        </jdbc:inbound-endpoint>
        <mulexml:object-to-xml-transformer doc:name="Object to XML"/>
        <file:outbound-endpoint path="C:\Users\nbiswas\Documents\Output" responseTimeout="10000" doc:name="Save the resulting information here"/>
    </flow>
</mule>

We are now almost done.Next we need to run the application.Right click on "JDBC-PostgreSQL-Mule-Experiment.mflow" and from the context menu, choose Run As->Mule Application.

We will encounter the below screen

Now visit the output folder and we will see many .dat files.Open and see the results.

References

Conclusion

Hope you enjoyed the step by step tutorial of using Mule Studio to read Data from PostgreSQL(Inbound) and write it to File (Outbound). I will write more on the Mule-ESB as and when I will perform some experiment.Thanks for reading.