Metacat

/**

 *        Name: DBSimpleQuery.java

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

 *              attributes that have free text matches to the query string.  

 *              It returns a result set consisting of the root nodeid 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: DBSimpleQuery.java 154 2000-06-14 04:43:21Z 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 to the query string.  It returns a result set 

 * consisting of the root nodeid for each document that satisfies the query

 */

public class DBSimpleQuery {

  static  String 	defaultDB = "jdbc:oracle:thin:@localhost:1521:test";

  private Connection	conn = null;

  /**

   * the main routine used to test the DBSimpleQuery utility.

   *

   * Usage: java DBSimpleQuery <query> <user> <password> [dbstring]

   *

   * @param query the text to search for in the element and attribute content

   * @param user the username to use for the database connection

   * @param password the password to use for the database connection

   * @param dbstring the connection info to use for the database connection

   */

  static public void main(String[] args) {

     if (args.length < 4)

     {

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

        System.err.println("USAGE: java DBSimpleQuery " +

                           "<query> <doctype> <user> <password> [dbstring]");

        return;

     } else {

        try {

          String query    = args[0];

          String doctype  = args[1];

          String user     = args[2];

          String password = args[3];

          String dbstring = null;

          if (args.length <= 4) {

            dbstring = defaultDB;

          } else {

            dbstring = args[4];

          }

          // Open a connection to the database

          Connection dbconn = MetaCatUtil.openDBConnection( 

                              "oracle.jdbc.driver.OracleDriver",

                              dbstring, user, password);

          // Execute the simple query

          DBSimpleQuery rd = new DBSimpleQuery(dbconn);

          Hashtable nodelist = null;

          if (doctype.equals("any") || doctype.equals("ANY")) {

            nodelist = rd.findDocuments(query);

          } else {

            nodelist = rd.findDocuments(query, doctype);

          }

          // Print the reulting root nodes

          StringBuffer result = new StringBuffer();

          String document = null;

          Long docid = null;

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

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

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

          Enumeration doclist = nodelist.keys(); 

          while (doclist.hasMoreElements()) {

            docid = (Long)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 DBSimpleQuery 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

   */

  public DBSimpleQuery( Connection conn ) 

                  throws IOException, 

                         SQLException, 

                         ClassNotFoundException

  {

    this.conn = conn;

  }

  /** 

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

   *

   * @param query the text to search for

   */

  public Hashtable findDocuments(String query) {

    return this.findDocuments(query, null);

  }

  /** 

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

   *

   * @param query the text to search for

   * @param requestedDoctype the type of documents to return from the query

   */

  public Hashtable findDocuments(String query, String requestedDoctype) {

      Hashtable	 docListResult = new Hashtable();

      PreparedStatement pstmt;

      // Now look up the document id

      long docid = 0;

      String docname = null;

      String doctype = null;

      String doctitle = null;

      StringBuffer document = null; 

      try {

        if (requestedDoctype == null || 

            requestedDoctype.equals("any") || 

            requestedDoctype.equals("ANY")) {

          pstmt = conn.prepareStatement(

                "SELECT docid,docname,doctype,doctitle " +

                "FROM xml_documents " +

                "WHERE docid IN " +

                "(SELECT docid " +

                "FROM xml_nodes WHERE nodedata LIKE ? )");

                // Bind the values to the query

                pstmt.setString(1, query);

        } else {

          pstmt = conn.prepareStatement(

                "SELECT docid,docname,doctype,doctitle " +

                "FROM xml_documents " +

                "WHERE docid IN " +

                "(SELECT docid " +

                "FROM xml_nodes WHERE nodedata LIKE ? ) " +

                "AND doctype = ?");

                // Bind the values to the query

                pstmt.setString(1, query);

                pstmt.setString(2, requestedDoctype);

        }

        pstmt.execute();

        ResultSet rs = pstmt.getResultSet();

        boolean tableHasRows = rs.next();

        while (tableHasRows) {

          docid = rs.getLong(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(new Long(docid),(String)document.toString());

          // Advance to the next record in the cursor

          tableHasRows = rs.next();

        }

        pstmt.close();

      } catch (SQLException e) {

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

      }

    return docListResult;

  }

}