Java > Open Source Codes > org > hsqldb > util > ZaurusTableForm


1 /* Copyright (c) 2001-2005, The HSQL Development Group
2  * All rights reserved.
3  *
4  * Redistribution and use in source and binary forms, with or without
5  * modification, are permitted provided that the following conditions are met:
6  *
7  * Redistributions of source code must retain the above copyright notice, this
8  * list of conditions and the following disclaimer.
9  *
10  * Redistributions in binary form must reproduce the above copyright notice,
11  * this list of conditions and the following disclaimer in the documentation
12  * and/or other materials provided with the distribution.
13  *
14  * Neither the name of the HSQL Development Group nor the names of its
15  * contributors may be used to endorse or promote products derived from this
16  * software without specific prior written permission.
17  *
18  * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
19  * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
20  * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
21  * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
22  * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
23  * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
24  * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
25  * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
26  * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
27  * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
28  * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
29  */
30
31
32 package org.hsqldb.util;
33
34 import java.sql.Connection ;
35 import java.sql.DatabaseMetaData ;
36 import java.sql.PreparedStatement ;
37 import java.sql.ResultSet ;
38 import java.sql.ResultSetMetaData ;
39 import java.sql.SQLException ;
40 import java.sql.Statement ;
41 import java.util.Vector ;
42 import java.awt.GridBagConstraints ;
43 import java.awt.GridBagLayout ;
44 import java.awt.Insets ;
45 import java.awt.Label ;
46 import java.awt.Panel ;
47 import java.awt.ScrollPane ;
48 import java.awt.event.ActionEvent ;
49 import java.awt.event.ActionListener ;
50 import java.awt.event.ItemEvent ;
51 import java.awt.event.ItemListener ;
52 import java.awt.event.TextEvent ;
53 import java.awt.event.TextListener ;
54
55 /**
56  * Class declaration
57  *
58  *
59  * @author ulrivo@users
60  * @version 1.0.0
61  */
62
63 // an entry panel to input/edit a record of a sql table
64 // ZaurusTableForm is constructed with a tableName and a connection
65 public class ZaurusTableForm extends ScrollPane
66 implements TextListener , ItemListener , ActionListener {
67
68     // connection to database - brought via the constructor
69 Connection cConn;
70     DatabaseMetaData dbmeta;
71
72     // the name of table for the form
73 String tableName;
74
75     // array holding the components (TextField or Choice) in the GUI
76 ZaurusComponent[] komponente;
77
78     // the columns of the table
79 String [] columns;
80
81     // and their types
82 short[] columnTypes;
83
84     // the names of the primary keys of the table
85 String [] primaryKeys;
86
87     // the position of the primary keys in the table i. e. the column index starting from 0
88 int[] pkColIndex;
89
90     // the names of the imported/foreign keys of the table
91 // first dimension is running through the constraints, second dim through the keys of one constraint
92 String [][] importedKeys;
93
94     // the position of the imported keys in the table i. e. the column index starting from 0
95 int[][] imColIndex;
96
97     // the names of the tables and columns which are the reference for the imported keys
98 String [] refTables;
99     String [][] refColumns;
100
101     // the position of the reference keys in the reference table i. e. the column index starting from 0
102 int[][] refColIndex;
103
104     // an array holding array of primary keys values matching the search condition
105 // first dimension through the results, second dimension running through the primary keys
106 Object [][] resultRowPKs;
107
108     // there is an explicit count because a delete may shrink the result rows
109 int numberOfResult;
110
111     // prepared statement to fetch the required rows
112 PreparedStatement pStmt;
113
114     // pointer into the resultRowPKs
115 int aktRowNr;
116
117     public ZaurusTableForm(String name, Connection con) {
118
119         super();
120
121         tableName = name;
122         cConn = con;
123
124         this.fetchColumns();
125         this.fetchPrimaryKeys();
126
127         // System.out.print("primaryKeys: ");
128 // for (int i=0; i<primaryKeys.length;i++) {
129 // System.out.print(primaryKeys[i]+", ");
130 // } // end of for (int i=0; i<primaryKeys.length;i++)
131 // System.out.println();
132 this.fetchImportedKeys();
133         this.initGUI();
134     }
135
136     // cancel the change/update of a row - show the row again
137 public void cancelChanges() {
138         this.showAktRow();
139     }
140
141     // delete current row, answer special action codes, see comment below
142 public int deleteRow() {
143
144         // build the delete string
145 String deleteString = "DELETE FROM " + tableName
146                               + this.generatePKWhere();
147
148         // System.out.println("delete string "+deleteString);
149 try {
150
151             // fill the question marks
152 PreparedStatement ps = cConn.prepareStatement(deleteString);
153
154             ps.clearParameters();
155
156             int i;
157
158             for (int j = 0; j < primaryKeys.length; j++) {
159                 ps.setObject(j + 1, resultRowPKs[aktRowNr][j]);
160             } // end of for (int i=0; i<primaryKeys.length; i++)
161
162             ps.executeUpdate();
163         } catch (SQLException e) {
164             ZaurusEditor.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 " + tableName);
286     }
287
288     // show next row
289 // answer true, if there is after the next row another row
290 public boolean nextRow() {
291
292         if (aktRowNr + 1 == numberOfResult) {
293             return false;
294         }
295
296         aktRowNr++;
297
298         this.showAktRow();
299
300         return (aktRowNr + 1 < numberOfResult);
301     }
302
303     // show prev row
304 // answer true, if there is previous the previous row another row
305 public boolean prevRow() {
306
307         if (aktRowNr == 0) {
308             return false;
309         }
310
311         aktRowNr--;
312
313         this.showAktRow();
314
315         return (aktRowNr > 0);
316     }
317
318     // save all changes which are be made in the textfelder to the database
319 // answer true, if the update succeeds
320 public boolean saveChanges() {
321
322         // the initial settings of the textfields counts with one
323 // so a real change by the user needs as many changes as there are columns
324 // System.out.print("Anderungen in den Feldern: ");
325 // there are changes to the database
326 // memorize all columns which have been changed
327 int[] changedColumns = new int[columns.length];
328         int countChanged = 0;
329
330         // build the update string
331 String updateString = "";
332
333         for (int i = 0; i < columns.length; i++) {
334             if (komponente[i].hasChanged()) {
335                 if (updateString != "") {
336                     updateString += ", ";
337                 }
338
339                 updateString += columns[i] + "=?";
340                 changedColumns[countChanged++] = i;
341             }
342         } // end of for (int i=0; i<columns.length; i++)
343
344         if (countChanged > 0) {
345             updateString = "UPDATE " + tableName + " SET " + updateString
346                            + this.generatePKWhere();
347
348             // System.out.println("update "+updateString);
349 try {
350
351                 // fill the question marks
352 PreparedStatement ps = cConn.prepareStatement(updateString);
353
354                 ps.clearParameters();
355
356                 int i;
357
358                 for (i = 0; i < countChanged; i++) {
359                     ps.setObject(i + 1,
360                                  komponente[changedColumns[i]].getContent());
361
362                     // System.out.print(" changed feld "+komponente[changedColumns[i]].getContent());
363 } // end of for (int i=0; i<countChanged; i++)
364
365                 // System.out.println();
366 for (int j = 0; j < primaryKeys.length; j++) {
367                     ps.setObject(i + j + 1, resultRowPKs[aktRowNr][j]);
368                 } // end of for (int i=0; i<primaryKeys.length; i++)
369
370                 ps.executeUpdate();
371                 ZaurusEditor.printStatus("changed row was saved to table "
372                                          + tableName);
373
374                 return true;
375             } catch (SQLException e) {
376                 ZaurusEditor.printStatus("SQL Exception: " + e.getMessage());
377
378                 return false;
379             } // end of try-catch
380 } else {
381
382             // System.out.println("no changes");
383 return true;
384         } // end of if (changed)
385 }
386
387     // save a new row
388 // answer true, if saving succeeds
389 public boolean saveNewRow() {
390
391         // check the fields of the primary keys whether one is empty
392 boolean onePKempty = false;
393         int tmp;
394
395         for (tmp = 0; tmp < primaryKeys.length; tmp++) {
396             if (komponente[pkColIndex[tmp]].getContent().equals("")) {
397                 onePKempty = true;
398
399                 break;
400             }
401         }
402
403         if (onePKempty) {
404             komponente[pkColIndex[tmp]].requestFocus();
405             ZaurusEditor.printStatus("no value for primary key "
406                                      + primaryKeys[tmp]);
407
408             return false;
409         } // end of if (onePKempty)
410
411         // build the insert string
412 String insertString = "INSERT INTO " + tableName + " VALUES(";
413
414         for (int j = 0; j < columns.length; j++) {
415             if (j > 0) {
416                 insertString += ", ";
417             }
418
419             insertString += "?";
420         } // end of for (int i=0; i<columns.length; i++)
421
422         insertString += ")";
423
424         // System.out.println("insert string "+insertString);
425 try {
426
427             // fill the question marks
428 PreparedStatement ps = cConn.prepareStatement(insertString);
429
430             ps.clearParameters();
431
432             int i;
433
434             for (i = 0; i < columns.length; i++) {
435                 ps.setObject(i + 1, komponente[i].getContent());
436             }
437
438             ps.executeUpdate();
439             ZaurusEditor.printStatus("new row was saved to table "
440                                      + tableName);
441
442             return true;
443         } catch (SQLException e) {
444             ZaurusEditor.printStatus("SQL Exception: " + e.getMessage());
445
446             return false;
447         } // end of try-catch
448 }
449
450     // read all primary key values into resultRowPKs for the rows which meet the search condition i. e.
451 // which contains the search words
452 // answer the number of found rows, -1 if there is an SQL exception
453 public int searchRows(String [] words, boolean allWords,
454                           boolean ignoreCase, boolean noMatchWhole) {
455
456         // System.out.print("search in " + tableName + " for: ");
457 // for (int i=0; i < words.length; i++) {
458 // System.out.print(words[i]+", ");
459 // }
460 // System.out.println("allWords = "+allWords+", ignoreCase = "+ignoreCase+", noMatchWhole= "+noMatchWhole);
461 String where = this.generateWhere(words, allWords, ignoreCase,
462                                           noMatchWhole);
463         Vector temp = new Vector (20);
464
465         try {
466             Statement stmt = cConn.createStatement();
467             ResultSet rs = stmt.executeQuery("SELECT "
468                                              + this.getPrimaryKeysString()
469                                              + " FROM " + tableName + where);
470
471             while (rs.next()) {
472                 Object [] pkValues = new Object [primaryKeys.length];
473
474                 for (int i = 0; i < primaryKeys.length; i++) {
475                     pkValues[i] = rs.getObject(pkColIndex[i] + 1);
476                 } // end of for (int i=0; i<primaryKeys.length; i++)
477
478                 temp.addElement(pkValues);
479             }
480
481             rs.close();
482         } catch (SQLException e) {
483             ZaurusEditor.printStatus("SQL Exception: " + e.getMessage());
484
485             return -1;
486         } // end of try-catch
487
488         resultRowPKs = new Object [temp.size()][primaryKeys.length];
489         numberOfResult = temp.size();
490
491         for (int i = 0; i < primaryKeys.length; i++) {
492             for (int j = 0; j < temp.size(); j++) {
493                 resultRowPKs[j][i] = ((Object []) temp.elementAt(j))[i];
494             } // end of for (int j=0; j<temp.size(); j++)
495 } // end of for (int i=0; i<primaryKeys.length; i++)
496
497         // prepare statement for fetching the result rows for later use
498 String stmtString = "SELECT * FROM " + tableName;
499
500         try {
501             pStmt = cConn.prepareStatement(stmtString
502                                            + this.generatePKWhere());
503         } catch (SQLException e) {
504             System.out.println("SQL Exception: " + e.getMessage());
505         } // end of try-catch
506
507         // System.out.println("prepared statement: "+stmtString);
508 if (numberOfResult > 0) {
509             this.disablePKFields();
510
511             aktRowNr = 0;
512
513             this.showAktRow();
514         } // end of if (numberOfResult > 0)
515
516         // System.out.println("number of rows: "+numberOfResult);
517 return numberOfResult;
518     }
519
520     public void actionPerformed(ActionEvent e) {}
521
522     public void textValueChanged(TextEvent e) {
523
524         for (int i = 0; i < columns.length; i++) {
525             if (komponente[i] == e.getSource()) {
526                 komponente[i].setChanged();
527
528                 break;
529             }
530         }
531     }
532
533     public void itemStateChanged(ItemEvent e) {
534
535         for (int i = 0; i < columns.length; i++) {
536             if (komponente[i] == e.getSource()) {
537                 komponente[i].setChanged();
538
539                 break;
540             }
541         }
542     }
543
544     // ******************************************************
545 // private methods
546 // ******************************************************
547 // set all fields for primary keys to not editable
548 private void disablePKFields() {
549
550         for (int i = 0; i < primaryKeys.length; i++) {
551             komponente[pkColIndex[i]].setEditable(false);
552         } // end of for (int i=0; i<columns.length; i++)
553 }
554
555     // fetch all values from a table and a column
556 // fill the ZaurusChoice zc with the row values for the Choice
557 // and the column values as values
558 private void fillZChoice(ZaurusChoice zc, String tab, String col) {
559
560         try {
561             if (cConn == null) {
562                 return;
563             }
564
565             Statement stmt = cConn.createStatement();
566             ResultSet rs = stmt.executeQuery("SELECT * FROM " + tab
567                                              + " ORDER BY " + col);
568             ResultSetMetaData rsmd = rs.getMetaData();
569             int numberOfColumns = rsmd.getColumnCount();
570             int colIndex = rs.findColumn(col);
571
572             while (rs.next()) {
573                 String tmp = "";
574
575                 for (int i = 1; i <= numberOfColumns; i++) {
576                     if (i > 1) {
577                         tmp += "; ";
578                     }
579
580                     tmp += rs.getString(i);
581                 } // end of for (int i=1; i<=numberOfColumns; i++)
582
583                 zc.add(tmp, rs.getString(colIndex));
584             }
585
586             rs.close();
587         } catch (SQLException e) {
588             System.out.println("SQL Exception: " + e.getMessage());
589         } // end of try-catch
590 }
591
592     // fetch all column names
593 private void fetchColumns() {
594
595         Vector temp = new Vector (20);
596         Vector tempType = new Vector (20);
597
598         try {
599             if (cConn == null) {
600                 return;
601             }
602
603             if (dbmeta == null) {
604                 dbmeta = cConn.getMetaData();
605             }
606
607             ResultSet colList = dbmeta.getColumns(null, null, tableName, "%");
608
609             while (colList.next()) {
610                 temp.addElement(colList.getString("COLUMN_NAME"));
611                 tempType.addElement(new Short (colList.getShort("DATA_TYPE")));
612             }
613
614             colList.close();
615         } catch (SQLException e) {
616             ZaurusEditor.printStatus("SQL Exception: " + e.getMessage());
617         }
618
619         columns = new String [temp.size()];
620
621         temp.copyInto(columns);
622
623         columnTypes = new short[temp.size()];
624
625         for (int i = 0; i < columnTypes.length; i++) {
626             columnTypes[i] = ((Short ) tempType.elementAt(i)).shortValue();
627         }
628     }
629
630     // fetch the imported keys i.e. columns which reference to foreign keys in other tables
631 private void fetchImportedKeys() {
632
633         Vector imKeys = new Vector (20);
634         Vector imKeyNames = null;
635         Vector refTabs = new Vector (20);
636         Vector refCols = new Vector (20);
637         Vector refColNames = null;
638
639         try {
640             if (cConn == null) {
641                 return;
642             }
643
644             if (dbmeta == null) {
645                 dbmeta = cConn.getMetaData();
646             }
647
648             ResultSet colList = dbmeta.getImportedKeys(null, null, tableName);
649             String pkTable, pkColumn, fkColumn;
650             int keySeq;
651
652             while (colList.next()) {
653                 pkTable = colList.getString("PKTABLE_NAME");
654                 pkColumn = colList.getString("PKCOLUMN_NAME");
655                 fkColumn = colList.getString("FKCOLUMN_NAME");
656                 keySeq = colList.getInt("KEY_SEQ");
657
658                 if (keySeq == 1) {
659                     if (imKeyNames != null) {
660                         imKeys.addElement(imKeyNames);
661                         refCols.addElement(refColNames);
662                     } // end of if (exKeyNames != null)
663
664                     imKeyNames = new Vector (20);
665                     refColNames = new Vector (20);
666
667                     refTabs.addElement(pkTable);
668                 } // end of if (keySeq == 1)
669
670                 imKeyNames.addElement(fkColumn);
671                 refColNames.addElement(pkColumn);
672             }
673
674             if (imKeyNames != null) {
675                 imKeys.addElement(imKeyNames);
676                 refCols.addElement(refColNames);
677             } // end of if (exKeyNames != null)
678
679             colList.close();
680         } catch (SQLException e) {
681             ZaurusEditor.printStatus("SQL Exception: " + e.getMessage());
682         }
683
684         // System.out.println("Imported Keys of "+tableName);
685 int numberOfConstraints = imKeys.size();
686
687         importedKeys = new String [numberOfConstraints][];
688         imColIndex = new int[numberOfConstraints][];
689         refTables = new String [numberOfConstraints];
690         refColumns = new String [numberOfConstraints][];
691         refColIndex = new int[numberOfConstraints][];
692
693         for (int i = 0; i < numberOfConstraints; i++) {
694             Vector keys = (Vector ) imKeys.elementAt(i);
695             Vector cols = (Vector ) refCols.elementAt(i);
696             int numberOfKeys = keys.size();
697
698             importedKeys[i] = new String [numberOfKeys];
699             imColIndex[i] = new int[numberOfKeys];
700             refColumns[i] = new String [numberOfKeys];
701             refColIndex[i] = new int[numberOfKeys];
702             refTables[i] = (String ) refTabs.elementAt(i);
703
704             // System.out.println("reference table "+refTables[i]);
705 for (int j = 0; j < numberOfKeys; j++) {
706                 importedKeys[i][j] = (String ) keys.elementAt(j);
707                 imColIndex[i][j] = this.getColIndex(importedKeys[i][j]);
708                 refColumns[i][j] = (String ) cols.elementAt(j);
709                 refColIndex[i][j] = this.getColIndex(refColumns[i][j],
710                                                      refTables[i]);
711
712                 // System.out.println(" importedKeys "+importedKeys[i][j]+"(Index: "+imColIndex[i][j]+") refColumns "+refColumns[i][j]+"(Index: "+refColIndex[i][j]+")");
713 } // end of for (int j=0; j<numberOfKeys; j++)
714 }
715     }
716
717     private void fetchPrimaryKeys() {
718
719         Vector temp = new Vector (20);
720
721         try {
722             if (cConn == null) {
723                 return;
724             }
725
726             if (dbmeta == null) {
727                 dbmeta = cConn.getMetaData();
728             }
729
730             ResultSet colList = dbmeta.getPrimaryKeys(null, null, tableName);
731
732             while (colList.next()) {
733                 temp.addElement(colList.getString("COLUMN_NAME"));
734             }
735
736             colList.close();
737         } catch (SQLException e) {
738             ZaurusEditor.printStatus("SQL Exception: " + e.getMessage());
739         }
740
741         primaryKeys = new String [temp.size()];
742
743         temp.copyInto(primaryKeys);
744
745         pkColIndex = new int[primaryKeys.length];
746
747         for (int i = 0; i < primaryKeys.length; i++) {
748             pkColIndex[i] = this.getColIndex(primaryKeys[i]);
749         } // end of for (int i=0; i<primaryKeys.length; i++)
750 }
751
752     private String generatePKWhere() {
753
754         String stmtString = " WHERE ";
755
756         for (int i = 0; i < primaryKeys.length; i++) {
757             if (i > 0) {
758                 stmtString += " AND ";
759             }
760
761             stmtString += primaryKeys[i] + "=?";
762         } // end of for (int i=0; i<primaryKeys.length; i++)
763
764         return stmtString;
765     }
766
767     // generate the Where-condition for the words
768 private String generateWhere(String [] words, boolean allWords,
769                                  boolean ignoreCase, boolean noMatchWhole) {
770
771         String result = "";
772
773         // if all words must match use AND between the different conditions
774 String join;
775
776         if (allWords) {
777             join = " AND ";
778         } else {
779             join = " OR ";
780         } // end of else
781
782         for (int wordInd = 0; wordInd < words.length; wordInd++) {
783             String oneCondition = "";
784
785             for (int col = 0; col < columns.length; col++) {
786                 if (oneCondition != "") {
787                     oneCondition += " OR ";
788                 }
789
790                 if (ignoreCase) {
791                     if (noMatchWhole) {
792                         oneCondition += "LOWER(" + columns[col] + ") LIKE '%"
793                                         + words[wordInd].toLowerCase() + "%'";
794                     } else {
795                         oneCondition += "LOWER(" + columns[col] + ") LIKE '"
796                                         + words[wordInd].toLowerCase() + "'";
797                     }
798                 } else {
799                     if (noMatchWhole) {
800                         oneCondition += columns[col] + " LIKE '%"
801                                         + words[wordInd] + "%'";
802                     } else {
803                         oneCondition += columns[col] + " LIKE '"
804                                         + words[wordInd] + "'";
805                     }
806                 }
807             }
808
809             if (result != "") {
810                 result += join;
811             }
812
813             result += "(" + oneCondition + ")";
814         }
815
816         if (result != "") {
817             result = " WHERE " + result;
818         } // end of if (result != "")
819
820         // System.out.println("result: "+result);
821 return result;
822     }
823
824     // answer the index of the column named name in the actual table
825 private int getColIndex(String name) {
826
827         for (int i = 0; i < columns.length; i++) {
828             if (name.equals(columns[i])) {
829                 return i;
830             } // end of if (name.equals(columns[i]))
831 } // end of for (int i=0; i<columns.length; i++)
832
833         return -1;
834     }
835
836     // answer the index of the column named colName in the table tabName
837 private int getColIndex(String colName, String tabName) {
838
839         int ordPos = 0;
840
841         try {
842             if (cConn == null) {
843                 return -1;
844             }
845
846             if (dbmeta == null) {
847                 dbmeta = cConn.getMetaData();
848             }
849
850             ResultSet colList = dbmeta.getColumns(null, null, tabName,
851                                                   colName);
852
853             colList.next();
854
855             ordPos = colList.getInt("ORDINAL_POSITION");
856
857             colList.close();
858         } catch (SQLException e) {
859             System.out.println("SQL Exception: " + e.getMessage());
860         }
861
862         return ordPos - 1;
863     }
864
865     // answer the index of the constraint for the column index
866 // answer -1, if the column is not part of any constraint
867 private int getConstraintIndex(int colIndex) {
868
869         for (int i = 0; i < imColIndex.length; i++) {
870             for (int j = 0; j < imColIndex[i].length; j++) {
871                 if (colIndex == imColIndex[i][j]) {
872                     return i;
873                 } // end of if (col == imColIndex[i][j])
874 } // end of for (int j=0; j<imColIndex[i].length; j++)
875 } // end of for (int i=0; i<imColIndex.length; i++)
876
877         return -1;
878     }
879
880     private void initGUI() {
881
882         Panel pEntry = new Panel ();
883
884         pEntry.setLayout(new GridBagLayout ());
885
886         GridBagConstraints c = new GridBagConstraints ();
887
888         c.fill = GridBagConstraints.HORIZONTAL;
889         c.insets = new Insets (3, 3, 3, 3);
890         c.gridwidth = 1;
891         c.gridheight = 1;
892         c.weightx = c.weighty = 1;
893         c.anchor = GridBagConstraints.WEST;
894         komponente = new ZaurusComponent[columns.length];
895
896         for (int i = 0; i < columns.length; i++) {
897             c.gridy = i;
898             c.gridx = 0;
899
900             pEntry.add(new Label ((String ) columns[i]), c);
901
902             c.gridx = 1;
903
904             int constraint = this.getConstraintIndex(i);
905
906             if (constraint >= 0 && imColIndex[constraint].length == 1) {
907
908                 // we use ony foreign keys with one index
909 ZaurusChoice tmp = new ZaurusChoice();
910
911                 this.fillZChoice(tmp, refTables[constraint],
912                                  refColumns[constraint][0]);
913                 tmp.addItemListener(this);
914
915                 komponente[i] = tmp;
916
917                 pEntry.add(tmp, c);
918             } else if (columnTypes[i] == java.sql.Types.DATE) {
919
920                 // System.out.println("hier gibt es eine Date-Spalte namens "+columns[i]);
921 ZaurusTextField tmp = new ZaurusTextField(8);
922
923                 tmp.addTextListener(this);
924                 pEntry.add(tmp, c);
925
926                 komponente[i] = tmp;
927             } else {
928                 ZaurusTextField tmp = new ZaurusTextField(5);
929
930                 tmp.addTextListener(this);
931                 pEntry.add(tmp, c);
932
933                 komponente[i] = tmp;
934             }
935
936             komponente[i].setEditable(true);
937         }
938
939         this.add(pEntry);
940     }
941
942     // get and show the values of the actual row in the GUI
943 private void showAktRow() {
944
945         try {
946             pStmt.clearParameters();
947
948             for (int i = 0; i < primaryKeys.length; i++) {
949                 pStmt.setObject(i + 1, resultRowPKs[aktRowNr][i]);
950             } // end of for (int i=0; i<primaryKeys.length; i++)
951
952             ResultSet rs = pStmt.executeQuery();
953
954             rs.next();
955
956             for (int i = 0; i < columns.length; i++) {
957                 komponente[i].setContent(rs.getString(i + 1));
958             } // end of for (int i=0; i<primaryKeys.length; i++)
959
960             rs.close();
961         } catch (SQLException e) {
962             ZaurusEditor.printStatus("SQL Exception: " + e.getMessage());
963         } // end of try-catch
964
965         for (int i = 0; i < columns.length; i++) {
966             komponente[i].clearChanges();
967         }
968     }
969 }
970

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags