Database Integration in WSO2 EI 7.1.0

Pre-Requisites

  1. Windows 10 OS.
  2. MySQL 8.0 server and Command-Line Client.
  3. JDBC driver for MySQL (http://dev.mysql.com/downloads/connector/j/). Download it to your local machine.
  4. WSO2 Integration Studio 8.0.0.

Implementation Steps

DB Creation (Run the below commands in MySQL command-line client to create a database and table for this demo)->

  1. Create Database Students
  2. Use Students
  3. CREATE TABLE Students (StudentID int, FirstName varchar(255), LastName varchar(255), Result varchar(255))

Service Implementation in Integration Studio

     1. Create a new Maven Multi-Module project on Getting Started page.

Database Integration in WSO2 EI 7.1.0

    2. Enter the details as below in Maven Modules Creation Wizard.

Database Integration in WSO2 EI 7.1.0

     3. Right-click on the new maven project and select New -> Data Service Configs and enter the details as below.

Database Integration in WSO2 EI 7.1.0

      4. Click Next and verify the details are as below and then click on Finish. Details can be edited if needed.

Database Integration in WSO2 EI 7.1.0

        5. Right Click on the maven project and select New -> Composite Exporter and enter Module Name as DBIntegration_CAR. Click Next, then verify details as in Step 4. Specify parent as DBIntegrationProject and click on Finish.

       6. The structure of the project will look as below:

Database Integration in WSO2 EI 7.1.0

       7. Right-click DBIntegrationModule and select New -> Data Service. Then select Create a New Data Service and click Next. Enter Data Service name and click Finish.

            Database Integration in WSO2 EI 7.1.0

        8. A new StudentsDataService.dbs file gets created. Click on Configure Data Sources and then Add New. Enter the details as below. Enter the username and password for your MySQL DB, test the connection, and verify it is successful.

Database Integration in WSO2 EI 7.1.0

                Database Integration in WSO2 EI 7.1.0

         9. Click on Configure Queries and add 3 queries to add, get and update student data in the DB.

               Database Integration in WSO2 EI 7.1.0

               i) Add student data – Enter the query and add input mappings manually by clicking Add New as below.

                   Database Integration in WSO2 EI 7.1.0

                       Database Integration in WSO2 EI 7.1.0

               ii) Get student data – Enter the query and add input and output mappings automatically by clicking Generate as below.

Database Integration in WSO2 EI 7.1.0

                    Database Integration in WSO2 EI 7.1.0

                              Database Integration in WSO2 EI 7.1.0

                    iii) Update student data – Enter the query and add input mappings manually by clicking Add New as below.

                           Database Integration in WSO2 EI 7.1.0

                           Database Integration in WSO2 EI 7.1.0

         10. Click on Configure Resources and add 3 resources as below.

                 Database Integration in WSO2 EI 7.1.0

Deployment: We will be using the embedded Micro Integrator server available in the WSO2 Integration studio to run our service.

            1. Click on the Embedded Micro Integrator server icon found at the top of the Integration Studio. Then click the + icon and add the MySQL driver jar (Step 2 of Prerequisites) to the /lib directory of the embedded Micro Integrator server. Click Save.

                  Database Integration in WSO2 EI 7.1.0

              2. Right-click on the DBIntegration_CAR module in the project explorer in Integration Studio and click on Export Project Artifacts and Run.

              3. In the dialog box that opens select the required artifacts and click Finish as shown below. In our case, DBIntegrationModule is the required artifact.

Database Integration in WSO2 EI 7.1.0

Testing: After the embedded Micro Integrator server starts, we can test this service using Postman as shown in the below images. We can also test using the inbuilt HTTP client present in WSO2 Integration Studio. The HTTP listener port is 8290 by default.

             1. Add student data.

Database Integration in WSO2 EI 7.1.0

           2. Get student data.

Database Integration in WSO2 EI 7.1.0

          3. Update student data.

Database Integration in WSO2 EI 7.1.0

           4. Get updated student data.
Database Integration in WSO2 EI 7.1.0

That's it! I hope you enjoyed reading this post. 

 

 

 

 

Top