RESTful Database Access with sqlREST

The sqlREST project enables easier exposure of database data via REST-style web services. The 15-Minute Guide to sqlREST demonstrates how to quickly try out sqlREST simply by copying the sqlREST directory into a Java EE web server's directory and sqlREST-provided database. In this blog posting, I will demonstrate use of sqlREST with GlassFish and an independent database (Oracle HR schema).

sqlREST 1.0 can be downloaded here. The file is currently called sqlrest-1.0.zip and is approximately 1.5 MB in size. For my example, I unzipped the contents of this ZIP file into a directory called C:\sqlrest-1.0. The next screen snapshot shows what the most important directory in this distribution (the WEB-INF/classes directory) looks like.



By the way, the docs directory includes versions of the previously mentioned 15-Minute Guide to sqlREST as well as the sqlREST Users Guide, the sqlREST Developer Guide (just barely started at this point), and the sqlREST Javadoc-based API documentation.

The 15-Minute Guide describes copying sqlREST files into your favorite Java EE web container's web application directory. This is often the easiest way to include a library or framework, but I like to build JARs and include them in my own WAR file's WEB-INF/lib directory. In order to do this, I need the sqlREST-provided .class files assembled into a JAR file. The following Ant build file instructs Ant to do just that.

dustin-build.xml

<?xml version="1.0" encoding="UTF-8"?>
<project name="Building sqlREST Example" default="jar" basedir=".">
   <description>Building sqlREST Example</description>

   <property name="dist.dir" value="dustin-dist" />
   <property name="classes.dir" value="webapps/sqlrest/WEB-INF/classes" />

   <property name="jar.name" value="sqlrest10.jar" />
   <property name="jar.filesonly" value="true" />

   <target name="-init">
      <!-- Don't need to create a classes directory because it was part of
           sqlrest download and included the .class files. -->
      <mkdir dir="${dist.dir}" />
   </target>

   <target name="jar"
           description="Package sqlREST compiled classes into JAR file"
    depends="-init">
      <jar destfile="${dist.dir}/${jar.name}"
           basedir="${classes.dir}"
           filesonly="${jar.filesonly}">
      </jar>
   </target>

   <target name="clean" description="Remove generated artifacts.">
      <!-- Don't clean classes directory because these .class files were
           downloaded rather than built. -->
      <delete dir="${dist.dir}" />
   </target>

</project>

I named this Ant build file dustin-build.xml to make it obvious that it is not part of the sqlREST distribution and is for my own use. To run it with Ant, I simply run the command ant -f dustin-build.xml and a directory called dustin-dist is created and a JAR file named sqlrest10.jar is placed in that newly created directory. The next screen snapshot shows that this JAR was created successfully.



I now have a JAR containing sqlREST classes that can be easily deployed with any WAR.

The sqlREST User Guide does not have much in it currently, but it does have a section called "Using a different database" that explains how one can expose one's own database REST-style with sqlREST. This section demonstrates that it is very simple to use a custom database with sqlREST.

To use a custom database, one edits the sqlrestconf.xml configuration file. In my case, this is located in the WEB-INF directory of the expanded contents of the downloaded sqlrest-1.0.zip file.

The following code snippet shows what this file looks like after I have edited it to use with an instance of the Oracle database.

sqlrestconf.xml

<?xml version="1.0" encoding="UTF-8"?>
<sqlrestconf>
  <database>
    <jdbc-driver-class>oracle.jdbc.pool.OracleDataSource</jdbc-driver-class>
    <database-url>jdbc:oracle:thin:@localhost:1521:orcl</database-url>
    <user>hr</user>
    <password>hr</password>
  </database>
</sqlrestconf>

The configuration file above references the OracleDataSource class. I need to ensure that this class is available to my web server. To do this, I'll add it to the WAR file that will contain the JAR of sqlREST classes along with the sqlREST-provided web.xml file and the just-discussed sqlrestconf.xml configuration file.

The revised Ant build file builds the WAR with these constituent pieces. The new version is shown next.

dustin-build.xml (revised to build WAR)

<?xml version="1.0" encoding="UTF-8"?>
<project name="Building sqlREST Example" default="war" basedir=".">
   <description>Building sqlREST Example</description>

   <property name="dist.dir" value="dustin-dist" />
   <property name="classes.dir" value="webapps/sqlrest/WEB-INF/classes" />

   <property name="jar.name" value="sqlrest10.jar" />
   <property name="jar.filesonly" value="true" />
   <property name="war.name" value="usingSqlRest.war" />

   <target name="-init">
      <!-- Don't need to create a classes directory because it was part of
           sqlrest download and included the .class files. -->
      <mkdir dir="${dist.dir}" />
   </target>

   <target name="jar"
           description="Package sqlREST compiled classes into JAR file"
    depends="-init">
      <jar destfile="${dist.dir}/${jar.name}"
           basedir="${classes.dir}"
           filesonly="${jar.filesonly}">
      </jar>
   </target>

   <target name="war"
           description="Generate a WAR file that includes sqlREST"
    depends="jar">
      <war destfile="${dist.dir}/${war.name}"
           webxml="webapps/sqlrest/WEB-INF/web.xml"
    filesonly="${jar.filesonly}">
         <lib dir="${dist.dir}" includes="${jar.name}" />
  <lib dir="webapps/sqlrest/WEB-INF/lib" includes="log4j-1.2.8.jar" />
  <lib dir="C:\app\Dustin\product\11.1.0\db_1\jdbc\lib"
       includes="ojdbc6.jar" />
  <zipfileset dir="webapps/sqlrest" includes="WEB-INF/sqlrestconf.xml" />
      </war>
   </target>

   <target name="clean" description="Remove generated artifacts.">
      <!-- Don't clean classes directory because these .class files were
           downloaded rather than built. -->
      <delete dir="${dist.dir}" />
   </target>

</project>

This build file leads to the successful assembly of the WAR file as shown in the next screen snapshot.



I don't show it here, but with this EAR built I can deploy it to my Java EE web container of choice. In my case, I have deployed it to GlassFish. By default, GlassFish names the web context based on the name of the deployed WAR, so it is "usingSqlRest" in this case. This means that "usingSqlRest" will be part of the URL used to access the data via sqlREST.

If I neglect to place the Oracle driver class in the WAR's classpath, the browser returns an empty XML element <resource> as shown in the next screen snapshot.



Once the Oracle JDBC driver class is made available on the classpath, I can see the representations of various resources by including the appropriate table name in the URL. The next two screen snapshots show this for the EMPLOYEES and COUNTRIES tables in the HR schema.






At this point, with only editing of the sqlrestconf.xml file and placing of appropriate sqlREST and database JDBC driver classes on the web server, we are able to get REST-style representations of our underlying database. The sqlREST product obviously uses the database's metadata to achieve this functionality.

Unfortunately, I have run into some issues using sqlREST with the Oracle database HR schema. First, I was not able to access the sqlREST root URL (http://localhost:8080/usingSqlRest/ in this case) to see available resources. The reason for this is that the Oracle metadata includes names with dollar sign in them and that causes havoc for the XML parser. This is shown in the next screen snapshot.



A second problem I ran into occurred when trying to GET a particular resource. This is shown in the next screen snapshot which indicates my attempt to access a specific employee.



In the appropriate GlassFish log, the top part of the associated stack trace looks like this:

java.lang.NullPointerException
 at de.oio.sqlrest.xml.TableDetailResponseBuilder.generate(TableDetailResponseBuilder.java:88)
 at de.oio.sqlrest.Dispatcher.dispatchGET(Dispatcher.java:76)
 at de.oio.sqlrest.MainServlet.doGet(MainServlet.java:73)
 at javax.servlet.http.HttpServlet.service(HttpServlet.java:718)
 at javax.servlet.http.HttpServlet.service(HttpServlet.java:831)
 at org.apache.catalina.core.ApplicationFilterChain.servletService

I really like the sqlREST concept and I believe that it has a lot of potential. The 15-Minute Guide shows its used with the bundled Hypersonic SQL database and it is likely that it works to differing degrees with various other relational databases with appropriate JDBC drivers. Because sqlREST is open source (BSD license), one could edit the code to address issues specific to an alternative database.

Dustin