001: package org.mandarax.examples.jdbc;
002:
003: /*
004: * Copyright (C) 1999-2004 <a href="mailto:mandarax@jbdietrich.com">Jens Dietrich</a>
005: *
006: * This library is free software; you can redistribute it and/or
007: * modify it under the terms of the GNU Lesser General Public
008: * License as published by the Free Software Foundation; either
009: * version 2 of the License, or (at your option) any later version.
010: *
011: * This library is distributed in the hope that it will be useful,
012: * but WITHOUT ANY WARRANTY; without even the implied warranty of
013: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
014: * Lesser General Public License for more details.
015: *
016: * You should have received a copy of the GNU Lesser General Public
017: * License along with this library; if not, write to the Free Software
018: * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
019: */
020:
021: import java.awt.*;
022: import java.awt.event.*;
023: import java.sql.*;
024: import javax.swing.*;
025: import javax.swing.event.DocumentEvent;
026: import javax.swing.event.DocumentListener;
027: import javax.swing.text.html.HTMLEditorKit;
028:
029: /**
030: * Simple mandarax JDBC client application.
031: * @author <a href="mailto:jens@jbdietrich.com">Jens Dietrich</a>
032: * @version 3.3.2 <29 December 2004>
033: */
034:
035: public class MandaraxJdbcClientDemoApp extends JFrame {
036:
037: // example data
038: private String[] URLS = {
039: "jdbc:mandarax:zkb:" + DemoKB.ZKB_FILE,
040: "jdbc:mandarax:xkb:" + DemoKB.XKB_FILE,
041: "jdbc:mandarax:ruleml:" + DemoKB.RULE_ML_FILE,
042: "jdbc:mandarax:ref:test.org.mandarax.jdbc.TestKnowledgeBaseRef",
043: "jdbc:mandarax:net:ref:test.org.mandarax.jdbc.TestKnowledgeBaseRef",
044: "jdbc:mandarax:net:zkb:/exampledata/example-family.zkb@http://localhost:8080/mandarax-server/jdbcserver",
045: "jdbc:mandarax:net:ruleml:/exampledata/example-family.ruleml@http://localhost:8080/mandarax-server/jdbcserver" };
046: private String[][] SELECTS = {
047: {
048: "select * from is_father_of",
049: "select * from is_son_of",
050: "select * from is_oncle_of",
051: "select distinct * from is_oncle_of",
052: "select grandchild from is_grandfather_of where grandfather='Klaus'",
053: "select * from is_grandfather_of where grandfather like 'K%'",
054: "select * from is_grandfather_of where grandfather like 'K%' or grandfather like 'O%'",
055: "select * from is_grandfather_of where not grandchild = 'Max'",
056: "select * from is_grandfather_of order by grandchild",
057: "select * from is_grandfather_of order by grandchild desc",
058: "select grandfather, count(*) from is_grandfather_of group by grandfather",
059: "select grandfather, count(*) from is_grandfather_of group by grandfather order by grandfather desc",
060: "select grandfather, max(grandchild) from is_grandfather_of group by grandfather order by max(grandchild)",
061: "select grandfather, count(*) from is_grandfather_of group by grandfather having grandfather='Klaus'" },
062: {
063: "select * from is_father_of",
064: "select * from is_son_of",
065: "select * from is_oncle_of",
066: "select distinct * from is_oncle_of",
067: "select * from is_grandfather_of",
068: "select * from is_grandfather_of where slot2 like 'K%'",
069: "select * from is_grandfather_of where slot2 like 'K%' or slot2 like 'O%'",
070: "select * from is_grandfather_of where not slot1 = 'Max'",
071: "select * from is_grandfather_of order by slot1",
072: "select * from is_grandfather_of order by slot1 desc",
073: "select slot2, count(*) from is_grandfather_of group by slot2",
074: "select slot2, count(*) from is_grandfather_of group by slot2 order by slot2 desc",
075: "select slot2, max(slot1) from is_grandfather_of group by slot2 order by max(slot1)",
076: "select slot2, count(*) from is_grandfather_of group by slot2 having slot2='Klaus'" },
077: {
078: "select * from is_father_of",
079: "select * from is_son_of",
080: "select * from is_grandfather_of",
081: "select * from is_grandfather_of where slot2 like 'K%'",
082: "select * from is_grandfather_of where slot2 like 'K%' or slot2 like 'O%'",
083: "select * from is_grandfather_of where not slot1 = 'Max'",
084: "select * from is_grandfather_of order by slot1",
085: "select * from is_grandfather_of order by slot1 desc",
086: "select slot2, count(*) from is_grandfather_of group by slot2",
087: "select slot2, count(*) from is_grandfather_of group by slot2 order by slot2 desc",
088: "select slot2, max(slot1) from is_grandfather_of group by slot2 order by max(slot1)",
089: "select slot2, count(*) from is_grandfather_of group by slot2 having slot2='Klaus'" },
090: {
091: "select * from people",
092: "select count(*) from people",
093: "select * from people where name='Meier'",
094: "select * from people where weight=80",
095: "select * from people where size=1.80",
096: "select * from people where dob='1990-01-01'",
097: "select * from people where first_name>'John'",
098: "select * from people where first_name<='John'",
099: "select * from people where dob<'1970-01-01'",
100: "select * from people where name like '%i%'",
101: "select * from people where name like 'Me_er'",
102: "select * from people where name like 'M%' and first_name = 'John'",
103: "select * from people where not name='Meier'",
104: "select * from people where (name = 'Meier' and first_name='John') or (name = 'Smith' and first_name='Jim')",
105: "select * from people order by first_name,name",
106: "select * from people order by first_name asc,name desc",
107: "select first_name,MAX(name),MAX(dob),MAX(weight),MAX(size) from people group by first_name",
108: "select first_name,MAX(name),MAX(dob),MAX(weight),MAX(size) from people group by first_name having MAX(size)>1.80 order by max(name) desc" },
109: {
110: "select * from is_father_of",
111: "select * from is_son_of",
112: "select * from is_oncle_of",
113: "select distinct * from is_oncle_of",
114: "select grandchild from is_grandfather_of where grandfather='Klaus'",
115: "select * from is_grandfather_of where grandfather like 'K%'",
116: "select * from is_grandfather_of where grandfather like 'K%' or grandfather like 'O%'",
117: "select * from is_grandfather_of where not grandchild = 'Max'",
118: "select * from is_grandfather_of order by grandchild",
119: "select * from is_grandfather_of order by grandchild desc",
120: "select grandfather, count(*) from is_grandfather_of group by grandfather",
121: "select grandfather, count(*) from is_grandfather_of group by grandfather order by grandfather desc",
122: "select grandfather, max(grandchild) from is_grandfather_of group by grandfather order by max(grandchild)",
123: "select grandfather, count(*) from is_grandfather_of group by grandfather having grandfather='Klaus'" },
124: {
125: "select * from is_father_of",
126: "select * from is_son_of",
127: "select * from is_oncle_of",
128: "select distinct * from is_oncle_of",
129: "select grandchild from is_grandfather_of where grandfather='Klaus'",
130: "select * from is_grandfather_of where grandfather like 'K%'",
131: "select * from is_grandfather_of where grandfather like 'K%' or grandfather like 'O%'",
132: "select * from is_grandfather_of where not grandchild = 'Max'",
133: "select * from is_grandfather_of order by grandchild",
134: "select * from is_grandfather_of order by grandchild desc",
135: "select grandfather, count(*) from is_grandfather_of group by grandfather",
136: "select grandfather, count(*) from is_grandfather_of group by grandfather order by grandfather desc",
137: "select grandfather, max(grandchild) from is_grandfather_of group by grandfather order by max(grandchild)",
138: "select grandfather, count(*) from is_grandfather_of group by grandfather having grandfather='Klaus'" }
139:
140: };
141: private String[] INFO = {
142: "<html>"
143: + "Example based on a knowledge base loaded from a zkb file."
144: + "<p>The kb file is <tt><b>" + DemoKB.ZKB_FILE
145: + "</b></tt>" + "</html>",
146: "<html>"
147: + "Example based on a knowledge base loaded from a xkb file."
148: + "Named slots in predicates (=columns) are not supported by XKB, generated names (slot1, slot2 etc) will be used."
149: + "<p>The kb file is <tt><b>" + DemoKB.XKB_FILE
150: + "</b></tt>" + "</html>",
151: "<html>"
152: + "Example based on a knowledge base loaded from a ruleml file."
153: + "Named slots in predicates (=columns) are not supported by RuleML, generated names (slot1, slot2 etc) will be used."
154: + "Note that queries using the <tt>is_oncle_of</tt> are not working correctly - ruleml cannot save "
155: + "the semantic <tt>equals()</tt> predicate!"
156: + "<p>The kb file is <tt><b>" + DemoKB.RULE_ML_FILE
157: + "</b></tt>" + "</html>",
158: "<html>"
159: + "Example based on a memory knowledge base used in the JUnit test cases. "
160: + "The derivations are trivial - the kb contains only facts. "
161: + "The query predicate contains (slots with) various data types (date , numeric) "
162: + "and a wide range of SQL commands (WHERE, GROUP BY, COUNT(*), ORDER BY, HAVING) can be tested with this kb. "
163: + "<p>The kb ref class is <tt><b>test.org.mandarax.jdbc.TestKnowledgeBaseRef</b></tt>"
164: + "</html>",
165: "<html>"
166: + "In this example the kb is accessed using the http network driver. "
167: + "The server must be running in order to execute this example. Start a local tomcat server (default port 8080) and copy the war file "
168: + "from the build/jdbc folder to the tomcat webapps folder. If the server is running on a different computer, replace the localhost part "
169: + "of the URL by the host name or IP address. If there is a proxy between the client and a server, the proxy information must be passed to java at startup"
170: + "</html>",
171: "<html>"
172: + "In this example the kb is accessed using the http network driver. "
173: + "The server must be running in order to execute this example. Start a local tomcat server (default port 8080) and copy the war file "
174: + "from the build/jdbc folder to the tomcat webapps folder. If the server is running on a different computer, replace the localhost part "
175: + "of the URL by the host name or IP address. If there is a proxy between the client and a server, the proxy information must be passed to java at startup"
176: + "</html>"
177:
178: };
179: private JToolBar toolbar = new JToolBar();
180: private JComboBox cbxQueries = new JComboBox();
181: private JComboBox cbxUrl = new JComboBox(URLS);
182: private JTextPane infoPanel = new JTextPane();
183: private Connection connection = null;
184: private SimpleSQLResultSetView resultView = new SimpleSQLResultSetView();
185: private Action actClose = null;
186: private Action actRun = null;
187: private Action actShowTables = null;
188: private Action actShowCols = null;
189: private Action actTestAllSQL = null;
190:
191: /**
192: * Constructor.
193: */
194: public MandaraxJdbcClientDemoApp() {
195: super ("Mandarax JDBC client Demo Application");
196: initialize();
197: }
198:
199: /*
200: * Pop up a the SQL query view in a window.
201: * @param args an array of parameters
202: */
203: public static void main(String[] args) {
204: MandaraxJdbcClientDemoApp application = new MandaraxJdbcClientDemoApp();
205: application.setSize(700, 450);
206: application.setLocationRelativeTo(null);
207: application.show();
208: }
209:
210: /**
211: * Set up the object.
212: */
213: private void initialize() {
214: // create example files
215: try {
216: DemoKB.setup();
217: } catch (Exception x) {
218: JOptionPane
219: .showMessageDialog(
220: MandaraxJdbcClientDemoApp.this ,
221: "Cannot create example files, see console for details",
222: "Error", JOptionPane.ERROR_MESSAGE);
223: x.printStackTrace();
224: }
225:
226: // load driver
227: try {
228: Class.forName("org.mandarax.jdbc.DriverImpl");
229: } catch (Exception x) {
230: JOptionPane
231: .showMessageDialog(
232: MandaraxJdbcClientDemoApp.this ,
233: "Cannot load mandarax jdbc driver, see console for details",
234: "Error", JOptionPane.ERROR_MESSAGE);
235: x.printStackTrace();
236: }
237: // init GUI
238: JPanel main = new JPanel(new BorderLayout(5, 5));
239: setContentPane(main);
240: toolbar.setFloatable(false);
241: main.add(toolbar, BorderLayout.NORTH);
242: JPanel innerPanel = new JPanel(new BorderLayout(5, 5));
243: innerPanel.setBorder(BorderFactory
244: .createEmptyBorder(3, 3, 3, 3));
245: main.add(innerPanel, BorderLayout.CENTER);
246: JPanel topPanel = new JPanel();
247: topPanel.setBorder(BorderFactory.createEtchedBorder());
248: innerPanel.add(topPanel, BorderLayout.NORTH);
249:
250: // central panel
251: GridBagLayout gridbag = new GridBagLayout();
252: JPanel centralPane = new JPanel(gridbag);
253: GridBagConstraints c = new GridBagConstraints();
254: c.fill = GridBagConstraints.BOTH;
255: c.weighty = 0.7;
256: c.weightx = 1.0;
257: resultView.setBorder(BorderFactory.createTitledBorder(
258: BorderFactory.createEtchedBorder(), "result set"));
259: gridbag.setConstraints(resultView, c);
260: c.gridx = 0;
261: c.gridy = 0;
262: centralPane.add(resultView);
263: c.gridy = 1;
264: c.weighty = 0.3;
265: JScrollPane comp = new JScrollPane(infoPanel);
266: comp.setBorder(BorderFactory.createTitledBorder(BorderFactory
267: .createEtchedBorder(), "kb info"));
268: gridbag.setConstraints(comp, c);
269: centralPane.add(comp);
270: innerPanel.add(centralPane, BorderLayout.CENTER);
271:
272: // configure info pane
273: infoPanel.setEditable(false);
274: infoPanel.setEditorKit(new HTMLEditorKit());
275:
276: // top panel
277: gridbag = new GridBagLayout();
278: topPanel.setLayout(gridbag);
279: c = new GridBagConstraints();
280: c.fill = GridBagConstraints.HORIZONTAL;
281: c.gridx = 0;
282: c.gridy = 0;
283: c.weightx = 0;
284: JLabel label1 = new JLabel(" URL: ", JLabel.RIGHT);
285: gridbag.setConstraints(label1, c);
286: topPanel.add(label1);
287: c.gridx = 1;
288: c.gridy = 0;
289: c.weightx = 1;
290: gridbag.setConstraints(cbxUrl, c);
291: topPanel.add(cbxUrl);
292: c.gridx = 0;
293: c.gridy = 1;
294: c.weightx = 0;
295: JLabel label2 = new JLabel(" Queries: ", JLabel.RIGHT);
296: gridbag.setConstraints(label2, c);
297: topPanel.add(label2);
298: c.gridx = 1;
299: c.gridy = 1;
300: c.weightx = 1;
301: gridbag.setConstraints(cbxQueries, c);
302: topPanel.add(cbxQueries);
303:
304: cbxQueries.setEditable(true);
305: cbxUrl.setEditable(true);
306:
307: // actions and other listeners
308: actClose = new AbstractAction("Exit") {
309: public void actionPerformed(ActionEvent e) {
310: dispose();
311: }
312: };
313: toolbar.add(actClose);
314:
315: actRun = new AbstractAction("Run Query") {
316: public void actionPerformed(ActionEvent e) {
317: String query = (String) cbxQueries.getSelectedItem();
318: if (query == null)
319: JOptionPane.showMessageDialog(
320: MandaraxJdbcClientDemoApp.this ,
321: "Cannot perform empty query", "Error",
322: JOptionPane.ERROR_MESSAGE);
323: run(connection, query);
324: }
325: };
326: actRun.setEnabled(false);
327: toolbar.add(actRun);
328:
329: actShowTables = new AbstractAction(
330: "Fetch All Tables (Predicates)") {
331: public void actionPerformed(ActionEvent e) {
332: try {
333: ResultSet rs = connection.getMetaData().getTables(
334: null, null, null, null);
335: resultView.display(rs);
336: } catch (SQLException x) {
337: JOptionPane.showMessageDialog(
338: MandaraxJdbcClientDemoApp.this ,
339: "Cannot perform catalog query", "Error",
340: JOptionPane.ERROR_MESSAGE);
341: }
342: }
343: };
344: actShowTables.setEnabled(false);
345: toolbar.add(actShowTables);
346:
347: actShowCols = new AbstractAction("Fetch All Columns (Slots)") {
348: public void actionPerformed(ActionEvent e) {
349: try {
350: ResultSet rs = connection.getMetaData().getColumns(
351: null, null, null, null);
352: resultView.display(rs);
353: } catch (SQLException x) {
354: JOptionPane.showMessageDialog(
355: MandaraxJdbcClientDemoApp.this ,
356: "Cannot perform catalog query", "Error",
357: JOptionPane.ERROR_MESSAGE);
358: }
359: }
360: };
361: actShowCols.setEnabled(false);
362: toolbar.add(actShowCols);
363: toolbar.add(resultView.actShowDerivation);
364:
365: actTestAllSQL = new AbstractAction("Test All") {
366: public void actionPerformed(ActionEvent e) {
367: try {
368: for (int i = 0; i < URLS.length; i++) {
369: Connection con = DriverManager
370: .getConnection(URLS[i]);
371: for (int j = 0; j < SELECTS[i].length; j++) {
372: Statement stmnt = con.createStatement();
373: stmnt.executeQuery(SELECTS[i][j]);
374: }
375: }
376: JOptionPane.showMessageDialog(
377: MandaraxJdbcClientDemoApp.this ,
378: "SQL commands tested sucessfully!");
379: } catch (SQLException x) {
380: JOptionPane.showMessageDialog(
381: MandaraxJdbcClientDemoApp.this ,
382: "Test failed, see console for details",
383: "Error", JOptionPane.ERROR_MESSAGE);
384: x.printStackTrace();
385: }
386: }
387: };
388: toolbar.add(actTestAllSQL);
389:
390: cbxQueries.addItemListener(new ItemListener() {
391: public void itemStateChanged(ItemEvent e) {
392: checkActions();
393: }
394: });
395: cbxUrl.addKeyListener(new KeyAdapter() {
396: public void keyReleased(KeyEvent e) {
397: setConnection((String) cbxUrl.getSelectedItem());
398: }
399: });
400: if (cbxUrl.getEditor().getEditorComponent() instanceof JTextField) {
401: JTextField txtField = (JTextField) cbxUrl.getEditor()
402: .getEditorComponent();
403: txtField.getDocument().addDocumentListener(
404: new DocumentListener() {
405: public void insertUpdate(DocumentEvent e) {
406: setConnection((String) cbxUrl
407: .getSelectedItem());
408: }
409:
410: public void removeUpdate(DocumentEvent e) {
411: setConnection((String) cbxUrl
412: .getSelectedItem());
413: }
414:
415: public void changedUpdate(DocumentEvent e) {
416: setConnection((String) cbxUrl
417: .getSelectedItem());
418: }
419: });
420: }
421:
422: // init values
423: cbxUrl.setSelectedIndex(0);
424: setConnection(cbxUrl.getSelectedItem().toString());
425: setQueries(SELECTS[0]);
426: infoPanel.setText(INFO[0]);
427: }
428:
429: /**
430: * Sets the connection.
431: * @param connection The connection to set
432: */
433: private void setConnection(String url) {
434: try {
435: connection = null;
436: connection = DriverManager.getConnection((String) cbxUrl
437: .getSelectedItem());
438: int index = cbxUrl.getSelectedIndex();
439: if (index > -1) {
440: setQueries(SELECTS[index]);
441: infoPanel.setText(INFO[index]);
442: } else {
443: setQueries(new String[] {});
444: infoPanel
445: .setText("no info available for this knowledge base");
446: }
447: } catch (SQLException x) {
448: JOptionPane.showMessageDialog(
449: MandaraxJdbcClientDemoApp.this ,
450: "Cannot create connection for url "
451: + cbxUrl.getSelectedItem(), "Error",
452: JOptionPane.ERROR_MESSAGE);
453: x.printStackTrace();
454: } finally {
455: checkActions();
456: // display appr. queries
457: setQueries(new String[] {});
458: resultView.clear();
459: if (connection != null) {
460: for (int i = 0; i < URLS.length; i++) {
461: if (URLS[i].equals(url)) {
462: setQueries(SELECTS[i]);
463: }
464: }
465: }
466: }
467: }
468:
469: /**
470: * Sets the queries.
471: * @param queries The queries to set
472: */
473: private void setQueries(String[] queries) {
474: cbxQueries.removeAllItems();
475: for (int i = 0; i < queries.length; i++)
476: cbxQueries.addItem(queries[i]);
477: checkActions();
478: }
479:
480: /**
481: * Enable / diable actions.
482: */
483: private void checkActions() {
484: actRun.setEnabled(connection != null
485: && cbxQueries.getSelectedItem() != null);
486: actShowTables.setEnabled(connection != null);
487: actShowCols.setEnabled(connection != null);
488: }
489:
490: /**
491: * Run a query.
492: * @param connection a connection
493: * @param query a SQL SELECT command
494: */
495: private void run(Connection connection, String query) {
496: resultView.execute(connection, query);
497: }
498:
499: }
|