jdbcmlgrid.java

有关JDBC的使用一些编程实例,有关与数据库连接的代码

/*
 *
 * jdbcMlGrid
 *
 * An extension of the MlGrid object which allows
 * in-place editing of a table.
 *
 * 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 java.awt.*;
import mlsoft.mct.*;
import java.sql.*;
import java.util.*;

public class jdbcMlGrid extends MlGrid {

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

  String table, column_list, pkey;

  int pkey_col;

  /**
   *
   * Constructs a new jdbcMlGrid 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 jdbcMlGrid(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);

    // do some preliminary grid setup
    //
    prepareGrid();

    // retrieve the data
    //
    getData();

    // do some more grid setup
    //
    layoutGrid();

    // Add a listener for grid events.
    //
    addMlGridListener( new GridEventHandler() );

  }

  /*
   *
   * 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 ();
    }
  }
  
  public void prepareGrid () {

    MlResources res;
    Font boldFont;

    res = new MlResources();

    this.setValue("layoutFrozen", true);

    res.add("hsbDisplayPolicy", "DISPLAY_ALWAYS");
    res.add("vsbDisplayPolicy", "DISPLAY_ALWAYS");
    res.add("highlightRowMode", false);
    res.add("selectionPolicy", "SELECT_CELL");
    res.add("allowColumnResize", false);
    res.add("allowRowResize", false);
    res.add("shadowThickness", 1);
    this.setValues(res);

    res.clear();
    res.add("cellDefaults", true);
    res.add("cellEditable", true);
    res.add("cellAlignment", "ALIGNMENT_LEFT");
    res.add("cellBackground", "#ffffff");
    res.add("cellLeftBorderType", "BORDER_NONE");
    res.add("cellTopBorderType", "BORDER_NONE");
    this.setValues(res);

    this.setValue("layoutFrozen", false);
  }

  public void layoutGrid() {

    MlResources res;
    Font boldFont;

    boldFont = new Font("Helvetica", Font.BOLD, 12);
    res = new MlResources();

    this.setValue("layoutFrozen", true);

    res.clear();
    res.add("rowType", "ALL_TYPES");
    res.add("columnType", "HEADING");
    res.add("column", 0);
    res.add("cellAlignment", "ALIGNMENT_CENTER");
    res.add("cellBackground", "#c0c0c0");
    res.add("cellFont", boldFont);
    res.add("cellLeftBorderType", "BORDER_LINE");
    res.add("cellTopBorderType", "BORDER_LINE");
    this.setValues(res);

    res.clear();
    res.add("rowType", "HEADING");
    res.add("row", 0);
    res.add("cellAlignment", "ALIGNMENT_CENTER");
    res.add("cellBackground", "#c0c0c0");
    res.add("cellFont", boldFont);
    res.add("cellLeftBorderType", "BORDER_LINE");
    res.add("cellTopBorderType", "BORDER_LINE");
    this.setValues(res);

    this.setValue("layoutFrozen", false);

  }

  /**
   * getData()
   *
   */
  protected void getData() {

    ResultSet rs;

    try {

      this.setValue("layoutFrozen", true); // freeze the layout

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

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

      // a resource object to manipulate the grid
      //
      MlResources res = new MlResources();

      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);

      }

      // use the resource object to add heading rows, heading
      // columns, and to set the number of columns and rows
      // according to the number of columns and rows in the
      // result set.
      //
      res.clear();
      res.add("headingRows", 1);
      res.add("headingColumns", 1);
      res.add("columns", meta.getColumnCount());
      res.add("rows", rowcount);
      this.setValues(res);

      // loop for each column. Here, the column headers
      // will be set to have the correct title and width.
      // 
      for (i = 1; i <= meta.getColumnCount(); i++) {

        // get the column name
        //
        String col_name = meta.getColumnName(i);

        // set the string of the column header
        //
        this.setStrings(MlGrid.HEADING, 0, MlGrid.CONTENT, 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());

        // prepare the resource object to set the column width
        //
        res.clear();
        res.add("column", i - 1);
        res.add("columnWidth", size);

        // 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)) {
          res.add("cellEditable", false);
          pkey_col = i - 1;
        }

        this.setValues(res);

      }

      // set each column value on a row by row basis
      //
      int j;
      for (j = 0; j < rows.size(); j++) {

        // number each row
        //
        this.setStrings(MlGrid.CONTENT, j, MlGrid.HEADING, 0,
          Integer.toString(j + 1));

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

        // add each column's value to the grid
        //
        for (i = 0; i < columns.length; i++) {
          res.clear();
          res.add("column",     i);
          res.add("row",        j);
          res.add("cellString", columns[i]);
          this.setValues(res);
        }

      }
       
      this.setValue("layoutFrozen", false);

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


  
  /**
   * 
   * instantiate the jdbcMlGrid 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");            // the tinySQL textFile driver
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");  // JDBC-ODBC bridge
      Class.forName("COM.imaginary.sql.msql.MsqlDriver"); // 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 = "";
    }

    // create a myFrame object that knows how to close itself
    //
    myFrame f = new myFrame();
    f.setLayout(new BorderLayout());

    // create an MlGrid object and add it to the frame
    //
    String column_list = "name, address, city, state, zip, country, phone, id";
    jdbcMlGrid grid = 
      new jdbcMlGrid(argv[0], user, pwd, "cardfile", column_list, "id");

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

    // resize and show the frame
    //
    f.setSize(400, 300);
    f.show();    
   
  }

  /**
   *
   * 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("");
    }
  }

  /**
   * A listener for the Grid Events
   *
   */
  class GridEventHandler implements MlGridListener {

    public void onGridEvent(MlGridEvent event) {

      if(event.getType() == MlGridEvent.EDIT_COMPLETE) {

        // get the column name that was edited
        //
        String column_name = (String)
          getCellValue(MlGrid.HEADING, 0, MlGrid.CONTENT, 
                       event.column, "cellString");

        // get the column value
        //
        String column_val = (String)
          getCellValue(event.row, event.column, "cellString");

        // get the value of this row's primary key
        //
        String pkey_val = (String)
          getCellValue(event.row, pkey_col, "cellString");

        // 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);
        }
      }
    }

  }
}