Sue Brandreth's Learning Resources

public interface TableModel

The TableModel interface specifies the methods the JTable will use to interrogate a tabular data model.
The JTable can be set up to display any data model which implements the TableModel interface with a couple of lines of code:

      TableModel myData = new MyTableModel();
      JTable table = new JTable(myData);

---

Using JDBC with GUI API – The Java™ Tutorials
(https://docs.oracle.com/javase/tutorial/jdbc/basics/jdbcswing.html)

The sample CoffeesFrame.java demonstrates how to integrate JDBC with a GUI API, in particular, the Swing API. It displays the contents of the COFFEES database table in a table and contains fields and buttons that enable you to add rows to the table. The following is a screenshot of this sample:

The sample contains five text fields that correspond to each of the columns in the COFFEES table. It also contains three buttons:

• Add row to table: Adds a row to the sample’s table based on the data entered in the text fields.
• Update database: Updates the table COFFEES based on the data in the sample’s table.
• Discard changes: Retrieves the contents of the COFFEES table, replacing the existing data in the sample’s table.

This sample (which requires CoffeesTableModel) demonstrates the following general steps to integrate JDBC with the Swing API:

1. Implementing the TableModel interface
2. Implementing the RowSetListener interface
3. Laying out the Swing components
4. Adding listeners for the buttons in the sample

---

Bob Hendry (2001) Mastering the JTable – Have Total Control over Your Data Part 1 of 3
(http://www2.sys-con.com/ITSG/virtualcd/Java/archives/0601/hendry/index.html)
(http://wireless.sys-con.com/node/36190)

Download Code Example Listing 1 and 2 (TXT 3KB)
(Change file EXT to ,java)

JBuilder Data Express controls enable JBuilder developers to use prebuilt objects to provide the user with an interface in which to view and manipulate data. For the most part, the use of Data Express components simplifies our task of programming data access functionality into our applets/applications. One drawback of using these components is that you’re restricted to using only functions and changing properties that are supported by that specific control. In other words, although JBuilder simplifies your task, you can use only prewritten functionality.

What if you wanted total control over your data? What if you wanted to control every aspect of how your data is formatted, displayed, edited, and up- dated? The answer to this is knowing how to use the native Java JTable. Mastering the use of this class is your key to exercising total control over data within your applet/application – albeit with a considerable tradeoff in added complexity.

Listing 1
import javax.swing.*;
import java.awt.*;

public class BradyGirls extends JPanel{
   static JTable myTable;

BradyGirls(){
   myTable = new JTable(3,2);
   JScrollPane myPane = new JScrollPane(myTable,
   JScrollPane.VERTICAL_SCROLLBAR_ALWAYS,
 		JScrollPane.HORIZONTAL_SCROLLBAR_ALWAYS);
   add(myPane);myTable.setPreferredScrollableViewportSize(new Dimension(500, 70));
} 

public static void main(String args[]){
   JFrame myFrame = new JFrame("Brady Bunch Girls");
   myFrame.getContentPane().add(new BradyGirls());
   myFrame.setVisible(true);
   myFrame.pack();
   myTable.setValueAt("Marsha",0,0);  
   myTable.setValueAt("Jan",1,0);
   myTable.setValueAt("Cindy",2,0);
   myTable.setValueAt(new Integer(18),0,1);  
   myTable.setValueAt(new Integer(17),1,1);
   myTable.setValueAt(new Integer(16),2,1);
   } 
}
 
Listing 2

import javax.swing.*;
import java.awt.*;
import java.sql.*;public class BradyGirls extends JPanel{
   static Connection Ex1Con;
   static Statement Ex1Stmt;
   static ResultSet Ex1rs;
   static JTable myTable;
  
BradyGirls(){
   myTable = new JTable(3,2);
   JScrollPane myPane = new JScrollPane(myTable,
   JScrollPane.VERTICAL_SCROLLBAR_ALWAYS,
   JScrollPane.HORIZONTAL_SCROLLBAR_ALWAYS);
   add(myPane);
   myTable.setPreferredScrollableViewportSize(new Dimension(500, 70));
   }
public static void main(String args[]) throws SQLException{
   JFrame myFrame = new JFrame("Brady Girls Table");
   myFrame.getContentPane().add(new BradyGirls());
   myFrame.setVisible(true);
   myFrame.pack();

   //Initialize and load the JDBC-ODBC driver.
   try {
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
   } catch(java.lang.ClassNotFoundException e) {
      	   System.err.print("ClassNotFoundException: ");
     		   System.err.println(e.getMessage());
   }
   String url = "jdbc:odbc:bradygirls";
   Ex1Con= DriverManager.getConnection(url, "", "");
   Ex1Stmt = Ex1Con.createStatement();
   Ex1rs = Ex1Stmt.executeQuery( "SELECT name, age FROM bradygirls ORDER BY name");
   int  li_row = 0;while (Ex1rs.next()) {
      myTable.setValueAt(Ex1rs.getString(1),li_row,0);
         myTable.setValueAt(Ex1rs.getString(2),li_row,1);
         li_row ++;
      } // while
   } 
}

---

Bob Hendry (2002) Using the JTable – Part 2 of 3
(http://www2.sys-con.com/ITSG/virtualcd/Java/archives/0607/hendry/index.html)
(http://java.sys-con.com/node/36636)

In Part 2 I continue my discussion on the use of the JTable. (Part 1, “Mastering the JTable,” can be found in the January issue of JDJ, [Vol. 6, issue 1].) I’ll briefly review the three major classes you’ll need while working with data within the JTable.

---

Bob Hendry (2002) Using the JTable – Part 3 of 3
(http://wireless.sys-con.com/node/36848)
(http://java.sys-con.com/node/36636)
(http://bobhendry.sys-con.com/node/36848)
(http://www2.sys-con.com/itsg/virtualcd/java/archives/0702/hendry/index.html)

Download Code Examples Part 3 (TXT 10KB)
(Change file EXT to ,java)

In Parts 1 and 2 of this article (JDJ, Vol. 6, issues 1 and 7) I discussed how to use a JTable with a table model and showed how much work is involved getting a JTable to work with data. This is quite a departure for veterans of other fourth-generation languages who may be used to developing in Visual Basic or PowerBuilder.

Both these languages have intelligent controls that keep track of the data as the user is manipulating it. These controls can then determine how to handle database changes such as inserts, updates, and deletes. Java doesn’t have any built-in functionality. Remember the old Java adage: “To use it you must first build it.”

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 windowlistenerthis.setContentPane(p);
      this.setVisible(true);
      this.pack();
   } // constructor

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

---

 

 

---