Project

General

Profile

1
/**
2
 *        Name: DBSimpleQuery.java
3
 *     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
 *              each document that satisfies the query
7
 *   Copyright: 2000 Regents of the University of California and the
8
 *              National Center for Ecological Analysis and Synthesis
9
 *     Authors: Matt Jones
10
 *
11
 *     Version: '$Id: DBSimpleQuery.java 51 2000-04-17 23:06:07Z jones $'
12
 */
13

    
14
package edu.ucsb.nceas.metacat;
15

    
16
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
/** 
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
public class DBSimpleQuery {
30

    
31
  static  String 	defaultDB = "jdbc:oracle:thin:@localhost:1521:test";
32
  private Connection	conn = null;
33

    
34
  /**
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
  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
          String query    = args[0];
56
          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
          // Open a connection to the database
67
          Connection dbconn = MetaCatUtil.openDBConnection( 
68
                              "oracle.jdbc.driver.OracleDriver",
69
                              dbstring, user, password);
70
          // Execute the simple query
71
          DBSimpleQuery rd = new DBSimpleQuery(dbconn);
72
          Hashtable nodelist = rd.findRootNodes(query);
73

    
74
          // Print the reulting root nodes
75
          StringBuffer result = new StringBuffer();
76
          long nodeid;
77
          result.append("<?xml version=\"1.0\"?>\n");
78
          result.append("<resultset>\n");
79
          Enumeration rootlist = nodelist.keys(); 
80
          while (rootlist.hasMoreElements()) {
81
            nodeid = ((Long)rootlist.nextElement()).longValue();
82
            result.append("  <nodeid>").append(nodeid).append("</nodeid>\n");
83
          }
84
          result.append("</resultset>");
85

    
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
  /**
97
   * construct an instance of the DBSimpleQuery class 
98
   *
99
   * <p>Generally, one would call the findRootNodes() routine after creating 
100
   * an instance to specify the search query</p>
101
   *
102
   * @param conn the JDBC connection that we use for the query
103
   */
104
  public DBSimpleQuery( Connection conn ) 
105
                  throws IOException, 
106
                         SQLException, 
107
                         ClassNotFoundException
108
  {
109
    this.conn = conn;
110
  }
111
  
112
  /** 
113
   * routine to search the elements and attributes looking to match query
114
   *
115
   * @param query the text to search for
116
   */
117
  public Hashtable findRootNodes(String query) {
118
      Hashtable	nodeListResult = new Hashtable();
119
      Hashtable	rootListResult = new Hashtable();
120

    
121
      PreparedStatement pstmt;
122
      long nodeid;
123

    
124
      try {
125
        pstmt =
126
          conn.prepareStatement("SELECT nodeid " +
127
                  "FROM xml_nodes WHERE nodedata LIKE ?");
128
        // Bind the values to the query
129
        pstmt.setString(1, query);
130

    
131
        pstmt.execute();
132
        try {
133
          ResultSet rs = pstmt.getResultSet();
134
          try {
135
            boolean tableHasRows = rs.next();
136
            while (tableHasRows) {
137
              try {
138
                nodeid = rs.getLong(1);
139
                nodeListResult.put(new Long(nodeid),new Long(nodeid));
140

    
141
              } catch (SQLException e) {
142
                System.out.println("Error with getInt: " + e.getMessage());
143
              }
144

    
145
              // Advance to the next record in the cursor
146
              tableHasRows = rs.next();
147
            }
148
          } catch (SQLException e) {
149
            System.out.println("Error with next: " + e.getMessage());
150
          }
151
        } catch (SQLException e) {
152
          System.out.println("Error with getrset: " + e.getMessage());
153
        }
154
        pstmt.close();
155
      } catch (SQLException e) {
156
        System.out.println("Error getting id: " + e.getMessage());
157
      }
158

    
159
      Enumeration nodelist = nodeListResult.keys(); 
160
      while (nodelist.hasMoreElements()) {
161
        nodeid = ((Long)nodelist.nextElement()).longValue();
162

    
163
        try {
164
          pstmt =
165
            conn.prepareStatement("SELECT nodeid " +
166
                    "FROM xml_nodes START WITH nodeid = ? " +
167
                    "CONNECT BY nodeid = PRIOR parentnodeid");
168
          // Bind the values to the query
169
          pstmt.setLong(1, nodeid);
170
  
171
          pstmt.execute();
172
          try {
173
            ResultSet rs = pstmt.getResultSet();
174
            try {
175
              boolean tableHasRows = rs.next();
176
              while (tableHasRows) {
177
                try {
178
                  nodeid = rs.getLong(1);
179
  
180
                } catch (SQLException e) {
181
                  System.out.println("Error with getInt: " + e.getMessage());
182
                }
183

    
184
                // Advance to the next record in the cursor
185
                tableHasRows = rs.next();
186

    
187
              }
188
            } catch (SQLException e) {
189
              System.out.println("Error with next: " + e.getMessage());
190
            }
191
          } catch (SQLException e) {
192
            System.out.println("Error with getrset: " + e.getMessage());
193
          }
194
          pstmt.close();
195
        } catch (SQLException e) {
196
          System.out.println("Error getting id: " + e.getMessage());
197
        }
198
        
199
        // Record that the last record should be the root
200
        rootListResult.put(new Long(nodeid),new Long(nodeid));
201
      } 
202

    
203
      return rootListResult;
204
  }
205
}
(11-11/29)