How to Connect MySQL with MuleSoft in Mule 4

How to Connect MySQL with MuleSoft in Mule 4

In this tutorial, we will demonstrate step by step guide to connect MySQL with MuleSoft in Mule 4 and also perform some basic operations such as Insert, On Table Row and Query.

Connect MySQL with MuleSoft

Before configuring the MySQL, you will need to add Database module to your project.

To add Database module, simply click on Add Modules and drag and drop the database module to your project.

Now follow below steps to configure global MySQL configuration.

Step 1: Click on Global Elements, Create and search for Database as follows.

database-module

Step 2: In the database configuration window, select Connection as MySQL Connection and then click on Configure -> Add recommended libraries.

mysql-libraries

Next, in the enter your Host, Port, User, Password and Database as follows,

mysql-connection-details

I highly recommend to encrypt your database password and other sensitive information. Read how to encrypt properties in Mule 4.

Once you configured the database connector, do test connection to verify the connection from your Studio.

test-connection

Now let’s see different operation with examples.

Insert

Insert operation is used to insert the records into a table. Let’s insert few employees data into the mulesoft table.

Step 1: Add HTTP Listener and configure to post a request.

Step 2: Add the Database Insert operation and paste the below query in the SQL Query Text.

INSERT INTO mulesoft (EmpId, EmpName, EmpDesignation) VALUES (:Id, :Name, :Designation)

Where, the :Id, :Name and :Designation are placeholder and doesn’t contain any data until unless you assign data.

To assign data, you will need to use Input parameters. Paste the below expression in the Input parameters.

{
     Id: payload.emp_id,
     Name: payload.emp_name,
     Designation : payload.emp_designation
 }
insert-operation

Step 3: Run the application and send a request to your Mule application from postman.

insert-postman-req

Now open MySQL CLI and run the Select command to retrieve the data.

select-query

To insert more than a record, you can use the Bulk insert operation. We will learn more about Bulk operation in the next tutorial.

Update

Now let’s update the existing record with new value, say update designation with Lead Developer.

Step 1: In the same flow replace the Insert operation with Update operation and configure as follows.

SQL Query Text:

UPDATE mulesoft SET EmpDesignation = :Designation WHERE EmpId = :Id

and the Input parameters,

{
     Id: payload.emp_id,
     Designation : payload.emp_designation
 }
update-operation

Step 2: Send the same request but remove the Name and replace the Designation with Lead Developer.

update-postman-req

Now retrieve the record from the mulesoft table and verify.

select-updated-record

Query single

This operation always return single record, regardless of the number of records returned by the actual SQL query.

For example, If you use select * from mulesoft, you will still get only one record with Query single operation.

Step 1: Add the Query single operation and configure as follows. Let’s write a query to retrieve the all records and observe the behavior of Query single.

query-single

If you run the same query in the MySQL CLI, you will get all the records but you won’t get all records when you use Query single operation.

select-all-records

We hope this tutorial helped you to learn how to connect MySQL with MuleSoft and perform some basic operations.

In the next tutorial, we will demonstrate other operations such as On Table Row, Stored Procedures, etc.,

Please do share it with your friends and don’t forget to follow us on FacebookTwitter and LinkedIn. Visit our MuleSoft Hub for more tutorials and updates.

In this tutorial, we will demonstrate step by step guide to connect MySQL with MuleSoft in Mule 4 and also perform some basic operations such as Insert, On Table Row and Query.

Connect MySQL with MuleSoft

Before configuring the MySQL, you will need to add Database module to your project.

To add Database module, simply click on Add Modules and drag and drop the database module to your project.

Now follow below steps to configure global MySQL configuration.

Step 1: Click on Global Elements, Create and search for Database as follows.

database-module

Step 2: In the database configuration window, select Connection as MySQL Connection and then click on Configure -> Add recommended libraries.

mysql-libraries

Next, in the enter your Host, Port, User, Password and Database as follows,

mysql-connection-details

I highly recommend to encrypt your database password and other sensitive information. Read how to encrypt properties in Mule 4.

Once you configured the database connector, do test connection to verify the connection from your Studio.

test-connection

Now let’s see different operation with examples.

Insert

Insert operation is used to insert the records into a table. Let’s insert few employees data into the mulesoft table.

Step 1: Add HTTP Listener and configure to post a request.

Step 2: Add the Database Insert operation and paste the below query in the SQL Query Text.

INSERT INTO mulesoft (EmpId, EmpName, EmpDesignation) VALUES (:Id, :Name, :Designation)

Where, the :Id, :Name and :Designation are placeholder and doesn’t contain any data until unless you assign data.

To assign data, you will need to use Input parameters. Paste the below expression in the Input parameters.

{
     Id: payload.emp_id,
     Name: payload.emp_name,
     Designation : payload.emp_designation
 }
insert-operation

Step 3: Run the application and send a request to your Mule application from postman.

insert-postman-req

Now open MySQL CLI and run the Select command to retrieve the data.

select-query

To insert more than a record, you can use the Bulk insert operation. We will learn more about Bulk operation in the next tutorial.

Update

Now let’s update the existing record with new value, say update designation with Lead Developer.

Step 1: In the same flow replace the Insert operation with Update operation and configure as follows.

SQL Query Text:

UPDATE mulesoft SET EmpDesignation = :Designation WHERE EmpId = :Id

and the Input parameters,

{
     Id: payload.emp_id,
     Designation : payload.emp_designation
 }
update-operation

Step 2: Send the same request but remove the Name and replace the Designation with Lead Developer.

update-postman-req

Now retrieve the record from the mulesoft table and verify.

select-updated-record

Query single

This operation always return single record, regardless of the number of records returned by the actual SQL query.

For example, If you use select * from mulesoft, you will still get only one record with Query single operation.

Step 1: Add the Query single operation and configure as follows. Let’s write a query to retrieve the all records and observe the behavior of Query single.

query-single

If you run the same query in the MySQL CLI, you will get all the records but you won’t get all records when you use Query single operation.

select-all-records

We hope this tutorial helped you to learn how to connect MySQL with MuleSoft and perform some basic operations.

In the next tutorial, we will demonstrate other operations such as On Table Row, Stored Procedures, etc.,

Please do share it with your friends and don’t forget to follow us on FacebookTwitter and LinkedIn. Visit our MuleSoft Hub for more tutorials and updates.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Related Articles

How to Create Multiple Folders at Once in Windows 10

Creating folders and subfolders manually is time consuming tasks...

How to Integrate Solace with MuleSoft using JMS API

In this tutorial, we will demonstrate step by step...

How to Open Port on AWS EC2 Instance

In this tutorial, we will demonstrate step by step...

LEAVE A REPLY

Please enter your comment!
Please enter your name here