Mule 4: MySQL connection using Java code without DB connector

The intention of this article is to explain how we can connect to a MySQL database with a Java code instead of using Database Connector and by following this method we can use Java code with any Java supported connectors. The reason for writing this article is that, as my organisation’s policy is blocking external urls, I am not able to import any modules from exchange in Anypoint Studio. To overcome this, we can either (1) whitelisting the blocked urls in order to import modules from exchange or (2) using Java code to connect MySQL database. I prefer option 1, as studio will take care of downloading all dependencies and provide us with list of operations, but in this article, I will explain option 2 with below steps

• Download mysql connector for Java
• Install mysql as maven dependency
• Use Java code to connect db
• Transform Java object to Json

Download mysql connector for Java

The first step is to download the mysql connector for Java from MySQL official website because without this connector we can’t establish mysql connection.

For Windows, go to this link https://dev.mysql.com/downloads/connector/j/, select operating system as Platform Independent and download ZIP Archive.

For Mac, go to this link https://dev.mysql.com/downloads/connector/j/, select operating system as Platform Independent and download TAR Archive.

---

Install mysql as maven dependency

After downloaded the file, extract it and you can see .jar file inside the folder. Go to Anypoint studio and right click on your project where you want to use mysql connection and go to Mule -> Add Maven Dependency.

To verify whether the dependency was successfully installed, you can see the below snippet was added to the pom file, also you can see Project Libraries section was added to your project.

<dependency>
     <groupId>mysql</groupId>
     <artifactId>mysql-connector-java</artifactId>
     <version>0.1</version>
</dependency>

---

Use Java code to connect db

Once above two steps are successfully completed, the next step is to write Java code to connect mysql database. I am creating two Java classes(TestConn.java & EmpData.java) under package(dbConn) inside src/main/java.

TestConn.java is having a method called getResult() , which is used to establish mysql connection and then return data from database. In this code, if try block gets executed successfully then it will return array of EmpData objects or else it will return null. I am using database name as muleDB, table name as employee and my table having columns such as empId, empName & empRole.

EmpData.java is a constructor class with those columns and we are creating instance of this class & add it inside ArrayList that will accumulate result as an array of EmpData objects.

---

Transform Java object to Json

The final steps are invoking the getResult() method and convert the Java object to Json.

You can either use Invoke method from Java module or calling the method from dataweave. In this example, I am invoking the method from dataweave like below

%dw 2.0
import java!dbConn::TestConn
output application/java
---
TestConn::getResult()

and it returns an array of EmpData object.

The next step is to parse the Java object into readable Json object by iterating over the result array using map operator

%dw 2.0
output application/json
---
payload map{
    "empId": $.empId,
    "empName": $.empName,
    "empRole": $.empRole
}

---

Thanks for reading this article and I hope you got some idea on working with Java modules with external dependencies. I am aware that Database Connector is available by default in studio and we don’t need to write any code apart from writing SQL query but in case of facing issues while importing modules from exchange, I believe this approach will be helpful.