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