Database Transport
Mule offers out-of-the-box connectivity to any
JDBC compliant Database. Mule Database endpoints are capable of performing
basic DB operations. Advanced support for Stored Procedures, Large Datasets and
Batch updates/inserts is available with Mule Enterprise Edition. More details
on Database transport can be read from Mule docs.
In the present example, we will learn how to poll
on to an Oracle Database table and read data. We will also understand how to
acknowledge read records so that duplicate reads are avoided.
Overview of steps required to complete this
example
·
Create DB schema, table and insert data
·
Create Oracle Datasource
·
Create Database connector
·
Create SQL Queries for Reading and Acknowledging
·
Create Database Endpoint and provide Database connector
reference
·
Add Logger component to display payload
Login to SQL XE Database as either SYSTEM or
SYS user. Run following script to create DEMO user and seed data.
[gist id=6932318]
Ensure successful creation of DEMO schema and
EMPLOYEES table.
Now that we have setup Database to read from
Mule ESB, lets proceed to creating Mule project. Create a new Mule flow in
“Mule Training” project and name it “database”.
Select Global Elements tab in database flow.
Then, click on Create button to bring up “Choose Global Type” dialog. Select
“Oracle Data Source” from Data Sources section and click OK.
Specify Database connection details as shown
below. Modify these values as needed.
Next, we need to create a Database connector
to specify other connection details including connection pooling, reconnection
strategies, SQL queries etc.,
Click on “Create” button again, select
“Database” from Connectors section and click OK.
Lets name this connector Database_Oracle.
Associate Oracle_Data_Source created in first step with this connector.
Save “database” flow and click on “Test
Connection” to ensure we can connect to Database successfully.
Go to “Queries” tab and define following two
queries to read data from EMPLOYEES table. Second query is for acknowledging
read records. As you can see, second query has a suffix “.ack”. Mule calls
“.ack” query as soon as each record is read. In this example, as soon as each
EMPLOYEES record is read, acknowledgment query updates PROCESSED column to ‘Y’.
Queries
·
readAllEmployees – select * from
EMPLOYEES where PROCESSED=’N’
·
readAllEmployees.ack – update EMPLOYEES
set PROCESSED=’Y’ where EMP_ID=#[map-payload:EMP_ID]
Also, Mule stores each Database row in a
map-payload variable which is a Hashmap. We can then refer to individual
columns using # [ map-payload:COLUMN ] notation.
Click OK and save the project.
Go back to graphical view of “database” flow
by clicking on “Message Flow” tab. Drag and drop a Database endpoint on to the
flow canvas.
Click on “References” section and associate
“Database_Oracle” as connector.
Now, go to “General” tab make sure Queries we
created earlier are visible in “Key” choice box. These queries are pulled from
the associated connector – “Database_Oracle”.
Specify polling frequency as 5000 ms.
Drag and drop a Logger component next to
Database endpoint. Logger is like a System.out.println statement. It displays a
Mule expression in console. Logger component uses Log4j behind the scenes.
Specify # [ message.payload ] for Logger Message.
This completes creation of Mule flow. Finally,
we need to add JDBC jar file to Mule reference library in order to successfully
run the project. Given below is the location of JDBC jar file in XE
installation.
Copy ojdbc6.jar to project root folder. Right
click on the jar and select “Add to Build Path”.
Make sure this jar is added to “Reference
Libraries” section.
Run the project and see output in console. We
can see two records read from EMPLOYEES table. If you look at EMPLOYEES data,
PROCESSED column would have been updated to ‘Y’.
Complete Database flow can be seen here.
