Query a JCR Repository Using JDBC

Jackrabbit does not implement the JDBC API, however it is possible to run SQL queries against a JCR repository using a JDBC to JCR bridge. There is currently no generic bridge available, but it is relatively simple to build a custom bridge. Here is a sample application that does just that. This method can be used to generate reports with tools do not support the JCR API yet, but support he JDBC API, such as Crystal Reports.

The example application uses the H2 Database Engine (an open source relational database written in Java).

import org.apache.jackrabbit.core.TransientRepository;

import java.sql.*;
import javax.jcr.*;
import javax.jcr.query.*;

/**
 * This example application shows how to query a JCR repository using the JDBC
 * API. It uses a user defined Java function that is used as a table. This
 * method may be used to query any JCR compliant repository using any
 * application that uses the JDBC API, including a report generator such as
 * Crystal Reports.
 */
public class TpsReport {

    /**
     * This method is called when executing this sample application from the
     * command line.
     * 
     * @param args the command line parameters
     */
    public static void main(String[] args) throws Exception {
        new TpsReport().run();
    }
    
    /**
     * Run the sample application. This will initialize the repository,
     * initialize the database, and then run the SQL query against the database,
     * which in turn returns the result of the JCR query.
     */
    void run() throws Exception {
        initJcrRepository();
        initDatabase();
        runQuery();
    }
    
    /**
     * Initialize the JCR repository. This will create a repository with one
     * node 'test' that has a property 'text'.
     */
    void initJcrRepository() throws Exception {
        Repository rep = new TransientRepository();
        Session session = rep.login(new SimpleCredentials("admin", "admin".toCharArray()));
        Node root = session.getRootNode();
        if (root.hasNode("test")) {
            root.getNode("test").remove();
        }
        Node n = root.addNode("test");
        n.setProperty("text", "Hello");
        session.save();
        session.logout();
    }
    
    /**
     * Initialize the database. This will create a database called 'jcr' that
     * contains a Java function 'TPS_REPORT'. This only needs to be done once,
     * before running the report. The term TPS_REPORT is used as a generic term
     * for any kind of report.
     */
    void initDatabase() throws Exception {
        Connection conn = openConnection();
        Statement stat = conn.createStatement();
        stat.execute("CREATE ALIAS IF NOT EXISTS " + 
            "TPS_REPORT FOR \"" + getClass().getName() + ".getTpsReport\"");
    }
    
    /**
     * Run the query SELECT * FROM TPS_REPORT against the database. This will
     * call the Java function getTpsReport(). The result of
     * the query is printed to system out. The query can also be run using a
     * reporting tool such as Crystal Reports.
     */
    void runQuery() throws Exception {
        Connection conn = openConnection();
        Statement stat = conn.createStatement();
        ResultSet rs = stat.executeQuery(
            "SELECT * FROM TPS_REPORT('admin', 'admin')");
        while(rs.next()) {
            System.out.print(rs.getString(1));
        }
        conn.close();
    }
    
    /**
     * Open a connection to a temporary database.
     * 
     * @return the connection
     */
    private static Connection openConnection() throws Exception {
        Class.forName("org.h2.Driver");
        return DriverManager.getConnection("jdbc:h2:~/jcr", "sa", "sa");
    }
    
    /**
     * This method opens a JCR session and run the XPath query '//test' against
     * the JCR repository. This method will also convert the JCR QueryResult to
     * a SQL ResultSet, which is then returned. Internally, this method is
     * actually called twice: first to get the column list of the result set,
     * and then to get the data.
     * 
     * @param conn
     *            the database connection
     * @param jcrUser
     *            the JCR user name as set when calling the database function
     * @param jcrPassword
     *            the JCR password as set when calling the database function
     * @return the SQL result set
     */
    public static ResultSet getTpsReport(Connection conn, String jcrUser,
            String jcrPassword) throws Exception {
        Statement stat = conn.createStatement();
        stat.execute("DROP TABLE IF EXISTS TEMP");
        stat.execute("CREATE TABLE TEMP(TEXT VARCHAR)");
        if (!conn.getMetaData().getURL().startsWith("jdbc:columnlist:")) {
            // first, the method is called to get the column list of the result
            // (to parse and prepare the query and resolve column names),
            // but here the method is called to get the actual data
            Repository rep = new TransientRepository();
            Session session = rep.login(new SimpleCredentials(jcrUser, jcrPassword
                    .toCharArray()));
            generateReportData(session, conn);
            session.logout();
        }
        return stat.executeQuery("SELECT * FROM TEMP");
    }
    
    /**
     * Read the resport data from the JCR repository and insert it into the
     * temporary table.
     * 
     * @param session the JCR session (source)
     * @param conn the database connection (target)
     */
    private static void generateReportData(Session session, Connection conn) 
            throws Exception {
        QueryManager qm = session.getWorkspace().getQueryManager();
        QueryResult result = qm.createQuery("//test", Query.XPATH).execute();
        NodeIterator it = result.getNodes();
        PreparedStatement prep = conn.prepareStatement(
            "INSERT INTO TEMP VALUES(?)");
        while (it.hasNext()) {
            Node n = it.nextNode();
            prep.setString(1, n.getProperty("text").getString());
            prep.execute();
        }
    }

}

Using the ODBC API

H2 supports using the PostgreSQL ODBC driver by acting like a PostgreSQL server.

Write Access

This example shows read access, but it is also possible to implement write access to a JCR repository, for example using database triggers.

QueryUsingJdbc (last edited 2009-09-20 23:45:19 by localhost)