zaurustableform.java

hsqldb是100％java实现的数据库,是一个开放源代码的JAVA数据库 l 具有标准的SQL语法和JAVA接口 l HSQLDB可以自由使用和分发 l 非常简洁和快速的

            String    pkTable, pkColumn, fkColumn;
            int       keySeq;

            while (colList.next()) {
                pkTable  = colList.getString("PKTABLE_NAME");
                pkColumn = colList.getString("PKCOLUMN_NAME");
                fkColumn = colList.getString("FKCOLUMN_NAME");
                keySeq   = colList.getInt("KEY_SEQ");

                if (keySeq == 1) {
                    if (imKeyNames != null) {
                        imKeys.addElement(imKeyNames);
                        refCols.addElement(refColNames);
                    }    // end of if (exKeyNames != null)

                    imKeyNames  = new Vector(20);
                    refColNames = new Vector(20);

                    refTabs.addElement(pkTable);
                }        // end of if (keySeq == 1)

                imKeyNames.addElement(fkColumn);
                refColNames.addElement(pkColumn);
            }

            if (imKeyNames != null) {
                imKeys.addElement(imKeyNames);
                refCols.addElement(refColNames);
            }            // end of if (exKeyNames != null)

            colList.close();
        } catch (SQLException e) {
            ZaurusEditor.printStatus("SQL Exception: " + e.getMessage());
        }

        // System.out.println("Imported Keys of "+tableName);
        int numberOfConstraints = imKeys.size();

        importedKeys = new String[numberOfConstraints][];
        imColIndex   = new int[numberOfConstraints][];
        refTables    = new String[numberOfConstraints];
        refColumns   = new String[numberOfConstraints][];
        refColIndex  = new int[numberOfConstraints][];

        for (int i = 0; i < numberOfConstraints; i++) {
            Vector keys         = (Vector) imKeys.elementAt(i);
            Vector cols         = (Vector) refCols.elementAt(i);
            int    numberOfKeys = keys.size();

            importedKeys[i] = new String[numberOfKeys];
            imColIndex[i]   = new int[numberOfKeys];
            refColumns[i]   = new String[numberOfKeys];
            refColIndex[i]  = new int[numberOfKeys];
            refTables[i]    = (String) refTabs.elementAt(i);

            // System.out.println("reference table "+refTables[i]);
            for (int j = 0; j < numberOfKeys; j++) {
                importedKeys[i][j] = (String) keys.elementAt(j);
                imColIndex[i][j]   = this.getColIndex(importedKeys[i][j]);
                refColumns[i][j]   = (String) cols.elementAt(j);
                refColIndex[i][j] = this.getColIndex(refColumns[i][j],
                                                     refTables[i]);

                // System.out.println("   importedKeys "+importedKeys[i][j]+"(Index: "+imColIndex[i][j]+") refColumns "+refColumns[i][j]+"(Index: "+refColIndex[i][j]+")");
            }    // end of for (int j=0; j<numberOfKeys; j++)
        }
    }

    private void fetchPrimaryKeys() {

        Vector temp = new Vector(20);

        try {
            if (cConn == null) {
                return;
            }

            if (dbmeta == null) {
                dbmeta = cConn.getMetaData();
            }

            ResultSet colList = dbmeta.getPrimaryKeys(null, null, tableName);

            while (colList.next()) {
                temp.addElement(colList.getString("COLUMN_NAME"));
            }

            colList.close();
        } catch (SQLException e) {
            ZaurusEditor.printStatus("SQL Exception: " + e.getMessage());
        }

        primaryKeys = new String[temp.size()];

        temp.copyInto(primaryKeys);

        pkColIndex = new int[primaryKeys.length];

        for (int i = 0; i < primaryKeys.length; i++) {
            pkColIndex[i] = this.getColIndex(primaryKeys[i]);
        }    // end of for (int i=0; i<primaryKeys.length; i++)
    }

    private String generatePKWhere() {

        String stmtString = " WHERE ";

        for (int i = 0; i < primaryKeys.length; i++) {
            if (i > 0) {
                stmtString += " AND ";
            }

            stmtString += primaryKeys[i] + "=?";
        }    // end of for (int i=0; i<primaryKeys.length; i++)

        return stmtString;
    }

    // generate the Where-condition for the words
    private String generateWhere(String[] words, boolean allWords,
                                 boolean ignoreCase, boolean noMatchWhole) {

        String result = "";

        // if all words must match use AND between the different conditions
        String join;

        if (allWords) {
            join = " AND ";
        } else {
            join = " OR ";
        }    // end of else

        for (int wordInd = 0; wordInd < words.length; wordInd++) {
            String oneCondition = "";

            for (int col = 0; col < columns.length; col++) {
                if (oneCondition != "") {
                    oneCondition += " OR ";
                }

                if (ignoreCase) {
                    if (noMatchWhole) {
                        oneCondition += "LOWER(" + columns[col] + ") LIKE '%"
                                        + words[wordInd].toLowerCase() + "%'";
                    } else {
                        oneCondition += "LOWER(" + columns[col] + ") LIKE '"
                                        + words[wordInd].toLowerCase() + "'";
                    }
                } else {
                    if (noMatchWhole) {
                        oneCondition += columns[col] + " LIKE '%"
                                        + words[wordInd] + "%'";
                    } else {
                        oneCondition += columns[col] + " LIKE '"
                                        + words[wordInd] + "'";
                    }
                }
            }

            if (result != "") {
                result += join;
            }

            result += "(" + oneCondition + ")";
        }

        if (result != "") {
            result = " WHERE " + result;
        }    // end of if (result != "")

        // System.out.println("result: "+result);
        return result;
    }

    // answer the index of the column named name in the actual table
    private int getColIndex(String name) {

        for (int i = 0; i < columns.length; i++) {
            if (name.equals(columns[i])) {
                return i;
            }    // end of if (name.equals(columns[i]))
        }        // end of for (int i=0; i<columns.length; i++)

        return -1;
    }

    // answer the index of the column named colName in the table tabName
    private int getColIndex(String colName, String tabName) {

        int ordPos = 0;

        try {
            if (cConn == null) {
                return -1;
            }

            if (dbmeta == null) {
                dbmeta = cConn.getMetaData();
            }

            ResultSet colList = dbmeta.getColumns(null, null, tabName,
                                                  colName);

            colList.next();

            ordPos = colList.getInt("ORDINAL_POSITION");

            colList.close();
        } catch (SQLException e) {
            System.out.println("SQL Exception: " + e.getMessage());
        }

        return ordPos - 1;
    }

    // answer the index of the constraint for the column index
    // answer -1, if the column is not part of any constraint
    private int getConstraintIndex(int colIndex) {

        for (int i = 0; i < imColIndex.length; i++) {
            for (int j = 0; j < imColIndex[i].length; j++) {
                if (colIndex == imColIndex[i][j]) {
                    return i;
                }    // end of if (col == imColIndex[i][j])
            }        // end of for (int j=0; j<imColIndex[i].length; j++)
        }            // end of for (int i=0; i<imColIndex.length; i++)

        return -1;
    }

    private void initGUI() {

        Panel pEntry = new Panel();

        pEntry.setLayout(new GridBagLayout());

        GridBagConstraints c = new GridBagConstraints();

        c.fill       = GridBagConstraints.HORIZONTAL;
        c.insets     = new Insets(3, 3, 3, 3);
        c.gridwidth  = 1;
        c.gridheight = 1;
        c.weightx    = c.weighty = 1;
        c.anchor     = GridBagConstraints.WEST;
        komponente   = new ZaurusComponent[columns.length];

        for (int i = 0; i < columns.length; i++) {
            c.gridy = i;
            c.gridx = 0;

            pEntry.add(new Label((String) columns[i]), c);

            c.gridx = 1;

            int constraint = this.getConstraintIndex(i);

            if (constraint >= 0 && imColIndex[constraint].length == 1) {

                // we use ony foreign keys with one index
                ZaurusChoice tmp = new ZaurusChoice();

                this.fillZChoice(tmp, refTables[constraint],
                                 refColumns[constraint][0]);
                tmp.addItemListener(this);

                komponente[i] = tmp;

                pEntry.add(tmp, c);
            } else if (columnTypes[i] == java.sql.Types.DATE) {

                //              System.out.println("hier gibt es eine Date-Spalte namens "+columns[i]);
                ZaurusTextField tmp = new ZaurusTextField(8);

                tmp.addTextListener(this);
                pEntry.add(tmp, c);

                komponente[i] = tmp;
            } else {
                ZaurusTextField tmp = new ZaurusTextField(5);

                tmp.addTextListener(this);
                pEntry.add(tmp, c);

                komponente[i] = tmp;
            }

            komponente[i].setEditable(true);
        }

        this.add(pEntry);
    }

    // get and show the values of the actual row in the GUI
    private void showAktRow() {

        try {
            pStmt.clearParameters();

            for (int i = 0; i < primaryKeys.length; i++) {
                pStmt.setObject(i + 1, resultRowPKs[aktRowNr][i]);
            }    // end of for (int i=0; i<primaryKeys.length; i++)

            ResultSet rs = pStmt.executeQuery();

            rs.next();

            for (int i = 0; i < columns.length; i++) {
                komponente[i].setContent(rs.getString(i + 1));
            }    // end of for (int i=0; i<primaryKeys.length; i++)

            rs.close();
        } catch (SQLException e) {
            ZaurusEditor.printStatus("SQL Exception: " + e.getMessage());
        }        // end of try-catch

        for (int i = 0; i < columns.length; i++) {
            komponente[i].clearChanges();
        }
    }
}