001: /*
002: ** $Id: QueryModel.java,v 1.9 2000/10/26 08:34:15 mrw Exp $
003: **
004: ** Mike Wilson, July 2000, mrw@whisperingwind.co.uk
005: **
006: ** (C) Copyright 2000, Mike Wilson, Reading, Berkshire, UK
007: **
008: ** This program is free software; you can redistribute it and/or modify
009: ** it under the terms of the GNU General Public License as published by
010: ** the Free Software Foundation; either version 2 of the License, or
011: ** (at your option) any later version.
012: **
013: ** This program is distributed in the hope that it will be useful,
014: ** but WITHOUT ANY WARRANTY; without even the implied warranty of
015: ** MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
016: ** GNU General Public License for more details.
017: **
018: ** You should have received a copy of the GNU Library General
019: ** Public License along with this library; if not, write to the
020: ** Free Software Foundation, Inc., 59 Temple Place - Suite 330,
021: ** Boston, MA 02111-1307 USA.
022: */
023:
024: package uk.co.whisperingwind.vienna;
025:
026: import java.io.BufferedReader;
027: import java.io.BufferedWriter;
028: import java.io.File;
029: import java.io.FileNotFoundException;
030: import java.io.FileOutputStream;
031: import java.io.FileReader;
032: import java.io.FileWriter;
033: import java.io.IOException;
034: import java.io.PrintStream;
035: import java.io.PrintWriter;
036: import java.sql.Connection;
037: import java.sql.PreparedStatement;
038: import java.sql.ResultSet;
039: import java.sql.ResultSetMetaData;
040: import java.sql.SQLException;
041: import java.sql.Types;
042: import java.util.StringTokenizer;
043: import java.util.Vector;
044: import javax.swing.JOptionPane;
045: import uk.co.whisperingwind.framework.Dialogs;
046: import uk.co.whisperingwind.framework.ExceptionDialog;
047: import uk.co.whisperingwind.framework.Model;
048: import uk.co.whisperingwind.framework.SwingThread;
049: import uk.co.whisperingwind.framework.VectorTableModel;
050:
051: /**
052: ** Model for the input and output of a query. Contains an
053: ** AbstractTableModel, which can be used as the data model for a
054: ** JTable. This class also executes the query. If I could work out
055: ** how, this would also be able to tell the query view where in a
056: ** query an error occurred.
057: */
058:
059: class QueryModel extends Model {
060: private Connection connection;
061: private String sql = "";
062: private String fileName = "";
063: private boolean dirty = false;
064: private VectorTableModel tableModel = new VectorTableModel();
065: private ConfigModel configModel = null;
066: private ExecuteWorker executeWorker = null;
067: private int columnType[] = null;
068:
069: public QueryModel(ConfigModel config) {
070: super ();
071: configModel = config;
072: }
073:
074: /*
075: ** The text of the query has changed -- a "save" is needed.
076: */
077:
078: public void setDirty(boolean state) {
079: dirty = state;
080: }
081:
082: /*
083: ** Returns true if a "save" is needed.
084: */
085:
086: public boolean isDirty() {
087: return dirty;
088: }
089:
090: /*
091: ** Set my SQL statement and notify any listeners.
092: */
093:
094: public void setSQL(String newSql, Object initiator) {
095: sql = newSql;
096: setDirty(true);
097: fireEvent(initiator, "sql", sql);
098: }
099:
100: /*
101: ** This accessor returns the unedited SQL, unlike cleanSQL which
102: ** strips out comment lines.
103: */
104:
105: public String getSQL() {
106: return sql;
107: }
108:
109: /*
110: ** Change my file name.
111: */
112:
113: public void setFileName(String newName) {
114: File file = new File(newName);
115: String path = file.getAbsolutePath();
116: fileName = path;
117: fireEvent(this , "name", fileName);
118: }
119:
120: public String getFileName() {
121: return fileName;
122: }
123:
124: /*
125: ** Load the named file into my SQL statement. Note that this calls
126: ** setSQL () which will notify listeners.
127: */
128:
129: public boolean openFile(String newName) {
130: boolean opened = false;
131: byte buffer[] = new byte[1024];
132: File file = new File(newName);
133:
134: try {
135: BufferedReader in = new BufferedReader(new FileReader(
136: newName));
137: String newSql = "";
138: String line = in.readLine();
139:
140: while (line != null) {
141: if (newSql.length() > 0)
142: newSql += "\n";
143:
144: newSql += line;
145: line = in.readLine();
146: }
147:
148: in.close();
149: opened = true;
150: setSQL(newSql, this );
151: setFileName(newName);
152: setDirty(false);
153: } catch (java.io.FileNotFoundException ex) {
154: new ExceptionDialog(ex);
155: } catch (java.io.IOException ex) {
156: new ExceptionDialog(ex);
157: }
158:
159: return opened;
160: }
161:
162: /*
163: ** Save the query with the name I used before.
164: */
165:
166: public boolean saveFile() {
167: return writeFile(fileName);
168: }
169:
170: /*
171: ** Save the query with a new name.
172: */
173:
174: public boolean saveFileAs(String newName) {
175: boolean result = true;
176: File theFile = new File(newName);
177:
178: if (theFile.exists()) {
179: result = false;
180: int reply = JOptionPane.showConfirmDialog(null, theFile
181: .getName()
182: + " already exists. Overwrite it?", "File exists",
183: JOptionPane.YES_NO_OPTION);
184:
185: if (reply == JOptionPane.YES_OPTION)
186: result = true;
187: }
188:
189: if (result)
190: result = writeFile(newName);
191:
192: return result;
193: }
194:
195: /**
196: ** Returns the table model created by executing the query.
197: */
198:
199: public VectorTableModel getTableModel() {
200: return tableModel;
201: }
202:
203: /*
204: ** Execute the query. Updates the names and rows Vectors and
205: ** notifies any listeners.
206: */
207:
208: public void execute(Connection c) {
209: if (executeWorker != null)
210: executeWorker.interrupt();
211:
212: connection = c;
213:
214: executeWorker = new ExecuteWorker();
215: executeWorker.start();
216: }
217:
218: /*
219: ** Cancel an executing query. Doesn't return until the thread
220: ** running the query completes.
221: */
222:
223: public void cancelExecute() {
224: if (executeWorker != null)
225: executeWorker.interrupt();
226: }
227:
228: public boolean isExecuting() {
229: boolean executing = false;
230:
231: if (executeWorker != null)
232: executing = !executeWorker.isInterrupted();
233:
234: return executing;
235: }
236:
237: public boolean canExport() {
238: return !isExecuting() && columnType != null
239: && tableModel.getRowCount() > 0;
240: }
241:
242: public int exportResult(String fileName, String separator,
243: String quoteString, boolean withQuotes, boolean withTitles) {
244: int result = 0;
245: File theFile = new File(fileName);
246:
247: if (theFile.exists()) {
248: int reply = JOptionPane.showConfirmDialog(null, fileName
249: + " already exists. Overwrite it?", "File exists",
250: JOptionPane.YES_NO_OPTION);
251:
252: if (reply == JOptionPane.YES_OPTION) {
253: if (doExport(fileName, separator, quoteString,
254: withQuotes, withTitles)) {
255: result = tableModel.getRowCount();
256: }
257: }
258: } else {
259: if (doExport(fileName, separator, quoteString, withQuotes,
260: withTitles)) {
261: result = tableModel.getRowCount();
262: }
263: }
264:
265: return result;
266: }
267:
268: private boolean doExport(String fileName, String separator,
269: String quoteString, boolean withQuotes, boolean withTitles) {
270: boolean exported = true;
271:
272: try {
273: int columnCount = tableModel.getColumnCount();
274: boolean[] quotes = new boolean[columnCount];
275:
276: /*
277: ** If quotes are requested, find out which columns should
278: ** be quoted. Otherwise, all entries in quotes will be
279: ** false.
280: */
281:
282: if (withQuotes) {
283: for (int i = 0; i < columnCount; i++) {
284: switch (columnType[i]) {
285: case Types.CHAR:
286: case Types.LONGVARBINARY:
287: case Types.LONGVARCHAR:
288: case Types.OTHER:
289: case Types.REF:
290: case Types.STRUCT:
291: case Types.VARBINARY:
292: case Types.VARCHAR:
293: quotes[i] = true;
294: break;
295:
296: default:
297: break;
298: }
299: }
300: }
301:
302: FileOutputStream os = new FileOutputStream(fileName);
303: PrintStream out = new PrintStream(os);
304:
305: /*
306: ** If titles are requested, write those now.
307: */
308:
309: if (withTitles) {
310: String line = "";
311:
312: for (int column = 0; column < columnCount; column++) {
313: if (column > 0)
314: line += separator;
315:
316: line += quoteString;
317: line += tableModel.getColumnName(column);
318: line += quoteString;
319: }
320:
321: out.println(line);
322: }
323:
324: /*
325: ** Get the values for each row from the table model...
326: */
327:
328: int rowCount = tableModel.getRowCount();
329:
330: for (int row = 0; row < rowCount; row++) {
331: String line = "";
332:
333: for (int column = 0; column < columnCount; column++) {
334: if (column > 0)
335: line += separator;
336:
337: if (quotes[column])
338: line += quoteString;
339:
340: line += tableModel.getValueAt(row, column);
341:
342: if (quotes[column])
343: line += quoteString;
344: }
345:
346: out.println(line);
347: Thread.yield();
348: }
349:
350: out.close();
351: } catch (FileNotFoundException ex) {
352: exported = false;
353: Dialogs.showError("Cannot open output file", ex
354: .getMessage());
355: }
356:
357: return exported;
358: }
359:
360: /*
361: ** Get the SQL for the query. This strips out any lines with a
362: ** leading "--" (which is taken to mean a line starting "--" is a
363: ** comment).
364: */
365:
366: private String cleanSQL() {
367: String stripped = "";
368: StringTokenizer tokenizer = new StringTokenizer(sql, "\n");
369:
370: while (tokenizer.hasMoreTokens()) {
371: String line = tokenizer.nextToken();
372:
373: /*
374: ** Strip off leading spaces before testing for a leading
375: ** "--".
376: */
377:
378: String temp = line;
379: temp.trim();
380:
381: if (temp.indexOf("--") == 0)
382: stripped = stripped + "\n";
383: else
384: stripped = stripped + line + "\n";
385: }
386:
387: return stripped;
388: }
389:
390: /*
391: ** Write the contents of my query to the named file.
392: */
393:
394: private boolean writeFile(String path) {
395: boolean saved = false;
396:
397: try {
398: PrintWriter out = new PrintWriter(new BufferedWriter(
399: new FileWriter(path)));
400: out.write(sql, 0, sql.length());
401: out.close();
402: saved = true;
403: setFileName(path);
404: setDirty(false);
405: } catch (IOException ex) {
406: new ExceptionDialog(ex);
407: }
408:
409: return saved;
410: }
411:
412: /**
413: ** Thread to execute the query and load the results into an
414: ** AbstractTableModel. Note that no one else knows about the new
415: ** model until construct () has completed and finished () has
416: ** run. finished () arranges for listeners to be notified in the
417: ** event thread, so Swing components are updated correctly.
418: */
419:
420: private class ExecuteWorker extends SwingThread {
421: private VectorTableModel newTableModel = new VectorTableModel();
422:
423: public void construct() {
424: int rowCount = 0;
425: waitEvent(QueryModel.this , "execute", "begin");
426: String resultType = "query";
427:
428: try {
429: waitEvent(QueryModel.this , "status", "Executing query");
430:
431: /*
432: ** This uses a PreparedStatement so I can get the
433: ** ResultSet and the ResultSetMetaData AND use execute ()
434: ** rather than executeQuery (). That means I can also
435: ** handle update/insert/delete.
436: */
437:
438: PreparedStatement statement = connection
439: .prepareStatement(cleanSQL());
440: statement.setMaxRows(configModel.getMaxRows());
441:
442: if (statement.execute()) {
443: ResultSet resultSet = statement.getResultSet();
444: ResultSetMetaData metaData = resultSet
445: .getMetaData();
446: waitEvent(QueryModel.this , "status",
447: "Fetching results");
448:
449: /*
450: ** Keep track of the column types -- needed later
451: ** for export etc.
452: */
453:
454: columnType = new int[metaData.getColumnCount()];
455:
456: /*
457: ** Build the names Vector.
458: */
459:
460: for (int i = 0; i < metaData.getColumnCount()
461: && !stopped; i++) {
462: Thread.yield();
463: columnType[i] = metaData.getColumnType(i + 1);
464: String columnName = metaData
465: .getColumnLabel(i + 1);
466:
467: newTableModel.addName(columnName.toLowerCase(),
468: metaData.getColumnDisplaySize(i + 1));
469: }
470:
471: /*
472: ** Build the rows Vector.
473: */
474:
475: while (resultSet.next() && !stopped) {
476: Thread.yield();
477: Vector row = newTableModel.addRow();
478:
479: for (int i = 0; i < metaData.getColumnCount(); i++)
480: row.add(resultSet.getString(i + 1));
481:
482: rowCount++;
483: }
484: } else {
485: /*
486: ** Query was probably update/insert/delete.
487: */
488:
489: rowCount = statement.getUpdateCount();
490: resultType = "update";
491:
492: if (rowCount > 0)
493: waitEvent(QueryModel.this , "updated", "");
494: }
495:
496: statement.close();
497:
498: if (rowCount == configModel.getMaxRows()) {
499: /*
500: ** If the query returned exactly my row limit, there could
501: ** be more available...
502: */
503:
504: Dialogs
505: .showWarning(
506: "Warning",
507: "The number of rows returned exactly matches your\n"
508: + "maximum rows. There are probably more rows to the\n"
509: + "query than are displayed here.");
510: }
511: } catch (SQLException ex) {
512: if (!stopped) {
513: /*
514: ** Error in the query. I would _really_ like to find out
515: ** exactly where in the statement the error occurred.
516: ** Anyone know how to get JDBC to tell me that?
517: */
518:
519: Dialogs.showError("SQL Error", ex.getMessage());
520: }
521: } finally {
522: /*
523: ** Notify observers that the names and rows have changed.
524: */
525:
526: if (stopped) {
527: waitEvent(QueryModel.this , "status",
528: "Query interrupted");
529: } else {
530: waitEvent(QueryModel.this , resultType, new Integer(
531: rowCount).toString());
532: }
533: }
534: }
535:
536: /**
537: ** Query has completed and the new table model is ready to be
538: ** used to populate a JTable. This runs in the event thread,
539: ** so I can safely change the tableModel.
540: */
541:
542: public void finished() {
543: executeWorker = null;
544: tableModel = newTableModel;
545: fireEvent(QueryModel.this , "execute", "end");
546: }
547: }
548: }
|