- Timestamp:
- 2005-05-16T11:02:50+12:00 (19 years ago)
- File:
-
- 1 edited
Legend:
- Unmodified
- Added
- Removed
-
trunk/gsdl3/src/java/org/greenstone/gsdl3/util/SQLQuery.java
r8746 r9874 6 6 import java.sql.ResultSet; 7 7 import java.sql.SQLException; 8 import java.sql.Statement; 8 9 9 10 import java.util.ArrayList; … … 17 18 18 19 public boolean setDatabase(String db_name) { 19 try {20 connection = GS3SQLConnectionFactory.getGS3SQLConnection(db_name);21 20 connection = GS3SQLConnectionFactory.getGS3SQLConnection(db_name); 21 try { 22 22 23 // test the connection 23 24 String query = "select * from "+GSSQL.DOCUMENT_TABLE+GSSQL.END; 24 if (!connection.execute(query)) { 25 //connection.close(); 26 connection = null; 27 return false; 28 } 25 Statement s = connection.createStatement(); 26 s.execute(query); 27 s.close(); 29 28 return true; 30 29 } catch (Exception e) { 31 System.err.println("SQLQuery.setDatabase Exception"); 32 return false; 30 System.err.println("SQLQuery.setDatabase():"+e); 31 } 32 closeConnection(); 33 return false; 34 } 35 36 public void closeConnection() { 37 if (connection != null) { 38 connection.close(); 39 connection=null; 33 40 } 34 41 } … … 48 55 GSSQL.END; 49 56 50 connection.execute(query);57 Statement statement = null; 51 58 ResultSet results = null; 52 59 String doc_id = null; 53 60 String meta_id = null; 54 61 try { 55 results = connection.getResultSet(); 56 results.first(); 57 doc_id = results.getString(GSSQL.DOCUMENT_ID); 58 meta_id = results.getString(GSSQL.METADATA_ID); 59 } catch (java.sql.SQLException e) { 60 61 System.err.println("SQLQuery.MGNum2OID() Error: "+e.getMessage()); 62 return null; 63 } 64 System.out.println("doc id ="+doc_id+", meta id = "+meta_id); 62 statement = connection.createStatement(); 63 results = statement.executeQuery(query); 64 if (results.first()) { 65 doc_id = results.getString(GSSQL.DOCUMENT_ID); 66 meta_id = results.getString(GSSQL.METADATA_ID); 67 } else { 68 statement.close(); 69 return null; 70 } 71 } catch (java.sql.SQLException e) { 72 System.err.println("SQLQuery.MGNum2OID(): "+e); 73 return null; 74 } 65 75 66 76 // now get division label … … 73 83 GSSQL.END; 74 84 75 connection.execute(query);85 76 86 String short_label = null; 77 87 try { 78 results = connection.getResultSet(); 79 results.first(); 80 short_label = results.getString(GSSQL.SHORT_LABEL); 81 82 } catch (java.sql.SQLException e) { 88 results = statement.executeQuery(query); 89 if (results.first()) { 90 short_label = results.getString(GSSQL.SHORT_LABEL); 91 } else { 92 statement.close(); 93 return null; 94 } 95 statement.close(); 96 } catch (SQLException e) { 83 97 84 98 System.err.println("SQLQuery.MGNum2OID() Error: "+e.getMessage()); 85 99 return null; 86 100 } 87 88 ///ystem.out.println("short label = "+short_label); 89 90 System.out.println("final doc_id = "+GS3OID.createOID(doc_id, short_label)); 101 91 102 return GS3OID.createOID(doc_id, short_label); 92 103 } 93 104 94 105 public String OID2MGNum(String oid) { 95 System.out.println("converting oid "+oid+" to mg num");96 106 String id = getDocumentMetadata(oid, "gsdl3.mgseqno"); 97 System.out.println("mg id = "+id);98 107 return id; 99 108 … … 113 122 " and "+ GSSQL.STRUCTURE_ID +GSSQL.EQUALS_QUOTE + "Section" + GSSQL.QUOTE + 114 123 GSSQL.END; 115 116 connection.execute(query); 117 ResultSet results = null; 118 try { 119 results = connection.getResultSet(); 120 if (results.next()) { 121 return true; 122 } 124 boolean is_hierarchical = false; 125 try { 126 Statement statement = connection.createStatement(); 127 ResultSet results = statement.executeQuery(query); 128 is_hierarchical = results.first(); 129 statement.close(); 123 130 } catch (java.sql.SQLException e) { 124 131 return false; 125 132 } 126 127 return false;133 134 return is_hierarchical; 128 135 } 129 136 … … 131 138 public String getClassifierMetadata(String oid, String full_meta_name) { 132 139 133 if (full_meta_name.equals("Title") || full_meta_name.equals("numleafdocs")) { 134 // get the description 135 String field_name=""; 136 if (full_meta_name.equals("Title")) { 137 field_name = GSSQL.DESCRIPTION; 138 } else { 139 field_name = GSSQL.NUM_LEAF_DOCUMENTS; 140 } 141 142 String query = "select "+ field_name+ 143 " from " + GSSQL.CLASSIFIER_TABLE + 144 " where " + GSSQL.CLASSIFIER_ID + GSSQL.EQUALS_QUOTE + oid + GSSQL.QUOTE + 145 GSSQL.END; 140 if (!full_meta_name.equals("Title") && !full_meta_name.equals("numleafdocs")) { 141 return null; 142 } 143 // get the description 144 String field_name=""; 145 if (full_meta_name.equals("Title")) { 146 field_name = GSSQL.DESCRIPTION; 147 } else { 148 field_name = GSSQL.NUM_LEAF_DOCUMENTS; 149 } 150 151 String query = "select "+ field_name+ 152 " from " + GSSQL.CLASSIFIER_TABLE + 153 " where " + GSSQL.CLASSIFIER_ID + GSSQL.EQUALS_QUOTE + oid + GSSQL.QUOTE + 154 GSSQL.END; 155 156 String value = null; 157 try { 158 Statement statement = connection.createStatement(); 159 ResultSet results = statement.executeQuery(query); 160 if (results.first()) { 161 value = results.getString(field_name); 162 } 163 statement.close(); 164 } catch (java.sql.SQLException e) { 146 165 147 connection.execute(query); 148 ResultSet results = null; 149 String value = null; 150 try { 151 results = connection.getResultSet(); 152 results.first(); 153 value = results.getString(field_name); 154 } catch (java.sql.SQLException e) { 155 156 System.err.println("SQLQuery.getClassifierMetadata() Error: "+e.getMessage()); 157 return null; 158 } 159 return value; 160 161 } else return null; 162 166 System.err.println("SQLQuery.getClassifierMetadata() Error: "+e.getMessage()); 167 return null; 168 } 169 return value; 170 163 171 } 164 172 … … 172 180 section_id = GS3OID.getSectionLabel(oid); 173 181 } 174 System.out.println("getDocChildren: oid = "+oid+", doc id = "+doc_id+", sec id ="+section_id);175 182 // int sep_index = oid.indexOf("."); 176 183 // if (sep_index != -1) { … … 186 193 GSSQL.END; 187 194 188 connection.execute(query);189 195 ResultSet results = null; 196 Statement statement = null; 190 197 String div_ref = null; 191 198 try { 192 results = connection.getResultSet(); 193 results.first(); 194 div_ref = results.getString(GSSQL.DIVISION_REF); 199 statement = connection.createStatement(); 200 results = statement.executeQuery(query); 201 if (results.first()) { 202 div_ref = results.getString(GSSQL.DIVISION_REF); 203 } else { 204 statement.close(); 205 return null; 206 } 195 207 } catch (java.sql.SQLException e) { 196 208 … … 206 218 " and "+GSSQL.PARENT_REF+GSSQL.EQUALS_QUOTE+div_ref+GSSQL.QUOTE+ 207 219 GSSQL.END; 208 connection.execute(query); 209 results = null; 210 try { 211 results = connection.getResultSet(); 220 try { 221 results = statement.executeQuery(query); 212 222 while (results.next()) { 213 223 String id = results.getString(GSSQL.SHORT_LABEL); 214 224 children.add(GS3OID.createOID(doc_id, id)); 215 225 } 226 statement.close(); 216 227 } catch (java.sql.SQLException e) { 217 228 … … 243 254 GSSQL.END; 244 255 245 connection.execute(query); 246 ResultSet results = null; 247 try { 248 results = connection.getResultSet(); 256 try { 257 Statement statement = connection.createStatement(); 258 ResultSet results = statement.executeQuery(query); 249 259 while (results.next()) { 250 260 String id = results.getString(GSSQL.CLASSIFIER_ID); 251 261 children.add(id); 252 262 } 253 } catch (java.sql.SQLException e) { 254 263 statement.close(); 264 } catch (java.sql.SQLException e) { 265 255 266 System.err.println("SQLQuery.getClassifierChildren Error: "+e.getMessage()); 256 267 return null; … … 260 271 261 272 public ArrayList getClassifierDocChildren(String oid) { 262 System.out.println("in getclassifierdocchildren");263 273 ArrayList children = new ArrayList(); 264 274 … … 269 279 " order by "+GSSQL.CLASS_DOCUMENT_TABLE+GSSQL.DOT+GSSQL.DOCUMENT_ORDER+ 270 280 GSSQL.END; 271 System.out.println("query="+query); 272 connection.execute(query); 273 ResultSet results = null; 274 try { 275 results = connection.getResultSet(); 281 try { 282 Statement statement = connection.createStatement(); 283 ResultSet results = statement.executeQuery(query); 276 284 while (results.next()) { 277 285 String id = results.getString(GSSQL.DOCUMENT_ID); … … 283 291 return null; 284 292 } 285 // now check for documents286 293 287 294 return children; … … 302 309 // } 303 310 304 System.out.println("get meta for "+oid+", doc id = "+doc_id+"sec id = "+section_id);305 311 306 312 // get the metadata block id … … 314 320 GSSQL.END; 315 321 316 connection.execute(query);322 Statement statement = null; 317 323 ResultSet results = null; 318 324 String meta_id = null; 319 325 try { 320 results = connection.getResultSet(); 321 results.first(); 322 meta_id = results.getString(GSSQL.METADATA_ID); 326 statement = connection.createStatement(); 327 results = statement.executeQuery(query); 328 if (results.first()) { 329 meta_id = results.getString(GSSQL.METADATA_ID); 330 } else { 331 statement.close(); 332 return null; 333 } 323 334 } catch (java.sql.SQLException e) { 324 335 … … 326 337 return null; 327 338 } 328 System.err.println("metadata block id = "+meta_id); 329 339 330 340 // now get the list of namespace refs for the right namespace 331 341 int sep_index = full_meta_name.indexOf('.'); … … 337 347 meta_name = full_meta_name.substring(sep_index+1); 338 348 } 339 System.out.println("orig meta = "+full_meta_name+", ns = "+meta_ns+", name="+meta_name);340 349 341 350 query = "select * "+ … … 347 356 " and "+GSSQL.NAMESPACE_TABLE+GSSQL.DOT+GSSQL.NAMESPACE_REF+GSSQL.EQUALS+GSSQL.METADATA_VALUE_TABLE+GSSQL.DOT+GSSQL.NAMESPACE_REF+ 348 357 GSSQL.END; 349 System.out.println("query="+query); 350 connection.execute(query); 358 351 359 String meta_value = null; 352 360 try { 353 results = connection.getResultSet(); 361 statement = connection.createStatement(); 362 results = statement.executeQuery(query); 354 363 while (results.next()) { 355 364 String m_name = results.getString(GSSQL.LABEL); … … 359 368 } 360 369 } 370 statement.close(); 361 371 } catch (java.sql.SQLException e) { 362 372
Note:
See TracChangeset
for help on using the changeset viewer.