Metacat

/**

 *  '$RCSfile$'

 *    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

 *

 *   '$Author: berkley $'

 *     '$Date: 2007-03-22 09:59:21 -0700 (Thu, 22 Mar 2007) $'

 * '$Revision: 3211 $'

 *

 * This program is free software; you can redistribute it and/or modify

 * it under the terms of the GNU General Public License as published by

 * the Free Software Foundation; either version 2 of the License, or

 * (at your option) any later version.

 *

 * This program is distributed in the hope that it will be useful,

 * but WITHOUT ANY WARRANTY; without even the implied warranty of

 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the

 * GNU General Public License for more details.

 *

 * You should have received a copy of the GNU General Public License

 * along with this program; if not, write to the Free Software

 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA

 */

package edu.ucsb.nceas.metacat;

import java.io.BufferedWriter;

import java.io.File;

import java.io.FileInputStream;

import java.io.FileReader;

import java.io.FileWriter;

import java.io.IOException;

import java.io.InputStream;

import java.io.PrintWriter;

import java.io.StringReader;

import java.io.StringWriter;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.Enumeration;

import java.util.Hashtable;

import java.util.StringTokenizer;

import java.util.Vector;

import java.util.zip.ZipEntry;

import java.util.zip.ZipOutputStream;

import javax.servlet.ServletOutputStream;

import javax.servlet.http.HttpServletResponse;

import javax.servlet.http.HttpSession;

import org.apache.log4j.Logger;

import edu.ucsb.nceas.morpho.datapackage.Triple;

import edu.ucsb.nceas.morpho.datapackage.TripleCollection;

/**

 * 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

{

    static final int ALL = 1;

    static final int WRITE = 2;

    static final int READ = 4;

    //private Connection conn = null;

    private String parserName = null;

    private MetaCatUtil util = new MetaCatUtil();

    private Logger logMetacat = Logger.getLogger(DBQuery.class);

    /** true if the metacat spatial option is installed **/

    private final boolean METACAT_SPATIAL = true;

    /** useful if you just want to grab a list of docids **/

    Vector docidOverride = new Vector();

    /**

     * 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 [-t] [-index] <xmlfile>");

            return;

        } else {

            try {

                int i = 0;

                boolean showRuntime = false;

                boolean useXMLIndex = false;

                if (args[i].equals("-t")) {

                    showRuntime = true;

                    i++;

                }

                if (args[i].equals("-index")) {

                    useXMLIndex = true;

                    i++;

                }

                String xmlfile = args[i];

                // Time the request if asked for

                double startTime = System.currentTimeMillis();

                // Open a connection to the database

                MetaCatUtil util = new MetaCatUtil();

                //Connection dbconn = util.openDBConnection();

                double connTime = System.currentTimeMillis();

                // Execute the query

                DBQuery queryobj = new DBQuery();

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

                Hashtable nodelist = null;

                //nodelist = queryobj.findDocuments(xml, null, null, useXMLIndex);

                // 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");

                if (!showRuntime) {

                    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");

                }

                // Time the request if asked for

                double stopTime = System.currentTimeMillis();

                double dbOpenTime = (connTime - startTime) / 1000;

                double readTime = (stopTime - connTime) / 1000;

                double executionTime = (stopTime - startTime) / 1000;

                if (showRuntime) {

                    System.out.print("  " + executionTime);

                    System.out.print("  " + dbOpenTime);

                    System.out.print("  " + readTime);

                    System.out.print("  " + nodelist.size());

                    System.out.println();

                }

                //System.out.println(result);

                //write into a file "result.txt"

                if (!showRuntime) {

                    File f = new File("./result.txt");

                    FileWriter fw = new FileWriter(f);

                    BufferedWriter out = new BufferedWriter(fw);

                    out.write(result.toString());

                    out.flush();

                    out.close();

                    fw.close();

                }

            } catch (Exception e) {

                System.err.println("Error in DBQuery.main");

                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 parserName the fully qualified name of a Java class implementing

     *            the org.xml.sax.XMLReader interface

     */

    public DBQuery()

    {

        String parserName = MetaCatUtil.getOption("saxparser");

        this.parserName = parserName;

    }

    /**

     * 

     * Construct an instance of DBQuery Class

     * BUT accept a docid Vector that will supersede

     * the query.printSQL() method

     *

     * If a docid Vector is passed in,

     * the docids will be used to create a simple IN query 

     * without the multiple subselects of the printSQL() method

     *

     * Using this constructor, we just check for 

     * a docidOverride Vector in the findResultDoclist() method

     *

     * @param docids List of docids to display in the resultset

     */

    public DBQuery(Vector docids)

    {

        this.docidOverride = docids;

        String parserName = MetaCatUtil.getOption("saxparser");

        this.parserName = parserName;

    }

  /**

   * Method put the search result set into out printerwriter

   * @param resoponse the return response

   * @param out the output printer

   * @param params the paratermer hashtable

   * @param user the user name (it maybe different to the one in param)

   * @param groups the group array

   * @param sessionid  the sessionid

   */

  public void findDocuments(HttpServletResponse response,

                                       PrintWriter out, Hashtable params,

                                       String user, String[] groups,

                                       String sessionid)

  {

    boolean useXMLIndex = (new Boolean(MetaCatUtil.getOption("usexmlindex")))

               .booleanValue();

    findDocuments(response, out, params, user, groups, sessionid, useXMLIndex);

  }

    /**

     * Method put the search result set into out printerwriter

     * @param resoponse the return response

     * @param out the output printer

     * @param params the paratermer hashtable

     * @param user the user name (it maybe different to the one in param)

     * @param groups the group array

     * @param sessionid  the sessionid

     */

    public void findDocuments(HttpServletResponse response,

                                         PrintWriter out, Hashtable params,

                                         String user, String[] groups,

                                         String sessionid, boolean useXMLIndex)

    {

      int pagesize = 0;

      int pagestart = 0;

      if(params.containsKey("pagesize") && params.containsKey("pagestart"))

      {

        String pagesizeStr = ((String[])params.get("pagesize"))[0];

        String pagestartStr = ((String[])params.get("pagestart"))[0];

        if(pagesizeStr != null && pagestartStr != null)

        {

          pagesize = (new Integer(pagesizeStr)).intValue();

          pagestart = (new Integer(pagestartStr)).intValue();

        }

      }

      // get query and qformat

      String xmlquery = ((String[])params.get("query"))[0];

      logMetacat.warn("SESSIONID: " + sessionid);

      logMetacat.warn("xmlquery: " + xmlquery);

      String qformat = ((String[])params.get("qformat"))[0];

      logMetacat.warn("qformat: " + qformat);

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

      QuerySpecification qspec = null;

      if ( xmlquery != null)

      {

         xmlquery = transformQuery(xmlquery);

         try

         {

           qspec = new QuerySpecification(xmlquery,

                                          parserName,

                                          MetaCatUtil.getOption("accNumSeparator"));

         }

         catch (Exception ee)

         {

           logMetacat.error("error generating QuerySpecification object"

                                    +" in DBQuery.findDocuments"

                                    + ee.getMessage());

         }

      }

      if (qformat != null && qformat.equals(MetaCatServlet.XMLFORMAT))

      {

        //xml format

        response.setContentType("text/xml");

        createResultDocument(xmlquery, qspec, out, user, groups, useXMLIndex, 

          pagesize, pagestart, sessionid);

      }//if

      else

      {

        //knb format, in this case we will get whole result and sent it out

        response.setContentType("text/html");

        PrintWriter nonout = null;

        StringBuffer xml = createResultDocument(xmlquery, qspec, nonout, user,

                                                groups, useXMLIndex, pagesize, 

                                                pagestart, sessionid);

        //transfer the xml to html

        try

        {

         DBTransform trans = new DBTransform();

         response.setContentType("text/html");

	       // if the user is a moderator, then pass a param to the 

         // xsl specifying the fact

         if(MetaCatUtil.isModerator(user, groups)){

        	 params.put("isModerator", new String[] {"true"});

         }

         trans.transformXMLDocument(xml.toString(), "-//NCEAS//resultset//EN",

                                 "-//W3C//HTML//EN", qformat, out, params,

                                 sessionid);

        }

        catch(Exception e)

        {

         logMetacat.error("Error in MetaCatServlet.transformResultset:"

                                +e.getMessage());

         }

      }//else

    }

  /*

   * Transforms a hashtable of documents to an xml or html result and sent

   * the content to outputstream. Keep going untill hastable is empty. stop it.

   * add the QuerySpecification as parameter is for ecogrid. But it is duplicate

   * to xmlquery String

   */

  public StringBuffer createResultDocument(String xmlquery,

                                            QuerySpecification qspec,

                                            PrintWriter out,

                                            String user, String[] groups,

                                            boolean useXMLIndex, int pagesize,

                                            int pagestart, String sessionid)

  {

    DBConnection dbconn = null;

    int serialNumber = -1;

    StringBuffer resultset = new StringBuffer();

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

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

    resultset.append("  <query>" + xmlquery + "</query>");

    // sent query part out

    if (out != null)

    {

      out.println(resultset.toString());

    }

    if (qspec != null)

    {

      try

      {

        //checkout the dbconnection

        dbconn = DBConnectionPool.getDBConnection("DBQuery.findDocuments");

        serialNumber = dbconn.getCheckOutSerialNumber();

        //print out the search result

        // search the doc list

        resultset = findResultDoclist(qspec, resultset, out, user, groups,

                                      dbconn, useXMLIndex, pagesize, pagestart, 

                                      sessionid);

      } //try

      catch (IOException ioe)

      {

        logMetacat.error("IO error in DBQuery.findDocuments:");

        logMetacat.error(ioe.getMessage());

      }

      catch (SQLException e)

      {

        logMetacat.error("SQL Error in DBQuery.findDocuments: "

                                 + e.getMessage());

      }

      catch (Exception ee)

      {

        logMetacat.error("Exception in DBQuery.findDocuments: "

                                 + ee.getMessage());

      }

      finally

      {

        DBConnectionPool.returnDBConnection(dbconn, serialNumber);

      } //finally

    }//if

    String closeRestultset = "</resultset>";

    resultset.append(closeRestultset);

    if (out != null)

    {

      out.println(closeRestultset);

    }

    return resultset;

  }//createResultDocuments

    /*

     * Find the doc list which match the query

     */

    private StringBuffer findResultDoclist(QuerySpecification qspec,

                                      StringBuffer resultsetBuffer,

                                      PrintWriter out,

                                      String user, String[]groups,

                                      DBConnection dbconn, boolean useXMLIndex,

                                      int pagesize, int pagestart, String sessionid)

                                      throws Exception

    {

      /*

      if pagesize != 0 then we need to process the query results in pages

      1) check to see what the sessionid is: look in MetacatServlet.getSessionHash()

      2) lookup the sessionid and the query in the paged_results table

      3) if there is already a page result for the session and query get that

         result and look at what our pagesize and pagestart is to get the next

         pagesize results

      4) if there is not a cached result, do the query, put the result in the 

         cache under the correct sessionid and return 0..pagesize results

      5) when the session expires or is logged out, delete the cached queryresults

      paged_results

      -------------

      sessionid (PK) (String)

      query (String)

      resultset (String)

      */

      Hashtable sessionHash = MetaCatServlet.getSessionHash();

      HttpSession sess = (HttpSession)sessionHash.get(sessionid);

      //now we have the session object, so we can cache the query there.

      int offset = 1;

      // this is a hack for offset

      if (out == null)

      {

        // for html page, we put everything into one page

        offset =

            (new Integer(MetaCatUtil.getOption("web_resultsetsize"))).intValue();

      }

      else

      {

          offset =

              (new Integer(MetaCatUtil.getOption("app_resultsetsize"))).intValue();

      }

      int count = 0;

      int index = 0;

      Hashtable docListResult = new Hashtable();

      PreparedStatement pstmt = null;

      String docid = null;

      String docname = null;

      String doctype = null;

      String createDate = null;

      String updateDate = null;

      StringBuffer document = null;

      int rev = 0;

      String query = null;

      /*

       * Check the docidOverride Vector

       * if defined, we bypass the qspec.printSQL() method

       * and contruct a simpler query based on a 

       * list of docids rather than a bunch of subselects

       */

      if ( this.docidOverride.size() == 0 ) {

          query = qspec.printSQL(useXMLIndex);

      } else {

          logMetacat.info("*** docid override " + this.docidOverride.size());

          StringBuffer queryBuffer = new StringBuffer( "SELECT docid,docname,doctype,date_created, date_updated, rev " );

          queryBuffer.append( " FROM xml_documents WHERE docid IN (" );

          for (int i = 0; i < docidOverride.size(); i++) {  

              queryBuffer.append("'");

              queryBuffer.append( (String)docidOverride.elementAt(i) );

              queryBuffer.append("',");

          }

          // empty string hack 

          queryBuffer.append( "'') " );

          query = queryBuffer.toString();

      } 

      String ownerQuery = getOwnerQuery(user);

      logMetacat.info("\n\n\n query: " + query);

      logMetacat.info("\n\n\n owner query: "+ownerQuery);

      // if query is not the owner query, we need to check the permission

      // otherwise we don't need (owner has all permission by default)

      if (!query.equals(ownerQuery))

      {

        // set user name and group

        qspec.setUserName(user);

        qspec.setGroup(groups);

        // Get access query

        String accessQuery = qspec.getAccessQuery();

        if(!query.endsWith("WHERE")){

            query = query + accessQuery;

        } else {

            query = query + accessQuery.substring(4, accessQuery.length());

        }

        logMetacat.warn("\n\n\n final query: " + query);

      }

      double startTime = System.currentTimeMillis() / 1000;

      pstmt = dbconn.prepareStatement(query);

      // Execute the SQL query using the JDBC connection

      pstmt.execute();

      ResultSet rs = pstmt.getResultSet();

      double queryExecuteTime = System.currentTimeMillis() / 1000;

      logMetacat.warn("Pagesize: " + pstmt.getFetchSize());

      logMetacat.warn("Time for execute query: "

                    + (queryExecuteTime - startTime));

      boolean tableHasRows = rs.next();

      while (tableHasRows)

      {

        docid = rs.getString(1).trim();

        docname = rs.getString(2);

        doctype = rs.getString(3);

        createDate = rs.getString(4);

        updateDate = rs.getString(5);

        rev = rs.getInt(6);

        // if there are returndocs to match, backtracking can be performed

        // otherwise, just return the document that was hit

        Vector returndocVec = qspec.getReturnDocList();

         if (returndocVec.size() != 0 && !returndocVec.contains(doctype)

                        && !qspec.isPercentageSearch())

        {

           logMetacat.warn("Back tracing now...");

           String sep = MetaCatUtil.getOption("accNumSeparator");

           StringBuffer btBuf = new StringBuffer();

           btBuf.append("select docid from xml_relation where ");

           //build the doctype list for the backtracking sql statement

           btBuf.append("packagetype in (");

           for (int i = 0; i < returndocVec.size(); i++)

           {

             btBuf.append("'").append((String) returndocVec.get(i)).append("'");

             if (i != (returndocVec.size() - 1))

             {

                btBuf.append(", ");

              }

            }

            btBuf.append(") ");

            btBuf.append("and (subject like '");

            btBuf.append(docid).append("'");

            btBuf.append("or object like '");

            btBuf.append(docid).append("')");

            PreparedStatement npstmt = dbconn.prepareStatement(btBuf.toString());

            //should incease usage count

            dbconn.increaseUsageCount(1);

            npstmt.execute();

            ResultSet btrs = npstmt.getResultSet();

            boolean hasBtRows = btrs.next();

            while (hasBtRows)

            {

               //there was a backtrackable document found

               DocumentImpl xmldoc = null;

               String packageDocid = btrs.getString(1);

               logMetacat.info("Getting document for docid: "

                                         + packageDocid);

                try

                {

                    //  THIS CONSTRUCTOR BUILDS THE WHOLE XML doc not

                    // needed here

                    // xmldoc = new DocumentImpl(dbconn, packageDocid);

                    //  thus use the following to get the doc info only

                    //  xmldoc = new DocumentImpl(dbconn);

                    String accNumber = packageDocid + MetaCatUtil.getOption("accNumSeparator") +

                    DBUtil.getLatestRevisionInDocumentTable(packageDocid);

                    xmldoc = new DocumentImpl(accNumber, false);

                    if (xmldoc == null)

                    {

                       logMetacat.info("Document was null for: "

                                                + packageDocid);

                    }

                }

                catch (Exception e)

                {

                    System.out.println("Error getting document in "

                                       + "DBQuery.findDocuments: "

                                       + e.getMessage());

                }

                String docid_org = xmldoc.getDocID();

                if (docid_org == null)

                {

                   logMetacat.info("Docid_org was null.");

                   //continue;

                }

                docid = docid_org.trim();

                docname = xmldoc.getDocname();

                doctype = xmldoc.getDoctype();

                createDate = xmldoc.getCreateDate();

                updateDate = xmldoc.getUpdateDate();

                rev = xmldoc.getRev();

                document = new StringBuffer();

                String completeDocid = docid

                                + MetaCatUtil.getOption("accNumSeparator");

                completeDocid += rev;

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

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

                if (docname != null)

                {

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

                }

                if (doctype != null)

                {

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

                }

                if (createDate != null)

                {

                 document.append("<createdate>" + createDate + "</createdate>");

                }

                if (updateDate != null)

                {

                  document.append("<updatedate>" + updateDate+ "</updatedate>");

                }

                // Store the document id and the root node id

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

                count++;

                // Get the next package document linked to our hit

                hasBtRows = btrs.next();

              }//while

              npstmt.close();

              btrs.close();

        }

        else if (returndocVec.size() == 0 || returndocVec.contains(doctype))

        {

           document = new StringBuffer();

           String completeDocid = docid

                            + MetaCatUtil.getOption("accNumSeparator");

           completeDocid += rev;

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

           if (docname != null)

           {

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

            }

            if (doctype != null)

            {

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

            }

            if (createDate != null)

            {

                document.append("<createdate>" + createDate + "</createdate>");

             }

             if (updateDate != null)

             {

               document.append("<updatedate>" + updateDate + "</updatedate>");

             }

              // Store the document id and the root node id

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

              count++;

        }//else

        // when doclist reached the offset number, send out doc list and empty

        // the hash table

        if (count == offset)

        {

          //reset count

          count = 0;

          handleSubsetResult(qspec,resultsetBuffer, out, docListResult,

                              user, groups,dbconn, useXMLIndex);

          // reset docListResult

          docListResult = new Hashtable();

        }

       // Advance to the next record in the cursor

       tableHasRows = rs.next();

     }//while

     rs.close();

     pstmt.close();

     //if docListResult is not empty, it need to be sent.

     if (!docListResult.isEmpty())

     {

       handleSubsetResult(qspec,resultsetBuffer, out, docListResult,

                              user, groups,dbconn, useXMLIndex);

     }

     double docListTime = System.currentTimeMillis() / 1000;

     logMetacat.warn("prepare docid list time: "

                    + (docListTime - queryExecuteTime));

     return resultsetBuffer;

    }//findReturnDoclist

    /*

     * Send completed search hashtable(part of reulst)to output stream

     * and buffer into a buffer stream

     */

    private StringBuffer handleSubsetResult(QuerySpecification qspec,

                                           StringBuffer resultset,

                                           PrintWriter out, Hashtable partOfDoclist,

                                           String user, String[]groups,

                                       DBConnection dbconn, boolean useXMLIndex)

                                       throws Exception

   {

     // check if there is a record in xml_returnfield

     // and get the returnfield_id and usage count

     int usage_count = getXmlReturnfieldsTableId(qspec, dbconn);

     boolean enterRecords = false;

     // get value of xml_returnfield_count

     int count = (new Integer(MetaCatUtil

                            .getOption("xml_returnfield_count")))

                            .intValue();

     // set enterRecords to true if usage_count is more than the offset

     // specified in metacat.properties

     if(usage_count > count){

         enterRecords = true;

     }

     if(returnfield_id < 0){

         logMetacat.warn("Error in getting returnfield id from"

                                  + "xml_returnfield table");

	enterRecords = false;

     }

     // get the hashtable containing the docids that already in the

     // xml_queryresult table

     logMetacat.info("size of partOfDoclist before"

                             + " docidsInQueryresultTable(): "

                             + partOfDoclist.size());

     Hashtable queryresultDocList = docidsInQueryresultTable(returnfield_id,

                                                        partOfDoclist, dbconn);

     // remove the keys in queryresultDocList from partOfDoclist

     Enumeration _keys = queryresultDocList.keys();

     while (_keys.hasMoreElements()){

         partOfDoclist.remove(_keys.nextElement());

     }

     // backup the keys-elements in partOfDoclist to check later

     // if the doc entry is indexed yet

     Hashtable partOfDoclistBackup = new Hashtable();

     _keys = partOfDoclist.keys();

     while (_keys.hasMoreElements()){

	 Object key = _keys.nextElement();

         partOfDoclistBackup.put(key, partOfDoclist.get(key));

     }

     logMetacat.info("size of partOfDoclist after"

                             + " docidsInQueryresultTable(): "

                             + partOfDoclist.size());

     //add return fields for the documents in partOfDoclist

     partOfDoclist = addReturnfield(partOfDoclist, qspec, user, groups,

                                        dbconn, useXMLIndex );

     //add relationship part part docid list for the documents in partOfDocList

     partOfDoclist = addRelationship(partOfDoclist, qspec, dbconn, useXMLIndex);

     Enumeration keys = partOfDoclist.keys();

     String key = null;

     String element = null;

     String query = null;

     int offset = (new Integer(MetaCatUtil

                               .getOption("queryresult_string_length")))

                               .intValue();

     while (keys.hasMoreElements())

     {

         key = (String) keys.nextElement();

         element = (String)partOfDoclist.get(key);

	 // check if the enterRecords is true, elements is not null, element's

         // length is less than the limit of table column and if the document

         // has been indexed already

         if(enterRecords && element != null

		&& element.length() < offset

		&& element.compareTo((String) partOfDoclistBackup.get(key)) != 0){

             query = "INSERT INTO xml_queryresult (returnfield_id, docid, "

                 + "queryresult_string) VALUES (?, ?, ?)";

             PreparedStatement pstmt = null;

             pstmt = dbconn.prepareStatement(query);

             pstmt.setInt(1, returnfield_id);

             pstmt.setString(2, key);

             pstmt.setString(3, element);

             dbconn.increaseUsageCount(1);

             pstmt.execute();

             pstmt.close();

         }

         // A string with element

         String xmlElement = "  <document>" + element + "</document>";

         //send single element to output

         if (out != null)

         {

             out.println(xmlElement);

         }

         resultset.append(xmlElement);

     }//while

     keys = queryresultDocList.keys();

     while (keys.hasMoreElements())

     {

         key = (String) keys.nextElement();

         element = (String)queryresultDocList.get(key);

         // A string with element

         String xmlElement = "  <document>" + element + "</document>";

         //send single element to output

         if (out != null)

         {

             out.println(xmlElement);

         }

         resultset.append(xmlElement);

     }//while

     return resultset;

 }

   /**

    * Get the docids already in xml_queryresult table and corresponding

    * queryresultstring as a hashtable

    */

   private Hashtable docidsInQueryresultTable(int returnfield_id,

                                              Hashtable partOfDoclist,

                                              DBConnection dbconn){

         Hashtable returnValue = new Hashtable();

         PreparedStatement pstmt = null;

         ResultSet rs = null;

         // get partOfDoclist as string for the query

         Enumeration keylist = partOfDoclist.keys();

         StringBuffer doclist = new StringBuffer();

         while (keylist.hasMoreElements())

         {

             doclist.append("'");

             doclist.append((String) keylist.nextElement());

             doclist.append("',");

         }//while

         if (doclist.length() > 0)

         {

             doclist.deleteCharAt(doclist.length() - 1); //remove the last comma

             // the query to find out docids from xml_queryresult

             String query = "select docid, queryresult_string from "

                          + "xml_queryresult where returnfield_id = " +

                          returnfield_id +" and docid in ("+ doclist + ")";

             logMetacat.info("Query to get docids from xml_queryresult:"

                                      + query);

             try {

                 // prepare and execute the query

                 pstmt = dbconn.prepareStatement(query);

                 dbconn.increaseUsageCount(1);

                 pstmt.execute();

                 rs = pstmt.getResultSet();

                 boolean tableHasRows = rs.next();

                 while (tableHasRows) {

                     // store the returned results in the returnValue hashtable

                     String key = rs.getString(1);

                     String element = rs.getString(2);

                     if(element != null){

                         returnValue.put(key, element);

                     } else {

                         logMetacat.info("Null elment found ("

                         + "DBQuery.docidsInQueryresultTable)");

                     }

                     tableHasRows = rs.next();

                 }

                 rs.close();

                 pstmt.close();

             } catch (Exception e){