HomeDigital EditionSys-Con RadioSearch Java Cd
Advanced Java AWT Book Reviews/Excerpts Client Server Corba Editorials Embedded Java Enterprise Java IDE's Industry Watch Integration Interviews Java Applet Java & Databases Java & Web Services Java Fundamentals Java Native Interface Java Servlets Java Beans J2ME Libraries .NET Object Orientation Observations/IMHO Product Reviews Scalability & Performance Security Server Side Source Code Straight Talking Swing Threads Using Java with others Wireless XML

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."

Remember that the JTable or the table model is in no way connected to the database. Even when you're instantiating the JTable based on its table model, the model simply populates a collection (usually vectors), then passes them back to the JTable. Any necessary functionality must be programmed to use the JTable to perform actual database manipulation.

It would be nice if a JTable or its associated model had a method called Update(). Unfortunately, it doesn't, at least not yet. With a bit of work, by the end of this article you should be able to program such a method. Before I discuss the login needed for real database updates, I'll discuss the groundwork involved. Primarily, how can the JTable and table model be configured to detect user changes and how to add and delete rows. When these three tasks can be accomplished, only then can the database be updated.

I'll walk through the steps needed for database updates. Listing 1 provides the complete code, and Figure 1 displays the application (Listings 1-9 can be found below.)

Figure 1
Figure  1:

Detecting User Changes
Where do we start? Before we can think about updating a database, the table model must first be aware of user changes. In case you haven't noticed, by default the JTable and associated table model don't apply any user-supplied changes. For example, if a cell value is "Cheeseburger" and the user types "Hotdog" over it, the new value is displayed only when that current cell has the focus. As soon as the user tabs to another cell, the old value is restored. This is not a bug. Remember, the programmer is responsible for all behavior. The old value is restored because no code exists to say otherwise.

How can we get the new value to remain in the cell? By coding the setValueAt() method in the table model. This method from the TableModel class is automatically fired when the contents of the cell are modified and the focus is changed to another cell. This method tells us the row, column, and new value of the cell. Then code has to be written to update the data (in our case vectors) that make up the table model. In Listing 2, the vectors that make up the table model are updated with the new value for the cell.

Adding Rows
Any good user interface has the functionality to add rows. Any GUI you write using a JTable should include it. But where can this functionality be added? Remember, the JTable is merely the view of the data. Most functionality, including the addition of rows, must occur in the table model. With that in mind, there should be a method called addRow() or insertRow(), for example, available for the table model, right? Guess again. Such methods must be programmed. If you think about it, the absence of such built-in Java methods makes sense. To understand why, you must first see what a "row" really is.

Because the table model contains the data for the JTable, it also controls and is aware of what a row looks like. The JTable is pretty oblivious to both these facts. A "row" in a table model can and will look very different from application to application. For example, Application 1 may have three columns with the data types string, integer, and boolean. Application 2 may have four columns with the data types string, string, float, and integer. The concept of a "row" really exists only for the beholder. As far as Java is concerned, a row is a vector of supporting classes. The data types for these classes are as varied as the imagination of the programmer who created them. Because of this variation there's no built-in Java method to insert or add a row to a table model, because Java doesn't keep track of what a row looks like. This is the responsibility of the programmer.

In our example, a row in a table model is made up of a vector. Each element within the vector reflects the data type of the database column retrieved into it. To add or insert a row, the vector must first be queried about what data types it contains. These data types can then be built and added into another vector. This resulting vector can then be added to our table model, effectively adding a row. Listing 3 demonstrates how to add a row to the table model. For brevity, only vectors containing strings, integers, and booleans can be added.

Deleting Rows
Fortunately, deleting rows in the table model is a bit less challenging. Deleting is fairly easy because it's irrelevant which data types the row consists of. The only real concern is to remove the row from the table model. However, the deleted row needs to be remembered in some way when we try to delete it from the database.

When the database is updated, SQL DELETE statements will have to be built. Even when the row no longer exists in the table model, the primary key for the deleted row must be remembered so the corresponding record in the database can also be deleted. This is accomplished by saving the primary key for the deleted row into a vector.

Later, when we build the SQL DELETE statement, we can ask the vector to tell us which row to delete. For simplicity, Listing 3 assumes the primary key is numeric and is the first column in the row. With a bit of ingenuity, this functionality can be expanded to include any number of columns with any data type. Listing 4 illustrates how to delete a row from a table model, but remember its primary key. This simple method deletes a row from within the vector and notifies the JTable to update the view - making the row removal visible to the user.

Updating the Database
Now for the fun part. So far our table model can handle data modifications, new rows, and deleted rows. The next step is to apply the changes to the database. Since the table model and the database don't know about each other, it's up to the programmer to determine how the database will be affected. The concept of applying changes to the database is simple - create and execute a SQL statement. Depending on the status of the row in the table model, a SQL INSERT, DELETE, or UPDATE statement needs to be built. Sounds easy? Well, it is. The hard part is writing the code that will build the SQL. Once the code is built, database transactions are a snap. The next section will discuss and demonstrate the code needed to generate SQL statements.

Groundwork
Before the code can be written to generate SQL, a few housekeeping chores are in order. Information such as the primary key, names of the columns in the database, and a user-entered value from the table model must be obtained. The primary key, as well as the database column names, can be retrieved through Java metadata methods. For simplicity, this table model will assume that the first column in each row is the primary key and the data type is numeric. Listing 5 keeps track of how many rows are in the table model, what the user-entered values are, and the value for each of the primary keys for each row.

At this point code needs to be written that will perform two loops. First, the code loops through each row of the table model, then through each element within the row. Remember that rows within the table model are really collections of other objects (e.g., strings and integers). Each element within the row must be queried for its value, data type, and whether it contains the value for the primary key.

When testing each column for its data type, you can start saving the user-supplied values to be used in the SQL. Data type is very important because it changes the way the program keeps track of the user-entered values. For example, if the user changes a column of the data type STRING, the program must wrap single quotes around the value, otherwise the SQL statement will fail. Listing 6 queries the elements in each row for three different data types. For simplicity, this example doesn't test for all possible types; this modification is simple once you understand the basics.

Performing the INSERT
Now that we have data describing each column - as well as the data itself - code can be added to INSERT rows in the database, basically creating and executing a SQL INSERT statement. First, the vector that contains all the new row numbers is queried as to how many entries it contains. If it contains any entries, the row number in the vector is compared to the current row number in the table model. If the row matches, the SQL statement can be built. Listing 7 loops through all the columns and data values and builds the SQL INSERT statement. After the statement is built, it's executed against the database. If all goes well, the new row is now saved.

Performing the UPDATE
If the current row has not been inserted, a SQL UPDATE statement is built. Listing 8 obtains the name of the database column as well as its value. After the UPDATE statement is created, it's executed against the database. The record has now been updated.

Performing the DELETE
Deletes work a little differently. Since the row no longer exists in the table model, there won't be any current row to loop through. When the row was deleted from the table model, the primary key of the row was saved in a vector. When deleting a row in a database, the column names and the data values are irrelevant. All that's needed is the primary key. Listing 9 obtains the key for deleted rows and builds a SQL DELETE statement. Also, for the sake of brevity, this code assumes that the record being deleted has no foreign key constraints. If it does, the DELETE statement would fail, of course.

Final Considerations
Now that wasn't too bad, was it? The tricky part was creating the SQL statements. As a disclaimer I'd like to point out that the code in this example has been simplified in order to demonstrate the basics of how to use the JTable and associated table model to save changes to the database. For example, this article doesn't take into consideration SQL errors that can occur when violating a database constraint. Also, row management would be better served by using hashtables to hold the status of each row as well as the primary key value. In any event, the code is functional and generic enough for use in most projects. Feel free to use and improve it as you see fit.

Author Bio
Bob Hendry is a Java instructor at the Illinois Institute of Technology. He is the author of Java as a First Language. [email protected]

	


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

  
 

All Rights Reserved
Copyright ©  2004 SYS-CON Media, Inc.
  E-mail: [email protected]

Java and Java-based marks are trademarks or registered trademarks of Sun Microsystems, Inc. in the United States and other countries. SYS-CON Publications, Inc. is independent of Sun Microsystems, Inc.