001: /* Copyright (c) 2001-2005, The HSQL Development 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 HSQL Development 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 HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
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:
031: package org.hsqldb.util;
032:
033: import java.sql.Connection;
034: import java.sql.DatabaseMetaData;
035: import java.sql.PreparedStatement;
036: import java.sql.ResultSet;
037: import java.sql.ResultSetMetaData;
038: import java.sql.SQLException;
039: import java.sql.Statement;
040: import java.util.Vector;
041: import java.awt.GridBagConstraints;
042: import java.awt.GridBagLayout;
043: import java.awt.Insets;
044: import java.awt.Label;
045: import java.awt.Panel;
046: import java.awt.ScrollPane;
047: import java.awt.event.ActionEvent;
048: import java.awt.event.ActionListener;
049: import java.awt.event.ItemEvent;
050: import java.awt.event.ItemListener;
051: import java.awt.event.TextEvent;
052: import java.awt.event.TextListener;
053:
054: /**
055: * Class declaration
056: *
057: *
058: * @author ulrivo@users
059: * @version 1.0.0
060: */
061:
062: // an entry panel to input/edit a record of a sql table
063: // ZaurusTableForm is constructed with a tableName and a connection
064: public class ZaurusTableForm extends ScrollPane implements
065: TextListener, ItemListener, ActionListener {
066:
067: // connection to database - brought via the constructor
068: Connection cConn;
069: DatabaseMetaData dbmeta;
070:
071: // the name of table for the form
072: String tableName;
073:
074: // array holding the components (TextField or Choice) in the GUI
075: ZaurusComponent[] komponente;
076:
077: // the columns of the table
078: String[] columns;
079:
080: // and their types
081: short[] columnTypes;
082:
083: // the names of the primary keys of the table
084: String[] primaryKeys;
085:
086: // the position of the primary keys in the table i. e. the column index starting from 0
087: int[] pkColIndex;
088:
089: // the names of the imported/foreign keys of the table
090: // first dimension is running through the constraints, second dim through the keys of one constraint
091: String[][] importedKeys;
092:
093: // the position of the imported keys in the table i. e. the column index starting from 0
094: int[][] imColIndex;
095:
096: // the names of the tables and columns which are the reference for the imported keys
097: String[] refTables;
098: String[][] refColumns;
099:
100: // the position of the reference keys in the reference table i. e. the column index starting from 0
101: int[][] refColIndex;
102:
103: // an array holding array of primary keys values matching the search condition
104: // first dimension through the results, second dimension running through the primary keys
105: Object[][] resultRowPKs;
106:
107: // there is an explicit count because a delete may shrink the result rows
108: int numberOfResult;
109:
110: // prepared statement to fetch the required rows
111: PreparedStatement pStmt;
112:
113: // pointer into the resultRowPKs
114: int aktRowNr;
115:
116: public ZaurusTableForm(String name, Connection con) {
117:
118: super ();
119:
120: tableName = name;
121: cConn = con;
122:
123: this .fetchColumns();
124: this .fetchPrimaryKeys();
125:
126: // System.out.print("primaryKeys: ");
127: // for (int i=0; i<primaryKeys.length;i++) {
128: // System.out.print(primaryKeys[i]+", ");
129: // } // end of for (int i=0; i<primaryKeys.length;i++)
130: // System.out.println();
131: this .fetchImportedKeys();
132: this .initGUI();
133: }
134:
135: // cancel the change/update of a row - show the row again
136: public void cancelChanges() {
137: this .showAktRow();
138: }
139:
140: // delete current row, answer special action codes, see comment below
141: public int deleteRow() {
142:
143: // build the delete string
144: String deleteString = "DELETE FROM " + tableName
145: + this .generatePKWhere();
146:
147: // System.out.println("delete string "+deleteString);
148: try {
149:
150: // fill the question marks
151: PreparedStatement ps = cConn.prepareStatement(deleteString);
152:
153: ps.clearParameters();
154:
155: int i;
156:
157: for (int j = 0; j < primaryKeys.length; j++) {
158: ps.setObject(j + 1, resultRowPKs[aktRowNr][j]);
159: } // end of for (int i=0; i<primaryKeys.length; i++)
160:
161: ps.executeUpdate();
162: } catch (SQLException e) {
163: ZaurusEditor
164: .printStatus("SQL Exception: " + e.getMessage());
165:
166: return 0;
167: } // end of try-catch
168:
169: // delete the corresponding primary key values from resultRowPKs
170: numberOfResult--;
171:
172: for (int i = aktRowNr; i < numberOfResult; i++) {
173: for (int j = 0; j < primaryKeys.length; j++) {
174: resultRowPKs[i][j] = resultRowPKs[i + 1][j];
175: }
176: }
177:
178: // there are the following outcomes after deleting aktRowNr:
179: /*
180: A B C D E F
181: no rows left J N N N N N
182: one row left - J N J N N
183: deleted row was the last row - J J N N N
184: deleted row was the pre-last - - - - J N
185:
186: first D X + D + *
187: . D X X D D
188: . D X +
189: last X
190:
191: new numberOfResult 0 1 2 1 2 2
192: old aktRowNr 0 1 2 0 1 0
193:
194: D - deleted row
195: X - any one row
196: + - one or more rows
197: * - zero or more rows
198:
199: */
200:
201: // A. return to the search panel and tell 'last row deleted' on the status line
202: // B. show the previous row and disable previous button
203: // C. show the previous row as akt row
204: // D. show akt row and disable next button
205: // E. show akt row and disable next button
206: // F. show akt row
207: // these actions reduce to the following actions for ZaurusEditor:
208: // 1. show search panel
209: // 2. disable previous button
210: // 3. disable next button
211: // 4. do nothing
212: // and 1,2,3,4 are the possible return codes
213: int actionCode;
214:
215: if (numberOfResult == 0) {
216:
217: // case A
218: actionCode = 1;
219:
220: ZaurusEditor.printStatus("Last row was deleted.");
221:
222: return actionCode;
223: } else if (numberOfResult == aktRowNr) {
224:
225: // B or C
226: // new aktRow is previous row
227: aktRowNr--;
228:
229: if (aktRowNr == 0) {
230:
231: // B
232: actionCode = 2;
233: } else {
234:
235: // C
236: actionCode = 4;
237: } // end of if (aktRowNr == 0)
238: } else {
239:
240: // D, E, F
241: if (numberOfResult >= 2 && aktRowNr < numberOfResult - 1) {
242:
243: // F
244: actionCode = 4;
245: } else {
246: actionCode = 3;
247: } // end of else
248: }
249:
250: this .showAktRow();
251: ZaurusEditor.printStatus("Row was deleted.");
252:
253: return actionCode;
254: }
255:
256: // answer a String containing a String list of primary keys i. e. "pk1, pk2, pk3"
257: public String getPrimaryKeysString() {
258:
259: String result = "";
260:
261: for (int i = 0; i < primaryKeys.length; i++) {
262: if (result != "") {
263: result += ", ";
264: }
265:
266: result += primaryKeys[i];
267: } // end of for (int i=0; i<primaryKeys.length; i++)
268:
269: return result;
270: }
271:
272: // open the panel to insert a new row into the table
273: public void insertNewRow() {
274:
275: // reset all fields
276: for (int i = 0; i < komponente.length; i++) {
277: komponente[i].clearContent();
278: } // end of for (int i=0; i<komponente.length; i++)
279:
280: // reset the field for the primary keys
281: for (int i = 0; i < primaryKeys.length; i++) {
282: komponente[pkColIndex[i]].setEditable(true);
283: }
284:
285: ZaurusEditor.printStatus("enter a new row for table "
286: + tableName);
287: }
288:
289: // show next row
290: // answer true, if there is after the next row another row
291: public boolean nextRow() {
292:
293: if (aktRowNr + 1 == numberOfResult) {
294: return false;
295: }
296:
297: aktRowNr++;
298:
299: this .showAktRow();
300:
301: return (aktRowNr + 1 < numberOfResult);
302: }
303:
304: // show prev row
305: // answer true, if there is previous the previous row another row
306: public boolean prevRow() {
307:
308: if (aktRowNr == 0) {
309: return false;
310: }
311:
312: aktRowNr--;
313:
314: this .showAktRow();
315:
316: return (aktRowNr > 0);
317: }
318:
319: // save all changes which are be made in the textfelder to the database
320: // answer true, if the update succeeds
321: public boolean saveChanges() {
322:
323: // the initial settings of the textfields counts with one
324: // so a real change by the user needs as many changes as there are columns
325: // System.out.print("Anderungen in den Feldern: ");
326: // there are changes to the database
327: // memorize all columns which have been changed
328: int[] changedColumns = new int[columns.length];
329: int countChanged = 0;
330:
331: // build the update string
332: String updateString = "";
333:
334: for (int i = 0; i < columns.length; i++) {
335: if (komponente[i].hasChanged()) {
336: if (updateString != "") {
337: updateString += ", ";
338: }
339:
340: updateString += columns[i] + "=?";
341: changedColumns[countChanged++] = i;
342: }
343: } // end of for (int i=0; i<columns.length; i++)
344:
345: if (countChanged > 0) {
346: updateString = "UPDATE " + tableName + " SET "
347: + updateString + this .generatePKWhere();
348:
349: // System.out.println("update "+updateString);
350: try {
351:
352: // fill the question marks
353: PreparedStatement ps = cConn
354: .prepareStatement(updateString);
355:
356: ps.clearParameters();
357:
358: int i;
359:
360: for (i = 0; i < countChanged; i++) {
361: ps.setObject(i + 1, komponente[changedColumns[i]]
362: .getContent());
363:
364: // System.out.print(" changed feld "+komponente[changedColumns[i]].getContent());
365: } // end of for (int i=0; i<countChanged; i++)
366:
367: // System.out.println();
368: for (int j = 0; j < primaryKeys.length; j++) {
369: ps.setObject(i + j + 1, resultRowPKs[aktRowNr][j]);
370: } // end of for (int i=0; i<primaryKeys.length; i++)
371:
372: ps.executeUpdate();
373: ZaurusEditor
374: .printStatus("changed row was saved to table "
375: + tableName);
376:
377: return true;
378: } catch (SQLException e) {
379: ZaurusEditor.printStatus("SQL Exception: "
380: + e.getMessage());
381:
382: return false;
383: } // end of try-catch
384: } else {
385:
386: // System.out.println("no changes");
387: return true;
388: } // end of if (changed)
389: }
390:
391: // save a new row
392: // answer true, if saving succeeds
393: public boolean saveNewRow() {
394:
395: // check the fields of the primary keys whether one is empty
396: boolean onePKempty = false;
397: int tmp;
398:
399: for (tmp = 0; tmp < primaryKeys.length; tmp++) {
400: if (komponente[pkColIndex[tmp]].getContent().equals("")) {
401: onePKempty = true;
402:
403: break;
404: }
405: }
406:
407: if (onePKempty) {
408: komponente[pkColIndex[tmp]].requestFocus();
409: ZaurusEditor.printStatus("no value for primary key "
410: + primaryKeys[tmp]);
411:
412: return false;
413: } // end of if (onePKempty)
414:
415: // build the insert string
416: String insertString = "INSERT INTO " + tableName + " VALUES(";
417:
418: for (int j = 0; j < columns.length; j++) {
419: if (j > 0) {
420: insertString += ", ";
421: }
422:
423: insertString += "?";
424: } // end of for (int i=0; i<columns.length; i++)
425:
426: insertString += ")";
427:
428: // System.out.println("insert string "+insertString);
429: try {
430:
431: // fill the question marks
432: PreparedStatement ps = cConn.prepareStatement(insertString);
433:
434: ps.clearParameters();
435:
436: int i;
437:
438: for (i = 0; i < columns.length; i++) {
439: ps.setObject(i + 1, komponente[i].getContent());
440: }
441:
442: ps.executeUpdate();
443: ZaurusEditor.printStatus("new row was saved to table "
444: + tableName);
445:
446: return true;
447: } catch (SQLException e) {
448: ZaurusEditor
449: .printStatus("SQL Exception: " + e.getMessage());
450:
451: return false;
452: } // end of try-catch
453: }
454:
455: // read all primary key values into resultRowPKs for the rows which meet the search condition i. e.
456: // which contains the search words
457: // answer the number of found rows, -1 if there is an SQL exception
458: public int searchRows(String[] words, boolean allWords,
459: boolean ignoreCase, boolean noMatchWhole) {
460:
461: // System.out.print("search in " + tableName + " for: ");
462: // for (int i=0; i < words.length; i++) {
463: // System.out.print(words[i]+", ");
464: // }
465: // System.out.println("allWords = "+allWords+", ignoreCase = "+ignoreCase+", noMatchWhole= "+noMatchWhole);
466: String where = this .generateWhere(words, allWords, ignoreCase,
467: noMatchWhole);
468: Vector temp = new Vector(20);
469:
470: try {
471: Statement stmt = cConn.createStatement();
472: ResultSet rs = stmt.executeQuery("SELECT "
473: + this .getPrimaryKeysString() + " FROM "
474: + tableName + where);
475:
476: while (rs.next()) {
477: Object[] pkValues = new Object[primaryKeys.length];
478:
479: for (int i = 0; i < primaryKeys.length; i++) {
480: pkValues[i] = rs.getObject(pkColIndex[i] + 1);
481: } // end of for (int i=0; i<primaryKeys.length; i++)
482:
483: temp.addElement(pkValues);
484: }
485:
486: rs.close();
487: } catch (SQLException e) {
488: ZaurusEditor
489: .printStatus("SQL Exception: " + e.getMessage());
490:
491: return -1;
492: } // end of try-catch
493:
494: resultRowPKs = new Object[temp.size()][primaryKeys.length];
495: numberOfResult = temp.size();
496:
497: for (int i = 0; i < primaryKeys.length; i++) {
498: for (int j = 0; j < temp.size(); j++) {
499: resultRowPKs[j][i] = ((Object[]) temp.elementAt(j))[i];
500: } // end of for (int j=0; j<temp.size(); j++)
501: } // end of for (int i=0; i<primaryKeys.length; i++)
502:
503: // prepare statement for fetching the result rows for later use
504: String stmtString = "SELECT * FROM " + tableName;
505:
506: try {
507: pStmt = cConn.prepareStatement(stmtString
508: + this .generatePKWhere());
509: } catch (SQLException e) {
510: System.out.println("SQL Exception: " + e.getMessage());
511: } // end of try-catch
512:
513: // System.out.println("prepared statement: "+stmtString);
514: if (numberOfResult > 0) {
515: this .disablePKFields();
516:
517: aktRowNr = 0;
518:
519: this .showAktRow();
520: } // end of if (numberOfResult > 0)
521:
522: // System.out.println("number of rows: "+numberOfResult);
523: return numberOfResult;
524: }
525:
526: public void actionPerformed(ActionEvent e) {
527: }
528:
529: public void textValueChanged(TextEvent e) {
530:
531: for (int i = 0; i < columns.length; i++) {
532: if (komponente[i] == e.getSource()) {
533: komponente[i].setChanged();
534:
535: break;
536: }
537: }
538: }
539:
540: public void itemStateChanged(ItemEvent e) {
541:
542: for (int i = 0; i < columns.length; i++) {
543: if (komponente[i] == e.getSource()) {
544: komponente[i].setChanged();
545:
546: break;
547: }
548: }
549: }
550:
551: // ******************************************************
552: // private methods
553: // ******************************************************
554: // set all fields for primary keys to not editable
555: private void disablePKFields() {
556:
557: for (int i = 0; i < primaryKeys.length; i++) {
558: komponente[pkColIndex[i]].setEditable(false);
559: } // end of for (int i=0; i<columns.length; i++)
560: }
561:
562: // fetch all values from a table and a column
563: // fill the ZaurusChoice zc with the row values for the Choice
564: // and the column values as values
565: private void fillZChoice(ZaurusChoice zc, String tab, String col) {
566:
567: try {
568: if (cConn == null) {
569: return;
570: }
571:
572: Statement stmt = cConn.createStatement();
573: ResultSet rs = stmt.executeQuery("SELECT * FROM " + tab
574: + " ORDER BY " + col);
575: ResultSetMetaData rsmd = rs.getMetaData();
576: int numberOfColumns = rsmd.getColumnCount();
577: int colIndex = rs.findColumn(col);
578:
579: while (rs.next()) {
580: String tmp = "";
581:
582: for (int i = 1; i <= numberOfColumns; i++) {
583: if (i > 1) {
584: tmp += "; ";
585: }
586:
587: tmp += rs.getString(i);
588: } // end of for (int i=1; i<=numberOfColumns; i++)
589:
590: zc.add(tmp, rs.getString(colIndex));
591: }
592:
593: rs.close();
594: } catch (SQLException e) {
595: System.out.println("SQL Exception: " + e.getMessage());
596: } // end of try-catch
597: }
598:
599: // fetch all column names
600: private void fetchColumns() {
601:
602: Vector temp = new Vector(20);
603: Vector tempType = new Vector(20);
604:
605: try {
606: if (cConn == null) {
607: return;
608: }
609:
610: if (dbmeta == null) {
611: dbmeta = cConn.getMetaData();
612: }
613:
614: ResultSet colList = dbmeta.getColumns(null, null,
615: tableName, "%");
616:
617: while (colList.next()) {
618: temp.addElement(colList.getString("COLUMN_NAME"));
619: tempType.addElement(new Short(colList
620: .getShort("DATA_TYPE")));
621: }
622:
623: colList.close();
624: } catch (SQLException e) {
625: ZaurusEditor
626: .printStatus("SQL Exception: " + e.getMessage());
627: }
628:
629: columns = new String[temp.size()];
630:
631: temp.copyInto(columns);
632:
633: columnTypes = new short[temp.size()];
634:
635: for (int i = 0; i < columnTypes.length; i++) {
636: columnTypes[i] = ((Short) tempType.elementAt(i))
637: .shortValue();
638: }
639: }
640:
641: // fetch the imported keys i.e. columns which reference to foreign keys in other tables
642: private void fetchImportedKeys() {
643:
644: Vector imKeys = new Vector(20);
645: Vector imKeyNames = null;
646: Vector refTabs = new Vector(20);
647: Vector refCols = new Vector(20);
648: Vector refColNames = null;
649:
650: try {
651: if (cConn == null) {
652: return;
653: }
654:
655: if (dbmeta == null) {
656: dbmeta = cConn.getMetaData();
657: }
658:
659: ResultSet colList = dbmeta.getImportedKeys(null, null,
660: tableName);
661: String pkTable, pkColumn, fkColumn;
662: int keySeq;
663:
664: while (colList.next()) {
665: pkTable = colList.getString("PKTABLE_NAME");
666: pkColumn = colList.getString("PKCOLUMN_NAME");
667: fkColumn = colList.getString("FKCOLUMN_NAME");
668: keySeq = colList.getInt("KEY_SEQ");
669:
670: if (keySeq == 1) {
671: if (imKeyNames != null) {
672: imKeys.addElement(imKeyNames);
673: refCols.addElement(refColNames);
674: } // end of if (exKeyNames != null)
675:
676: imKeyNames = new Vector(20);
677: refColNames = new Vector(20);
678:
679: refTabs.addElement(pkTable);
680: } // end of if (keySeq == 1)
681:
682: imKeyNames.addElement(fkColumn);
683: refColNames.addElement(pkColumn);
684: }
685:
686: if (imKeyNames != null) {
687: imKeys.addElement(imKeyNames);
688: refCols.addElement(refColNames);
689: } // end of if (exKeyNames != null)
690:
691: colList.close();
692: } catch (SQLException e) {
693: ZaurusEditor
694: .printStatus("SQL Exception: " + e.getMessage());
695: }
696:
697: // System.out.println("Imported Keys of "+tableName);
698: int numberOfConstraints = imKeys.size();
699:
700: importedKeys = new String[numberOfConstraints][];
701: imColIndex = new int[numberOfConstraints][];
702: refTables = new String[numberOfConstraints];
703: refColumns = new String[numberOfConstraints][];
704: refColIndex = new int[numberOfConstraints][];
705:
706: for (int i = 0; i < numberOfConstraints; i++) {
707: Vector keys = (Vector) imKeys.elementAt(i);
708: Vector cols = (Vector) refCols.elementAt(i);
709: int numberOfKeys = keys.size();
710:
711: importedKeys[i] = new String[numberOfKeys];
712: imColIndex[i] = new int[numberOfKeys];
713: refColumns[i] = new String[numberOfKeys];
714: refColIndex[i] = new int[numberOfKeys];
715: refTables[i] = (String) refTabs.elementAt(i);
716:
717: // System.out.println("reference table "+refTables[i]);
718: for (int j = 0; j < numberOfKeys; j++) {
719: importedKeys[i][j] = (String) keys.elementAt(j);
720: imColIndex[i][j] = this .getColIndex(importedKeys[i][j]);
721: refColumns[i][j] = (String) cols.elementAt(j);
722: refColIndex[i][j] = this .getColIndex(refColumns[i][j],
723: refTables[i]);
724:
725: // System.out.println(" importedKeys "+importedKeys[i][j]+"(Index: "+imColIndex[i][j]+") refColumns "+refColumns[i][j]+"(Index: "+refColIndex[i][j]+")");
726: } // end of for (int j=0; j<numberOfKeys; j++)
727: }
728: }
729:
730: private void fetchPrimaryKeys() {
731:
732: Vector temp = new Vector(20);
733:
734: try {
735: if (cConn == null) {
736: return;
737: }
738:
739: if (dbmeta == null) {
740: dbmeta = cConn.getMetaData();
741: }
742:
743: ResultSet colList = dbmeta.getPrimaryKeys(null, null,
744: tableName);
745:
746: while (colList.next()) {
747: temp.addElement(colList.getString("COLUMN_NAME"));
748: }
749:
750: colList.close();
751: } catch (SQLException e) {
752: ZaurusEditor
753: .printStatus("SQL Exception: " + e.getMessage());
754: }
755:
756: primaryKeys = new String[temp.size()];
757:
758: temp.copyInto(primaryKeys);
759:
760: pkColIndex = new int[primaryKeys.length];
761:
762: for (int i = 0; i < primaryKeys.length; i++) {
763: pkColIndex[i] = this .getColIndex(primaryKeys[i]);
764: } // end of for (int i=0; i<primaryKeys.length; i++)
765: }
766:
767: private String generatePKWhere() {
768:
769: String stmtString = " WHERE ";
770:
771: for (int i = 0; i < primaryKeys.length; i++) {
772: if (i > 0) {
773: stmtString += " AND ";
774: }
775:
776: stmtString += primaryKeys[i] + "=?";
777: } // end of for (int i=0; i<primaryKeys.length; i++)
778:
779: return stmtString;
780: }
781:
782: // generate the Where-condition for the words
783: private String generateWhere(String[] words, boolean allWords,
784: boolean ignoreCase, boolean noMatchWhole) {
785:
786: String result = "";
787:
788: // if all words must match use AND between the different conditions
789: String join;
790:
791: if (allWords) {
792: join = " AND ";
793: } else {
794: join = " OR ";
795: } // end of else
796:
797: for (int wordInd = 0; wordInd < words.length; wordInd++) {
798: String oneCondition = "";
799:
800: for (int col = 0; col < columns.length; col++) {
801: if (oneCondition != "") {
802: oneCondition += " OR ";
803: }
804:
805: if (ignoreCase) {
806: if (noMatchWhole) {
807: oneCondition += "LOWER(" + columns[col]
808: + ") LIKE '%"
809: + words[wordInd].toLowerCase() + "%'";
810: } else {
811: oneCondition += "LOWER(" + columns[col]
812: + ") LIKE '"
813: + words[wordInd].toLowerCase() + "'";
814: }
815: } else {
816: if (noMatchWhole) {
817: oneCondition += columns[col] + " LIKE '%"
818: + words[wordInd] + "%'";
819: } else {
820: oneCondition += columns[col] + " LIKE '"
821: + words[wordInd] + "'";
822: }
823: }
824: }
825:
826: if (result != "") {
827: result += join;
828: }
829:
830: result += "(" + oneCondition + ")";
831: }
832:
833: if (result != "") {
834: result = " WHERE " + result;
835: } // end of if (result != "")
836:
837: // System.out.println("result: "+result);
838: return result;
839: }
840:
841: // answer the index of the column named name in the actual table
842: private int getColIndex(String name) {
843:
844: for (int i = 0; i < columns.length; i++) {
845: if (name.equals(columns[i])) {
846: return i;
847: } // end of if (name.equals(columns[i]))
848: } // end of for (int i=0; i<columns.length; i++)
849:
850: return -1;
851: }
852:
853: // answer the index of the column named colName in the table tabName
854: private int getColIndex(String colName, String tabName) {
855:
856: int ordPos = 0;
857:
858: try {
859: if (cConn == null) {
860: return -1;
861: }
862:
863: if (dbmeta == null) {
864: dbmeta = cConn.getMetaData();
865: }
866:
867: ResultSet colList = dbmeta.getColumns(null, null, tabName,
868: colName);
869:
870: colList.next();
871:
872: ordPos = colList.getInt("ORDINAL_POSITION");
873:
874: colList.close();
875: } catch (SQLException e) {
876: System.out.println("SQL Exception: " + e.getMessage());
877: }
878:
879: return ordPos - 1;
880: }
881:
882: // answer the index of the constraint for the column index
883: // answer -1, if the column is not part of any constraint
884: private int getConstraintIndex(int colIndex) {
885:
886: for (int i = 0; i < imColIndex.length; i++) {
887: for (int j = 0; j < imColIndex[i].length; j++) {
888: if (colIndex == imColIndex[i][j]) {
889: return i;
890: } // end of if (col == imColIndex[i][j])
891: } // end of for (int j=0; j<imColIndex[i].length; j++)
892: } // end of for (int i=0; i<imColIndex.length; i++)
893:
894: return -1;
895: }
896:
897: private void initGUI() {
898:
899: Panel pEntry = new Panel();
900:
901: pEntry.setLayout(new GridBagLayout());
902:
903: GridBagConstraints c = new GridBagConstraints();
904:
905: c.fill = GridBagConstraints.HORIZONTAL;
906: c.insets = new Insets(3, 3, 3, 3);
907: c.gridwidth = 1;
908: c.gridheight = 1;
909: c.weightx = c.weighty = 1;
910: c.anchor = GridBagConstraints.WEST;
911: komponente = new ZaurusComponent[columns.length];
912:
913: for (int i = 0; i < columns.length; i++) {
914: c.gridy = i;
915: c.gridx = 0;
916:
917: pEntry.add(new Label((String) columns[i]), c);
918:
919: c.gridx = 1;
920:
921: int constraint = this .getConstraintIndex(i);
922:
923: if (constraint >= 0 && imColIndex[constraint].length == 1) {
924:
925: // we use ony foreign keys with one index
926: ZaurusChoice tmp = new ZaurusChoice();
927:
928: this .fillZChoice(tmp, refTables[constraint],
929: refColumns[constraint][0]);
930: tmp.addItemListener(this );
931:
932: komponente[i] = tmp;
933:
934: pEntry.add(tmp, c);
935: } else if (columnTypes[i] == java.sql.Types.DATE) {
936:
937: // System.out.println("hier gibt es eine Date-Spalte namens "+columns[i]);
938: ZaurusTextField tmp = new ZaurusTextField(8);
939:
940: tmp.addTextListener(this );
941: pEntry.add(tmp, c);
942:
943: komponente[i] = tmp;
944: } else {
945: ZaurusTextField tmp = new ZaurusTextField(5);
946:
947: tmp.addTextListener(this );
948: pEntry.add(tmp, c);
949:
950: komponente[i] = tmp;
951: }
952:
953: komponente[i].setEditable(true);
954: }
955:
956: this .add(pEntry);
957: }
958:
959: // get and show the values of the actual row in the GUI
960: private void showAktRow() {
961:
962: try {
963: pStmt.clearParameters();
964:
965: for (int i = 0; i < primaryKeys.length; i++) {
966: pStmt.setObject(i + 1, resultRowPKs[aktRowNr][i]);
967: } // end of for (int i=0; i<primaryKeys.length; i++)
968:
969: ResultSet rs = pStmt.executeQuery();
970:
971: rs.next();
972:
973: for (int i = 0; i < columns.length; i++) {
974: komponente[i].setContent(rs.getString(i + 1));
975: } // end of for (int i=0; i<primaryKeys.length; i++)
976:
977: rs.close();
978: } catch (SQLException e) {
979: ZaurusEditor
980: .printStatus("SQL Exception: " + e.getMessage());
981: } // end of try-catch
982:
983: for (int i = 0; i < columns.length; i++) {
984: komponente[i].clearChanges();
985: }
986: }
987: }
|