Create basic data service using WSO2 Data Service

This tutorial describes how you create simple data service using WSO2 Data Service. WSO2 Data Service is latest web service application introduced by the WSO2.Before you create your data service you have to setup WSO2 Data Service Server. Following are the prerequisites for set up the Server.

Prerequisites

1. Download WSO2 Data Service

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

3. Download JDK 1.5 or Higher

Before setup WSO2 Data service you have to install JDK and MySQL. Once you set up JDK and MySQL you have to setup sample database. here's the sample database format.

Database Name : Test

Table Format : Table Name - student

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

mysql>create database 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 setup the WSO2 data service server. Extract the downloaded WSO2 Data service pack in any location.

Then you need to add MySQL database driver to WSO2 data service. thus otherwise you cannot access MySQL database from the WSO2 Data service. for that download “mysql-connector-java-5.0.4-bin.jar and copy it to “\lib\extensions” inside data service folder.

If you are windows user you have to run wso2server.bat file or if you are Linux user you have to run wso2server.sh script using command prompt or shell to start WSO2 Data Service Server.

Once server start you can browser data service user interface using browser by using 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 service server

Now you will see 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.

In this tutorial use only few major functionalities in WSO2 Data service application. Following are the two major functionalities use in this tutorial.

• Data Services (See above Screen shot) - Displays List of data services currently deployed in the server.

• 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 new basic data service using MySQL Database. Follow there steps to create your data service. Chamara = 380

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

2. Select "RDBMS" Data Source Type in Step-2 window and click Add button for configure the data base connection

(For Integrate Relational databases like MySQL,MSSQL Server,Oracle etc RDBMS data source type should select. in addition to that WSO2 Data service support MS Excel,CSV and JNDI Data Sources also.)

3. Insert data service connection id and select data base type as follows. you can insert any name for Connection id )

4. Once you fill above fields Driver class will automatically load.

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 a example your database machine ip is 10.100.1.149 and MySQL port 3306 and database name id "Test", this is the way to fill up JDBC url.

jdbc:mysql://10.100.1.149:3306/test

6. Insert user name and password for database login

7. Click Test Connection button for establish data base connection.(if you are successfully configured in data service following message will applier) 545 chamara

8. Click Ok in configuration window and click next button in Step-2 window.

9.In Step 3 window click New query button for Add new query,

10. Insert following information in to 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. Insert output mappings for get out put from the data service

for the 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 the output mapping and click ok button.

13. click Next button for add new operation..

14. Click add new Operation button and type new operation name, select a query and click ok button.

15.Click Finish button for finish data service creation process.

Now you finished the data service creation process. you will see the created data service in data service list page.

For checking the data service you can use Try-it tool.fot that click "MyDataService"link in Service list.you will see page as follows.

Click Try this Service link and click Sample Operation button for get the output of the data service

Finally you will the the output as follows.

How to Access MySQL Database fom the Remote Location using Command Line

The is the way to access MySQL Database from the remote location using command line.

Before you access database from the remote location, you have to grant the permission to access database . (See How to Grant Permission )

then from remote location use following command

Syntax : mysql -h [IP Address of MySQL DB] -u [UserName] -p

IP Address of MySQL DB = IP Address of MySQL Server Machine

UserName = Username which granted in MySQL Server

Ex : mysql -h 10.100.1.42 -u chamara -p

When you enter above command MySQL Server will ask Password.you have to submit password which you enter in granting permission phase.

How to Retrieve data using MySQL in Java Console

This is simple example of data retrieving from the MySQL Database using java code. For connecting mysql database from the java code, you have to add MySQL Connector jar file ( Download MySQL Connector ) in to your reference libraries.

Here's the sample Code....

public class ReadingMySQL

{

    public static void main(String[] args)

   {

          Connection conn = null;
           Statement stmt = null;
            ResultSet rs = null;

            try {
              String dbURL = "jdbc:mysql://localhost:3306/wsasset";   // url of the mysql database
              String username = "root";    // username of  wsasset database
              String password = "";    // password of  wsasset database

               Class.forName("com.mysql.jdbc.Driver");

               conn = DriverManager.getConnection(dbURL, username, password);

               stmt = conn.createStatement();

               if (stmt.execute("select * from assets"))

               {
                     rs = stmt.getResultSet();
                }

               else

               {
                    System.err.println("select failed");
                }
               while (rs.next())

               {
                   String entry = rs.getString(2);
                   System.out.println(entry);
                   System.out.println(rs.getString(3));
                   System.out.println(rs.getString(4));
                   System.out.println(rs.getString(5));
                   System.out.println(rs.getString(6));
                   System.out.println(rs.getString(7));
               }

           } catch (ClassNotFoundException ex)

             {
                  System.err.println("Failed to load mysql driver");
                   System.err.println(ex);
              }

       catch (SQLException ex) {
            System.out.println("SQLException: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("VendorError: " + ex.getErrorCode());
            }

       finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) { /* ignore */ }
                rs = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ex) { /* ignore */ }
                stmt = null;
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ex) { /* ignore */ }
                conn = null;
            }
        }
    }
}