Project

General

Profile

1 27 jones
/**
2
 *        Name: DBSimpleQuery.java
3 31 jones
 *     Purpose: A Class that searches a relational DB for elements and
4
 *              attributes that have free text matches to the query string.
5
 *              It returns a result set consisting of the root nodeid for
6 27 jones
 *              each document that satisfies the query
7 35 jones
 *   Copyright: 2000 Regents of the University of California and the
8
 *              National Center for Ecological Analysis and Synthesis
9 27 jones
 *     Authors: Matt Jones
10
 *
11
 *     Version: '$Id$'
12
 */
13
14 51 jones
package edu.ucsb.nceas.metacat;
15
16 27 jones
import java.io.*;
17
import java.net.URL;
18
import java.net.MalformedURLException;
19
import java.sql.*;
20
import java.util.Stack;
21
import java.util.Hashtable;
22
import java.util.Enumeration;
23
24 31 jones
/**
25
 * A Class that searches a relational DB for elements and attributes that
26
 * have free text matches to the query string.  It returns a result set
27
 * consisting of the root nodeid for each document that satisfies the query
28
 */
29 27 jones
public class DBSimpleQuery {
30
31
  static  String 	defaultDB = "jdbc:oracle:thin:@localhost:1521:test";
32
  private Connection	conn = null;
33
34 31 jones
  /**
35
   * the main routine used to test the DBSimpleQuery utility.
36
   *
37
   * Usage: java DBSimpleQuery <query> <user> <password> [dbstring]
38
   *
39
   * @param query the text to search for in the element and attribute content
40
   * @param user the username to use for the database connection
41
   * @param password the password to use for the database connection
42
   * @param dbstring the connection info to use for the database connection
43
   */
44 27 jones
  static public void main(String[] args) {
45
46
     if (args.length < 3)
47
     {
48
        System.err.println("Wrong number of arguments!!!");
49
        System.err.println("USAGE: java DBSimpleQuery " +
50
                           "<query> <user> <password> [dbstring]");
51
        return;
52
     } else {
53
        try {
54
55 28 jones
          String query    = args[0];
56 27 jones
          String user     = args[1];
57
          String password = args[2];
58
          String dbstring = null;
59
60
          if (args.length <= 3) {
61
            dbstring = defaultDB;
62
          } else {
63
            dbstring = args[3];
64
          }
65
66 50 jones
          // Open a connection to the database
67
          Connection dbconn = MetaCatUtil.openDBConnection(
68
                              "oracle.jdbc.driver.OracleDriver",
69
                              dbstring, user, password);
70 27 jones
          // Execute the simple query
71 50 jones
          DBSimpleQuery rd = new DBSimpleQuery(dbconn);
72 86 jones
          Hashtable nodelist = rd.findDocuments(query);
73 27 jones
74
          // Print the reulting root nodes
75
          StringBuffer result = new StringBuffer();
76 86 jones
          long docid = 0;
77 27 jones
          result.append("<?xml version=\"1.0\"?>\n");
78
          result.append("<resultset>\n");
79 86 jones
          Enumeration doclist = nodelist.keys();
80
          while (doclist.hasMoreElements()) {
81
            docid = ((Long)doclist.nextElement()).longValue();
82
            result.append("  <docid>").append(docid).append("</docid>\n");
83 27 jones
          }
84 28 jones
          result.append("</resultset>");
85 27 jones
86
          System.out.println(result);
87
88
        } catch (Exception e) {
89
          System.err.println("EXCEPTION HANDLING REQUIRED");
90
          System.err.println(e.getMessage());
91
          e.printStackTrace(System.err);
92
        }
93
     }
94
  }
95
96 31 jones
  /**
97
   * construct an instance of the DBSimpleQuery class
98
   *
99 86 jones
   * <p>Generally, one would call the findDocuments() routine after creating
100 50 jones
   * an instance to specify the search query</p>
101 31 jones
   *
102 50 jones
   * @param conn the JDBC connection that we use for the query
103 31 jones
   */
104 50 jones
  public DBSimpleQuery( Connection conn )
105 27 jones
                  throws IOException,
106
                         SQLException,
107
                         ClassNotFoundException
108
  {
109 50 jones
    this.conn = conn;
110 27 jones
  }
111
112 31 jones
  /**
113
   * routine to search the elements and attributes looking to match query
114
   *
115
   * @param query the text to search for
116
   */
117 86 jones
  public Hashtable findDocuments(String query) {
118 27 jones
      Hashtable	nodeListResult = new Hashtable();
119
      Hashtable	rootListResult = new Hashtable();
120 86 jones
      Hashtable	 docListResult = new Hashtable();
121 27 jones
122
      PreparedStatement pstmt;
123
      long nodeid;
124
125
      try {
126
        pstmt =
127
          conn.prepareStatement("SELECT nodeid " +
128 28 jones
                  "FROM xml_nodes WHERE nodedata LIKE ?");
129 27 jones
        // Bind the values to the query
130 28 jones
        pstmt.setString(1, query);
131 27 jones
132
        pstmt.execute();
133
        try {
134
          ResultSet rs = pstmt.getResultSet();
135
          try {
136
            boolean tableHasRows = rs.next();
137
            while (tableHasRows) {
138
              try {
139
                nodeid = rs.getLong(1);
140
                nodeListResult.put(new Long(nodeid),new Long(nodeid));
141
142
              } catch (SQLException e) {
143
                System.out.println("Error with getInt: " + e.getMessage());
144
              }
145
146
              // Advance to the next record in the cursor
147
              tableHasRows = rs.next();
148
            }
149
          } catch (SQLException e) {
150
            System.out.println("Error with next: " + e.getMessage());
151
          }
152
        } catch (SQLException e) {
153
          System.out.println("Error with getrset: " + e.getMessage());
154
        }
155
        pstmt.close();
156
      } catch (SQLException e) {
157
        System.out.println("Error getting id: " + e.getMessage());
158
      }
159
160
      Enumeration nodelist = nodeListResult.keys();
161
      while (nodelist.hasMoreElements()) {
162
        nodeid = ((Long)nodelist.nextElement()).longValue();
163
164
        try {
165
          pstmt =
166
            conn.prepareStatement("SELECT nodeid " +
167
                    "FROM xml_nodes START WITH nodeid = ? " +
168
                    "CONNECT BY nodeid = PRIOR parentnodeid");
169
          // Bind the values to the query
170
          pstmt.setLong(1, nodeid);
171
172
          pstmt.execute();
173
          try {
174
            ResultSet rs = pstmt.getResultSet();
175
            try {
176
              boolean tableHasRows = rs.next();
177
              while (tableHasRows) {
178
                try {
179
                  nodeid = rs.getLong(1);
180
181
                } catch (SQLException e) {
182
                  System.out.println("Error with getInt: " + e.getMessage());
183
                }
184
185
                // Advance to the next record in the cursor
186
                tableHasRows = rs.next();
187
188
              }
189
            } catch (SQLException e) {
190
              System.out.println("Error with next: " + e.getMessage());
191
            }
192
          } catch (SQLException e) {
193
            System.out.println("Error with getrset: " + e.getMessage());
194
          }
195
          pstmt.close();
196
        } catch (SQLException e) {
197
          System.out.println("Error getting id: " + e.getMessage());
198
        }
199
200
        // Record that the last record should be the root
201
        rootListResult.put(new Long(nodeid),new Long(nodeid));
202
      }
203
204 86 jones
      // Now look up the document id
205
      long docid = 0;
206
      Enumeration rootlist = rootListResult.keys();
207
      while (rootlist.hasMoreElements()) {
208
        nodeid = ((Long)rootlist.nextElement()).longValue();
209
210
        try {
211
          pstmt =
212
            conn.prepareStatement("SELECT docid " +
213
                    "FROM xml_documents " +
214
                    "WHERE rootnodeid = ?");
215
          // Bind the values to the query
216
          pstmt.setLong(1, nodeid);
217
218
          pstmt.execute();
219
          try {
220
            ResultSet rs = pstmt.getResultSet();
221
            try {
222
              boolean tableHasRows = rs.next();
223
              while (tableHasRows) {
224
                try {
225
                  docid = rs.getLong(1);
226
227
                } catch (SQLException e) {
228
                  System.out.println("Error with getLong: " + e.getMessage());
229
                }
230
231
                // Advance to the next record in the cursor
232
                tableHasRows = rs.next();
233
234
              }
235
            } catch (SQLException e) {
236
              System.out.println("Error with next: " + e.getMessage());
237
            }
238
          } catch (SQLException e) {
239
            System.out.println("Error with getrset: " + e.getMessage());
240
          }
241
          pstmt.close();
242
        } catch (SQLException e) {
243
          System.out.println("Error getting id: " + e.getMessage());
244
        }
245
246
        // Store the document id and the root node id
247
        docListResult.put(new Long(docid),new Long(nodeid));
248
      }
249
250
      return docListResult;
251 27 jones
  }
252
}