001: /* Copyright (c) 1995-2000, The Hypersonic SQL Group.
002: * All rights reserved.
003: *
004: * Redistribution and use in source and binary forms, with or without
005: * modification, are permitted provided that the following conditions are met:
006: *
007: * Redistributions of source code must retain the above copyright notice, this
008: * list of conditions and the following disclaimer.
009: *
010: * Redistributions in binary form must reproduce the above copyright notice,
011: * this list of conditions and the following disclaimer in the documentation
012: * and/or other materials provided with the distribution.
013: *
014: * Neither the name of the Hypersonic SQL Group nor the names of its
015: * contributors may be used to endorse or promote products derived from this
016: * software without specific prior written permission.
017: *
018: * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
019: * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
020: * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
021: * ARE DISCLAIMED. IN NO EVENT SHALL THE HYPERSONIC SQL GROUP,
022: * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
023: * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
024: * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
025: * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
026: * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
027: * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
028: * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
029: *
030: * This software consists of voluntary contributions made by many individuals
031: * on behalf of the Hypersonic SQL Group.
032: *
033: *
034: * For work added by the HSQL Development Group:
035: *
036: * Copyright (c) 2001-2005, The HSQL Development Group
037: * All rights reserved.
038: *
039: * Redistribution and use in source and binary forms, with or without
040: * modification, are permitted provided that the following conditions are met:
041: *
042: * Redistributions of source code must retain the above copyright notice, this
043: * list of conditions and the following disclaimer.
044: *
045: * Redistributions in binary form must reproduce the above copyright notice,
046: * this list of conditions and the following disclaimer in the documentation
047: * and/or other materials provided with the distribution.
048: *
049: * Neither the name of the HSQL Development Group nor the names of its
050: * contributors may be used to endorse or promote products derived from this
051: * software without specific prior written permission.
052: *
053: * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
054: * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
055: * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
056: * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
057: * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
058: * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
059: * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
060: * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
061: * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
062: * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
063: * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
064: */
065:
066: package org.hsqldb.util;
067:
068: import java.applet.Applet;
069: import java.io.BufferedReader;
070: import java.io.FileReader;
071: import java.io.IOException;
072: import java.sql.Connection;
073: import java.sql.DriverManager;
074: import java.sql.ResultSet;
075: import java.sql.ResultSetMetaData;
076: import java.sql.SQLException;
077: import java.sql.Statement;
078: import java.util.Properties;
079: import java.awt.BorderLayout;
080: import java.awt.Button;
081: import java.awt.Choice;
082: import java.awt.Event;
083: import java.awt.Font;
084: import java.awt.Frame;
085: import java.awt.Label;
086: import java.awt.Menu;
087: import java.awt.MenuBar;
088: import java.awt.Panel;
089: import java.awt.SystemColor;
090: import java.awt.TextArea;
091: import java.awt.event.ActionEvent;
092: import java.awt.event.ActionListener;
093: import java.awt.event.WindowEvent;
094: import java.awt.event.WindowListener;
095:
096: import org.hsqldb.lib.java.JavaSystem;
097:
098: /**
099: * Simple demonstration applet
100: *
101: *
102: * @author Thomas Mueller (Hypersonic SQL Group)
103: * @version 1.7.0
104: * @since Hypersonic SQL
105: */
106: public class QueryTool extends Applet implements WindowListener,
107: ActionListener {
108:
109: static Properties pProperties = new Properties();
110: boolean bApplication;
111:
112: /**
113: * You can start QueryTool without a browser and applet
114: * using using this method. Type 'java QueryTool' to start it.
115: * This is necessary if you want to use the standalone version
116: * because appletviewer and internet browers do not allow the
117: * applet to write to disk.
118: */
119: static Frame fMain;
120:
121: /**
122: * Method declaration
123: *
124: *
125: * @param arg
126: */
127: public static void main(String[] arg) {
128:
129: fMain = new Frame("Query Tool");
130:
131: QueryTool q = new QueryTool();
132:
133: q.bApplication = true;
134:
135: for (int i = 0; i < arg.length; i++) {
136: String p = arg[i];
137:
138: if (p.equals("-?")) {
139: printHelp();
140: }
141:
142: if (p.charAt(0) == '-') {
143: pProperties.put(p.substring(1), arg[i + 1]);
144:
145: i++;
146: }
147: }
148:
149: q.init();
150: q.start();
151: fMain.add("Center", q);
152:
153: MenuBar menu = new MenuBar();
154: Menu file = new Menu("File");
155:
156: file.add("Exit");
157: file.addActionListener(q);
158: menu.add(file);
159: fMain.setMenuBar(menu);
160: fMain.setSize(500, 400);
161: fMain.show();
162: fMain.addWindowListener(q);
163: }
164:
165: Connection cConn;
166: Statement sStatement;
167:
168: /**
169: * Initializes the window and the database and inserts some test data.
170: */
171: public void init() {
172:
173: initGUI();
174:
175: Properties p = pProperties;
176:
177: if (!bApplication) {
178:
179: // default for applets is in-memory (.)
180: p.put("database", ".");
181:
182: try {
183:
184: // but it may be also a HTTP connection (http://)
185: // try to use url as provided on the html page as parameter
186: pProperties.put("database", getParameter("database"));
187: } catch (Exception e) {
188: }
189: }
190:
191: String driver = p
192: .getProperty("driver", "org.hsqldb.jdbcDriver");
193: String url = p.getProperty("url", "jdbc:hsqldb:");
194: String database = p.getProperty("database", ".");
195: String user = p.getProperty("user", "sa");
196: String password = p.getProperty("password", "");
197: boolean test = p.getProperty("test", "true").equalsIgnoreCase(
198: "true");
199: boolean log = p.getProperty("log", "true").equalsIgnoreCase(
200: "true");
201:
202: try {
203: if (log) {
204: trace("driver =" + driver);
205: trace("url =" + url);
206: trace("database=" + database);
207: trace("user =" + user);
208: trace("password=" + password);
209: trace("test =" + test);
210: trace("log =" + log);
211: JavaSystem.setLogToSystem(true);
212: }
213:
214: // As described in the JDBC FAQ:
215: // http://java.sun.com/products/jdbc/jdbc-frequent.html;
216: // Why doesn't calling class.forName() load my JDBC driver?
217: // There is a bug in the JDK 1.1.x that can cause Class.forName() to fail.
218: // new org.hsqldb.jdbcDriver();
219: Class.forName(driver).newInstance();
220:
221: cConn = DriverManager.getConnection(url + database, user,
222: password);
223: } catch (Exception e) {
224: System.out.println("QueryTool.init: " + e.getMessage());
225: e.printStackTrace();
226: }
227:
228: sRecent = new String[iMaxRecent];
229: iRecent = 0;
230:
231: try {
232: sStatement = cConn.createStatement();
233: } catch (SQLException e) {
234: System.out.println("Exception: " + e);
235: }
236:
237: if (test) {
238: insertTestData();
239: }
240:
241: txtCommand.requestFocus();
242: }
243:
244: /**
245: * Method declaration
246: *
247: *
248: * @param s
249: */
250: void trace(String s) {
251: System.out.println(s);
252: }
253:
254: /**
255: * This is function handles the events when a button is clicked or
256: * when the used double-clicked on the listbox of recent commands.
257: */
258: public boolean action(Event evt, Object arg) {
259:
260: String s = arg.toString();
261:
262: if (s.equals("Execute")) {
263: String sCmd = txtCommand.getText();
264: String[] g = new String[1];
265:
266: try {
267: sStatement.execute(sCmd);
268:
269: int r = sStatement.getUpdateCount();
270:
271: if (r == -1) {
272: formatResultSet(sStatement.getResultSet());
273: } else {
274: g[0] = "update count";
275:
276: gResult.setHead(g);
277:
278: g[0] = String.valueOf(r);
279:
280: gResult.addRow(g);
281: }
282:
283: setRecent(txtCommand.getText());
284: } catch (SQLException e) {
285: g[0] = "SQL Error";
286:
287: gResult.setHead(g);
288:
289: g[0] = e.getMessage();
290:
291: gResult.addRow(g);
292: }
293:
294: gResult.repaint();
295: txtCommand.selectAll();
296: txtCommand.requestFocus();
297: } else if (s.equals("Script")) {
298: String sScript = getScript();
299:
300: txtCommand.setText(sScript);
301: txtCommand.selectAll();
302: txtCommand.requestFocus();
303: } else if (s.equals("Import")) {
304: String sImport = getImport();
305:
306: txtCommand.setText(sImport);
307: txtCommand.selectAll();
308: txtCommand.requestFocus();
309: } else if (s.equals("Exit")) {
310: System.exit(0);
311: } else { // recent
312: txtCommand.setText(s);
313: }
314:
315: return true;
316: }
317:
318: /**
319: * Method declaration
320: *
321: *
322: * @param r
323: */
324: void formatResultSet(ResultSet r) {
325:
326: try {
327: ResultSetMetaData m = r.getMetaData();
328: int col = m.getColumnCount();
329: String[] h = new String[col];
330:
331: for (int i = 1; i <= col; i++) {
332: h[i - 1] = m.getColumnLabel(i);
333: }
334:
335: gResult.setHead(h);
336:
337: while (r.next()) {
338: for (int i = 1; i <= col; i++) {
339: h[i - 1] = r.getString(i);
340:
341: if (r.wasNull()) {
342: h[i - 1] = "(null)";
343: }
344: }
345:
346: gResult.addRow(h);
347: }
348: } catch (SQLException e) {
349: }
350: }
351:
352: /**
353: * Method declaration
354: *
355: *
356: * @return
357: */
358: String getScript() {
359:
360: ResultSet rResult = null;
361:
362: try {
363: rResult = sStatement.executeQuery("SCRIPT");
364:
365: StringBuffer a = new StringBuffer();
366:
367: while (rResult.next()) {
368: a.append(rResult.getString(1));
369: a.append('\n');
370: }
371:
372: a.append('\n');
373:
374: return a.toString();
375: } catch (SQLException e) {
376: return "";
377: } finally {
378: if (rResult != null) {
379: try {
380: rResult.close();
381: } catch (Exception e) {
382: }
383: }
384: }
385: }
386:
387: /**
388: * Method declaration
389: *
390: *
391: * @return
392: */
393: String getImport() {
394:
395: StringBuffer a = new StringBuffer();
396: String filename = "import.sql";
397: BufferedReader in = null;
398:
399: try {
400: in = new BufferedReader(new FileReader(filename));
401:
402: String line;
403:
404: while ((line = in.readLine()) != null) {
405: a.append(line);
406: a.append('\n');
407: }
408:
409: a.append('\n');
410: in.close();
411:
412: return a.toString();
413: } catch (Exception e) {
414: if (in != null) {
415: try {
416: in.close();
417: } catch (IOException e1) {
418: }
419: }
420:
421: return "";
422: }
423: }
424:
425: /**
426: * Adds a String to the Listbox of recent commands.
427: */
428: private void setRecent(String s) {
429:
430: for (int i = 0; i < iMaxRecent; i++) {
431: if (s.equals(sRecent[i])) {
432: return;
433: }
434: }
435:
436: if (sRecent[iRecent] != null) {
437: choRecent.remove(sRecent[iRecent]);
438: }
439:
440: sRecent[iRecent] = s;
441: iRecent = (iRecent + 1) % iMaxRecent;
442:
443: choRecent.addItem(s);
444: }
445:
446: String[] sRecent;
447: static int iMaxRecent = 24;
448: int iRecent;
449: TextArea txtCommand;
450: Button butExecute, butScript;
451: Button butImport;
452: Choice choRecent;
453: Grid gResult;
454:
455: /**
456: * Create the graphical user interface. This is AWT code.
457: */
458: private void initGUI() {
459:
460: // all panels
461: Panel pQuery = new Panel();
462: Panel pCommand = new Panel();
463: Panel pButton = new Panel();
464: Panel pRecent = new Panel();
465: Panel pResult = new Panel();
466: Panel pBorderWest = new Panel();
467: Panel pBorderEast = new Panel();
468: Panel pBorderSouth = new Panel();
469:
470: pQuery.setLayout(new BorderLayout());
471: pCommand.setLayout(new BorderLayout());
472: pButton.setLayout(new BorderLayout());
473: pRecent.setLayout(new BorderLayout());
474: pResult.setLayout(new BorderLayout());
475: pBorderWest.setBackground(SystemColor.control);
476: pBorderSouth.setBackground(SystemColor.control);
477: pBorderEast.setBackground(SystemColor.control);
478:
479: // labels
480: Label lblCommand = new Label(" Command", Label.LEFT);
481: Label lblRecent = new Label(" Recent", Label.LEFT);
482: Label lblResult = new Label(" Result", Label.LEFT);
483:
484: lblCommand.setBackground(SystemColor.control);
485: lblRecent.setBackground(SystemColor.control);
486: lblResult.setBackground(SystemColor.control);
487:
488: // buttons
489: butExecute = new Button("Execute");
490: butScript = new Button("Script");
491: butImport = new Button("Import");
492:
493: pButton.add("South", butScript);
494: pButton.add("Center", butExecute);
495: pButton.add("North", butImport);
496:
497: // command - textarea
498: Font fFont = new Font("Dialog", Font.PLAIN, 12);
499:
500: txtCommand = new TextArea(5, 40);
501:
502: txtCommand.setFont(fFont);
503:
504: // recent - choice
505: choRecent = new Choice();
506:
507: // result - grid
508: gResult = new Grid();
509:
510: // combine it
511: setLayout(new BorderLayout());
512: pRecent.add("Center", choRecent);
513: pRecent.add("North", lblRecent);
514: pCommand.add("North", lblCommand);
515: pCommand.add("East", pButton);
516: pCommand.add("Center", txtCommand);
517: pCommand.add("South", pRecent);
518: pResult.add("North", lblResult);
519: pResult.add("Center", gResult);
520: pQuery.add("North", pCommand);
521: pQuery.add("Center", pResult);
522: add("Center", pQuery);
523: add("West", pBorderWest);
524: add("East", pBorderEast);
525: add("South", pBorderSouth);
526:
527: // fredt@users 20011210 - patch 450412 by elise@users
528: doLayout();
529: }
530:
531: static String[] sTestData = {
532: "drop table Place if exists",
533: "create table Place (Code integer,Name varchar(255))",
534: "create index iCode on Place (Code)",
535: "delete from place",
536: "insert into Place values (4900,'Langenthal')",
537: "insert into Place values (8000,'Zurich')",
538: "insert into Place values (3000,'Berne')",
539: "insert into Place values (1200,'Geneva')",
540: "insert into Place values (6900,'Lugano')",
541: "drop table Customer if exists",
542: "create table Customer (Nr integer,Name varchar(255),Place integer)",
543: "create index iNr on Customer (Nr)",
544: "delete from Customer",
545: "insert into Customer values (1,'Meier',3000)",
546: "insert into Customer values (2,'Mueller',8000)",
547: "insert into Customer values (3,'Devaux',1200)",
548: "insert into Customer values (4,'Rossi',6900)",
549: "insert into Customer values (5,'Rickli',3000)",
550: "insert into Customer values (6,'Graf',3000)",
551: "insert into Customer values (7,'Mueller',4900)",
552: "insert into Customer values (8,'May',1200)",
553: "insert into Customer values (9,'Berger',8000)",
554: "insert into Customer values (10,'D''Ascoli',6900)",
555: "insert into Customer values (11,'Padruz',1200)",
556: "insert into Customer values (12,'Hug',4900)" };
557:
558: /**
559: * Method declaration
560: *
561: */
562: void insertTestData() {
563:
564: for (int i = 0; i < sTestData.length; i++) {
565: try {
566: sStatement.executeQuery(sTestData[i]);
567: } catch (SQLException e) {
568: System.out.println("Exception: " + e);
569: }
570: }
571:
572: setRecent("select * from place");
573: setRecent("select * from Customer");
574: setRecent("select * from Customer where place<>3000");
575: setRecent("select * from place where code>3000 or code=1200");
576: setRecent("select * from Customer where nr<=8\nand name<>'Mueller'");
577: setRecent("update Customer set name='Russi'\nwhere name='Rossi'");
578: setRecent("delete from Customer where place=8000");
579: setRecent("insert into place values(3600,'Thun')");
580: setRecent("drop index Customer.iNr");
581: setRecent("select * from Customer where name like '%e%'");
582: setRecent("select count(*),min(code),max(code),sum(code) from place");
583:
584: String s = "select * from Customer,place\n"
585: + "where Customer.place=place.code\n"
586: + "and place.name='Berne'";
587:
588: setRecent(s);
589: txtCommand.setText(s);
590: txtCommand.selectAll();
591: }
592:
593: /**
594: * Method declaration
595: *
596: */
597: static void printHelp() {
598:
599: System.out
600: .println("Usage: java QueryTool [-options]\n"
601: + "where options include:\n"
602: + " -driver <classname> name of the driver class\n"
603: + " -url <name> first part of the jdbc url\n"
604: + " -database <name> second part of the jdbc url\n"
605: + " -user <name> username used for connection\n"
606: + " -password <name> password for this user\n"
607: + " -test <true/false> insert test data\n"
608: + " -log <true/false> write log to system out");
609: System.exit(0);
610: }
611:
612: /**
613: * Method declaration
614: *
615: *
616: * @param e
617: */
618: public void windowActivated(WindowEvent e) {
619: }
620:
621: /**
622: * Method declaration
623: *
624: *
625: * @param e
626: */
627: public void windowDeactivated(WindowEvent e) {
628: }
629:
630: /**
631: * Method declaration
632: *
633: *
634: * @param e
635: */
636: public void windowClosed(WindowEvent e) {
637: }
638:
639: /**
640: * Method declaration
641: *
642: *
643: * @param ev
644: */
645: public void windowClosing(WindowEvent ev) {
646:
647: try {
648: cConn.close();
649: } catch (Exception e) {
650: }
651:
652: if (fMain != null) {
653: fMain.dispose();
654: }
655:
656: System.exit(0);
657: }
658:
659: /**
660: * Method declaration
661: *
662: *
663: * @param e
664: */
665: public void windowDeiconified(WindowEvent e) {
666: }
667:
668: /**
669: * Method declaration
670: *
671: *
672: * @param e
673: */
674: public void windowIconified(WindowEvent e) {
675: }
676:
677: /**
678: * Method declaration
679: *
680: *
681: * @param e
682: */
683: public void windowOpened(WindowEvent e) {
684: }
685:
686: /**
687: * Method declaration
688: *
689: *
690: * @param ev
691: */
692: public void actionPerformed(ActionEvent ev) {
693:
694: String s = ev.getActionCommand();
695:
696: if (s != null && s.equals("Exit")) {
697: windowClosing(null);
698: }
699: }
700: }
|