zaurustableform.java

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

        // there are changes to the database
        // memorize all columns which have been changed
        int[] changedColumns = new int[columns.length];
        int   countChanged   = 0;

        // build the update string
        String updateString = "";

        for (int i = 0; i < columns.length; i++) {
            if (komponente[i].hasChanged()) {
                if (updateString != "") {
                    updateString += ", ";
                }

                updateString                   += columns[i] + "=?";
                changedColumns[countChanged++] = i;
            }
        }    // end of for (int i=0; i<columns.length; i++)

        if (countChanged > 0) {
            updateString = "UPDATE " + tableName + " SET " + updateString
                           + this.generatePKWhere();

            // System.out.println("update "+updateString);
            try {

                // fill the question marks
                PreparedStatement ps = cConn.prepareStatement(updateString);

                ps.clearParameters();

                int i;

                for (i = 0; i < countChanged; i++) {
                    ps.setObject(i + 1,
                                 komponente[changedColumns[i]].getContent());

                    // System.out.print(" changed feld "+komponente[changedColumns[i]].getContent());
                }    // end of for (int i=0; i<countChanged; i++)

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

                ps.executeUpdate();
                ZaurusEditor.printStatus("changed row was saved to table "
                                         + tableName);

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

                return false;
            }        // end of try-catch
        } else {

            //              System.out.println("no changes");
            return true;
        }            // end of if (changed)
    }

    // save a new row
    // answer true, if saving succeeds
    public boolean saveNewRow() {

        // check the fields of the primary keys whether one is empty
        boolean onePKempty = false;
        int     tmp;

        for (tmp = 0; tmp < primaryKeys.length; tmp++) {
            if (komponente[pkColIndex[tmp]].getContent().equals("")) {
                onePKempty = true;

                break;
            }
        }

        if (onePKempty) {
            komponente[pkColIndex[tmp]].requestFocus();
            ZaurusEditor.printStatus("no value for primary key "
                                     + primaryKeys[tmp]);

            return false;
        }    // end of if (onePKempty)

        // build the insert string
        String insertString = "INSERT INTO " + tableName + " VALUES(";

        for (int j = 0; j < columns.length; j++) {
            if (j > 0) {
                insertString += ", ";
            }

            insertString += "?";
        }    // end of for (int i=0; i<columns.length; i++)

        insertString += ")";

        // System.out.println("insert string "+insertString);
        try {

            // fill the question marks
            PreparedStatement ps = cConn.prepareStatement(insertString);

            ps.clearParameters();

            int i;

            for (i = 0; i < columns.length; i++) {
                ps.setObject(i + 1, komponente[i].getContent());
            }

            ps.executeUpdate();
            ZaurusEditor.printStatus("new row was saved to table "
                                     + tableName);

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

            return false;
        }    // end of try-catch
    }

    // read all primary key values into resultRowPKs for the rows which meet the search condition i. e.
    // which contains the search words
    // answer the number of found rows, -1 if there is an SQL exception
    public int searchRows(String[] words, boolean allWords,
                          boolean ignoreCase, boolean noMatchWhole) {

        // System.out.print("search in " + tableName + " for: ");
        //  for (int i=0; i < words.length; i++) {
        //      System.out.print(words[i]+", ");
        //  }
        // System.out.println("allWords = "+allWords+", ignoreCase = "+ignoreCase+", noMatchWhole= "+noMatchWhole);
        String where = this.generateWhere(words, allWords, ignoreCase,
                                          noMatchWhole);
        Vector temp = new Vector(20);

        try {
            Statement stmt = cConn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT "
                                             + this.getPrimaryKeysString()
                                             + " FROM " + tableName + where);

            while (rs.next()) {
                Object[] pkValues = new Object[primaryKeys.length];

                for (int i = 0; i < primaryKeys.length; i++) {
                    pkValues[i] = rs.getObject(pkColIndex[i] + 1);
                }    // end of for (int i=0; i<primaryKeys.length; i++)

                temp.addElement(pkValues);
            }

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

            return -1;
        }            // end of try-catch

        resultRowPKs   = new Object[temp.size()][primaryKeys.length];
        numberOfResult = temp.size();

        for (int i = 0; i < primaryKeys.length; i++) {
            for (int j = 0; j < temp.size(); j++) {
                resultRowPKs[j][i] = ((Object[]) temp.elementAt(j))[i];
            }    // end of for (int j=0; j<temp.size(); j++)
        }        // end of for (int i=0; i<primaryKeys.length; i++)

        // prepare statement for fetching the result rows for later use
        String stmtString = "SELECT * FROM " + tableName;

        try {
            pStmt = cConn.prepareStatement(stmtString
                                           + this.generatePKWhere());
        } catch (SQLException e) {
            System.out.println("SQL Exception: " + e.getMessage());
        }    // end of try-catch

        // System.out.println("prepared statement: "+stmtString);
        if (numberOfResult > 0) {
            this.disablePKFields();

            aktRowNr = 0;

            this.showAktRow();
        }    // end of if (numberOfResult > 0)

        // System.out.println("number of rows: "+numberOfResult);
        return numberOfResult;
    }

    public void actionPerformed(ActionEvent e) {}

    public void textValueChanged(TextEvent e) {

        for (int i = 0; i < columns.length; i++) {
            if (komponente[i] == e.getSource()) {
                komponente[i].setChanged();

                break;
            }
        }
    }

    public void itemStateChanged(ItemEvent e) {

        for (int i = 0; i < columns.length; i++) {
            if (komponente[i] == e.getSource()) {
                komponente[i].setChanged();

                break;
            }
        }
    }

    // ******************************************************
    // private methods
    // ******************************************************
    // set all fields for primary keys to not editable
    private void disablePKFields() {

        for (int i = 0; i < primaryKeys.length; i++) {
            komponente[pkColIndex[i]].setEditable(false);
        }    // end of for (int i=0; i<columns.length; i++)
    }

    // fetch all values from a table and a column
    // fill the ZaurusChoice zc with the row values for the Choice
    // and the column values as values
    private void fillZChoice(ZaurusChoice zc, String tab, String col) {

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

            Statement stmt = cConn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM " + tab
                                             + " ORDER BY " + col);
            ResultSetMetaData rsmd            = rs.getMetaData();
            int               numberOfColumns = rsmd.getColumnCount();
            int               colIndex        = rs.findColumn(col);

            while (rs.next()) {
                String tmp = "";

                for (int i = 1; i <= numberOfColumns; i++) {
                    if (i > 1) {
                        tmp += "; ";
                    }

                    tmp += rs.getString(i);
                }    // end of for (int i=1; i<=numberOfColumns; i++)

                zc.add(tmp, rs.getString(colIndex));
            }

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

    // fetch all column names
    private void fetchColumns() {

        Vector temp     = new Vector(20);
        Vector tempType = new Vector(20);

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

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

            ResultSet colList = dbmeta.getColumns(null, null, tableName, "%");

            while (colList.next()) {
                temp.addElement(colList.getString("COLUMN_NAME"));
                tempType.addElement(new Short(colList.getShort("DATA_TYPE")));
            }

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

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

        temp.copyInto(columns);

        columnTypes = new short[temp.size()];

        for (int i = 0; i < columnTypes.length; i++) {
            columnTypes[i] = ((Short) tempType.elementAt(i)).shortValue();
        }
    }

    // fetch the imported keys i.e. columns which reference to foreign keys in other tables
    private void fetchImportedKeys() {

        Vector imKeys      = new Vector(20);
        Vector imKeyNames  = null;
        Vector refTabs     = new Vector(20);
        Vector refCols     = new Vector(20);
        Vector refColNames = null;

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

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

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