This article demonstrates how Java application can query data from relational databases (well, in this case more specifically the Oracle RDBMS) without the use of plain old JDBC but instead through a simple HTTP interaction with a RESTful service that returns the data in JSON format.
In this way, issues with JDBC drivers, database connections, data sources and firewall and other network complications are avoided and the Java application does not contain a single bit of SQL.
This article will demonstrate how easy it is to set up the RESTful API from the database – using Oracle’s dbms_epg package – and how this API can be accessed from Java using JAX-RS (Jersey) and the json-lib utility library.
This topic is one of many to be discussed in Friday’s (4th February) Masterclass on Leveraging the Oracle Database in Java Application (some seats are available – go to http://www.amis.nl/trainingen/oracle-database-in-java-applications for registration and details).
Creating the RESTful PL/SQL API
The Oracle Database can be accessed over HTTP. The most common way in over HTTP is through URLs registered through DADs (Database Access Descriptions) with the dbms_epg package. EPG stands for Embedded PL/SQL Gateway and has been around since Oracle 10g. Prior to EPG, Oracle shipped with the MOD_PLSQL plugin for Apache (Oracle HTTP Server) that also supports handling of HTTP requests by PL/SQL packages.
Before a package can be accessed via HTTP, a DAD has to be created and authorized for the database schema that contains the package. This has to be done by a user with the XDBADMIN role – for example SYS:
begin
DBMS_EPG.create_dad
( dad_name => 'hrmrestapi'
, path => '/hrmapi/*'
);
dbms_epg.authorize_dad('hrmrestapi','SCOTT');
end;
The HTTP port of the database needs to be enabled as well. Port 2100 is ocasionally used, but other port-numers can be used as well.
EXECUTE dbms_xdb.SETHTTPPORT(2100);
Some special configuration is required when the URL should not include the name of package directly, but instead a more intricate URL pattern is to be used – like in this example. In such situations, a handler package for handling special URLs is registered:
BEGIN dbms_epg.set_dad_attribute ( dad_name => 'hrmrestapi' , attr_name => 'path-alias' , attr_value => 'rest' ); dbms_epg.set_dad_attribute ( dad_name => 'hrmrestapi' , attr_name => 'path-alias-procedure' , attr_value => 'scott.hrm_rest_api.handle_request' ); END;
This code specifies that URLs to the hrmrestapi DAD that use the path-alias rest (for example: host:port/hrmrestapi/rest/something) should be handled by the package hrm_rest_api, and more specifically by its procedure handle_request.
The package specification for our RESTful API package is quite simple:
create or replace package hrm_rest_api is procedure handle_request ( p_path in varchar2 ); end hrm_rest_api;
The package body contains the central dispatcher procedure handle_request:
procedure handle_request (p_path in varchar2)
is
l_path_elements t_array := split(p_path,'/');
begin
if lower(l_path_elements(1))='departments'
then
process_departments(sub_array(l_path_elements, 2));
elsif lower(l_path_elements(1))='employees'
then
process_employees(sub_array(l_path_elements, 2), null);
end if;
end;
This procedure is handed the URL path (the part that follows after http://server:port/hrmrestapi/rest). It checks whether the path starts with departments or with employees – the two currently supported paths. It invokes the processing procedures accordingly.
The process_departments procedure is shown here, the other procedures are similar and can be found in the source code associated with this article.
procedure process_departments(l_path_elements in t_array)
is
l_first boolean;
begin
if l_path_elements.count = 0 or l_path_elements(1)='all'
then
htp.p('{"departments": [');
l_first:= true;
for l_rec in (select * from dept ) loop
if not l_first
then
htp.p(',');
else
l_first:= false;
end if;
htp.p('{"department": {');
htp.p('"departmentIdentifier":'||l_rec.deptno);
htp.p(', "departmentName":"'||l_rec.dname||'"');
htp.p(', "departmentLocation":"'||l_rec.loc||'"');
htp.p('}}');
end loop;
htp.p(']}');
else
process_department(p_deptno => l_path_elements(1), p_path_elements => sub_array(l_path_elements,2));
end if;
end;
Note: using the PL/JSON library for PL/SQL, this code can be written even more elegantly and with less room for errors. See: http://database-geek.com/2009/03/25/json-in-and-out-of-oracle-json-data-type/.
The data published by the database package can be accessed from any browser, using a REST-style URL – naming resource type and resource identifiers:
When you check the Page Source in the browser, the JSON structure is revealed. JSON can be parsed directly in JavaScript – in rich web clients – and also in many other languages including PL/SQL itself (using PL/JSON for example) as an alternative to database links and in Java, for example using json-lib as demonstrated below.
Code the Java to access the RESTful HRM service
Using the JAX-RS implementation provides by Jersey and the json-lib library for parsing JSON objects, it is fairly straightforward to retrieve the data from the database via HTTP and the HRM api described above.
This simple Java application makes calls to the RESTful service using a generic method – in go the resource path that should be accessed and out comes the JSON response:
private static String BASE_URI = "http://xp-vm:2100/hrmapi/rest";
public static String restfulCall(String path) {
Client client = Client.create();
client.addFilter(new HTTPBasicAuthFilter("SCOTT", "tiger"));
WebResource webResource = client.resource(BASE_URI);
String response =
webResource.path(path).accept(MediaType.TEXT_PLAIN).get(String.class);
return response;
}
Note how the username and password for Basic Authentication are set on the requests sent to the RESTful API exposed by the Oracle RDBMS.
The main method in the Java Class traverses all Department resources across the RESTful service and drills down into the employees and even their subordinates. It uses helper classes from the json-lib library to help parse the JSON data received from the service. Two converter methods – departmentConstructor and employeeConstructor – turn a JSONObject into a POJO.
public static void main(String[] args) {
String response = Consumer.restfulCall("departments");
JSONObject json = (JSONObject)JSONSerializer.toJSON(response);
JSONArray departments = json.getJSONArray("departments");
for (int i = 0; i < departments.size(); ++i) {
JSONObject department =
(JSONObject)departments.getJSONObject(i).get("department");
Department d = Consumer.departmentConstructor(department);
System.out.println("Department " + d.getIdentifier() +
" is called " + d.getName() +
" and located in " + d.getLocation());
json = (JSONObject)JSONSerializer.toJSON(Consumer.restfulCall
("departments/" + d.getIdentifier() + "/employees"));
JSONArray employees = json.getJSONArray("employees");
for (int j = 0; j < employees.size(); ++j) {
JSONObject employee =
(JSONObject)employees.getJSONObject(j).get("employee");
Employee e = Consumer.employeeConstructor(employee);
System.out.println(" * Employee " + e.getIdentifier() +
" is called " + e.getName() +
" and works as " + e.getJob());
json =
(JSONObject)JSONSerializer.toJSON(Consumer.restfulCall("departments/" +
d.getIdentifier() +
"/employees/" +
e.getIdentifier() +
"/subordinates"));
JSONArray subordinates = json.getJSONArray("subordinates");
for (int k = 0; k < subordinates.size(); ++k) {
JSONObject subordinate =
(JSONObject)subordinates.getJSONObject(k).get("employee");
Employee s = Consumer.employeeConstructor(subordinate);
System.out.println(" - Staff member " +
s.getIdentifier() + " is called " +
s.getName() + " and works as " +
s.getJob());
}
}
}
}
One of the converter methods:
public static Department departmentConstructor(JSONObject json) {
Department d = new Department();
d.setIdentifier(json.getString("departmentIdentifier"));
d.setName(json.getString("departmentName"));
d.setLocation(json.getString("departmentLocation"));
return d;
}
The outcome of the calls to the RESTful service is used to produce the following output:
The following URLs have been accessed to produce this output:
- http://server:2100/hrmapi/rest/departments
- http://server:2100/hrmapi/rest/departments/#/employees
- http://server:2100/hrmapi/rest/departments/#/employees/#/subordinates
other supported URLs are:
- http://server:2100/hrmapi/rest/departments/#
- http://server:2100/hrmapi/rest/employees
- http://server:2100/hrmapi/rest/employees/#
- http://server:2100/hrmapi/rest/departments/#/employees/#
In these URLs, the # represents the identifier of one of the resources.
It is not exceedingly efficient – though very REST-stylish – to retrieve the data for each resources individually. When you need to retrieve more complex data structures from the database in your Java application, JSON can easily be used to create fairly complex, deeply nested data graphs that are returned in a single call. It’s bit like XML – but much more compact (and error prone).
Resources
The source code for this article. In order to compile and run the Java project, you have to add the jersey-bundle-1.5.jar and the Apache Commons Language library (for example commons-lang-2.6.jar) as libraries. RESTfulJavaDBInteraction_EncapsulationOfTheDataModel.zip
http://ora-00001.blogspot.com/2009/07/creating-rest-web-service-with-plsql.html
http://www.oracle-base.com/articles/10g/dbms_epg_10gR2.php
http://answers.oreilly.com/topic/257-how-to-parse-json-in-java/
http://blogs.sun.com/naresh/entry/jersey_client_api_in_action
I am not sure how this approach avoid issues with firewall and other network complications. It does not sound good, security and performance wise, to expose a database directly via an internet protocol like http?