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
 * Institution: National Center for Ecological Analysis and Synthesis
8
 *   Copyright: 2000
9
 *     Authors: Matt Jones
10
 *
11
 *     Version: '$Id: DBSimpleQuery.java 31 2000-04-12 03:04:55Z jones $'
12
 */
13

    
14
import java.io.*;
15
import java.net.URL;
16
import java.net.MalformedURLException;
17
import java.sql.*;
18
import java.util.Stack;
19
import java.util.Hashtable;
20
import java.util.Enumeration;
21

    
22
/** 
23
 * A Class that searches a relational DB for elements and attributes that
24
 * have free text matches to the query string.  It returns a result set 
25
 * consisting of the root nodeid for each document that satisfies the query
26
 */
27
public class DBSimpleQuery {
28

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

    
32
  /**
33
   * the main routine used to test the DBSimpleQuery utility.
34
   *
35
   * Usage: java DBSimpleQuery <query> <user> <password> [dbstring]
36
   *
37
   * @param query the text to search for in the element and attribute content
38
   * @param user the username to use for the database connection
39
   * @param password the password to use for the database connection
40
   * @param dbstring the connection info to use for the database connection
41
   */
42
  static public void main(String[] args) {
43
     
44
     if (args.length < 3)
45
     {
46
        System.err.println("Wrong number of arguments!!!");
47
        System.err.println("USAGE: java DBSimpleQuery " +
48
                           "<query> <user> <password> [dbstring]");
49
        return;
50
     } else {
51
        try {
52
                    
53
          String query    = args[0];
54
          String user     = args[1];
55
          String password = args[2];
56
          String dbstring = null;
57

    
58
          if (args.length <= 3) {
59
            dbstring = defaultDB;
60
          } else {
61
            dbstring = args[3];
62
          }
63

    
64
          // Execute the simple query
65
          DBSimpleQuery rd = new DBSimpleQuery(user, password, dbstring);
66
          Hashtable nodelist = rd.findRootNodes(query);
67

    
68
          // Print the reulting root nodes
69
          StringBuffer result = new StringBuffer();
70
          long nodeid;
71
          result.append("<?xml version=\"1.0\"?>\n");
72
          result.append("<resultset>\n");
73
          Enumeration rootlist = nodelist.keys(); 
74
          while (rootlist.hasMoreElements()) {
75
            nodeid = ((Long)rootlist.nextElement()).longValue();
76
            result.append("  <nodeid>").append(nodeid).append("</nodeid>\n");
77
          }
78
          result.append("</resultset>");
79

    
80
          System.out.println(result);
81

    
82
        } catch (Exception e) {
83
          System.err.println("EXCEPTION HANDLING REQUIRED");
84
          System.err.println(e.getMessage());
85
          e.printStackTrace(System.err);
86
        }
87
     }
88
  }
89
  
90
  /**
91
   * construct an instance of the DBSimpleQuery class 
92
   *
93
   * Generally, one would call the findRootNodes() routine after creating 
94
   * an instance to specify the query to search for
95
   *
96
   * @param query the text to search for in the element and attribute content
97
   * @param user the username to use for the database connection
98
   * @param password the password to use for the database connection
99
   * @param dbstring the connection info to use for the database connection
100
   */
101
  private DBSimpleQuery( String user, String password, String dbstring) 
102
                  throws IOException, 
103
                         SQLException, 
104
                         ClassNotFoundException
105
  {
106
     // Open a connection to the database
107
     conn = openDBConnection(
108
                "oracle.jdbc.driver.OracleDriver",
109
                dbstring, user, password);
110

    
111
  }
112
  
113
  /** Utility message to establish a JDBC database connection */
114
  private Connection openDBConnection(String dbDriver, String connection,
115
                String user, String password)
116
                throws SQLException, ClassNotFoundException {
117
     // Load the Oracle JDBC driver
118
     Class.forName (dbDriver);
119

    
120
     // Connect to the database
121
     Connection conn = DriverManager.getConnection( connection, user, password);
122
     return conn;
123
  }
124

    
125
  /** 
126
   * routine to search the elements and attributes looking to match query
127
   *
128
   * @param query the text to search for
129
   */
130
  public Hashtable findRootNodes(String query) {
131
      Hashtable	nodeListResult = new Hashtable();
132
      Hashtable	rootListResult = new Hashtable();
133

    
134
      PreparedStatement pstmt;
135
      long nodeid;
136

    
137
      try {
138
        pstmt =
139
          conn.prepareStatement("SELECT nodeid " +
140
                  "FROM xml_nodes WHERE nodedata LIKE ?");
141
        // Bind the values to the query
142
        pstmt.setString(1, query);
143

    
144
        pstmt.execute();
145
        try {
146
          ResultSet rs = pstmt.getResultSet();
147
          try {
148
            boolean tableHasRows = rs.next();
149
            while (tableHasRows) {
150
              try {
151
                nodeid = rs.getLong(1);
152
                nodeListResult.put(new Long(nodeid),new Long(nodeid));
153

    
154
              } catch (SQLException e) {
155
                System.out.println("Error with getInt: " + e.getMessage());
156
              }
157

    
158
              // Advance to the next record in the cursor
159
              tableHasRows = rs.next();
160
            }
161
          } catch (SQLException e) {
162
            System.out.println("Error with next: " + e.getMessage());
163
          }
164
        } catch (SQLException e) {
165
          System.out.println("Error with getrset: " + e.getMessage());
166
        }
167
        pstmt.close();
168
      } catch (SQLException e) {
169
        System.out.println("Error getting id: " + e.getMessage());
170
      }
171

    
172
      Enumeration nodelist = nodeListResult.keys(); 
173
      while (nodelist.hasMoreElements()) {
174
        nodeid = ((Long)nodelist.nextElement()).longValue();
175

    
176
        try {
177
          pstmt =
178
            conn.prepareStatement("SELECT nodeid " +
179
                    "FROM xml_nodes START WITH nodeid = ? " +
180
                    "CONNECT BY nodeid = PRIOR parentnodeid");
181
          // Bind the values to the query
182
          pstmt.setLong(1, nodeid);
183
  
184
          pstmt.execute();
185
          try {
186
            ResultSet rs = pstmt.getResultSet();
187
            try {
188
              boolean tableHasRows = rs.next();
189
              while (tableHasRows) {
190
                try {
191
                  nodeid = rs.getLong(1);
192
  
193
                } catch (SQLException e) {
194
                  System.out.println("Error with getInt: " + e.getMessage());
195
                }
196

    
197
                // Advance to the next record in the cursor
198
                tableHasRows = rs.next();
199

    
200
              }
201
            } catch (SQLException e) {
202
              System.out.println("Error with next: " + e.getMessage());
203
            }
204
          } catch (SQLException e) {
205
            System.out.println("Error with getrset: " + e.getMessage());
206
          }
207
          pstmt.close();
208
        } catch (SQLException e) {
209
          System.out.println("Error getting id: " + e.getMessage());
210
        }
211
        
212
        // Record that the last record should be the root
213
        rootListResult.put(new Long(nodeid),new Long(nodeid));
214
      } 
215

    
216
      return rootListResult;
217
  }
218
}
(8-8/13)