jdbcjtable.java

jdbc 的实例源码

/*
 *
 * jdbcJTable
 *
 * Copyright 1996 John Wiley & Sons, Inc. All Rights Reserved. Reproduction
 * or translation of this work beyond that permitted in Section 117 of the 1976
 * United States Copyright Act without the express written permission of the
 * copyright owner is unlawful. Requests for further information should be 
 * addressed to Permissions Department, John Wiley & Sons, Inc. The 
 * purchaser may make back-up copies for his/her own use only and not for 
 * distribution or resale. The Publisher assumes no responsibility for errors, 
 * omissions, or damages, caused by the use of this    software or from the use
 * of the information contained herein.
 *
 */

import com.sun.java.swing.*;
import com.sun.java.swing.table.*;

import java.awt.*;
import java.awt.event.*;
import java.sql.*;
import java.util.*;
import java.lang.*;

public class jdbcJTable {

    JTable grid;

    // a JDBC connection, and a Statement object
    //
    Connection con;
    Statement stmt;

    String table, column_list, pkey;

    int pkey_col;

    /**
     *
     * Constructs a new jdbcJTable object.
     * @param url a URL to a valid JDBC data source.
     * @param user a username
     * @param pwd a password
     * @param tbl the table to edit
     * @param cols a comma-delimited list of columns to view
     * @param pk the name of the primary key
     *
     */
    public jdbcJTable(String url, String user, String pwd, 
                      String tbl, String cols, String pk) {

        table       = tbl;
        column_list = cols;
        pkey        = pk;

        // invoke the connect() method
        //
        connect(url, user, pwd);

        // retrieve the data
        //
        getData();

        // create a Frame
        //
        Frame f = new Frame();
        // Set up a WindowAdapter that can close the Frame when the user clicks
        // on the close control.
        //
        f.addWindowListener(new WindowAdapter() {
            public void windowClosing(WindowEvent e) {
                System.exit(0);
            }
        });

        f.setLayout(new BorderLayout());
        JScrollPane scrollpane = JTable.createScrollPaneForTable(grid);
        scrollpane.setPreferredSize(new Dimension(700, 300));
                        
        grid.getTableHeader().setUpdateTableInRealTime(false);

        f.add("Center", scrollpane);
        f.setTitle("cardfile");

        // resize and show the frame
        //
        f.pack();
        f.show();        

    }

    /*
     *
     * make a JDBC connection.
     *
     */
    public void connect (String url, String user, String pwd) {

        try {

            // ask the DriverManager to find
            // a suitable driver for this URL.
            //
            con = DriverManager.getConnection(url, user, pwd);

            // create a statement object. This is the
            // gateway into the world of playing
            // with fun things like SQL statements
            // and results
            //
            stmt = con.createStatement();
            
        } catch (Exception e) {
            e.printStackTrace ();
        }
    }
    
    /**
     * getData()
     *
     */
    protected void getData() {

        ResultSet rs;

        try {

            // retrieve all rows from the table.
            //
            rs = stmt.executeQuery("SELECT " + column_list + " FROM " + table);

            // get a ResultSetMetaData object
            //
            ResultSetMetaData meta = rs.getMetaData();

            final Vector rows = new Vector(); // a Vector to contain each row

            int i;
            int rowcount = 0;

            // process the result set
            //
            while (rs.next()) {

                rowcount++;

                // create an array of columns; this will hold the
                // String objects corresponding to each column,
                // and will be inserted into the rows Vector.
                //
                String[] columns = new String[meta.getColumnCount()];

                // store the value of each column
                //
                for (i = 1; i <= meta.getColumnCount(); i++) {
                    columns[i - 1] = rs.getString(i);
                }
                rows.addElement(columns);


            }

            // loop for each column. Here, the column headers
            // will be set to have the correct title and width.
            // 
            final String[] column_list = new String[meta.getColumnCount()];

            for (i = 1; i <= meta.getColumnCount(); i++) {

                // get the column name
                //
                String col_name = meta.getColumnName(i);
                column_list[i - 1] = col_name;

                // determine a suitable column width: the greater
                // of the column's display size or the size of
                // its column name.
                //
                int size = Math.max(meta.getColumnDisplaySize(i), col_name.length());
                //grid.addColumn(col_name, size, null, new DefaultCellEditor(new JTextField("")));

                // if this column is the primary key, make it non-editable,
                // and store the index in the pkey_col field.
                //
                if (col_name.equals(pkey)) {
                    pkey_col = i - 1;
                }

            }

            // set each column value on a row by row basis
            //
            final Object[][] data = new Object[rows.size()][meta.getColumnCount()];
            int j;
            for (j = 0; j < rows.size(); j++) {

                // get the column array from the rows Vector
                //
                String[] columns = (String[]) rows.elementAt(j);

                // add each column's value to the array
                //
                for (i = 0; i < columns.length; i++) {
                    data[j][i] = columns[i];
                }

            }

            final int pkey_colnum = pkey_col;

            TableModel dataModel = new AbstractTableModel() {
        
                public int getColumnCount() { return column_list.length; }
                public int getRowCount() { return rows.size(); }
                public Object getValueAt(int row, int col) {
                        return data[row][col];
                }
        
                public String getColumnName(int column) {
                        return column_list[column];
                }
            
                public boolean isCellEditable(int row, int col) {
                        return (col != pkey_colnum);
                }
                public void setValueAt(Object aValue, int row, int column) {

                    // get the column name that was edited
                    //
                    String column_name = column_list[column];

                    // get the column value
                    //
                    String column_val = (String) aValue;

                    // get the value of this row's primary key
                    //
                    String pkey_val = (String) data[row][pkey_colnum];

                    // construct an SQL UPDATE statement
                    //
                    String update = "UPDATE " + table + 
                                                    " SET " + column_name + " = '" + column_val + "' " +
                                                    " WHERE " + pkey + " = " + pkey_val;
        
                    // issue the UPDATE
                    //
                    try {
                        stmt.executeUpdate(update);
                    } catch (SQLException e) {
                        SQLErr(e);
                    }
                    data[row][column] = aValue;
                }
            };
            grid = new JTable(dataModel);

            // Add a JTextField based editor for every column except the primary
            // key column. This will probably change in later releases of JFC.
            //
            for (int k = 0; k < column_list.length; k++) {

                TableColumn c = grid.getColumn(new Integer(k));

                // Get this column's renderer.
                //
                DefaultCellRenderer renderer = 
                  new DefaultCellRenderer(new JLabel());
                c.setCellRenderer(renderer);

                if (k != pkey_col) {
                    JTextField tf = new JTextField();
                    tf.setBorder(null);
                    c.setCellEditor(new DefaultCellEditor(tf));
                    renderer.setToolTipText("Double click to edit");
                } else {
                    renderer.setToolTipText(
                        "Sorry, this field is read-only");
                }

            }

        } catch (SQLException e) {
            SQLErr(e);
        }
    }


    
    /**
     * 
     * instantiate the jdbcJTable object with test data.
     *
     */
    public static void main (String argv[]) {
        
        try {

            // register all of the JDBC classes you might use
            // you can comment out or remove the ones you
            // are not using.
            //
            Class.forName("textFileDriver").newInstance();               // the tinySQL textFile driver
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance(); // JDBC-ODBC bridge
            Class.forName("com.sybase.jdbc.SybDriver").newInstance();    // Sybase
            Class.forName("com.imaginary.sql.msql.MsqlDriver").newInstance();    // mSQL
            
        } catch (Exception e) {
            e.printStackTrace();
        }

        if (argv.length == 0) {
                System.out.println("You must supply a URL to a JDBC data source.");
                System.out.println("");
                System.out.println("Example:");
                System.out.println("java DDL jdbc:odbc:DATA_SOURCE_NAME;" +
                                                     "UID=userid;PWD=password");
            System.exit(0);
        }

        // the user might have passed in a user name or password,
        // so try to read those in, as well
        //
        String user, pwd;
        if (argv.length > 1) {
            user = argv[1];
        } else {
            user = "";
        }
        if (argv.length > 2) {
            pwd = argv[2];
        } else {
            pwd = "";
        }
        String column_list = 
            "name, address, city, state, zip, country, phone, id";
        jdbcJTable j = 
            new jdbcJTable(argv[0], user, pwd, "cardfile", column_list, "id");
     
    }

    /**
     *
     * mindless error handler which prints out state, message,
     * and vendor info for SQL errors.
     */
    public void SQLErr(SQLException e) {
        while (e != null) {
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("Message:    " + e.getMessage());
            System.out.println("Vendor:     " + e.getErrorCode());
            e.printStackTrace();
            e = e.getNextException();
            System.out.println("");
        }
    }

}