jackrabbit-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Apache Wiki <wikidi...@apache.org>
Subject [Jackrabbit Wiki] Update of "QueryUsingJdbc" by ThomasMueller
Date Thu, 17 Jul 2008 21:53:14 GMT
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

The comment on the change is:
insert the data into a table to support large result sets

------------------------------------------------------------------------------
  
  {{{
  import org.apache.jackrabbit.core.TransientRepository;
- import org.h2.tools.SimpleResultSet;
  
  import java.sql.*;
  import javax.jcr.*;
@@ -22, +21 @@

   * Crystal Reports.
   */
  public class TpsReport {
-     
+ 
      /**
       * This method is called when executing this sample application from the
       * command line.
@@ -68, +67 @@

       * for any kind of report.
       */
      void initDatabase() throws Exception {
+         Connection conn = openConnection();
-         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\"");
@@ -82, +80 @@

       * reporting tool such as Crystal Reports.
       */
      void runQuery() throws Exception {
+         Connection conn = openConnection();
-         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')");
@@ -91, +88 @@

              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");
      }
      
      /**
@@ -108, +115 @@

       *            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)

+     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 {
-         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;
+         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();
          }
-         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;
      }
+ 
  }
  }}}
- 
- === Large Result Sets ===
- This example application constructs the result set in memory. To support large result sets,
the result set needs to be generated 'on the fly'. Another option is to first insert the result
into a temporary table, and then query this temporary table.
  
  === Using the ODBC API ===
  H2 supports using the PostgreSQL ODBC driver by acting like a PostgreSQL server.

Mime
View raw message