JSON Data Services

Sample Number

215

Level

Intermediary

Description

This sample demonstrates the usage of UltraESB to create RESTful JSON Data Services and extension to support CRUD operations, XML/CSV style output etc.

Use Case

I want to expose results of specified SQL queries as RESTful JSON data, and/or create CRUD style Data Services to manipulate a relational database using a REST interface

The UltraESB facilitates the integration of systems in a simple, yet powerful manner. One such feature is its ability to create RESTful JSON Data Services with just a very few lines of configuration. The REST services can be mapped to specific URL’s, and when invoked would execute the given SQL statements and passed parameters to return results in a JSON format. These services could be used to expose existing relational data to Rich client UI’s supporting AJAX and other related technologies such as GWT or custom clients.

The Data Service we will build is expected to execute the following SQL queries for the corresponding URI request patterns, and return the results as a JSON response. Note that parameters to URI formats are given as \{variableName:Type} where the variableName then maps to the SQL statement variables. The types are  the field names of java.sql.Types - and currently the types : VARCHAR, INTEGER, BOOLEAN, BIGINT, DATE, DOUBLE, FLOAT, SMALLINT and TIMESTAMP are supported.

URI pattern SQL Query to select result
Result

/getPermanentEmployees

select * from employee where permanent = 1

Array

/getTaxPayers

select * from employee where salary > 20000

Array

/getEmployee?\{id:INTEGER}

select * from employee where employeeId = :id

Single Element

/byDeptAndDivision/\{department:INTEGER}/\{division:INTEGER}?\{age:INTEGER},\{sex:VARCHAR}

select * from employee where division = :division and department = :department and sex = :sex and age > :age

Array

/\{id:INTEGER}

select * from employee where employeeId = :id

Single Element

 

select * from employee

Array

Sample Configuration

The full configuration driving the complete sample we use is listed below, and as usual, this is also a standard Spring configuration file.  The specific lines for this example total to less than 10. The sample Data Service is a subset of the UltraESB Sample #215 that ships with the distribution.  At lines 67 and 71, we define a transactional JDBC Datasource as per standard Spring semantics. Line 37-49 defines the bean "empJsonSvc" which implements the basic JSON Data Service functionality. It is configured by specifying values for its "queryMap" which basically requires a key/value pair for each URI pattern and SQL to be executed, as shown in the table above. SQL statements that are expected to return a HTTP 404 response (instead of an empty JSON array) when no results are found, should be marked as "SINGLE: <sql statement>".

Sample Configuration for RESTful JSON Data Services

 1<?xml version="1.0" encoding="UTF-8"?>
 2<beans xmlns="http://www.springframework.org/schema/beans"
 3       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 4       xmlns:u="http://www.adroitlogic.org/ultraesb"
 5       xmlns:s="http://www.springframework.org/schema/security"
 6       xsi:schemaLocation="
 7http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.2.xsd
 8http://www.springframework.org/schema/security http://www.springframework.org/schema/security/spring-security-3.2.xsd
 9http://www.adroitlogic.org/ultraesb http://schemas.adroitlogic.org/ultraesb/v2_6/ultraesb-artifacts.xsd">
10
11    <bean id="ultra-config" class="org.adroitlogic.ultraesb.core.ConfigurationImpl"/>
12
13    <bean id="fileCache" class="org.adroitlogic.ultraesb.core.PooledMessageFileCache">
14        <constructor-arg type="int" value="20"/>
15    </bean>
16
17    <bean id="http-8280" class="org.adroitlogic.ultraesb.transport.http.HttpNIOListener">
18        <constructor-arg ref="fileCache"/>
19        <property name="port" value="8280"/>
20    </bean>
21
22    <!--Demonstrates a basic query-only JSON data service. This only supports GET method, and is able to return one
23    or all rows from a table using the specified queries-->
24    <u:proxy id="basicEmployeeService">
25        <u:transport id="http-8280">
26            <u:property name="ultra.transport.url" value="basicEmployeeService*"/>
27        </u:transport>
28        <u:target>
29            <u:inSequence>
30                <u:java><![CDATA[
31                    mediation.getJSONSupport().processBasicJSONDataServiceRequest(msg, "empJsonSvc", "dataSource");
32                ]]></u:java>
33            </u:inSequence>
34        </u:target>
35    </u:proxy>
36
37    <bean id="empJsonSvc" class="org.adroitlogic.ultraesb.core.helper.BasicJSONDataService" init-method="init">
38        <property name="queryMap">
39            <map>
40                <entry key="/getPermanentEmployees" value="select * from employee where permanent = 1"/>
41                <entry key="/getTaxPayers" value="select * from employee where salary > 20000"/>
42                <entry key="/getEmployee?{id:INTEGER}" value="SINGLE: select * from employee where employeeId = :id"/>
43                <entry key="/byDeptAndDivision/{department:INTEGER}/{division:INTEGER}?{age:INTEGER},{sex:VARCHAR}"
44                    value="select * from employee where division = :division and department = :department and sex = :sex and age > :age"/>
45                <entry key="/{id:INTEGER}" value="SINGLE: select * from employee where employeeId = :id"/>
46                <entry key="" value="select * from employee"/>
47            </map>
48        </property>
49    </bean>
50
51    <!--Demonstrates a custom JSON data service, that supports GET, POST, PUT and DELETE operations on the Employee
52     domain object. Uses the Springframework to invoke JDBC calls, and the Jackson parser to generate and parse
53     requests and responses as JSON. Is exposed at a user specified custom URL /employees. The sequence may be easily
54     step-through debugged with a standard Java IDE -->
55    <u:proxy id="employees">
56        <u:transport id="http-8280">
57            <u:property name="ultra.transport.url" value="/employees*"/>
58        </u:transport>
59        <u:target>
60            <u:inSequence>
61                <u:class name="samples.json.JsonCRUDSequence"/>
62            </u:inSequence>
63        </u:target>
64    </u:proxy>
65
66    <!--Standard Spring transactional DataSource definition-->
67    <bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
68        <property name="dataSource" ref="dataSource"/>
69    </bean>
70
71    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
72        <property name="driverClassName" value="org.apache.derby.jdbc.ClientDriver"/>
73        <property name="url" value="jdbc:derby://localhost:1527/localderby;create=true;user=admin;password=admin"/>
74    </bean>
75
76</beans>

Executing the Sample

Follow steps in the appendix (at the end of this document) to create the sample Derby database, and to populate the employee table with test data.

This sample configuration ships as Sample #215. However, note that the default JDBC URL has been altered in this example, as the database is being created manually (Note: the default configuration is utilized by the JUnit test case BasicJSONDataServiceTest found with the source distribution). To start the sample Data Services, edit the samples/conf/ultra-sample-215.xml and change the JDBC URL as per line #73 shown above. (You could also replace the full content of the file with that shown above). Next, execute "bin/ultraesb.sh -sample 215" or "bin\ultraesb.bat -sample 215" to start this sample configuration #215.

To execute a sample client, start the SOA ToolBox as "bin/toolbox.sh" or "bin\toolbox.bat" from the UltraESB distribution bin directory. Start an HTTP/S Client from the File menu of the SOA ToolBox, and select "GET" as the HTTP method. Enter the following URLs into the URL field, and test the responses received. Note that the service context "/service" and the service URI "basicEmployeeService" etc. could easily be customized if required.

  1. GET http://localhost:8280/service/basicEmployeeService/getPermanentEmployees

    [{"EMPLOYEEID":2,"EMPLOYEENAME":"mary","AGE":36,"DEPARTMENT":2,"DIVISION":4,"SALARY":4454.0,"SEX":"female","PERMANENT":1,"ADDRESS":"tokyo","DATEOFBIRTH":"1974-12-05"},
    {"EMPLOYEEID":3,"EMPLOYEENAME":"nancy","AGE":39,"DEPARTMENT":2,"DIVISION":3,"SALARY":22342.0,"SEX":"male","PERMANENT":1,"ADDRESS":"london","DATEOFBIRTH":"1971-05-14"},
    {"EMPLOYEEID":4,"EMPLOYEENAME":"bill","AGE":60,"DEPARTMENT":3,"DIVISION":4,"SALARY":44533.0,"SEX":"female","PERMANENT":1,"ADDRESS":"dulles","DATEOFBIRTH":"1950-04-16"}]
  2. GET http://localhost:8280/service/basicEmployeeService/getTaxPayers

    [{"EMPLOYEEID":1,"EMPLOYEENAME":"tom","AGE":35,"DEPARTMENT":2,"DIVISION":3,"SALARY":34500.0,"SEX":"male","PERMANENT":0,"ADDRESS":"milano","DATEOFBIRTH":"1975-02-23"},
    {"EMPLOYEEID":3,"EMPLOYEENAME":"nancy","AGE":39,"DEPARTMENT":2,"DIVISION":3,"SALARY":22342.0,"SEX":"male","PERMANENT":1,"ADDRESS":"london","DATEOFBIRTH":"1971-05-14"},
    {"EMPLOYEEID":4,"EMPLOYEENAME":"bill","AGE":60,"DEPARTMENT":3,"DIVISION":4,"SALARY":44533.0,"SEX":"female","PERMANENT":1,"ADDRESS":"dulles","DATEOFBIRTH":"1950-04-16"}]
  3. GET http://localhost:8280/service/basicEmployeeService/getEmployee?id=3

    {"EMPLOYEEID":3,"EMPLOYEENAME":"nancy","AGE":39,"DEPARTMENT":2,"DIVISION":3,"SALARY":22342.0,"SEX":"male","PERMANENT":1,"ADDRESS":"london","DATEOFBIRTH":"1971-05-14"}
  4. GET http://localhost:8280/service/basicEmployeeService/getEmployee?id=6

    HTTP/1.0 404 Not Found
  5. GET http://localhost:8280/service/basicEmployeeService/byDeptAndDivision/2/3?age=20&sex=male

    [{"EMPLOYEEID":1,"EMPLOYEENAME":"tom","AGE":35,"DEPARTMENT":2,"DIVISION":3,"SALARY":34500.0,"SEX":"male","PERMANENT":0,"ADDRESS":"milano","DATEOFBIRTH":"1975-02-23"},
    {"EMPLOYEEID":3,"EMPLOYEENAME":"nancy","AGE":39,"DEPARTMENT":2,"DIVISION":3,"SALARY":22342.0,"SEX":"male","PERMANENT":1,"ADDRESS":"london","DATEOFBIRTH":"1971-05-14"}]
  6. GET http://localhost:8280/service/basicEmployeeService/4

    {"EMPLOYEEID":4,"EMPLOYEENAME":"bill","AGE":60,"DEPARTMENT":3,"DIVISION":4,"SALARY":44533.0,"SEX":"female","PERMANENT":1,"ADDRESS":"dulles","DATEOFBIRTH":"1950-04-16"}
  7. GET http://localhost:8280/service/basicEmployeeService/12

    HTTP/1.0 404 Not Found
  8. GET http://localhost:8280/service/basicEmployeeService

    [{"EMPLOYEEID":1,"EMPLOYEENAME":"tom","AGE":35,"DEPARTMENT":2,"DIVISION":3,"SALARY":34500.0,"SEX":"male","PERMANENT":0,"ADDRESS":"milano","DATEOFBIRTH":"1975-02-23"},{"EMPLOYEEID":2,"EMPLOYEENAME":"mary","AGE":36,"DEPARTMENT":2,"DIVISION":4,"SALARY":4454.0,"SEX":"female","PERMANENT":1,"ADDRESS":"tokyo","DATEOFBIRTH":"1974-12-05"},
    {"EMPLOYEEID":3,"EMPLOYEENAME":"nancy","AGE":39,"DEPARTMENT":2,"DIVISION":3,"SALARY":22342.0,"SEX":"male","PERMANENT":1,"ADDRESS":"london","DATEOFBIRTH":"1971-05-14"},
    {"EMPLOYEEID":4,"EMPLOYEENAME":"bill","AGE":60,"DEPARTMENT":3,"DIVISION":4,"SALARY":44533.0,"SEX":"female","PERMANENT":1,"ADDRESS":"dulles","DATEOFBIRTH":"1950-04-16"}]

Extending the Sample - Support for CRUD Operations, XML, CSV etc

The JSON Data Services support could be expanded to create RESTful XML Data Services as well. In addition, support for SQL inserts, deletes and updates, as well as the execution of stored procedures could be performed as well. An example, is the "employees" proxy service and its samples.json.JsonCRUDSequence sequence. Creation of Data Services supporting any other message formats such as CSV, Hessian etc will also become a trivial extention.

Appendix - Setting up the sample database with Derby

In this example we use a Derby database that ships with the JDK to host our table. First change to the JDK directory and traverse into the db/bin directory (e.g. /opt/jdk/db/bin). Now execute the script "startNetworkServer" or "startNetworkServer.bat" as follows to start a Network enabled Derby database:

asankha@asankha:/opt/jdk/db/bin$ ./startNetworkServer
Security manager installed using the Basic server security policy.
Apache
 Derby Network Server - 10.4.2.0 - (689064) started and ready to accept
connections on port 1527 at 2010-10-23 10:55:06.287 GMT

This will start the Derby database, and listen over port 1527 for client connections. From another terminal, execute the script "ij" as shown below:

asankha@asankha:/opt/jdk/db/bin$ ./ij
ij version 10.4
ij>

This will allow us to create the schema and a table, and populate it with test data. But first execute the following commands to connect and create the database, and then to create a schema 'admin'

ij> connect 'jdbc:derby://localhost:1527/localderby;create=true;user=admin;password=admin';
ij> create schema admin;
0 rows inserted/updated/deleted

Proceed to create the table, and to populate it with four sample rows:

ij> CREATE TABLE ADMIN.EMPLOYEE (
employeeId      INTEGER GENERATED ALWAYS AS IDENTITY(START WITH 1, INCREMENT BY 1) NOT NULL,
employeeName    VARCHAR(25),
age             INTEGER,
department      INTEGER,
division        INTEGER,
salary          DOUBLE,
sex             VARCHAR(6),
permanent       SMALLINT,
address         VARCHAR(25),
dateOfBirth     DATE);
0 rows inserted/updated/deleted

ij> INSERT INTO "ADMIN"."EMPLOYEE"(employeeName, department, division, age, salary, sex, permanent, address, dateOfBirth)
VALUES('tom', 2, 3, 35, 34500.0, 'male', 0, 'milano', '1975-02-23');
1 row inserted/updated/deleted

ij> INSERT INTO "ADMIN"."EMPLOYEE"(employeeName, department, division, age, salary, sex, permanent, address, dateOfBirth)
VALUES('mary', 2, 4, 36, 4454.0, 'female', 1, 'tokyo', '1974-12-05');
1 row inserted/updated/deleted

ij> INSERT INTO "ADMIN"."EMPLOYEE"(employeeName, department, division, age, salary, sex, permanent, address, dateOfBirth)
VALUES('nancy', 2, 3, 39, 22342.0, 'male', 1, 'london', '1971-05-14');
1 row inserted/updated/deleted

ij> INSERT INTO "ADMIN"."EMPLOYEE"(employeeName, department, division, age, salary, sex, permanent, address, dateOfBirth)
VALUES('bill', 3, 4, 60, 44533.0, 'female', 1, 'dulles', '1950-04-16');
1 row inserted/updated/deleted
In this topic
In this topic
Contact Us