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 that the OData feature can only be used for RDBMS, Cassandra, and MongoDB datasources.
Let's create a MySQL database with the required data.
- Install the MySQL server.
Create a MySQL database named
CREATE DATABASE CompanyAccounts;
Create a table in the
CompanyAccountsdatabase 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');
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_HOMEthroughout this tutorial.
Download the JDBC driver for MySQL from here and copy it to the
If you are running this sample using the embedded Micro Integrator of WSO2 Integration Studio, the
libdirectory is located in
MI_TOOLING_HOME/Contents/Eclipse/runtime/microesb/(for Linux/MacOS/CentOS) or
If the driver class does not exist in the relevant directory 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 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