Mule Database Connector With HSQLDB

In this article, I will let you know how to use the database connector in MuleSoft for HSQLDB. Below are the points of which I will be giving a brief description:

1. How to install the HSQL database on your system.

2. The Mule flow to connect to the above-created database.

First, we will have some idea about the HSQL database. HSQLDB (HyperSQL Database) is a relational database engine written in Java. I have used version 2.3.4 in my example. This will not take much of the memory and space on your system and will install easily. Below are the steps:

Download the latest version of the HSQLDB database. I have version 2.3.4 downloaded from this link.

Extract the zip file you downloaded in the C directory.

Create a properties file named server.properties under C:\hsqldb-2.3.4\hsqldb, which defines a new database named Mydemodb with the below property lines:

server.database.0 = file:hsqldb/MyDemodb
server.dbname.0 = testdb

Then execute the below command in the command line:

cd C:\hsqldb-2.3.4\hsqldb
hsqldb>java -classpath lib/hsqldb.jar org.hsqldb.server.Server

Once we have created a database, we have to start the database by using the following command:

\>cd C:\hsqldb-2.3.4\hsqldb
hsqldb>java -classpath lib/hsqldb.jar org.hsqldb.server.Server --database.0
file:hsqldb/demodb --dbname.0 testdb

 Now you can open the database GUI with runManagerSwing.bat from the C:\hsqldb-2.3.4\hsqldb\bin location. You will be asked to fill in the connection settings; fill them in as below:

Now the database GUI will open and you can create your test tables and insert the data.

Now we will see how we can use this database in our Mule flow.

Create a new Mule project "DB_connector_example." We will be creating a flow like below:

 First, insert an HTTP inbound connector listening on 8081 and on path /dbconnector.

Insert a logger to log in a message.

Now, here comes the important part: the database connector. Place a DB connector after the logger and define the global database connector as below:

Also, add the hsqldb2.3.4 jar file in the referenced libraries so that the driver class name can be looked into that Jar file. Right click on your project -->Build Path-->Add external archive and point to the jar file.

Now, if your database is running, you can test the connection in the global test configuration. If that is successfully tested, you may proceed with your flow.

Now, if you will place the last logger ( skipping the Object to JSON Transformer) and run your application you will get the below distorted output.

¬í sr java.util.LinkedList)S]J`ˆ"  xpw   sr $org.mule.util.CaseInsensitiveHashMapÑÙïgEÎ
  xpw?@       t ¬í sr java.util.LinkedList)S]J`ˆ"  xpw   sr $org.mule.util.CaseInsensitiveHashMapÑÙïgEÎ
  xpw?@       t EMPLOYEEIDsr java.lang.Integerâ ¤÷‡8 I valuexr java.lang.Number†¬•”à‹
  xp   {t EMPLOYEENAMEt mohitxsq ~ w?@       q ~ q ~ q ~ q ~ xx

Because the output is java.util.LinkedList, which displays on the client side, it is not converted into any standard message format. To convert it to a JSON format, we will use an Object to JSON Transformer in between the logger and DB connector.

Now save your project and run the application. From any browser, hit http://localhost:8081/dbconnector and hit enter; you will get the data from the table.