//----------------------------------------------------------------------------
//
// Module:      DBJTableBean.java
//
//	Author: Julius Dichter 2002 (c) (revised)
//
// Description: Java Bean for ODBC API interface. It uses the JDBC calls into
//              a database. Then we create a swing class table and load it up
//              with the data from the ResultSet and ResultSetMetaData. All the
//              is for display only. It can be modified, but there is no
//              database updating implemented
//
//              A JComboBox is used as a default editor for the state column.
//              The JScrollPane has a lowered beveled border 
//              
//              We now update the database whenever a user changes a row
//              Key fields Last and First are NOT updatable
//
//				program has a small bug in killing JVM
//
//
//----------------------------------------------------------------------------

import java.net.URL;
import java.sql.*;

import java.awt.event.WindowAdapter;
import java.awt.event.WindowEvent;
import java.awt.Dimension;

import javax.swing.*;
import javax.swing.table.*;
import javax.swing.border.*;

public class DBJTableBean extends JPanel {

Connection con;
Statement stmt;
String url;

public static void main (String args[]) { 
	
	JFrame frame = new JFrame("Database Query");
	frame.setBounds(200,200,500,400);
	DBJTableBean bean = new DBJTableBean(); 
	frame.getContentPane().add(bean);
	frame.show();

	frame.addWindowListener(new WindowAdapter() {
		public void WindowClosing(WindowEvent e) {
			System.exit(0); } } ); }

public DBJTableBean() {

System.err.println("Entering Constructor DBJTableBean");
url   = "jdbc:odbc:names";
String query = "SELECT * FROM PEOPLE";

try {
    Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver");
    con = DriverManager.getConnection (url, "my-user", "my-passwd");
    checkForWarning (con.getWarnings ());
    DatabaseMetaData dma = con.getMetaData ();

    System.out.println("\nConnected to " + dma.getURL());
    System.out.println("Driver       " +  dma.getDriverName());
    System.out.println("Version      " +  dma.getDriverVersion());
    System.out.println("");

    stmt = con.createStatement ();
    ResultSet rs = stmt.executeQuery (query);

    // Now interogate the rs in order to build the 2-D array to hold rs data

    // note: the folowing three need to be final

    final int cols = rs.getMetaData().getColumnCount();
    final int rows = getRowCount(rs);  // calling a local method

    System.out.println("Got " + cols + " columns");
    System.out.println("Got " + rows + " rows");

    final Object [][] data = new Object[rows][cols];
    final String [] colNames = new String[cols];

    // reexecute query to get to beginning of data

    rs = stmt.executeQuery (query);

    // load the data Object array
    for (int i = 0; i < rows ; i++)  {
      rs.next();
      for (int j = 0 ; j < cols ; j++) 
        data[i][j] = rs.getString(j+1); }

    for (int i = 0 ; i < cols ; i++)
      colNames[i] = rs.getMetaData().getColumnName(i+1);


     TableModel dataModel = new AbstractTableModel() {
       public int getColumnCount() { return cols; }
       public int getRowCount() { return rows; }
       public Object getValueAt(int x, int y) { return data[x][y]; }

       public boolean isCellEditable(int row, int col) {
          return col != 0 && col != 1; }

       public String getColumnName(int i) { return colNames[i]; }

       // We update the database here

       public void setValueAt(Object o, int x, int y) {
       String cellValue = o.toString();  // store the original value;
       data[x][y] = o;                   // set the new value

       String sql = "";

       // Create sql string to update the appropriate column

       if (getColumnName(y).equalsIgnoreCase("City"))
         sql +=    "UPDATE People SET City = '" + cellValue +
                   "' WHERE Last = '" + getValueAt(x,1) + "' AND " +
                   "First = '" + getValueAt(x,0) + "'";
       else
         if (getColumnName(y).equalsIgnoreCase("State"))
           sql +=    "UPDATE People SET State = '" + cellValue +
                     "' WHERE Last = '" + getValueAt(x,1) + "' AND " +
                     "First = '" + getValueAt(x,0) + "'";
         else
           sql +=    "UPDATE People SET Age = '" + cellValue +
                     "' WHERE Last = '" + getValueAt(x,1) + "' AND " +
                     "First = '" + getValueAt(x,0) + "'";
       try {

       String url2   = "jdbc:odbc:names";
       Connection con2 = DriverManager.getConnection (url2, "my-user", "my-passwd");

       Statement st2 = con2.createStatement();
       int howMany = st2.executeUpdate(sql);
       System.out.println("Updated " + howMany + " rows");}
      catch (SQLException e) { e.printStackTrace(); } } } ;


     JTable tableView = new JTable(dataModel);

     JComboBox combo = new JComboBox();
     combo.addItem("AL"); combo.addItem("AZ"); combo.addItem("CA");
     combo.addItem("CT"); combo.addItem("DE"); combo.addItem("FL");
     combo.addItem("GA"); combo.addItem("HI"); combo.addItem("LA");
     combo.addItem("MI"); combo.addItem("MO"); combo.addItem("OH");
     combo.addItem("TX"); combo.addItem("UT"); combo.addItem("VA");
     combo.addItem("VT");

     TableColumn stateColumn = tableView.getColumn("State");
     stateColumn.setCellEditor(new DefaultCellEditor(combo));
	 JScrollPane sPane = new JScrollPane(tableView);

     sPane.setBorder(new BevelBorder(BevelBorder.LOWERED));
     sPane.setPreferredSize(new Dimension(430,260));
     
	 add(sPane);
     setVisible(true);

     dispResultSet (rs);
     rs.close();
     stmt.close();
     con.close();  }

catch (SQLException ex) {

     System.out.println ("\n*** SQLException caught ***\n");

      while (ex != null) {
           System.out.println ("SQLState: " +  ex.getSQLState ());
           System.out.println ("Message:  " +  ex.getMessage ());
           System.out.println ("Vendor:   " +  ex.getErrorCode ());
           ex = ex.getNextException ();
           System.out.println ("");  }  }

catch (java.lang.Exception ex) {

        // Got some other type of exception.  Dump it.
        ex.printStackTrace ();  }   }



public String getUrl() {
  return url; }

public void setUrl(String s) {
  url = new String(s); }

private static boolean checkForWarning (SQLWarning warn) throws SQLException {
    boolean rc = false;
    if (warn != null) {
         System.out.println ("\n *** Warning ***\n");
         rc = true;
         while (warn != null) {
               System.out.println ("SQLState: " +  warn.getSQLState ());
               System.out.println ("Message:  " +  warn.getMessage ());
               System.out.println ("Vendor:   " +  warn.getErrorCode ());
               System.out.println ("");
               warn = warn.getNextWarning ();  } }
                return rc;   }

private static void dispResultSet (ResultSet rs)  throws SQLException  {
int i;

    // Get the ResultSetMetaData.  Used for the column headings

    ResultSetMetaData rsmd = rs.getMetaData ();

    // Get the number of columns in the result set

     int numCols = rsmd.getColumnCount ();

     // Display column headings

     for (i=1; i<=numCols; i++) {
           if (i > 1) System.out.print(",");
                  System.out.print(rsmd.getColumnLabel(i));  }
      System.out.println("");
		
      // Display data, fetching until end of the result set

       while (rs.next ()) {

      // Loop through each column, getting the
      // column data and displaying

      for (i=1; i<=numCols; i++) {
            if (i > 1) System.out.print(",");
            System.out.print(rs.getString(i));  }
            System.out.println("");

            // Fetch the next result set row
}  }

private int getRowCount(ResultSet rs) throws SQLException {
int count = 0;
	while ( rs.next() )
		count ++ ;
	return count; }


private static void dispJTableResultSet (ResultSet rs)  throws SQLException  {
int i;

    // Get the ResultSetMetaData.  This will be used for
    // the column headings

    ResultSetMetaData rsmd = rs.getMetaData ();

    // Get the number of columns in the result set

     int numCols = rsmd.getColumnCount ();

     // Display column headings

     for (i=1; i<=numCols; i++) {
           if (i > 1) System.out.print(",");
                  System.out.print(rsmd.getColumnLabel(i));  }
      System.out.println("");
		
      // Display data, fetching until end of the result set

       while (rs.next ()) {

      // Loop through each column, getting the
      // column data and displaying

      for (i=1; i<=numCols; i++) {
            if (i > 1) System.out.print(",");
            System.out.print(rs.getString(i));  }
            System.out.println("");

            // Fetch the next result set row
}  }  } // class DBJTableBean