zaurustableform.java

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

/* Copyright (c) 2001-2005, The HSQL Development Group
 * All rights reserved.
 *
 * Redistribution and use in source and binary forms, with or without
 * modification, are permitted provided that the following conditions are met:
 *
 * Redistributions of source code must retain the above copyright notice, this
 * list of conditions and the following disclaimer.
 *
 * Redistributions in binary form must reproduce the above copyright notice,
 * this list of conditions and the following disclaimer in the documentation
 * and/or other materials provided with the distribution.
 *
 * Neither the name of the HSQL Development Group nor the names of its
 * contributors may be used to endorse or promote products derived from this
 * software without specific prior written permission.
 *
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
 * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
 * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
 * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
 * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
 * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
 * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 */


package org.hsqldb.util;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;
import java.awt.GridBagConstraints;
import java.awt.GridBagLayout;
import java.awt.Insets;
import java.awt.Label;
import java.awt.Panel;
import java.awt.ScrollPane;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.ItemEvent;
import java.awt.event.ItemListener;
import java.awt.event.TextEvent;
import java.awt.event.TextListener;

/**
 * Class declaration
 *
 *
 * @author ulrivo@users
 * @version 1.0.0
 */

// an entry panel to input/edit a record of a sql table
// ZaurusTableForm is constructed with a tableName and a connection
public class ZaurusTableForm extends ScrollPane
implements TextListener, ItemListener, ActionListener {

    // connection to database - brought via the constructor
    Connection       cConn;
    DatabaseMetaData dbmeta;

    // the name of table for the form
    String tableName;

    // array holding the components (TextField or Choice) in the GUI
    ZaurusComponent[] komponente;

    // the columns of the table
    String[] columns;

    // and their types
    short[] columnTypes;

    // the names of the primary keys of the table
    String[] primaryKeys;

    // the position of the primary keys in the table i. e. the column index starting from 0
    int[] pkColIndex;

    // the names of the imported/foreign keys of the table
    // first dimension is running through the constraints, second dim through the keys of one constraint
    String[][] importedKeys;

    // the position of the imported keys in the table i. e. the column index starting from 0
    int[][] imColIndex;

    // the names of the tables and columns which are the reference for the imported keys
    String[]   refTables;
    String[][] refColumns;

    // the position of the reference keys in the reference table i. e. the column index starting from 0
    int[][] refColIndex;

    // an array holding array of primary keys values matching the search condition
    // first dimension through the results, second dimension running through the primary keys
    Object[][] resultRowPKs;

    // there is an explicit count because a delete may shrink the result rows
    int numberOfResult;

    // prepared statement to fetch the required rows
    PreparedStatement pStmt;

    // pointer into the resultRowPKs
    int aktRowNr;

    public ZaurusTableForm(String name, Connection con) {

        super();

        tableName = name;
        cConn     = con;

        this.fetchColumns();
        this.fetchPrimaryKeys();

        // System.out.print("primaryKeys: ");
        //  for (int i=0; i<primaryKeys.length;i++) {
        // System.out.print(primaryKeys[i]+", ");
        //  } // end of for (int i=0; i<primaryKeys.length;i++)
        //  System.out.println();
        this.fetchImportedKeys();
        this.initGUI();
    }

    // cancel the change/update of a row - show the row again
    public void cancelChanges() {
        this.showAktRow();
    }

    // delete current row, answer special action codes, see comment below
    public int deleteRow() {

        // build the delete string
        String deleteString = "DELETE FROM " + tableName
                              + this.generatePKWhere();

        // System.out.println("delete string "+deleteString);
        try {

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

            ps.clearParameters();

            int i;

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

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

            return 0;
        }        // end of try-catch

        // delete the corresponding primary key values from resultRowPKs
        numberOfResult--;

        for (int i = aktRowNr; i < numberOfResult; i++) {
            for (int j = 0; j < primaryKeys.length; j++) {
                resultRowPKs[i][j] = resultRowPKs[i + 1][j];
            }
        }

        // there are the following outcomes after deleting aktRowNr:
        /*
                                           A B C D E F
        no rows left                   J N N N N N
        one row left                   - J N J N N
        deleted row was the last row   - J J N N N
        deleted row was the pre-last   - - - - J N

            first                          D X + D + *
             .                               D X X D D
             .                                 D   X +
            last                                     X

            new numberOfResult             0 1 2 1 2 2
            old aktRowNr                     0 1 2 0 1 0

        D - deleted row
            X - any one row
            + - one or more rows
        * - zero or more rows

        */

        // A. return to the search panel and tell 'last row deleted' on the status line
        // B. show the previous row and disable previous button
        // C. show the previous row as akt row
        // D. show akt row and disable next button
        // E. show akt row and disable next button
        // F. show akt row
        // these actions reduce to the following actions for ZaurusEditor:
        // 1. show search panel
        // 2. disable previous button
        // 3. disable next button
        // 4. do nothing
        // and 1,2,3,4 are the possible return codes
        int actionCode;

        if (numberOfResult == 0) {

            // case A
            actionCode = 1;

            ZaurusEditor.printStatus("Last row was deleted.");

            return actionCode;
        } else if (numberOfResult == aktRowNr) {

            // B or C
            // new aktRow is previous row
            aktRowNr--;

            if (aktRowNr == 0) {

                // B
                actionCode = 2;
            } else {

                // C
                actionCode = 4;
            }    // end of if (aktRowNr == 0)
        } else {

            // D, E, F
            if (numberOfResult >= 2 && aktRowNr < numberOfResult - 1) {

                // F
                actionCode = 4;
            } else {
                actionCode = 3;
            }    // end of else
        }

        this.showAktRow();
        ZaurusEditor.printStatus("Row was deleted.");

        return actionCode;
    }

    // answer a String containing a String list of primary keys i. e. "pk1, pk2, pk3"
    public String getPrimaryKeysString() {

        String result = "";

        for (int i = 0; i < primaryKeys.length; i++) {
            if (result != "") {
                result += ", ";
            }

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

        return result;
    }

    // open the panel to insert a new row into the table
    public void insertNewRow() {

        // reset all fields
        for (int i = 0; i < komponente.length; i++) {
            komponente[i].clearContent();
        }    // end of for (int i=0; i<komponente.length; i++)

        // reset the field for the primary keys
        for (int i = 0; i < primaryKeys.length; i++) {
            komponente[pkColIndex[i]].setEditable(true);
        }

        ZaurusEditor.printStatus("enter a new row for table " + tableName);
    }

    // show next row
    // answer true, if there is after the next row another row
    public boolean nextRow() {

        if (aktRowNr + 1 == numberOfResult) {
            return false;
        }

        aktRowNr++;

        this.showAktRow();

        return (aktRowNr + 1 < numberOfResult);
    }

    // show prev row
    // answer true, if there is previous the previous row another row
    public boolean prevRow() {

        if (aktRowNr == 0) {
            return false;
        }

        aktRowNr--;

        this.showAktRow();

        return (aktRowNr > 0);
    }

    // save all changes which are be made in the textfelder to the database
    // answer true, if the update succeeds
    public boolean saveChanges() {

        // the initial settings of the textfields counts with one
        // so a real change by the user needs as many changes as there are columns
        // System.out.print("Anderungen in den Feldern: ");