|
1
65
66
67 package org.hsqldb.util;
68
69 import java.applet.Applet  ;
70 import java.io.BufferedReader ;
71 import java.io.FileReader ;
72 import java.io.IOException ;
73 import java.sql.Connection ;
74 import java.sql.DriverManager ;
75 import java.sql.ResultSet ;
76 import java.sql.ResultSetMetaData ;
77 import java.sql.SQLException ;
78 import java.sql.Statement ;
79 import java.util.Properties ;
80 import java.awt.BorderLayout ;
81 import java.awt.Button ;
82 import java.awt.Choice ;
83 import java.awt.Event ;
84 import java.awt.Font ;
85 import java.awt.Frame ;
86 import java.awt.Label ;
87 import java.awt.Menu ;
88 import java.awt.MenuBar ;
89 import java.awt.Panel ;
90 import java.awt.SystemColor ;
91 import java.awt.TextArea ;
92 import java.awt.event.ActionEvent ;
93 import java.awt.event.ActionListener ;
94 import java.awt.event.WindowEvent ;
95 import java.awt.event.WindowListener ;
96
97 import org.hsqldb.lib.java.JavaSystem;
98
99
107 public class QueryTool extends Applet
108 implements WindowListener , ActionListener {
109
110 static Properties pProperties = new Properties ();
111 boolean bApplication;
112
113
120 static Frame fMain;
121
122
128 public static void main(String [] arg) {
129
130 fMain = new Frame ("Query Tool");
131
132 QueryTool q = new QueryTool();
133
134 q.bApplication = true;
135
136 for (int i = 0; i < arg.length; i++) {
137 String p = arg[i];
138
139 if (p.equals("-?")) {
140 printHelp();
141 }
142
143 if (p.charAt(0) == '-') {
144 pProperties.put(p.substring(1), arg[i + 1]);
145
146 i++;
147 }
148 }
149
150 q.init();
151 q.start();
152 fMain.add("Center", q);
153
154 MenuBar menu = new MenuBar ();
155 Menu file = new Menu ("File");
156
157 file.add("Exit");
158 file.addActionListener(q);
159 menu.add(file);
160 fMain.setMenuBar(menu);
161 fMain.setSize(500, 400);
162 fMain.show();
163 fMain.addWindowListener(q);
164 }
165
166 Connection cConn;
167 Statement sStatement;
168
169
172 public void init() {
173
174 initGUI();
175
176 Properties p = pProperties;
177
178 if (!bApplication) {
179
180 p.put("database", ".");
182
183 try {
184
185 pProperties.put("database", getParameter("database"));
188 } catch (Exception e) {}
189 }
190
191 String driver = p.getProperty("driver", "org.hsqldb.jdbcDriver");
192 String url = p.getProperty("url", "jdbc:hsqldb:");
193 String database = p.getProperty("database", ".");
194 String user = p.getProperty("user", "sa");
195 String password = p.getProperty("password", "");
196 boolean test = p.getProperty("test", "true").equalsIgnoreCase("true");
197 boolean log = p.getProperty("log", "true").equalsIgnoreCase("true");
198
199 try {
200 if (log) {
201 trace("driver =" + driver);
202 trace("url =" + url);
203 trace("database=" + database);
204 trace("user =" + user);
205 trace("password=" + password);
206 trace("test =" + test);
207 trace("log =" + log);
208 JavaSystem.setLogToSystem(true);
209 }
210
211 Class.forName(driver).newInstance();
217
218 cConn = DriverManager.getConnection(url + database, user,
219 password);
220 } catch (Exception e) {
221 System.out.println("QueryTool.init: " + e.getMessage());
222 e.printStackTrace();
223 }
224
225 sRecent = new String [iMaxRecent];
226 iRecent = 0;
227
228 try {
229 sStatement = cConn.createStatement();
230 } catch (SQLException e) {
231 System.out.println("Exception: " + e);
232 }
233
234 if (test) {
235 insertTestData();
236 }
237
238 txtCommand.requestFocus();
239 }
240
241
247 void trace(String s) {
248 System.out.println(s);
249 }
250
251
255 public boolean action(Event evt, Object arg) {
256
257 String s = arg.toString();
258
259 if (s.equals("Execute")) {
260 String sCmd = txtCommand.getText();
261 String [] g = new String [1];
262
263 try {
264 sStatement.execute(sCmd);
265
266 int r = sStatement.getUpdateCount();
267
268 if (r == -1) {
269 formatResultSet(sStatement.getResultSet());
270 } else {
271 g[0] = "update count";
272
273 gResult.setHead(g);
274
275 g[0] = String.valueOf(r);
276
277 gResult.addRow(g);
278 }
279
280 setRecent(txtCommand.getText());
281 } catch (SQLException e) {
282 g[0] = "SQL Error";
283
284 gResult.setHead(g);
285
286 g[0] = e.getMessage();
287
288 gResult.addRow(g);
289 }
290
291 gResult.repaint();
292 txtCommand.selectAll();
293 txtCommand.requestFocus();
294 } else if (s.equals("Script")) {
295 String sScript = getScript();
296
297 txtCommand.setText(sScript);
298 txtCommand.selectAll();
299 txtCommand.requestFocus();
300 } else if (s.equals("Import")) {
301 String sImport = getImport();
302
303 txtCommand.setText(sImport);
304 txtCommand.selectAll();
305 txtCommand.requestFocus();
306 } else if (s.equals("Exit")) {
307 System.exit(0);
308 } else { txtCommand.setText(s);
310 }
311
312 return true;
313 }
314
315
321 void formatResultSet(ResultSet r) {
322
323 try {
324 ResultSetMetaData m = r.getMetaData();
325 int col = m.getColumnCount();
326 String [] h = new String [col];
327
328 for (int i = 1; i <= col; i++) {
329 h[i - 1] = m.getColumnLabel(i);
330 }
331
332 gResult.setHead(h);
333
334 while (r.next()) {
335 for (int i = 1; i <= col; i++) {
336 h[i - 1] = r.getString(i);
337
338 if (r.wasNull()) {
339 h[i - 1] = "(null)";
340 }
341 }
342
343 gResult.addRow(h);
344 }
345 } catch (SQLException e) {}
346 }
347
348
354 String getScript() {
355
356 ResultSet rResult = null;
357
358 try {
359 rResult = sStatement.executeQuery("SCRIPT");
360
361 StringBuffer a = new StringBuffer ();
362
363 while (rResult.next()) {
364 a.append(rResult.getString(1));
365 a.append('\n');
366 }
367
368 a.append('\n');
369
370 return a.toString();
371 } catch (SQLException e) {
372 return "";
373 } finally {
374 if (rResult != null) {
375 try {
376 rResult.close();
377 } catch (Exception e) {}
378 }
379 }
380 }
381
382
388 String getImport() {
389
390 StringBuffer a = new StringBuffer ();
391 String filename = "import.sql";
392 BufferedReader in = null;
393
394 try {
395 in = new BufferedReader (new FileReader (filename));
396
397 String line;
398
399 while ((line = in.readLine()) != null) {
400 a.append(line);
401 a.append('\n');
402 }
403
404 a.append('\n');
405 in.close();
406
407 return a.toString();
408 } catch (Exception e) {
409 if (in != null) {
410 try {
411 in.close();
412 } catch (IOException e1) {}
413 }
414
415 return "";
416 }
417 }
418
419
422 private void setRecent(String s) {
423
424 for (int i = 0; i < iMaxRecent; i++) {
425 if (s.equals(sRecent[i])) {
426 return;
427 }
428 }
429
430 if (sRecent[iRecent] != null) {
431 choRecent.remove(sRecent[iRecent]);
432 }
433
434 sRecent[iRecent] = s;
435 iRecent = (iRecent + 1) % iMaxRecent;
436
437 choRecent.addItem(s);
438 }
439
440 String [] sRecent;
441 static int iMaxRecent = 24;
442 int iRecent;
443 TextArea txtCommand;
444 Button butExecute, butScript;
445 Button butImport;
446 Choice choRecent;
447 Grid gResult;
448
449
452 private void initGUI() {
453
454 Panel pQuery = new Panel ();
456 Panel pCommand = new Panel ();
457 Panel pButton = new Panel ();
458 Panel pRecent = new Panel ();
459 Panel pResult = new Panel ();
460 Panel pBorderWest = new Panel ();
461 Panel pBorderEast = new Panel ();
462 Panel pBorderSouth = new Panel ();
463
464 pQuery.setLayout(new BorderLayout ());
465 pCommand.setLayout(new BorderLayout ());
466 pButton.setLayout(new BorderLayout ());
467 pRecent.setLayout(new BorderLayout ());
468 pResult.setLayout(new BorderLayout ());
469 pBorderWest.setBackground(SystemColor.control);
470 pBorderSouth.setBackground(SystemColor.control);
471 pBorderEast.setBackground(SystemColor.control);
472
473 Label lblCommand = new Label (" Command", Label.LEFT);
475 Label lblRecent = new Label (" Recent", Label.LEFT);
476 Label lblResult = new Label (" Result", Label.LEFT);
477
478 lblCommand.setBackground(SystemColor.control);
479 lblRecent.setBackground(SystemColor.control);
480 lblResult.setBackground(SystemColor.control);
481
482 butExecute = new Button ("Execute");
484 butScript = new Button ("Script");
485 butImport = new Button ("Import");
486
487 pButton.add("South", butScript);
488 pButton.add("Center", butExecute);
489 pButton.add("North", butImport);
490
491 Font fFont = new Font ("Dialog", Font.PLAIN, 12);
493
494 txtCommand = new TextArea (5, 40);
495
496 txtCommand.setFont(fFont);
497
498 choRecent = new Choice ();
500
501 gResult = new Grid();
503
504 setLayout(new BorderLayout ());
506 pRecent.add("Center", choRecent);
507 pRecent.add("North", lblRecent);
508 pCommand.add("North", lblCommand);
509 pCommand.add("East", pButton);
510 pCommand.add("Center", txtCommand);
511 pCommand.add("South", pRecent);
512 pResult.add("North", lblResult);
513 pResult.add("Center", gResult);
514 pQuery.add("North", pCommand);
515 pQuery.add("Center", pResult);
516 add("Center", pQuery);
517 add("West", pBorderWest);
518 add("East", pBorderEast);
519 add("South", pBorderSouth);
520
521 doLayout();
523 }
524
525 static String [] sTestData = {
526 "drop table Place if exists",
527 "create table Place (Code integer,Name varchar(255))",
528 "create index iCode on Place (Code)", "delete from place",
529 "insert into Place values (4900,'Langenthal')",
530 "insert into Place values (8000,'Zurich')",
531 "insert into Place values (3000,'Berne')",
532 "insert into Place values (1200,'Geneva')",
533 "insert into Place values (6900,'Lugano')",
534 "drop table Customer if exists",
535 "create table Customer (Nr integer,Name varchar(255),Place integer)",
536 "create index iNr on Customer (Nr)", "delete from Customer",
537 "insert into Customer values (1,'Meier',3000)",
538 "insert into Customer values (2,'Mueller',8000)",
539 "insert into Customer values (3,'Devaux',1200)",
540 "insert into Customer values (4,'Rossi',6900)",
541 "insert into Customer values (5,'Rickli',3000)",
542 "insert into Customer values (6,'Graf',3000)",
543 "insert into Customer values (7,'Mueller',4900)",
544 "insert into Customer values (8,'May',1200)",
545 "insert into Customer values (9,'Berger',8000)",
546 "insert into Customer values (10,'D''Ascoli',6900)",
547 "insert into Customer values (11,'Padruz',1200)",
548 "insert into Customer values (12,'Hug',4900)"
549 };
550
551
555 void insertTestData() {
556
557 for (int i = 0; i < sTestData.length; i++) {
558 try {
559 sStatement.executeQuery(sTestData[i]);
560 } catch (SQLException e) {
561 System.out.println("Exception: " + e);
562 }
563 }
564
565 setRecent("select * from place");
566 setRecent("select * from Customer");
567 setRecent("select * from Customer where place<>3000");
568 setRecent("select * from place where code>3000 or code=1200");
569 setRecent("select * from Customer where nr<=8\nand name<>'Mueller'");
570 setRecent("update Customer set name='Russi'\nwhere name='Rossi'");
571 setRecent("delete from Customer where place=8000");
572 setRecent("insert into place values(3600,'Thun')");
573 setRecent("drop index Customer.iNr");
574 setRecent("select * from Customer where name like '%e%'");
575 setRecent("select count(*),min(code),max(code),sum(code) from place");
576
577 String s = "select * from Customer,place\n"
578 + "where Customer.place=place.code\n"
579 + "and place.name='Berne'";
580
581 setRecent(s);
582 txtCommand.setText(s);
583 txtCommand.selectAll();
584 }
585
586
590 static void printHelp() {
591
592 System.out.println(
593 "Usage: java QueryTool [-options]\n" + "where options include:\n"
594 + " -driver <classname> name of the driver class\n"
595 + " -url <name> first part of the jdbc url\n"
596 + " -database <name> second part of the jdbc url\n"
597 + " -user <name> username used for connection\n"
598 + " -password <name> password for this user\n"
599 + " -test <true/false> insert test data\n"
600 + " -log <true/false> write log to system out");
601 System.exit(0);
602 }
603
604
610 public void windowActivated(WindowEvent e) {}
611
612
618 public void windowDeactivated(WindowEvent e) {}
619
620
626 public void windowClosed(WindowEvent e) {}
627
628
634 public void windowClosing(WindowEvent ev) {
635
636 try {
637 cConn.close();
638 } catch (Exception e) {}
639
640 if (fMain != null) {
641 fMain.dispose();
642 }
643
644 System.exit(0);
645 }
646
647
653 public void windowDeiconified(WindowEvent e) {}
654
655
661 public void windowIconified(WindowEvent e) {}
662
663
669 public void windowOpened(WindowEvent e) {}
670
671
677 public void actionPerformed(ActionEvent ev) {
678
679 String s = ev.getActionCommand();
680
681 if (s != null && s.equals("Exit")) {
682 windowClosing(null);
683 }
684 }
685 }
686 |
Popular Tags |
Java API By Example, From Geeks To Geeks.