Using an OData Service¶
This example demonstrates how an RDBMS can be exposed as an OData service. When OData is enabled, you do not need to manually define CRUD operations. Therefore, OData services are an easy way to enable CRUD operations for a data service.
Note
Note that the OData feature can only be used for RDBMS, Cassandra, and MongoDB datasources.
Prerequisites¶
Let's create a MySQL database with the required data.
- Install the MySQL server.
-
Create a MySQL database named
CompanyAccounts
. .CREATE DATABASE CompanyAccounts;
-
Create a table in the
CompanyAccounts
database as follows.CREATE TABLE ACCOUNT(AccountID int NOT NULL,Branch varchar(255) NOT NULL, AccountNumber varchar(255),AccountType ENUM('CURRENT', 'SAVINGS') NOT NULL, Balance FLOAT,ModifiedDate DATE,PRIMARY KEY (AccountID));
-
Enter the following data into the table:
INSERT INTO ACCOUNT VALUES (1,"AOB","A00012","CURRENT",231221,'2014-12-02');
Synapse configuration¶
Given below is the data service configuration you need to build. See the instructions on how to build and run this example.
<data name="odata_service" transports="http https local">
<config enableOData="true" id="Datasource">
<property name="driverClassName">com.mysql.jdbc.Driver</property>
<property name="url">jdbc:mysql://localhost:3306/CompanyAccounts</property>
<property name="username">root</property>
<property name="password">password</property>
</config>
</data>
Build and run¶
Create the artifacts:
- Set up WSO2 Integration Studio. The path to this folder is referred to as
MI_TOOLING_HOME
throughout this tutorial. -
Download the JDBC driver for MySQL from here and copy it to the
MI_TOOLING_HOME/Contents/Eclipse/runtime/microesb/lib/
(for MacOS) orMI_TOOLING_HOME/runtime/microesb/lib/
(for Windows) directory.Note
If the driver class does not exist in the relevant folders when you create the datasource, you will get an exception such as
Cannot load JDBC driver class com.mysql.jdbc.Driver
. - Create the data service with the configurations given above.
- Deploy the artifacts in your Micro Integrator.
Open a command prompt execute the following CURL commands using CRUD operations:
Note
Note that you should have privileges to perform CRUD operations on the database. If not, the OData service will not work properly.
-
To get the service document:
curl -X GET -H 'Accept: application/json' http://localhost:8290/odata/odata_service/Datasource
-
To get the metadata of the service:
curl -X GET -H 'Accept: application/xml' http://localhost:8290/odata/odata_service/Datasource/$metadata
-
To read details from the ACCOUNT table:
curl -X GET -H 'Accept: application/xml' http://localhost:8290/odata/odata_service/Datasource/ACCOUNT
Supported functionality
- Navigation (e.g. GET /EMPLOYEES(1001)/DEPARTMENTS)
- One to One
- One to Many
- Count
- Append count to the result set (e.g. GET /EMPLOYEES?$count=true)
- Get only the count (e.g. GET /EMPLOYEES/$count)
- Top (e.g. GET /EMPLOYEES?$top=10)
- Skip (e.g. GET /EMPLOYEES?$skip=5)
- Select (e.g. GET /EMPLOYEES?$select=emp_no,last_name)
- Sort
- Ascending (e.g. GET /EMPLOYEES?$orderby=last_name asc)
- Descending (e.g. GET /EMPLOYEES?$orderby=last_name desc)
- Filter (e.g. GET /EMPLOYEES?$filter=dept_no eq 'd001' and emp_no eq 10001)
- Pagination (e.g. GET /EMPLOYEES?$skiptoken=5)