Create a Simple Data Service with WSO2 Data Services

Introduction

WSO2 Data Services is the latest Web services creation tool introduced by WSO2. Before you create your data services, you need to setup the WSO2 Data Services server. Following are the prerequisites for set up the Server.

Prerequisites

1. Download WSO2 Data Services
   
   

2. Download MySQL (This tutorial explains create a data service using MySQL Database)

3. Download JDK 1.5 or Higher

In order to setup WSO2 Data Services, you have to install JDK and MySQL. Thereafter, you need to setup a sample database. Here's the sample database format:

Database Name : Test

Table Format : Table Name - student

Name	Marks	Status
Chamara	88	Pass
John	76	Pass
Suminda	69	Pass
Peter	41	Fail

You can use following SQL Command in MySQL command prompt for creating a database:

mysql>DROP DATABASE IF EXIST Test;
mysql>create database Test;
mysql>use Test;
mysql>CREATE table student(Name varchar(100),Marks int(50),Status varchar(100));
mysql>insert INTO student values ('Chamara',88,'Pass');
mysql>insert INTO student values ('John',76,'Pass');
mysql>insert INTO student values ('Suminda',69,'Pass');
mysql>insert INTO student values ('Peter',41,'Fail');

Next step, is to setup the WSO2 Data Service server. Extract the downloaded WSO2 Data Services pack to any location on your hard drive.

Then you need to add the MySQL database driver to the WSO2 Data Services, to allow access the MySQL database from the WSO2 Data Services. Download “mysql-connector-java-5.0.4-bin.jar and copy it to “\lib\extensions” inside data services folder.

If you are a Windows user, you need to run wso2server.bat file, or, for a Linux user you need to run wso2server.sh script using command prompt or shell to start WSO2 Data Services server.

Once the server starts up, you can browse the Data Services user interface using your browser by pointing it to the following URL.:

For local Access :https://localhost:9443/ds/index.html

For Remote Access :https://[ServerIP]:9443/ds/index.html

[ServerIP] = IP address of the WSO2 Data Services server

Now, you will see the WSO2 Data service User interface.

 To login to the Management Console Use following default user Credentials in sign in page.

              User Name : admin

              Password   : admin

After login you can see WSO2 Data Service Home page as follows. 

This tutorial illustrates only few major functionalities in the WSO2 Data Services tool. Following are the two major functionalities covered in this tutorial:

• Add (see above screen shot) - Use to create new data service.

• Try it tool - use check output of the data service

( For more information about WSO2 Data Services functionalities,refer User guide.)

Now you are ready to create simple data service using the MySQL database. The following three steps are needed to create your data service.

1. Click "Add" and insert a data service name and description. Click "next"

2.  Select RDBMS as the Data Source Type in the following window and click the Add button to configure the data base connection.

(For integrating relational databases such as MySQL,MSSQL Server, Oracle etc., RDBMS should be selected as the data source type. In addition to RDBMS, the WSO2 Data Services product supports MS Excel, CSV and JNDI sources also.)

3. Insert a data service connection id, and select data base type as shown in the following diagram. (you can insert any name for Connection id )

4. Once you've filled the above fields, the driver class will be automatically loaded.

5.JDBC url you have to change for your database information

    Default JDBC url display as "jdbc:mysql://[machine-name/ip]:[port]/[database-name]". For example, if your database machine IP is 10.100.1.149 and MySQL port is 3306 and database name id is "Test", your JDBC url should be:

    jdbc:mysql://10.100.1.149:3306/test

if your database and the SQL server, both are in one machine, then you can use the jdbc url as:

   jdbc:mysql://localhost:3306/test

6. Insert the user name and password for database login

7. Click the Test Connection button in order to establish data base connectivity.(if you are successfully connected, the following message will appear)

8. Click Ok in the configuration window and select the next button to proceed.

9.On the same screen as in step 3, select New Query button to add a new query,

10. Insert following information into the add new query window:

        Query ID = Name of the query (Test Query)

        Data Source = Select data source connection created earlier. (Sample Connection)

        SQL Statement = select * from Test

        Grouped by element = Name of the group element (BaseElement)

        Row name = Name of the sub element (Element)

11. Specify output mappings for out put from the data service

      Click Add New Output Mapping button and select element mapping type, insert output field name and SQL Column name. (output field name can be any name, but SQL Column name should equal to the database column name )

12. Add all output mapping and click the OK button.

13. click Next

14. Click the Add New Operation button and type new operation name. Select a query and click the OK button.

15.Select the Finish button to complete the data service creation process.

You will now see the newly created data service in the data service list page.

In order to test the newly created data service, use the Try-it tool.

First, select the "MyDataService" link in the service list and you will see the following page:

Now, select the Try This Service link and click Sample Operation to get the output of the data service

Finally, you will will see the output:

After create data service,you have to write proper client for call that web service.this is the way to do that.

16. Go to the Service Management page and click "Generate Client" link for Gerate Client Jar file.

17. select unpack class as false.

18. Click Generate Client button for create client jar file.

19.Open New java Project in Eclipse and add external jars from

       > DataServices\\repository\lib

in addtion to that add generated client jar file aslo.

20. Finnaly wite a java code for invoke the services.

 package Org;



import org.wso2.wsas.client.SQL_Trim_MyStub;









public class TestService {

    public static void main(String[] args) throws Exception{

        MyDataServiceStub stub = new MyDataServiceStub();

        MyDataServiceStub .SampleOperation request1 =  new MyDataServiceStub .SampleOperation();

      

        try{

        

            MyDataServiceStub.BaseElementE Response1 = stub.SampleOperation(request1);

          

            System.out.println(Response1.getBaseElement().getElement()[1].getName());

            System.out.println(Response1.getBaseElement().getElement()[1].getMarks());

            System.out.println(Response1.getBaseElement().getElement()[1].getStatus());

       

            }

           

       catch (Exception e) {

            // TODO: handle exception

        }

    }



}

When running this java code you can see the output.

Author

Suminda Chamara Silva is a Senior Software Engineer - Quality Assurance at WSO2. chamara at wso2 dot com