How to RESTify your data with WSO2 Data Services?

Introduction

When it comes to REST, every resource has a unique URL. So can be a record in your database. With WSO2 Data Services now you can expose each record in your data store with a unique URL.  Lets see how this can be done.

Step 1 : Creating a sample database

Let's create a sample database with one table & populate it with some data.

DROP DATABASE IF EXISTS DATASERVICE_SAMPLE;
CREATE DATABASE DATASERVICE_SAMPLE;
GRANT ALL ON DATASERVICE_SAMPLE.* TO 'dsuser'@'localhost' IDENTIFIED BY 'user123';
USE DATASERVICE_SAMPLE;


DROP TABLE IF EXISTS Employees;
CREATE TABLE Products(
    productCode VARCHAR(15),
    productName VARCHAR(70),
    productLine VARCHAR(50),
    productScale VARCHAR(10),
    productVendor VARCHAR(50),
    productDescription LONG VARCHAR,
    quantityInStock INTEGER,
    buyPrice DOUBLE,
    MSRP DOUBLE
);


CREATE UNIQUE INDEX products_pk ON Products( productCode );


insert into Products values ('S10_1678','1969 Harley Davidson Ultimate Chopper','Motorcycles','1:10','Min Lin Diecast','This replica features working kickstand, front suspension, gear-shift lever, footbrake lever, drive chain, wheels and steering. All parts are particularly delicate due to their precise scale and require special care and attention.',7933,48.81,95.7);


insert into Products values ('S10_1949','1952 Alpine Renault 1300','Classic Cars','1:10','Classic Metal Creations','Turnable front wheels; steering function; detailed interior; detailed engine; opening hood; opening trunk; opening doors; and detailed chassis.',7305,98.58,214.3);


insert into Products values ('S10_2016','1996 Moto Guzzi 1100i','Motorcycles','1:10','Highway 66 Mini Classics','Official Moto Guzzi logos and insignias, saddle bags located on side of motorcycle, detailed engine, working steering, working suspension, two leather seats, luggage rack, dual exhaust pipes, small saddle bag located on handle bars, two-tone paint with chrome accents, superior die-cast detail , rotating wheels , working kick stand, diecast metal with plastic parts and baked enamel finish.',6625,68.99,118.94);


insert into Products values ('S10_4698','2003 Harley-Davidson Eagle Drag Bike','Motorcycles','1:10','Red Start Diecast','Model features, official Harley Davidson logos and insignias, detachable rear wheelie bar, heavy diecast metal with resin parts, authentic multi-color tampo-printed graphics, separate engine drive belts, free-turning front fork, rotating tires and rear racing slick, certificate of authenticity, detailed engine, display stand precision diecast replica, baked enamel finish, 1:10 scale model, removable fender, seat and tank cover piece for displaying the superior detail of the v-twin engine',5582,91.02,193.66);


insert into Products values ('S10_4757','1972 Alfa Romeo GTA','Classic Cars','1:10','Motor City Art Classics','Features include: Turnable front wheels; steering function; detailed interior; detailed engine; opening hood; opening trunk; opening doors; and detailed chassis.',3252,85.68,136.0);


insert into Products values ('S10_4962','1962 LanciaA Delta 16V','Classic Cars','1:10','Second Gear Diecast','Features include: Turnable front wheels; steering function; detailed interior; detailed engine; opening hood; opening trunk; opening doors; and detailed chassis.',6791,103.42,147.74);


insert into Products values ('S12_1099','1968 Ford Mustang','Classic Cars','1:12','Autoart Studio Design','Hood, doors and trunk all open to reveal highly detailed interior features. Steering wheel actually turns the front wheels. Color dark green.',68,95.34,194.57);


insert into Products values ('S12_1108','2001 Ferrari Enzo','Classic Cars','1:12','Second Gear Diecast','Turnable front wheels; steering function; detailed interior; detailed engine; opening hood; opening trunk; opening doors; and detailed chassis.',3619,95.59,207.8);

Step 2 : Data Service configuration

Now we have a Product table containing some interesting products available for sale. Let's see how each of these product details can be accessed using a unique URL. First we inform Data Services of our data source. Following <config> section does that.

   <config>
      <property name="org.wso2.ws.dataservice.driver">com.mysql.jdbc.Driver</property>
      <property name="org.wso2.ws.dataservice.protocol">jdbc:mysql://localhost:3306/DATASERVICE_SAMPLE</property>
      <property name="org.wso2.ws.dataservice.user">dsuser</property>
      <property name="org.wso2.ws.dataservice.password">user123</property>
      <property name="org.wso2.ws.dataservice.minpoolsize">1</property>
      <property name="org.wso2.ws.dataservice.maxpoolsize">25</property>
   </config>

Then we write a <query> which will extract Product details matching a given criteria & produce a XML output. For this I am going to use following simple sql, which will return one product record.

  select productCode,productName,productLine,productScale,productVendor,productDescription,quantityInStock,buyPrice,MSRP 
  from 
    Products 
  where 
    productCode=?

   <query id="productQuery" useConfig="SalaryDB">
      <sql>select productCode,productName,productLine,productScale,productVendor,productDescription,quantityInStock,buyPrice 
           from Products 
           where productCode= ?</sql>
      <result element="Product" rowName="Details" defaultNamespace="http://product.abc.com">
         <element name="ProductCode" column="productCode" />
         <element name="Name" column="productName" />
         <element name="ProductLine" column="productLine" />
         <element name="Scale" column="productScale" />
         <element name="Vendor" column="productVendor" />
         <element name="Description" column="productDescription" />
         <element name="quantityInStock" column="quantityInStock" />
         <element name="Price" column="buyPrice" />
      </result>
      <param name="productCode" sqlType="STRING" />
   </query>

Now we need to map out put of this query to a resource. This is how we do that.

   <resource path="product" method="GET">
      <call-query href="productQuery">
         <with-param name="productCode" query-param="productCode" />
      </call-query>
   </resource>

This is the complete configuration.

<data name="Products">  
  <config>
      <property name="org.wso2.ws.dataservice.driver">com.mysql.jdbc.Driver</property>
      <property name="org.wso2.ws.dataservice.protocol">jdbc:mysql://localhost:3306/DATASERVICE_SAMPLE</property>
      <property name="org.wso2.ws.dataservice.user">dsuser</property>
      <property name="org.wso2.ws.dataservice.password">user123</property>
      <property name="org.wso2.ws.dataservice.minpoolsize">1</property>
      <property name="org.wso2.ws.dataservice.maxpoolsize">25</property>
   </config>

   <query id="productQuery">
      <sql>select productCode,productName,productLine,productScale,productVendor,productDescription,quantityInStock,buyPrice          
           from Products 
           where productCode= ?</sql>
      <result element="Product" rowName="Details" defaultNamespace="http://product.abc.com">
         <element name="ProductCode" column="productCode" />
         <element name="Name" column="productName" />
         <element name="ProductLine" column="productLine" />
         <element name="Scale" column="productScale" />
         <element name="Vendor" column="productVendor" />
         <element name="Description" column="productDescription" />
         <element name="quantityInStock" column="quantityInStock" />
         <element name="Price" column="buyPrice" />
      </result>
      <param name="productCode" sqlType="STRING" />
   </query>

   <resource path="product/{productCode}" method="GET">
      <call-query href="productQuery">
         <with-param name="productCode" query-param="productCode" />
      </call-query>
   </resource>
</data>

Step 3 : Accessing product details through URL

Once you have above service deployed, you can access details of a particular product as follows.

 http://10.0.0.3:9763/services/Products/product/S10_4962

The general format for the accessing Product details via URL is as follows.

 http://<IP>:<PORT>/services/<Service-name>/<path-defined-in-resource-definition-section>/<productCode>