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