Our Experience with Writing a Table Editor GUI for DB2

Ever wanted a simple, user-friendly interface to update, delete, and query data in DB2 tables? Our authors have written a table editor GUI that you can try for yourself. Or, if you're just interested in reading about the experiences our authors had as they coded the Java-language application, this articles offers their experiences.

Share:

Kulvir Bhogal (mailto:kbhogal@us.bim.com), Consultant, IBM

Kulvir Singh Bhogal works as an IBM consultant, devising and implementing Java-centric solutions at customer sites across the nation. You can reach Kulvir at kbhogal@us.ibm.com .



Chaitanya Laxminarayan (mailto:chaitanya_l@alumni.utexas.net), Software Engineer , IBM

Chaitanya Laxminarayan is a software engineer with extensive expertise in developing applications for the Internet using Java and XML technologies. He can be reached at chaitanya_l@alumni.utexas.net .



06 June 2002

© 2002 International Business Machines Corporation. All rights reserved.

Introduction

Although you might be the best programmer from here to the farthest cubicle your eyes can see, the reality is you refer to programming manuals because you can't commit all of those programming APIs to memory. Don't worry; you are not alone. If you are the average programmer, you probably lead a pretty busy life, scrambling to finish up a project that management has committed to, even though they were totally oblivious to what they were committing you to! You begin to question what the difference is between your job and indentured servitude.

Every so often, there is something designed to make your life a little bit easier. These "somethings" are particularly helpful when they take away some of the menial tasks that we must perform. If you are paid hourly, we apologize beforehand; the presented code in this article will probably allow you to get your job done a bit quicker.

Wouldn't it be nice if you had a simple, platform-independent application to let you add, delete, and update rows in your DB2 database tables without having to know the table names and column names off the top of your head? The application would basically give you a visual display of what is in the tables you are trying to manipulate. Guess what? We have you covered.

The SQL utility we describe here is an application that gives you the ability to interact with your IBM DB2 database in an intuitive and easy-to-use manner. The utility is platform-independent, because it was developed using JavaTM-based technologies. The GUIs you will see in this application were built using Java Swing components, and the database access layer was built using the JDBC 2.0 driver provided by IBM. The application was developed and tested using JDK 1.3.1_02.

Bear in mind that this simple tool in no way replaces the full-function DB2 Table Editor that is shipped and supported by IBM. Our tool is much more limited in functionality and scope, but we think it can help serve a need for certain users, not to mention providing us with an interesting exercise in coding a DB2 Java language application. .


Prerequisites

This project is made possible via the power of JDBC, an API that lets you interact with a database via the Java programming language. Since we mentioned Java, what should come to mind is the concept of platform independence. Accordingly, the code we are presenting will port easily to the platform of your choice (AIX®, Windows NT®, etc.). We coded the application in a Windows 2000 environment. However, as you will see from the code used, we use no technology that is platform-dependent (e.g., ODBC, MFC, etc.)

However, our code has the following requirements:

  • DB2 Universal Database Version 7.2 (also known as 7.1 with Fixpak 3).
  • Make sure that you have the JDBC 2.0 driver for DB2 in your classpath. This is of utmost importance because we tap into some of the JDBC 2.0 functionality.
  • Also, you will need to make sure that you are using a JDK that supports Swing. We use many of the offerings of Swing to pull off some of the screens you will be using to interact with the DB2 database.

Overview of the application

As shown in Figure 1, the SQLUtility application allows you to accomplish four things with their IBM DB2 database:

  • View a table.
  • Add a row to a table.
  • Delete a row from a table.
  • Update a table.
Figure 1: What you can do with the SQLUtility application
What you can do with the SQLUtility application

It would be a good idea for you to download the code for this project and take a look at it to understand how things work. We have done our best to comment the code such that you can understand what we did. Our suggestion is to use an integrated development environment (IDE) such as IBM VisualAge® for Java that will allow you to step through the code and get a grasp on the flow of the application.


A closer look at the application

A user must be authorized before being able to view or change tables in a database. The login phase of the application (made possible by LoginDialog.java) verifies whether a user is authorized or not. Figure 2 shows the authentication dialog that you will see when you first access the utility.

Figure 2. The authentication dialog
The authentication dialog

You are authorized when a connection is successfully established with the specified database using the information entered in the authentication dialog. The code for authorization resides in the constructor of SQLFacade.java, in which a JDBC connection is made to the database using the code shown in Listing 1. If a successful connection is made, you are authorized; otherwise, not.

Listing 1. Connecting to the database

if (!username.equals("")) 
 
{ 
       connection = 
             DriverManager.getConnection(dbURL, username, password); 
} 
else 
{ 
       connection = DriverManager.getConnection(dbURL); 
}

If a successful connection is made to the specified database, a screen similar to that seen in Figure 3 appears.

Figure 3. The SQLUtility after a user is successfully authenticated
The SQLUtility after a user is successfully authenticated

Figure 4 shows how you are presented with a list of tables in the database.

Figure 4. Drop-down menu of user-created database tables
Drop-down menu of user-created database tables

The data to create the drop down menu is acquired using the DatabaseMetaData object (SQLFacade.java, line 122)

When you select a table from the drop-down menu and click the Go button, the utility displays the contents of the database table in a user-friendly tabular view (as shown in Figure 5). The first column displayed is the row number, which lets you easily identify a particular row of data. Using the GUI, you can add a row, delete a row, or make updates to existing rows in the chosen table.

Figure 5. SQLUtility displaying the contents of the TEST table
SQLUtility displaying the contents of the TEST table

Adding a row

To add a row, click the Add Row button. The ability to add a row to a DB2 table is facilitated via the code of AddRowDialog.java. After the Add Row button is clicked, a dialog box with two columns appears (as shown in Figure 6). In this interface, the first column lists the column names of the table; the second column is where you enter the values for each column of the database table.

Figure 6. The GUI to add a new row
SQLUtility displaying the contents of the TEST table

After you enter the values for each column and click Add New Row, the values you entered are temporarily stored in a hash table whose keys are the column names. This information is passed to the method addRowToDatabase. This method of ResultSetModel.java takes on the task of adding the desired row to the database.

Of course, there is always the possibility that you may be entering data that is illegal. By "illegal," we mean that such things as the use of invalid characters, the entering of null values for non-null columns, etc. When the addRowToDatabase method tries to add a row that is considered illegal, an SQL exception is thrown. Consequently, the add will not be allowed and you will see an error as shown in Figure 7.

Figure 7. Error dialog
Error dialog

On the other hand, if the data in the desired row to be added is "legal," the utility appends the row to the current table. As a result, the GUI is automatically updated (as shown in Figure 8). Notice the new row for Tom Little.

Figure 8. A new row added to the TEST table
A new row added to the TEST table

Deleting a row

To delete a row, all you have to do is select a row by clicking on it (as seen in Figure 9) and click on Delete Row. The method responsible for deleting the row from the database is deleteRow, found in ResultSetModel.java.

Figure 9. Deleting a row from the TEST table
Deleting a row from the TEST table

Updating existing rows

To update existing rows, select a cell (the name Jones is selected in Figure 10), and types in the new value. You can make changes to multiple columns in multiple rows. Each edited cell appears in red. After editing is complete, click Update Database to make the changes in the database.

Figure 10. Updating multiple values in the TEST table
Updating multiple values in the TEST table

The method responsible for making the updates is updateDatabase, found in ResultSetModel.java. The program keeps track of changes made to the cells in the table. When you click Update Database, the method updateDatabase() iterates over each modified row, and pushes the changes to the database. Just as an error dialog box is shown when an error happens adding a new row, a dialog box is displayed to you when an error happens updating existing rows. Because you might be manipulating multiple rows during one update, a dialog box is shown for every row that has an error (see Figure 11).

Figure 11. An error dialog box appears for each row that is incorrectly updated
An error dialog box appears for each row that is incorrectly updated

Notice that the row number is identified in the title of the error dialog box. With this information, you can easily identify a row because the row number is the first column of every row.

Concurrency implications: It is important to note concurrency implications of our setup. When data is retrieved from the database, we do not perform a lock on the record. Accordingly, it could happen that another user could come in and manipulate the same row you are working on while you are trying to update it. Our methodology of updating a row in essence takes a snapshot of what the row looked like before an update. It then allows you to state what you want to change. You then tell the database that you want to change the row that looks like the snapshot. If a row that looks like the snapshot does not exist (which could occur due to a race condition), then the update operation will not be possible. There is also the remote possibility (in the case of a badly designed database model) that there are two rows that match the identity of the snapshot (see Limitations to learn more about this issue).


Complications we faced

We developed the SQL Utility application using the JDBC driver provided by IBM. Unfortunately, because the driver we used does not currently provide a scrollable and updateable java.sql.ResultSet implementation, our job of coding the SQL Utility application was a bit tricky. We had to iterate over the whole of the ResultSet object and store the information locally instead of scrolling to the row that we were interested in. This methodology is shown in ResultSetModel.java. Of course, having a scrollable ResultSet would have made all of the code in the method reloadTableModel() much simpler. However, the workaround we have gets the job done.

In addition, the lack of an updateable ResultSet implementation meant we had to create a SQL Update query to make changes to the database instead of using the updateXXX() methods of the jaba.sql.ResultSet interface. Accordingly, in the method updateDatabase(), we have to dynamically generate an SQL update statement.

Limitations

The lack of an updateable ResultSet also created some limitations in our utility. In order to delete or update a row, it has to be uniquely identified. With updateable results sets, this would have happened automatically. Instead, we have to identify the row of interest ourselves, and this is where we could possibly run into problems. We identify the row of interest in the SQL query used for update or delete as follows.

	DELETE from TESTTABLE WHERE col1 = 'value1' AND col2 = 
'value2' AND col3='value3' 
 
	UPDATE TESTABLE SET col1 = 'new value1' WHERE 
col1='value1' AND col2 = 'value2' AND col3='value3'

The problem arises when the database table has two or more rows with duplicate data, which causes the WHERE clause of the queries to match multiple rows instead of one. This causes all the duplicate rows to be updated or deleted according to the queries we generate. However having duplicate rows is not a common occurrence in properly designed relational databases. Accordingly, this "bug" should be seldom seen.


Conclusion

DB2 is no doubt an extremely powerful database. It can house gargantuan amounts of data without blinking. IBM DB2 serves as the bedrock technology for many mission critical business applications. As more people use DB2, simple tools like the one we have provided here can help users make simple edits to the data visually without having to know SQL. Even for those people who do know SQL, when dealing with a huge number of complex tables it can be difficult to compose queries easily. SQL statements can sometimes be pretty long and tedious to type. If you forget one apostrophe, you are in trouble. Our goal in this article was to deliver code to you that can make your life easier, providing you with a utility that can visually manipulate DB2 tables.

You can modify the Java code in this article to your own purposes, but you are using it at your own risk. Neither the authors nor IBM accept any responsibility for any consequences of using the code.

In a future article, we plan on porting the SQL Utility presented here to the Web using Java Servlets and Java Server Pages.


Download

DescriptionNameSize
Code samplesrc.zip  ( HTTP | FTP )16 KB

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management, IBM i
ArticleID=13106
ArticleTitle=Our Experience with Writing a Table Editor GUI for DB2
publish-date=06062002