[Jackrabbit Wiki] Update of "QueryUsingJdbc" by ThomasMueller

[Apache Wiki <wi...@apache.org>]

Dear Wiki user,

You have subscribed to a wiki page or wiki category on "Jackrabbit Wiki" for change notification.

The following page has been changed by ThomasMueller:
http://wiki.apache.org/jackrabbit/QueryUsingJdbc

New page:
= 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. 

{{{
import org.apache.jackrabbit.core.TransientRepository;
import org.h2.tools.SimpleResultSet;

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 {
        // 
        Class.forName("org.h2.Driver");
        Connection conn = DriverManager.getConnection("jdbc:h2:~/jcr", "sa", "sa");
        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 {
        Class.forName("org.h2.Driver");
        Connection conn = DriverManager.getConnection("jdbc:h2:~/jcr", "sa", "sa");
        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();
    }
    
    /**
     * 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 {
        SimpleResultSet rs = new SimpleResultSet();
        rs.addColumn("NAME", Types.VARCHAR, 0, 0);
        if (conn.getMetaData().getURL().startsWith("jdbc:columnlist:")) {
            // the method is called to get the column list of the result set
            // in this case, it is not required to run the query
            return rs;
        }
        Repository rep = new TransientRepository();
        Session session = rep.login(new SimpleCredentials(
            jcrUser, jcrPassword.toCharArray()));
        QueryManager qm = session.getWorkspace().getQueryManager();
        QueryResult result = qm.createQuery("//test", Query.XPATH).execute();    
        NodeIterator it = result.getNodes();
        while(it.hasNext()) {
            Node n = it.nextNode();
            rs.addRow(new Object[]{n.getProperty("text").getString()});
        }
        session.logout();
        return rs;
    }
}
}}}

=== 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.