Accessing Relational Data as SLING RESTful URLs

There are scenarios where is not convenient to import data into your JCR repository and also is not a good approach to code an OSGi bundle that expose services to grab datas from the relational database, because this mean that, more or less:

Suppose instead that you want to expose your relational data (readonly) as any other JCR Resource.

In this case you can code a Sling Resouce Provider that can allow you to access legacy data in the same way we access JCR Repository resources.

Modelling the REST URL

Before we start we must decide on how to "model" such resources. This highly depends on what we want to obtain. The first, easiest (only at first glance) approach is to code in a way for which every row has a single url, may be composed this way:


very good to access single data, and very easy to code too. This way we will make a query on <tablename>, selecting all columns for the row with <identifier> primary key. Instead omitting the <identifier> from the URL:


we can obtain all rows in <tablename>, so that we can click on a row and get details.

But what about relationship between tables ??

It's not uncommon to have relationship between tables in realational database, in this case the above mapping is not so suitable, because to navigate a hit for every realation is necessary. If this is the most common case we can use another approach, we can navigate from the many side and reach the data we are interested on.

Now I try to explain better with a very simple example, italian organization of region, province and town:

In this case we can navigate very easy and reach resources this way:


and have properties that reflect the row that contains "fiesole" in the TOWN table. It's not so hard to think about getting a region or a province:



and even a list of province:


Getting started, populating and installing a database into the OSGi Container

For this example we will use an embedded H2 database, a very good full Java database that has a quite easy to use administration console (browser based). To use it, launch the jar into the bin directory, after started it'll launch a browser pointing to the admin console, where we can insert data about our database:

to create a new file for us we can simply insert a new database file name:


with that unix-like notation we'll create a file C:\Users\ictlm1.MIL\sling-test.h2.db if running from a Windows 7 operating system.

Once connected we can create and insert the data for the test we are coding:







INSERT INTO PRODUCT VALUES(1, 'Champagne Bottle', 100, 'http://localhost:4502/content/dam/geometrixx/nature/alley.jpg');

INSERT INTO PRODUCT VALUES(2, 'Flutes Glasses', 18, 'http://localhost:4502/content/dam/geometrixx/nature/barn%20at%20the%20lake.jpg');

INSERT INTO PRODUCT VALUES(3, 'Squared Pot', 27, 'http://localhost:4502/content/dam/geometrixx/nature/corn%20field.jpg');

INSERT INTO PRODUCT VALUES(4, 'Cabernet Glasses', 10, 'http://localhost:4502/content/dam/geometrixx/nature/desert.jpg');

INSERT INTO PRODUCT VALUES(5, 'Selected wine bottles', 140, 'http://localhost:4502/content/dam/geometrixx/nature/waves.jpg');

INSERT INTO PRODUCT VALUES(6, 'Matthew Boulton II', 100, 'http://localhost:4502/content/dam/geometrixx/nature/winter%20river.jpg');


as a result here we should see the list of the inserted row exactly into the web output. Now it's good to exit using the shutdown option, disconnect the current session (the icon in the left-top corner) and from the Preferences page click the "Shutdown" button. This is necessary because we will go to install it into the Sling server as an OSGi container.

Database as an OSGi service

Go to the Felix System Console, Bundles tab (http://localhost:4502/system/console/bundles) and here click the "Install or Updates..." button (the one with ellipsis, on the right), select the h2 jar (the same we started above), the "Start Bundle" checkbox and finally "Install or Updates". After a while (about 10 seconds on my system) you should see the row "H2 Database Engine" in Active status.

Create the Maven POM

The Bundle will be created using Maven, so we need to create a simple project, with this coordinates:



<name>Database ResourceProvider Sample</name>


<description>This is a sample implementation of a Sling ResourceProvider that load data from a database table</description>

Then we need to setup the plugins needed to build the Bundle:





























































Here we configure three plugins:

Finally we start coding !!!!! 

Our bundle must contain a service implementation of ResourceProvider interface with SCR annotation that the maven-scr-plugin parses to create OSGi Manifest and components configuration. This is the declaration of the class:

@Component(name="DBResourceProvider",                                           // (1)


            description="Sample DB Resource Provider",


@Service                                                                                                                   // (2)

@Properties({                                                                   // (3)

      @Property(name="service.description", value="Sample DB Resource Provider"),

      @Property(name="service.vendor", value=""),

      @Property(name=ResourceProvider.ROOTS, value="/content/mynamespace/products"),

      @Property(name="jdbc.url", value="jdbc:h2:~/sling-test"),

    @Property(name="jdbc.user", value="sa"),

      @Property(name="jdbc.pass", value=""),

      @Property(name=SlingConstants.PROPERTY_RESOURCE_TYPE, value="/apps/dbprovider/dbprovider.jsp")


public class DBResourceProvider implements ResourceProvider {





Here we can see that:

Configuring and connecting to the running DB during the Component activation

In the Component activation code we save those runtime properties inside some instance variables, we do some setup code and finally we connect to the running database instance:

    protected void activate(BundleContext bundleContext, Map<?, ?> props) throws SQLException {


        providerRoot = props.get(ROOTS).toString();

        resourceType = props.get(PROPERTY_RESOURCE_TYPE).toString();


        this.providerRootPrefix = providerRoot.concat("/");


        this.ds = JdbcConnectionPool.create(props.get("jdbc.url").toString(), props.get("jdbc.user").toString(), props.get("jdbc.pass").toString());

 "providerRoot: "+providerRoot);"providerRootPrefix: "+providerRootPrefix);"resourceType: "+resourceType);"H2 connection pool: "+ds);



    protected void deactivate() throws SQLException {



        this.ds = null;

        this.providerRoot = null;

        this.providerRootPrefix = null;

        this.resourceType = null;


also notice the JDBC Pool cleanup code during the deactivation. The instance variable cleanup is only esthetical in my knowledge, has no runtime implication.

Everything is a Resource, also my DB Rows !!!

Now we have to implement the three methods inside the ResourceProvider interface. One is simply delegated:

public Resource getResource(ResourceResolver paramResourceResolver,

                  HttpServletRequest paramHttpServletRequest, String paramString) {

            return getResource(paramResourceResolver, paramString);


The others must be really implemented. The first implemented method is the getResource, who tries to understand the request URL path, accepting or rejecting and then returning appropriated resources:

public Resource getResource(final ResourceResolver resourceResolver,

                  final String path) {


    if( providerRoot.equals(path) || providerRootPrefix.equals(path) ) {                                            // (1)"path "+path+" matches this provider root folder: "+providerRoot);


        return new SyntheticResource(resourceResolver, path, "nt:folder");

    } else if ( path.startsWith(providerRootPrefix) && isNumber(path.substring(providerRootPrefix.length()))) {     // (2)


        List<Resource> resources1 = runQuery("SELECT * FROM PRODUCT WHERE ID = ?", new RowMapper<Resource>() {


            public Resource mapRow(ResultSet rs) throws SQLException {


                ResultSetMetaData rsmd = rs.getMetaData();

                ResourceMetadata resourceMetaData = new ResourceMetadata();


                for(int i=1;i<=rsmd.getColumnCount();i++) {

                    resourceMetaData.put(rsmd.getColumnName(i), rs.getObject(i));




                Resource resource = new SyntheticResource(resourceResolver, resourceMetaData, resourceType);


                          return resource;


        }, path.substring(providerRootPrefix.length()));


             return resources1.size()==1?resources1.get(0):null;



       return null;                                                                                                   // (3)


The getResource method is divided in three main parts:

The runQuery method is a simple template method that make a query and map the resulting rows using the passed closure.

Be fast !!!!!

This kind of ResourceProvider is called for every URL, so the implemented getResource must be really fast in returning null when the URL is not in his namespace, otherwise all the system will be slowed down.

What about your children ??

The second implemented method is the listChildren, which simply return some fake Resource in case the parent is the table Resource, to give the opportunity to render the table page like a File System

public Iterator<Resource> listChildren(final Resource paramResource) {

    if( providerRoot.equals(paramResource.getPath()) ) {


        List<Resource> resources = runQuery("SELECT ID FROM PRODUCT", new RowMapper<Resource>() {

            public Resource mapRow(ResultSet rs) throws SQLException {

                return new SyntheticResource(paramResource.getResourceResolver(), providerRootPrefix+rs.getInt(1), resourceType);




        return resources.iterator();



    return null;


A simple Sling Script for rendering

To be able to render our data we must implement the declared rendering script, as in the property SLING_RESOURCE_TYPE. We'll do that using a JSP (/apps/dbprovider/dbprovider.jsp): 

<%@ page session="false" %>

<%@ page import="javax.jcr.*,"


<%@ taglib prefix="sling" uri="" %>


<sling:defineObjects />

<title><%= resource.toString() %></title>

Ciao !!!!

<%= resource.getPath() %>

This show us how to use the resource variables declared in <sling:defineObjects />. In a more productive way we can print also the Properties, or use the Adaptable pattern to convert that Resource in something else more appropriate in our use case.

This JSP resource can be inserted using WebDav (remember to enable it in Windows Vista/7), doing a POST with cURL or with the CRXDE Lite if you are using the CQ5 product and not only SLING. Another approach is to let this Resource be part of a BundleResourceProvider (something I will examine in another article).

The Final Result

Now, calling the address http://localhost:4502/content/mynamespace/products/ from the browser we can see this:

clicking on a url show us the page rendered with our JSP Script:

What to do now ??

From here we can do a lot of things:


My main source of documentation was the Sling source code, but I also get a lot interesting informations and concepts from those references: