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();
        }
    }

}