source: branches/ant-install-branch/gsdl3/src/java/org/greenstone/gsdl3/util/SQLQuery.java@ 9858

Last change on this file since 9858 was 9858, checked in by kjdon, 19 years ago

OK, changed my mind about making SQLConnection kill off the previous statement.
To make it more transparent what is happening, you now have to create a Statement (connection.createStatement()), then use the Statement to execute the query. This means that the thing doing the query owns the Statement, and can kill it off when finished with it, and nothing else can kill it off unexpectedly. The previous way this was all implemented meant that there was a large memory leak, and some functionality actually relied on this. A newer version of the mysql connector/J has fixed the bug where the statement wasn't closed on garbage collection, but it still seems better to close it explicitly.
Hopefully I have got it all back to working as well as it was bfore, and haven't introduced any bugs :-)
  • Property svn:keywords set to Author Date Id Revision
File size: 13.4 KB
Line 
1package org.greenstone.gsdl3.util;
2
3import org.greenstone.gsdl3.gs3build.database.GS3SQLConnectionFactory;
4import org.greenstone.gsdl3.gs3build.database.GS3SQLConnection;
5
6import java.sql.ResultSet;
7import java.sql.SQLException;
8import java.sql.Statement;
9
10import java.util.ArrayList;
11
12public class SQLQuery {
13 GS3SQLConnection connection = null;
14
15 public SQLQuery() {
16
17 }
18
19 public boolean setDatabase(String db_name) {
20 connection = GS3SQLConnectionFactory.getGS3SQLConnection(db_name);
21 try {
22
23 // test the connection
24 String query = "select * from "+GSSQL.DOCUMENT_TABLE+GSSQL.END;
25 Statement s = connection.createStatement();
26 s.execute(query);
27 s.close();
28 return true;
29 } catch (Exception e) {
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;
40 }
41 }
42
43 public String MGNum2OID(String mg_num) {
44 System.out.println("converting mg num to oid "+mg_num);
45 // get doc id and meta id
46 String query = "select "+
47 GSSQL.METADATA_TABLE+GSSQL.DOT+GSSQL.METADATA_ID+
48 GSSQL.COMMA+GSSQL.METADATA_TABLE+GSSQL.DOT+GSSQL.DOCUMENT_ID+
49 " from "+GSSQL.METADATA_VALUE_TABLE+GSSQL.COMMA+GSSQL.NAMESPACE_TABLE+GSSQL.COMMA+GSSQL.METADATA_TABLE+
50 " where "+
51 GSSQL.METADATA_VALUE_TABLE+GSSQL.DOT+GSSQL.LABEL+GSSQL.EQUALS_QUOTE+GSSQL.MG_NUM+GSSQL.QUOTE+
52 " and "+GSSQL.METADATA_VALUE_TABLE+GSSQL.DOT+GSSQL.VALUE+GSSQL.EQUALS_QUOTE+mg_num+GSSQL.QUOTE+
53 " and "+GSSQL.METADATA_VALUE_TABLE+GSSQL.DOT+GSSQL.NAMESPACE_REF+GSSQL.EQUALS+GSSQL.NAMESPACE_TABLE+GSSQL.DOT+GSSQL.NAMESPACE_REF+
54 " and "+GSSQL.NAMESPACE_TABLE+GSSQL.DOT+GSSQL.METADATA_REF+GSSQL.EQUALS+GSSQL.METADATA_TABLE+GSSQL.DOT+GSSQL.METADATA_REF+
55 GSSQL.END;
56
57 Statement statement = null;
58 ResultSet results = null;
59 String doc_id = null;
60 String meta_id = null;
61 try {
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 }
75
76 // now get division label
77 query = "select "+
78 GSSQL.DIVISION_TABLE+GSSQL.DOT+GSSQL.SHORT_LABEL+
79 " from "+GSSQL.DIVISION_TABLE+GSSQL.COMMA+GSSQL.DIVISION_METADATA_TABLE+
80 " where "+GSSQL.DIVISION_METADATA_TABLE+GSSQL.DOT+GSSQL.METADATA_ID+GSSQL.EQUALS_QUOTE+meta_id+GSSQL.QUOTE+
81 " and "+GSSQL.DIVISION_METADATA_TABLE+GSSQL.DOT+GSSQL.DOCUMENT_ID+GSSQL.EQUALS_QUOTE+doc_id+GSSQL.QUOTE+
82 " and "+GSSQL.DIVISION_METADATA_TABLE+GSSQL.DOT+GSSQL.DIVISION_REF+GSSQL.EQUALS+GSSQL.DIVISION_TABLE+GSSQL.DOT+GSSQL.DIVISION_REF+
83 GSSQL.END;
84
85
86 String short_label = null;
87 try {
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) {
97
98 System.err.println("SQLQuery.MGNum2OID() Error: "+e.getMessage());
99 return null;
100 }
101
102 return GS3OID.createOID(doc_id, short_label);
103 }
104
105 public String OID2MGNum(String oid) {
106 String id = getDocumentMetadata(oid, "gsdl3.mgseqno");
107 return id;
108
109 }
110
111 // does the document have sections, or is it just a single page document?
112 public boolean isHierarchicalDocument(String oid) {
113 // first just check the OID - it may already have section info in it
114 if (!GS3OID.isDocTop(oid)) {
115 return true;
116 }
117
118 // now we need to check the database
119 String query = "select * "+
120 " from "+GSSQL.STRUCTURE_TABLE +
121 " where "+ GSSQL.DOCUMENT_ID +GSSQL.EQUALS_QUOTE + oid + GSSQL.QUOTE +
122 " and "+ GSSQL.STRUCTURE_ID +GSSQL.EQUALS_QUOTE + "Section" + GSSQL.QUOTE +
123 GSSQL.END;
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();
130 } catch (java.sql.SQLException e) {
131 return false;
132 }
133
134 return is_hierarchical;
135 }
136
137 // classifier metadata is not actually metadata
138 public String getClassifierMetadata(String oid, String full_meta_name) {
139
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) {
165
166 System.err.println("SQLQuery.getClassifierMetadata() Error: "+e.getMessage());
167 return null;
168 }
169 return value;
170
171 }
172
173 public ArrayList getDocumentChildren(String oid) {
174
175 ArrayList children = new ArrayList();
176 String doc_id = oid;
177 String section_id = "All"; // if we are just passed a doc id
178 if (!GS3OID.isDocTop(oid)) {
179 doc_id = GS3OID.getDocID(oid);
180 section_id = GS3OID.getSectionLabel(oid);
181 }
182// int sep_index = oid.indexOf(".");
183// if (sep_index != -1) {
184// doc_id = oid.substring(0, sep_index);
185// section_id = oid.substring(sep_index+1);
186// }
187
188 // get the division ref for this section
189 String query = "select "+GSSQL.DIVISION_REF +
190 " from "+GSSQL.DIVISION_TABLE+
191 " where "+GSSQL.DOCUMENT_ID+GSSQL.EQUALS_QUOTE+doc_id+GSSQL.QUOTE+
192 " and "+GSSQL.SHORT_LABEL+GSSQL.EQUALS_QUOTE+section_id+GSSQL.QUOTE+
193 GSSQL.END;
194
195 ResultSet results = null;
196 Statement statement = null;
197 String div_ref = null;
198 try {
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 }
207 } catch (java.sql.SQLException e) {
208
209 System.err.println("SQLQuery.getDocumentChildren() Error1: "+e.getMessage());
210 return null;
211 }
212
213 // now get the list of children
214 query = "select "+GSSQL.SHORT_LABEL +
215 " from "+GSSQL.DIVISION_TABLE+
216 " where "+ GSSQL.DOCUMENT_ID+GSSQL.EQUALS_QUOTE+doc_id+GSSQL.QUOTE+
217 " and "+GSSQL.PARENT_TYPE+GSSQL.EQUALS_QUOTE+GSSQL.DIVISION+GSSQL.QUOTE+
218 " and "+GSSQL.PARENT_REF+GSSQL.EQUALS_QUOTE+div_ref+GSSQL.QUOTE+
219 GSSQL.END;
220 try {
221 results = statement.executeQuery(query);
222 while (results.next()) {
223 String id = results.getString(GSSQL.SHORT_LABEL);
224 children.add(GS3OID.createOID(doc_id, id));
225 }
226 statement.close();
227 } catch (java.sql.SQLException e) {
228
229 System.err.println("SQLQuery.getDocumentChildren() Error2: "+e.getMessage());
230 return null;
231 }
232
233 return children;
234
235 }
236
237 public boolean documentHasChildren(String oid) {
238 // for now, use getChildren, but can we do this a better way??
239 ArrayList children = getDocumentChildren(oid);
240 if (children==null || children.size()==0) {
241 return false;
242 }
243 return true;
244
245 }
246 public ArrayList getClassifierChildren(String oid) {
247
248 ArrayList children = new ArrayList();
249
250 String query = "select "+ GSSQL.CLASSIFIER_ID +
251 " from " + GSSQL.CLASSIFIER_TABLE +
252 " where " + GSSQL.PARENT_ID + GSSQL.EQUALS_QUOTE + oid + GSSQL.QUOTE +
253 " and " + GSSQL.NUM_LEAF_DOCUMENTS + GSSQL.NOT+GSSQL.EQUALS_QUOTE+ "0"+GSSQL.QUOTE +
254 GSSQL.END;
255
256 try {
257 Statement statement = connection.createStatement();
258 ResultSet results = statement.executeQuery(query);
259 while (results.next()) {
260 String id = results.getString(GSSQL.CLASSIFIER_ID);
261 children.add(id);
262 }
263 statement.close();
264 } catch (java.sql.SQLException e) {
265
266 System.err.println("SQLQuery.getClassifierChildren Error: "+e.getMessage());
267 return null;
268 }
269 return children;
270 }
271
272 public ArrayList getClassifierDocChildren(String oid) {
273 ArrayList children = new ArrayList();
274
275 String query = "select "+ GSSQL.CLASS_DOCUMENT_TABLE+GSSQL.DOT+ GSSQL.DOCUMENT_ID+
276 " from " + GSSQL.CLASSIFIER_TABLE + GSSQL.COMMA+GSSQL.CLASS_DOCUMENT_TABLE+
277 " where " + GSSQL.CLASSIFIER_TABLE +GSSQL.DOT+ GSSQL.CLASSIFIER_ID + GSSQL.EQUALS_QUOTE + oid + GSSQL.QUOTE +
278 " and " + GSSQL.CLASSIFIER_TABLE +GSSQL.DOT+ GSSQL.CLASSIFIER_REF+GSSQL.EQUALS+GSSQL.CLASS_DOCUMENT_TABLE+GSSQL.DOT+ GSSQL.CLASSIFIER_REF+
279 " order by "+GSSQL.CLASS_DOCUMENT_TABLE+GSSQL.DOT+GSSQL.DOCUMENT_ORDER+
280 GSSQL.END;
281 try {
282 Statement statement = connection.createStatement();
283 ResultSet results = statement.executeQuery(query);
284 while (results.next()) {
285 String id = results.getString(GSSQL.DOCUMENT_ID);
286 children.add(id);
287 }
288 } catch (java.sql.SQLException e) {
289
290 System.err.println("SQLQuery.getClassifierDocChildren() Error: "+e.getMessage());
291 return null;
292 }
293
294 return children;
295 }
296
297 public String getDocumentMetadata(String oid, String full_meta_name) {
298
299 String doc_id = oid;
300 String section_id = "All"; // if we are just passed a doc id
301 if (!GS3OID.isDocTop(oid)) {
302 doc_id = GS3OID.getDocID(oid);
303 section_id = GS3OID.getSectionLabel(oid);
304 }
305// int sep_index = oid.indexOf(".");
306// if (sep_index != -1) {
307// doc_id = oid.substring(0, sep_index);
308// section_id = oid.substring(sep_index+1);
309// }
310
311
312 // get the metadata block id
313 String query = "select "+
314 GSSQL.DIVISION_METADATA_TABLE+GSSQL.DOT+GSSQL.METADATA_ID +
315 " from " + GSSQL.DIVISION_METADATA_TABLE+GSSQL.COMMA+GSSQL.DIVISION_TABLE+
316 " where " + GSSQL.DIVISION_TABLE+GSSQL.DOT+GSSQL.DIVISION_REF+GSSQL.EQUALS+GSSQL.DIVISION_METADATA_TABLE+GSSQL.DOT+GSSQL.DIVISION_REF+
317 " and " + GSSQL.DIVISION_TABLE+GSSQL.DOT+GSSQL.SHORT_LABEL+GSSQL.EQUALS_QUOTE+section_id+GSSQL.QUOTE+
318 " and " + GSSQL.DIVISION_TABLE+GSSQL.DOT+GSSQL.DOCUMENT_ID+GSSQL.EQUALS_QUOTE+doc_id+GSSQL.QUOTE+
319 " and "+GSSQL.DIVISION_METADATA_TABLE+GSSQL.DOT+GSSQL.DOCUMENT_ID+GSSQL.EQUALS_QUOTE+doc_id+GSSQL.QUOTE+
320 GSSQL.END;
321
322 Statement statement = null;
323 ResultSet results = null;
324 String meta_id = null;
325 try {
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 }
334 } catch (java.sql.SQLException e) {
335
336 System.err.println("SQLQuery.getDocumentMetadata() Error: "+e.getMessage());
337 return null;
338 }
339
340 // now get the list of namespace refs for the right namespace
341 int sep_index = full_meta_name.indexOf('.');
342 String meta_ns = "gsdl3";
343 String meta_name = full_meta_name;
344 if (sep_index != -1) {
345
346 meta_ns = full_meta_name.substring(0, sep_index);
347 meta_name = full_meta_name.substring(sep_index+1);
348 }
349
350 query = "select * "+
351 " from "+GSSQL.NAMESPACE_TABLE+GSSQL.COMMA+GSSQL.METADATA_TABLE+GSSQL.COMMA+GSSQL.METADATA_VALUE_TABLE+
352 " where "+GSSQL.METADATA_TABLE+GSSQL.DOT+GSSQL.METADATA_ID+GSSQL.EQUALS_QUOTE+meta_id+GSSQL.QUOTE+
353 " and "+ GSSQL.METADATA_TABLE+GSSQL.DOT+GSSQL.DOCUMENT_ID+GSSQL.EQUALS_QUOTE+doc_id+GSSQL.QUOTE+
354 " and " + GSSQL.METADATA_TABLE+GSSQL.DOT+GSSQL.METADATA_REF+GSSQL.EQUALS+GSSQL.NAMESPACE_TABLE+GSSQL.DOT+GSSQL.METADATA_REF+
355 " and "+ GSSQL.NAMESPACE_TABLE+GSSQL.DOT+GSSQL.NAMESPACE_TYPE+GSSQL.EQUALS_QUOTE+meta_ns+GSSQL.QUOTE+
356 " and "+GSSQL.NAMESPACE_TABLE+GSSQL.DOT+GSSQL.NAMESPACE_REF+GSSQL.EQUALS+GSSQL.METADATA_VALUE_TABLE+GSSQL.DOT+GSSQL.NAMESPACE_REF+
357 GSSQL.END;
358
359 String meta_value = null;
360 try {
361 statement = connection.createStatement();
362 results = statement.executeQuery(query);
363 while (results.next()) {
364 String m_name = results.getString(GSSQL.LABEL);
365 if (meta_name.equals(m_name)) {
366 meta_value = results.getString(GSSQL.VALUE);
367 break;
368 }
369 }
370 statement.close();
371 } catch (java.sql.SQLException e) {
372
373 System.err.println("SQLQuery.getDocumentMetadata() Error: "+e.getMessage());
374 return null;
375 }
376
377 return meta_value;
378 }
379 public static void main (String [] args) {
380
381 SQLQuery self = new SQLQuery();
382 self.setDatabase("gs3test");
383 System.out.println("getting oid for mg num 3");
384 String oid = self.MGNum2OID("stx.3");
385 System.out.println("oid was "+oid);
386
387 System.out.println("title metadata for mg num 3:");
388 String meta = self.getDocumentMetadata(oid, "gsdl3.title");
389 if (meta != null) {System.out.println(meta);}
390 System.out.println("subject metadata for mg num 3:");
391 meta = self.getDocumentMetadata(oid, "gsdl3.Subject");
392 if (meta != null) {System.out.println(meta);}
393
394 System.out.println("converting back to mg, oid "+oid);
395 String id = self.OID2MGNum(oid);
396 System.out.println(""+id);
397
398 System.out.println("getting oid for mg num 8");
399 oid = self.MGNum2OID("stx.8");
400 System.out.println("oid was "+oid);
401
402 System.out.println("title metadata for mg num 8:");
403 meta = self.getDocumentMetadata(oid, "gsdl3.title");
404 if (meta != null) {System.out.println(meta);}
405 System.out.println("subject metadata for mg num 8:");
406 meta = self.getDocumentMetadata(oid, "gsdl3.Subject");
407 if (meta != null) {System.out.println(meta);}
408
409 System.out.println("converting back to mg, oid "+oid);
410 id = self.OID2MGNum(oid);
411 System.out.println(""+id);
412 }
413
414
415
416
417}
Note: See TracBrowser for help on using the repository browser.