Mule Application Development Using MySQL and ActiveMQ Connectors

Introduction

In this blog, I am going to explain how to use Mule ESB to perform integration with database and messaging brokers. I use MySQL as database engine and ActiveMQ as message broker.
The youtube video now available at https://www.youtube.com/watch?v=DAsFv045nlw&feature=youtu.be

Here is the use case:

1. A user sends HTTP GET request to retrieve customer information based on company name
2. A Mule flow will retrieve all the records from database table named Customer
3. Send each record to JMS Queue
4. Return use the counter of total records
5. handle exceptions

Infrastructure Setup

In order achieve this, we need to setup MySQL database. Also we need to setup ActiveMQ.

Setup MySQL

Here is the procedures to setup MySQL on MacBook Pro:

• Down load MySQL dmg
• Install it
• Start the mysqld by run: sudo $MYSQL_INSTALLATION/mysql/support-files/mysql.server start

After start MySQL server, you need to do the following:

$mysql -u root

mysql> create user 'mule'@'localhost' identified by 'mule';

mysql> create database dataformule;

$mysql -u mule -p dataformule

mysql> grant all privileges on * . * to 'mule'@'localhost';

mysql> show databases;

mysql> select user from mysql.user;

mysql -u mule -p dataformule

use dataformule;
show tables;

create table Customer (
     id INT NOT NULL AUTO_INCREMENT,
     company_name char(30) NOT NULL,
     contact_name char (30) NOT NULL,
     phone char(30),
     company_address char(30),
     PRIMARY KEY (id)
);

mysql> desc Customer;
+-----------------+----------+------+-----+---------+----------------+
| Field           | Type     | Null | Key | Default | Extra          |
+-----------------+----------+------+-----+---------+----------------+
| id              | int(11)  | NO   | PRI | NULL    | auto_increment |
| company_name    | char(30) | NO   |     | NULL    |                |
| contact_name    | char(30) | NO   |     | NULL    |                |
| phone           | char(30) | YES  |     | NULL    |                |
| company_address | char(30) | YES  |     | NULL    |                |
+-----------------+----------+------+-----+---------+----------------+
5 rows in set (0.01 sec)



select * from dataformule.Customer;

INSERT INTO dataformule.Customer (id, company_name, contact_name, phone, company_address) VALUES (102, 'Company_B', 'Contact_B', '999-888-7778', 'Address_B');
INSERT INTO dataformule.Customer (id, company_name, contact_name, phone, company_address) VALUES (103, 'Company_C', 'Contact_C', '999-888-7779', 'Address_C');
INSERT INTO dataformule.Customer (id, company_name, contact_name, phone, company_address) VALUES (104, 'Company_D', 'Contact_D', '999-888-7781', 'Address_D');
INSERT INTO dataformule.Customer (id, company_name, contact_name, phone, company_address) VALUES (105, 'Company_E', 'Contact_E', '999-888-7782', 'Address_E');


mysql> select * from customer;
+-----+--------------+--------------+--------------+-----------------+
| id  | company_name | contact_name | phone        | company_address |
+-----+--------------+--------------+--------------+-----------------+
| 102 | Company_B    | Contact_B    | 999-888-7778 | Address_B       |
| 103 | Company_C    | Contact_C    | 999-888-7779 | Address_C       |
| 104 | Company_D    | Contact_D    | 999-888-7781 | Address_D       |
| 105 | Company_E    | Contact_E    | 999-888-7782 | Address_E       |
+-----+--------------+--------------+--------------+-----------------+
4 rows in set (0.01 sec)

For setup ActiveMQ with SSL, you may search my previous blog. I have documented in details about the set up

The Code

The complete code is available at GitHub at:
https://github.com/garyliu1119/Mule-Development/tree/master/ActiveMQ-Messaging
The mule flow code list as the following:

    
    
        
    
    
    
    
        <![CDATA[SELECT * FROM Customer WHERE company_name = :companyName;]]>
        
    
    
        <![CDATA[SELECT * FROM CUSTOMER WHERE phone = :phoneNumber;]]>
        
    
    
        
        
            
                
            
            
                
            
            
                
                    <![CDATA[throw new IllegalArgumentException('Paramenter Not Acceptable')]]>
                
            
        
        
        
        
        
        
            
                
                
            
            
                
                
            
            
                
                
            
        
    
    
        
            
            
            
            
        
    
    
        
            
            
        
        
        
    
    
        
            
            
        
        
        
    
    
        
        
    

Detailed Explanation

You can view the video for detailed explanation about this application at http://youtu.be/DAsFv045nlw 

How To Stop mysqld

Recently, I find I could not stop the mysqld process on my MacBook Pro. After some research, trial and errors, I found the following command works:

sudo launchctl unload -w /Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist 

Mule ESB Tutorial: Introduction To DataWeave

Introduction

MuleSoft's DataWeave available only from Anypoint Studio 3.7 since May 2015. It is a new powerful tool for data transformation. Earlier, we use DataMapper, or customer transformer to perform data transformation in addition to the out-of-box transformers, like Byte Array to String, object to JSON, XML to JSON, etc., just to name a few.

With DataWeave, the transformation become much less painful. In particular, if you don't want to write customer transform in java or scripts. Still, if you are good at writing java code, like me, customer transformer still be the best from development point of view. But it may not be the best for the long term maintenance. Once you get to the habits to write write customer transformer, you tend to use it a lot. I think it is time to change with this kind of powerful tool.

In this tutorial, I am going to demonstrate the basics about the DataWeave. Later, I will introduce more complicated cases. I will put this tutorial to Youtube.com. If you prefer to watch the usage in action.

Using Using Data Weave

The flow is like this:

With this configuration, we will use the input of JSON like the follwoing:

{
 "abc":
     {
     "cba":"ddd",
     "ccc":"eee",
     "aaa":["fgh","ghf","hgf"]
     }
}

Put the above file into your local somewhere name it as transform1.json. I strongly recommend a tool named: subline test 2 for edition JSON.

Now create HTTP connector. Everything is the same as simple configuration. Only extra procedure is add Metadata as the following:

Now, we can add Transform Message to the flow. Note that the "Transform Message" which is DataWeave component, it is not a transformer!

Simple Transform Doing Nothing

For the doing nothing case, the transformer will be the following:

%dw 1.0
%input payload application/json
%output application/xml
---
payload

Here is the snapshot of the transform panel:

Now we can test using PostMan with url: http://lcoalhost:8082/transform1. You should see the xml output like the following:

    ddd
    eee
    
        fgh
        ghf
        hgf
    

All above is nothing fancy. Note, the payload in the transform panel. It means doing nothing. The power comes from that panel. All the logics will be input there. We will see in the next section.

Transform From JSON To XML

Now let's change the transform scripts to the following:

%dw 1.0
%input payload application/json
%output application/xml
---
items: {
 name: payload.abc.aaa[1],
 address: payload.abc.cba,
 ID: payload.abc.ccc
}

Now if you run the POST operation, you will get the following result:

    ghf
    
ddd
    eee

This is the transformation a bit interesting. Of course, you can do this with data mapper transformer. But I hope you see the power behind it. It opens up the huge possibility in manipulate data for the transformation purpose. I will cover more cases in my later blogs.

Conclusion

I think DataWeave is a powerful tool. It takes time to learn the syntaxes, but I think it worths the time spending if you want to be a good integration architect! You can visit my youtube channel to get dynamics of the DataWeave!

Mule Flow: Setup HTTPS Connector and Setup Content-type

Introduction

The most popular connector probably is HTTP connector. However, for the enterprise application, this is not good enough. We need to setup HTTPS connect. Since Anypoint Studio, previously called Mule Studio, the configuration of HTTPS has been change. In this blog, I am going document the procedures on how to setup the HTTPS connector for the on-premise application, together with few other technical aspects. Here is the flow I have setup:

In this flow, I have few interesting parts. One is about content-based-routing, and the other is how to setup response content-type. In my case, I setup it as applicaiton/json.

Generate Self-signed Keystore

For the self-signed certificates, we can use keytool coming with JDK. First created a directory name something like certs. In my case, it is located at:

/Users/Gary2013/AnypointStudio-3.7/certs

Now inside the certs directory, execute the following command:

keytool -genkey -alias mule-server -keyalg RSA -keystore keystore.jks

That is all you need to do!

Setup HTTPS Connector

The following 2 pictures show where to put the certificates. In order to setup HTTPS connector, we need to configure 2 pages from the Anypoint Studio as shown below:

Setup The Mule Flow

The flow setup is not that complicated. Here I just provide the configuration file. For the details, I will provide a video, you can my youtube video.

From the above xml file, you may see that I have configured a HTTP and HTTPS connectors. The HTTPS configuration can be view in the following xml form:

You may note that the variable"${https.port.onpremise}". This together with other parameters are defined in the file name mule-app.properties.

Test The Flow

I use PostMan to test the flow for GET and POST methods with following URL:

https://localhost:8443/inventory

To test POST method, put the following JSON into the body of the PostMan:

{
  "name": "Barack Obama",
  "phone": "888-555-8888",
  "address": "1600 Pennsylvania Avenue Northwest, Washington, DC 20500",
  "Title" : "US President"
}

You should get the same json back as the following:

{
  "name": "Barack Obama",
  "phone": "888-555-8888",
  "address": "1600 Pennsylvania Avenue Northwest, Washington, DC 20500",
  "Title": "US President"
}

To test GET method, you use the following URL:

https://localhost:8443/inventory?name=Gary Liu Jr.

You should see the following JSON output:

{
  "name": "Gary Liu Jr."
}

Conclusion

This blog explains the basic procedure to configure HTTPS connector together with few other interesting aspects of mule flow, such as, content-base-routing, response content-type setup etc.

Change Mule Application Logging Level

Introduction

MuleSoft has changed many stuff for the last year. One of the questions developer will ask is how to change the logging level. One might think that he can just create log4j.properties file and set the logging there. Unfortunately, this will not work. We have to create a file name log4j2.xml.

Solution

Put the following file (named as: log4j2.xml) into src/main/resources:

Mule's Anypoint Studio comes with slf4j librarary. You can create your logger like this:

   private static Logger logger = LoggerFactory.getLogger(TransformListOfObjects.class);

After you put the file of log4j2.xml in place as stated above, you can run your application from the Anypoint Studio, you will see debug messages. These message can be very valuable, in particular, to check the parameters set for various connectors.