Metacat

/**

 *      Name: DBQuery.java

 *   Purpose: A Class that searches a relational DB for elements and 

 *            attributes that have free text matches a query string,

 *            or structured query matches to a path specified node in the 

 *            XML hierarchy.  It returns a result set consisting of the 

 *            document ID for each document that satisfies the query

 * Copyright: 2000 Regents of the University of California and the

 *            National Center for Ecological Analysis and Synthesis

 *   Authors: Matt Jones

 *

 *   Version: '$Id: DBQuery.java 184 2000-06-21 02:57:19Z jones $'

 */

package edu.ucsb.nceas.metacat;

import java.io.*;

import java.net.URL;

import java.net.MalformedURLException;

import java.sql.*;

import java.util.Stack;

import java.util.Hashtable;

import java.util.Enumeration;

/** 

 * A Class that searches a relational DB for elements and 

 * attributes that have free text matches a query string,

 * or structured query matches to a path specified node in the 

 * XML hierarchy.  It returns a result set consisting of the 

 * document ID for each document that satisfies the query

 */

public class DBQuery {

  private Connection	conn = null;

  private String	parserName = null;

  /**

   * the main routine used to test the DBQuery utility.

   * <p>

   * Usage: java DBQuery <xmlfile>

   *

   * @param xmlfile the filename of the xml file containing the query

   */

  static public void main(String[] args) {

     if (args.length < 1)

     {

        System.err.println("Wrong number of arguments!!!");

        System.err.println("USAGE: java DBQuery <xmlfile>");

        return;

     } else {

        try {

          String xmlfile  = args[0];

          // Open a connection to the database

          MetaCatUtil   util = new MetaCatUtil();

          Connection dbconn = util.openDBConnection();

          // Execute the query

          DBQuery queryobj = new DBQuery(dbconn, util.getOption("saxparser"));

          FileReader xml = new FileReader(new File(xmlfile));

          Hashtable nodelist = null;

          nodelist = queryobj.findDocuments(xml);

          // Print the reulting document listing

          StringBuffer result = new StringBuffer();

          String document = null;

          String docid = null;

          result.append("<?xml version=\"1.0\"?>\n");

          result.append("<resultset>\n");

          result.append("  <query>" + xmlfile + "</query>\n");

          Enumeration doclist = nodelist.keys(); 

          while (doclist.hasMoreElements()) {

            docid = (String)doclist.nextElement();

            document = (String)nodelist.get(docid);

            result.append("  <document>\n    " + document + 

                          "\n  </document>\n");

          }

          result.append("</resultset>\n");

          System.out.println(result);

        } catch (Exception e) {

          System.err.println("EXCEPTION HANDLING REQUIRED");

          System.err.println(e.getMessage());

          e.printStackTrace(System.err);

        }

     }

  }

  /**

   * construct an instance of the DBQuery class 

   *

   * <p>Generally, one would call the findDocuments() routine after creating 

   * an instance to specify the search query</p>

   *

   * @param conn the JDBC connection that we use for the query

   * @param parserName the fully qualified name of a Java class implementing

   *                   the org.xml.sax.Parser interface

   */

  public DBQuery( Connection conn, String parserName ) 

                  throws IOException, 

                         SQLException, 

                         ClassNotFoundException {

    this.conn = conn;

    this.parserName = parserName;

  }

  /** 

   * routine to search the elements and attributes looking to match query

   *

   * @param xmlquery the xml serialization of the query (@see pathquery.dtd)

   */

  public Hashtable findDocuments(Reader xmlquery) {

      Hashtable	 docListResult = new Hashtable();

      PreparedStatement pstmt;

      String docid = null;

      String docname = null;

      String doctype = null;

      String doctitle = null;

      StringBuffer document = null; 

      try {

        // Get the XML query and covert it into a SQL statment

        QuerySpecification qspec = new QuerySpecification(xmlquery, 

                                   parserName);

        //System.out.println(qspec.printSQL());

        pstmt = conn.prepareStatement( qspec.printSQL() );

        // Execute the SQL query using the JDBC connection

        pstmt.execute();

        ResultSet rs = pstmt.getResultSet();

        boolean tableHasRows = rs.next();

        while (tableHasRows) {

          docid = rs.getString(1);

          docname = rs.getString(2);

          doctype = rs.getString(3);

          doctitle = rs.getString(4);

          document = new StringBuffer();

          document.append("<docid>").append(docid).append("</docid>");

          if (docname != null) {

            document.append("<docname>" + docname + "</docname>");

          }

          if (doctype != null) {

            document.append("<doctype>" + doctype + "</doctype>");

          }

          if (doctitle != null) {

            document.append("<doctitle>" + doctitle + "</doctitle>");

          }

          // Store the document id and the root node id

          docListResult.put(docid,(String)document.toString());

          // Advance to the next record in the cursor

          tableHasRows = rs.next();

        }

        pstmt.close();

      } catch (SQLException e) {

        System.err.println("Error getting id: " + e.getMessage());

      } catch (IOException ioe) {

        System.err.println("Error printing qspec:");

        System.err.println(ioe.getMessage());

      }

    return docListResult;

  }

  /**

   * format a simple free-text value query as an XML document that conforms

   * to the pathquery.dtd and is appropriate for submission to the DBQuery

   * structured query engine

   *

   * @param value the text string to search for in the xml catalog

   * @param doctype the type of documents to include in the result set -- use

   *        "any" or "ANY" for unfiltered result sets

   */

   public static String createQuery(String value, String doctype) {

     StringBuffer xmlquery = new StringBuffer();

     xmlquery.append("<?xml version=\"1.0\"?>\n");

     xmlquery.append("<!DOCTYPE pathquery PUBLIC ");

     xmlquery.append("\"-//NCEAS//pathquery-1.0//EN\" ");

     xmlquery.append("\"http://24.237.19.164/xmltodb/lib/pathquery.dtd\" >");

     xmlquery.append("<pathquery version=\"1.0\">");

     xmlquery.append("<meta_file_id>Unspecified</meta_file_id>");

     xmlquery.append("<querytitle>Unspecified</querytitle>");

     if (!doctype.equals("any") && !doctype.equals("ANY")) {

       xmlquery.append("<returndoctype>");

       xmlquery.append(doctype).append("</returndoctype>");

     }

     xmlquery.append("<querygroup operator=\"UNION\">");

     xmlquery.append("<queryterm casesensitive=\"false\" ");

     xmlquery.append("searchmode=\"contains\">");

     xmlquery.append("<value>").append(value).append("</value>");

     xmlquery.append("</queryterm>");

     xmlquery.append("</querygroup>");

     xmlquery.append("</pathquery>");

     return (xmlquery.toString());

   }

  /**

   * format a simple free-text value query as an XML document that conforms

   * to the pathquery.dtd and is appropriate for submission to the DBQuery

   * structured query engine

   *

   * @param value the text string to search for in the xml catalog

   */

   public static String createQuery(String value) {

     return createQuery(value, "any");

   }

}