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 on the JDJ Web site, www.sys-con.com/java/sourcec.cfm.)

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 Table- Model 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.