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