"Using the Jtable"
Vol. 7, Issue 2, p. 100

	


Complete Code Example:

// new class. This is the table model
import javax.swing.table.*;
import java.sql.*;
import java.util.Vector;

public class MyTableModel extends AbstractTableModel {
Connection con;
Statement stat;
ResultSet rs;
int li_cols = 0;
Vector allRows;
Vector row;
Vector newRow;
Vector colNames;
String dbColNames[];
String pkValues[];
String tableName;
ResultSetMetaData myM;
String pKeyCol;
Vector deletedKeys;
Vector newRows;
boolean ibRowNew = false;
boolean ibRowInserted = false;

   MyTableModel(){
      try{
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
      }
      catch (ClassNotFoundException e){
            System.out.println("Cannot Load Driver!");
      }
      try{
         String url = "jdbc:odbc:northwind";
         con = DriverManager.getConnection(url);
         stat = con.createStatement();
         rs = stat.executeQuery("SELECT productid, productname," +
               "quantityperunit, unitsinstock," +
               "discontinued from products;");
         deletedKeys = new Vector();
         newRows = new Vector();
         myM = rs.getMetaData();
         tableName = myM.getTableName(1);
         li_cols = myM.getColumnCount();
         dbColNames = new String[li_cols];
         for(int col = 0; col < li_cols; col ++){
            dbColNames[col] = myM.getColumnName(col + 1);
         }
         allRows = new Vector();
         while(rs.next()){
            newRow = new Vector();
            for(int i = 1; i <= li_cols; i++){
               newRow.addElement(rs.getObject(i));
            } // for
            allRows.addElement(newRow);
         } // while
      } 
      catch(SQLException e){
         System.out.println(e.getMessage());
      } 
   }
   public Class getColumnClass(int col){
      return getValueAt(0,col).getClass();
   }
   public boolean isCellEditable(int row, int col){
      if (ibRowNew){
         return true;
      }
      if (col == 0){
         return  false;
      } else {
         return true;
      }
   }
   public String getColumnName(int col){
      return dbColNames[col];
   }
   public int getRowCount(){
      return allRows.size();
   } 
   public int getColumnCount(){
      return li_cols;
   }
   public Object getValueAt(int arow, int col){
      row = (Vector) allRows.elementAt(arow);
      return row.elementAt(col);
   }
   public void setValueAt(Object aValue, int aRow, int aCol) {
      Vector dataRow = (Vector) allRows.elementAt(aRow);
      dataRow.setElementAt(aValue, aCol);
      fireTableCellUpdated(aRow, aCol);
   }
   public void updateDB(){
 String updateLine[] = new String[dbColNames.length];
      try{
         DatabaseMetaData dbData = con.getMetaData();
         String catalog;
         // Get the name of all of the columns for this table
         String curCol;
         colNames = new Vector();
         ResultSet rset1 = dbData.getColumns(null,null,tableName,null);
         while (rset1.next()) {
            curCol = rset1.getString(4);
            colNames.addElement(curCol);
         }
         rset1.close();
         pKeyCol = colNames.firstElement().toString();
              
         // Go through the rows and perform INSERTS/UPDATES/DELETES
         int totalrows;
         totalrows = allRows.size();
         String dbValues[];
         Vector currentRow = new Vector();
         pkValues = new String[allRows.size()];

         // Get column names and values
         for(int i=0;i < totalrows;i++){
            currentRow = (Vector) allRows.elementAt(i);
            int numElements = currentRow.size();
            dbValues = new String[numElements];
            for(int x = 0; x < numElements; x++){
               String classType = currentRow.elementAt(x).getClass().toString();
               int pos = classType.indexOf("String");
               if(pos > 0){ // we have a String

                  dbValues[x] = "'" + currentRow.elementAt(x) + "'";
                  updateLine[x] = dbColNames[x] + " = " + "'" + currentRow.elementAt(x) + "',";
                  if (dbColNames[x].toUpperCase().equals(pKeyCol.toUpperCase())){
                    pkValues[i] = currentRow.elementAt(x).toString() ;
                  }
               }
               pos = classType.indexOf("Integer");
               if(pos > 0){ // we have an Integer
                  dbValues[x] = currentRow.elementAt(x).toString();
                  if (dbColNames[x].toUpperCase().equals(pKeyCol.toUpperCase())){
                     pkValues[i] = currentRow.elementAt(x).toString();
                  }
                  else{
                     updateLine[x] = dbColNames[x] + " = " + currentRow.elementAt(x).toString() + ",";
                  }
               }
               pos = classType.indexOf("Boolean");
               if(pos > 0){ // we have a Boolean
                  dbValues[x] = currentRow.elementAt(x).toString();
                  updateLine[x] = dbColNames[x] + " = " + currentRow.elementAt(x).toString() + ",";
                  if (dbColNames[x].toUpperCase().equals(pKeyCol.toUpperCase())){
                     pkValues[i] = currentRow.elementAt(x).toString() ;
                  }
               }
            } // For Loop
    
            // If we are here, we have read one entire row of data. Do an UPDATE or an INSERT
            int numNewRows = newRows.size();
            int insertRow = 0;
            boolean newRowFound;
            
            for (int z = 0;z < numNewRows;z++){
               insertRow = ((Integer) newRows.get(z)).intValue();
               if(insertRow == i+1){
                  StringBuffer InsertSQL = new StringBuffer();
                  InsertSQL.append("INSERT INTO " + tableName + " ("); 
                  for(int zz=0;zz<=dbColNames.length-1;zz++){
                     if (dbColNames[zz] != null){
                        InsertSQL.append(dbColNames[zz] + ",");
                     }
                  }
                  // Strip out last comma
                  InsertSQL.replace(InsertSQL.length()-1,InsertSQL.length(),")");
                  InsertSQL.append(" VALUES(" + pkValues[i] + ",");
                  for(int c=1;c < dbValues.length;c++){
                     InsertSQL.append(dbValues[c] + ",");
                  }
                  InsertSQL.replace(InsertSQL.length()-1,InsertSQL.length(),")"); 
                  stat.executeUpdate(InsertSQL.toString());
                  ibRowInserted=true;
               }
            } // End of INSERT Logic
            
            // If row has not been INSERTED perform an UPDATE
            if(ibRowInserted == false){
               StringBuffer updateSQL = new StringBuffer();
               updateSQL.append("UPDATE " + tableName + " SET ");
               for(int z=0;z<=updateLine.length-1;z++){
                  if (updateLine[z] != null){
                     updateSQL.append(updateLine[z]);
                  }
               }
               // Replace the last ',' in the SQL statement with a blank. Then add WHERE clause
               updateSQL.replace(updateSQL.length()-1,updateSQL.length()," ");
               updateSQL.append(" WHERE " + pKeyCol + " = " + pkValues[i] );
               stat.executeUpdate(updateSQL.toString());
               } //for
            }
         }
         catch(Exception ex){
            System.out.println("SQL Error! Cannot perform SQL UPDATE " + ex.getMessage());
         }
         // Delete records from the DB
         try{
            int numDeletes = deletedKeys.size();
            String deleteSQL;
            for(int i = 0; i < numDeletes;i++){
               deleteSQL = "DELETE FROM " + tableName + " WHERE " + pKeyCol + " = " +
                                            ((Integer) deletedKeys.get(i)).toString();
            System.out.println(deleteSQL);
               stat.executeUpdate(deleteSQL);
            }
            // Assume deletes where successful. Recreate Vector holding PK Keys
            deletedKeys = new Vector();
         }
         catch(Exception ex){
            System.out.println(ex.getMessage());
         }
      }
      public void deleteRow(int rowToDelete){
         // Mark row for a SQL DELETE from the Database
         Vector deletedRow = (Vector) allRows.get(rowToDelete);
         Integer pkKey = (Integer) deletedRow.get(0);
         deletedKeys.add(pkKey);
         allRows.remove(rowToDelete);
         fireTableRowsDeleted(rowToDelete,rowToDelete);
      }
      public void addRow(){
         // Mark the row for a SQL INSERT in the Database
         newRows.add(new Integer(allRows.size() +1));
         // Get the total number of rows in the Vector
         int rowNumber = allRows.size();
         int pos;
      
         // Get what a row looks like
         int numElements = newRow.size();
         Vector newRowVect = new Vector();
         for(int i = 0; i < numElements; i++){
            String classType = newRow.elementAt(i).getClass().toString();
            pos = classType.indexOf("String");
            if(pos > 0){ // we have a String
               String blankString = new String();
               newRowVect.addElement(blankString);
            }
            pos = classType.indexOf("Integer");
            if(pos > 0){ // we have an Integer
               Integer blankInt = new Integer("0");
               newRowVect.addElement(blankInt);
            }
            pos = classType.indexOf("Boolean");
            if(pos > 0){ // we have a Boolean
               Boolean blankBool = new Boolean(false);
               newRowVect.addElement(blankBool);
            }
         }
         allRows.addElement(newRowVect);
         ibRowNew = true;
         this.isCellEditable(allRows.size(),0);
         System.out.println(allRows.size());
         fireTableRowsInserted(rowNumber,rowNumber);
      }
   }
   
import javax.swing.*;
import java.awt.event.*;
import java.awt.*;
import javax.swing.event.*;
import javax.swing.table.*;

public class MyTableApp extends JFrame{

JTable myTable;
JButton update;
JButton insert;
JButton delete;
JPanel p;
MyTableModel tm;
JScrollPane myPane;

   MyTableApp(){
      try{
         UIManager.setLookAndFeel("com.sun.java.swing.plaf.windows.WindowsLookAndFeel");
      }
      catch(Exception e){
         System.out.println("Error on look and feel");
      } 
      update = new JButton("Update");
      insert = new JButton("Add");
      delete = new JButton("Delete");
      p = new JPanel();
      tm = new MyTableModel();
      myTable = new JTable(tm);
      myPane = new JScrollPane(myTable,
                           JScrollPane.VERTICAL_SCROLLBAR_AS_NEEDED,
                           JScrollPane.HORIZONTAL_SCROLLBAR_AS_NEEDED);
      myTable.setSelectionForeground(Color.white);
      myTable.setSelectionBackground(Color.red);
      myTable.setSelectionMode(ListSelectionModel.SINGLE_SELECTION);
      p.add(myPane);
      p.add(update);
      p.add(insert);
      p.add(delete);
      update.addActionListener(new ActionListener(){
         public void actionPerformed(ActionEvent e){
             tm.updateDB();
         }
      });
      insert.addActionListener(new ActionListener(){
         public void actionPerformed(ActionEvent e){
            tm.addRow();
            myTable.setEditingRow(tm.getRowCount());
            myTable.setRowSelectionInterval(tm.getRowCount()-1,tm.getRowCount()-1);
         }
      });
      delete.addActionListener(new ActionListener(){
         public void actionPerformed(ActionEvent e){
            int rowToDelete = myTable.getSelectedRow();
            tm.deleteRow(rowToDelete);
            myTable.setEditingRow(rowToDelete -1);
            myTable.setRowSelectionInterval(rowToDelete -1,rowToDelete -1);
         }
      });
      this.addWindowListener(new WindowAdapter(){
         public void windowClosing(WindowEvent e){
            System.exit(0);
         }
      }); // end windowlistener
      this.setContentPane(p);
      this.setVisible(true);
      this.pack();
   } // constructor

   public static void main (String args[]){
       new MyTableApp();
   } // main
} //class