How to create a MYSQL data service using WSO2 data services Server

In this post I am going to explain how to create a simple data service to expose data as a service using WSO2 Data service Server..

Before we begin since I am going to create a data service for MYSQL data store you need to install and have mysql server(this can be any database server) .

Lets create a database call MyDSDB and create a table call Person.

Create database MyDSDB;
use MyDSDB;
CREATE TABLE Persons
(
P_Id int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

show tables;

Now that we created a database and a table lets see how we can create a data service to insert and retrieve data from this table.

If you are using wso2 products for the first time ...

You need to download wso2 dataservices server.

Start up the server -> go to DS_HOME/bin/wso2server.bat | wso2server.sh (DS_HOME is where your set up is located)

Once the server is up and running open a web browser and navigate to https://localhost:9443/carbon.

Login it the server using the default credentials (username=admin, password=admin).

Also note that you need to provide the related jdbc driver for the database and put in repository/component/lib in order for this feature to work.

Step 1- Create a Data source.

Go to left hand side menu and click on Webservice -> Add -> Data Service -> Create and give a suitable service name for your data service.

Data Service Name* - MyFirstDSS

Click on Next to create the Datasource. 

Inorder to create the data service you need to specify your database information. That is done under create Data source. WSO2 data services support many types of data sources such as RDBMS,Excel,CSV,RDF etc. Here we are creating a data service for RDBMS->MYSQL therefore, lets select RDBMS as the Data Source Type and MYSQL as Database Engine.

DataSource Id - MyDS

Data Source Type - RDBMS

Database Engine - MYSQL 

Driver Class - com.mysql.jdbc.Driver

JDBC URL - jdbc:mysql://localhost:3306/MyDSDB

User Name - root

Password - password

Click test connection to test your connection.

Step 2 - Create Query.

In data services creating query doesnt mean just giving the SQL query needed for the data service. It also means defining the input parameters, and response (output parameters) and how we should get the results of our data service.

Lets first create a query to insertPersons.  In insert Person query you only give set of input parameters to input the data into the table.      

Click on Add new Query to create a new Query.

Query ID - InsertPersonQ

Data Source - MyDS

SQL - INSERT INTO Persons (P_Id, LastName, FirstName, Address,City) VALUES(?,?,?,?,?)

Click on Add input mappings to add input parameters to the query. Here we have to map each input parameter to the Mapping name along with their data type as shown below. When you are going to give input parameters you can also validate the input by adding a validator to your input such as (long,double,date and also custom validators). Please refer Adding Custom validators in oder to see how you can add custom validators.

Once you add the input parameters go to main configurations and your query will look like shown below. 

Click on save to save thequery. Now we have written a query to insert data.

Lets see how we can create a query to retrieve data.

 Add new Query -> 

Query ID - SelectPersonsQ

Data Source - MyDS

SQL - Select  P_Id, LastName, FirstName, Address,City From Persons 

Since we are getting a results (Select Query returns set of rows from our table) we need to define the result set in the data service. There are two options to add result set to your Query. You can manually go and click on add New Output Mappings and add the results (before you add you need to give the RowName and Grouped by elements ). Or you can simply create Generate response link data service server will auto generate your response for you.

Once you create the query add save and go to operation section.

Step 3 - Creating Operations

Creation operations is easy, once you create a query you need to create an operation mapping to the Queries you just created.
Click on add new operations, and it will display an operation wizard. Give an operation Name and map that operation to the queries you created.

Operation Name - GetPeople
Query ID - SelectPersonsQ

Operation Name - InsertPerson

Query ID - InsertPersonQ

Once you have added the two queries click on finish to save the data service. It will deploy the created data service as an axis2 service. If you go to Web services list you can see your newly created data service as shown below.

If you click on the data service you can see list of Quality of service operations given for that data service. You can add throttling security and catching for that data service. And also you can view the dbs (XML) of the created service by clicking on Data Service XML Editor.



<data name="MyFirstDSS">

  <config id="MyDS">
     <property name="org.wso2.ws.dataservice.driver">com.mysql.jdbc.Driver</property>
     <property name="org.wso2.ws.dataservice.protocol">jdbc:mysql://localhost:3306/MyDSDB</property>
     <property name="org.wso2.ws.dataservice.user">root</property>
     <property name="org.wso2.ws.dataservice.password">root</property>
  </config>
  <query id="InsertPersonQ" useConfig="MyDS">
     <sql>INSERT INTO Persons(P_Id, LastName, FirstName, Address,City) VALUES(?,?,?,?,?)</sql>
     <param name="P_Id" sqlType="STRING" ordinal="1" />
     <param name="LastName" sqlType="STRING" ordinal="2" />
     <param name="FirstName" sqlType="STRING" ordinal="3" />
     <param name="Address" sqlType="STRING" ordinal="4" />
     <param name="City" sqlType="STRING" ordinal="5" />
  </query>
  <query id="SelectPersonsQ" useConfig="MyDS">
     <sql>Select  P_Id, LastName, FirstName, Address,City From Persons</sql>
     <result element="Keys" rowName="Key">
        <element name="P_Id" column="P_Id" />
        <element name="LastName" column="LastName" />
        <element name="FirstName" column="FirstName" />
        <element name="Address" column="Address" />
        <element name="City" column="City" />
     </result>
  </query>
  <operation name="GetPeople">
     <call-query href="SelectPersonsQ" />
  </operation>
  <operation name="InsertPerson">
     <call-query href="InsertPersonQ">
        <with-param name="P_Id" query-param="P_Id" />
        <with-param name="LastName" query-param="LastName" />
        <with-param name="FirstName" query-param="FirstName" />
        <with-param name="Address" query-param="Address" />
        <with-param name="City" query-param="City" />
     </call-query>
  </operation>

</data>


You can invoke your service by the try-it feature by clicking on the try it link in the service dashboard.