Generating REST from the Oracle HR Schema

More and more web applications are being written with HTML, CSS, and (especially) Javascript.  There are a good many frameworks out there – like Angular2 (4?) and EmberJS – for doing this.  The goal of these frameworks is to have the client, the user's web browsers, do most of the work, rather than the server as in my JEE/JSF-based Oracle Application Development Framework (ADF) applications.  They also want to give the user a more responsive, and eye pleasing interface.  But ultimately, they do have to talk to the database, and update the data that is shared with other users.  For this, they tend to use web services, particularly Representational State Transfer (REST) services, which take advantage of the capabilities of the Hypertext Transfer Protocol (HTTP) beyond what is usually used to serve pages over the World Wide Web.

ADF Business Components as REST

I've been playing with various methods to expose my database as REST services, and I've been using the HR schema that comes as a sample schema with every Oracle database as my test platform.  Since I'm primarily an ADF developer right now, the first thing I tried was ADF 12.2.2's greatly improved ability to expose ADF Business Components (ADF BC) developed from the database as REST services.  This works quite well, and is very nice for its ability to use your customizations and View Criteria.  But I'm especially interested in using ADF Essentials and deploying to open source application servers like Glassfish.  And as far as I can determine, REST Web Services from ADF BC is not part of ADF Essentials, so you can only deploy these to Oracle WebLogic Server.

Oracle REST Data Services

Another alternative is to use Oracle REST Data Services (ORDS), which can be deployed to most JEE application servers, including Glassfish and Apache Tomcat.  ORDS even contains an embedded JETTY application server, so it can be deployed standalone for light use.  While ORDS is not open source, it is free to use as part of your Oracle RDBMS license.  ORDS works fine and you can use SQL Developer to expose your tables through REST on ORDS.  I'm not totally comfortable with exposing tables directly, so I would suggest using a three-schema architecture and exposing updatable views based on your tables, rather than the tables themselves.  I did have a little trouble with ORDS configuration, however, and I need to work on this some more.  I will probably write another post about my experiences with ORDS.

JAX-RS – Java REST Services from JPA

Java web developers tend to use Java Persistence Architecture (JPA) and one of its implementations like Hibernate or Eclipselink to do Object-Relational Mapping (ORM) from their relational database to Java objects.  Even ADF developers who write their applications in Eclipse instead of JDeveloper with ADF Essentials will usually use JPA instead of ADF BC.  And there is a standard API called JAX-RS for exposing JPA entity classes as REST services.  The nice thing is that both the Eclipse IDE and the NetBeans IDE have plug-ins that can generate JPA entity classes from tables in the database, and can even generate the code for exposing these as REST services.  I've tried both and while I prefer NetBeans, Eclipse works just fine.  Here is a link to a NetBeans tutorial I used: https://netbeans.org/kb/docs/websvc/rest.html.

I generated RESTful services from the DEPARTMENTS, EMPLOYEES and JOBS tables in the HR schema and deployed to Glassfish.  The JOBS service worked fine, and the count services giving the number of rows in the tables worked for all three tables.  But the other services didn't work for DEPARTMENTS and EMPLOYEES.  It took me a while to figure it out, because it gave a 500 error without putting error messages in the Glassfish logs.  I finally found some information on StackExchange that told me that I needed a class implementing ExceptionMapper<Exception>.  Here is my class:

/*

 * To change this license header, choose License Headers in Project Properties.

 * To change this template file, choose Tools | Templates

 * and open the template in the editor.

 */

package com.smdi.johnf.hr.service;

import javax.ws.rs.core.Response;

import javax.ws.rs.ext.Provider;

/**

 *

 * @author JohnF

 */

@Provider

public class ExceptionMapper implements javax.ws.rs.ext.ExceptionMapper<Exception> {

    @Override

    public Response toResponse(Exception exception) {

      exception.printStackTrace();

      return Response.status(500).build();

    }

}

Ahhh – with this class to tell it to print a stack trace upon an error, I could finally see what was wrong.  The problem is that there are circular references in DEPARTMENTS and EMPLOYEES.  DEPARTMENTS, for instance has a column named MANAGER_ID, which references the EMPLOYEE_ID of an EMPLOYEES row for the department's manager.  This in turn has a DEPARTMENT_ID for the DEPARTMENT for which the employee works and its own MANAGER_ID for the employee's manager.  When generating JSON or XML for the service, the API doesn't have a way to stop.  One answer for DEPARTMENTS is to add the @XmlTransient annotation to the List of Employees in the department – which tells the API not to continue to dive deeper into the hierarchy of related entities.

Hope this exploration of creating REST services for your database is useful.