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:
You can't integrate this data with your WCM (for instance CQ5) authoring system
You need to have some datas on your JCR repository that point to data into the relational database
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:
/content/mynamespace/<tablename>/<identifier>
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:
/content/mynamespace/<tablename>/
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:
/toscana/firenze/fiesole
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:
/toscana/firenze
/toscana
and even a list of province:
/toscana/
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:
jdbc:h2:~/sling-test
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:
DROP TABLE IF EXISTS PRODUCT;
CREATE TABLE PRODUCT(ID INT PRIMARY KEY,
DESC VARCHAR(255),
PRICE INT,
IMG VARCHAR(255)
);
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');
SELECT * FROM PRODUCT ORDER BY ID;
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:
<groupId>net.lucamasini</groupId>
<artifactId>sling-db-resourceprovider</artifactId>
<name>Database ResourceProvider Sample</name>
<version>0.0.1-SNAPSHOT</version>
<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:
<plugin>
<groupId>org.apache.sling</groupId>
<artifactId>maven-sling-plugin</artifactId>
<version>2.0.4-incubator</version>
<executions>
<execution>
<id>install-bundle</id>
<goals>
<goal>install</goal>
</goals>
</execution>
</executions>
<configuration>
<slingUrl>http://localhost:4502/system/console</slingUrl>
<user>admin</user>
<password>admin</password>
</configuration>
</plugin>
<plugin>
<groupId>org.apache.felix</groupId>
<artifactId>maven-scr-plugin</artifactId>
<configuration>
<specVersion>1.1</specVersion>
</configuration>
<executions>
<execution>
<id>generate-scr-scrdescriptor</id>
<goals>
<goal>scr</goal>
</goals>
</execution>
</executions>
</plugin>
<plugin>
<groupId>org.apache.felix</groupId>
<artifactId>maven-bundle-plugin</artifactId>
<extensions>true</extensions>
<inherited>true</inherited>
<configuration>
<instructions>
<Bundle-DocURL>
http://www.lucamasini.net
</Bundle-DocURL>
<Private-Package>
net.lucamasini.dbprovider
</Private-Package>
<Import-Package>
*
</Import-Package>
</instructions>
</configuration>
<executions>
<execution>
<id>generate-bundle-description</id>
<goals>
<goal>bundle</goal>
</goals>
</execution>
</executions>
</plugin>
Here we configure three plugins:
The sling-plugin to install the bundle into the OSGi container (adjust slingUrl and user/password for the running environment)
The Felix SCR plugin to generate OSGI component's descriptors, based on our annotated ResourceProvider
Finally the Felix Bundle plugin that generate the bundle's descriptors
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)
label="DBResourceProvider",
description="Sample DB Resource Provider",
getConfigurationFactory=true)
@Service // (2)
@Properties({ // (3)
@Property(name="service.description", value="Sample DB Resource Provider"),
@Property(name="service.vendor", value="lucamasini.net"),
@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:
This class is declared as an OSGi Component inside our Bundle
Also we declares this component as a ResourceProvider Service implementor
Finally some runtime-properties are passed to the component at "activation" time
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());
log.info("providerRoot: "+providerRoot);
log.info("providerRootPrefix: "+providerRootPrefix);
log.info("resourceType: "+resourceType);
log.info("H2 connection pool: "+ds);
}
protected void deactivate() throws SQLException {
this.ds.dispose();
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)
log.info("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));
}
resourceMetaData.setResolutionPath(path);
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:
Here we try to understand if the caller is asking for the table listing (or the more appropriate version with a trailing slash) and in case we return a SynteticResource wrapping a virtual folder
If the caller is instead asking directly for the row (the table's path followed by the row's PK) then we query the Database for that row and we popoluate the ResourceMetaData with columns values. Also notice that now the type is the path for the Sling renderer.
Finally, if none of the above is true, we return null, delegating the task of finding the resource to the next ResourceProvider.
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.*,
org.apache.sling.api.resource.Resource"
%>
<%@ taglib prefix="sling" uri="http://sling.apache.org/taglibs/sling/1.0" %>
<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:
Use a BundleResourceProvider for our script resources
Make this OSGi Component generic, so that we can use it in a schema for every table
Implement Resource object instead of using SynteticResource and implementing the Sling Adatable interface for a more appropriate use in rendering pages
Try to integrate into CQ Components
Use of Sling selectors for max results and pagination of results
References
My main source of documentation was the Sling source code, but I also get a lot interesting informations and concepts from those references:
http://dev.day.com/content/ddc/blog/2008/07/slinglegacydata.html