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>JSON Data Services
Sample Number |
215 |
Level |
|
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
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.
-
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"}] -
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"}] -
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"} -
GET http://localhost:8280/service/basicEmployeeService/getEmployee?id=6
HTTP/1.0 404 Not Found
-
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"}] -
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"} -
GET http://localhost:8280/service/basicEmployeeService/12
HTTP/1.0 404 Not Found
-
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