Writing a Web-based Table Editor GUI for DB2

The authors extend their Swing-based table editor to use a browser-based front end, enabling you to edit table data over the Web. The application is built using the new JDBC Type 4 driver in DB2 Version 8.1 and WebSphere Studio Application Developer.

Share:

Kulvir Bhogal (kbhogal@us.ibm.com), AIM Services consultant, IBM, Software Group

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



Joe Puli (joepuli@hotmail.com), Software Engineer

Joe Puli is an independent Software Engineer working in the Bay Area. He has worked for a number of industries and specializes in Java-centric projects. You can reach Joe at joepuli@hotmail.com.



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

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



14 November 2002

© 2002 International Business Machines Corporation. All rights reserved.

Introduction

You want your e-business running twenty-four by seven because this is good for business. However, it may not be so good for those people who make the wheels of your e-business turn. In the prequel to this article, we provided you with a JavaTM Swing-based application that users of IBM® DB2® Universal DatabaseTM can use to add, edit, and delete rows in a database. However, the fact is that many times you might not be around your database, yet you still need to be able to perform such manipulations of your enterprise data. Why not take advantage of the World Wide Web?

In this article, we will provide you with a full-fledged Web application that we call DB2 webSQL that will allow you to access your DB2 V8 data via HTTP. To use this code, you will need to be running a Web application server that can handle JSPs and Servlets. We developed our code with a WebSphere® Application Server audience in mind; however, our design should be able to work on other Web application servers without too much rework.

For our development environment, we used WebSphere Studio Application Developer 4.0. The built-in WebSphere V4.0 test environment offered us an environment for us to work out the kinks in our program. WebSphere Studio Application Developer lets us seamlessly build applications with a J2EE-compliant approach.

In this article, our project is made possible via the Type 4 JDBC Universal driver of DB2 V8. This driver, new to DB2 V8, offers the following:

  • Updateable ResultSet support
  • Improved security for DB2 authentication
  • Improved Java SQL error information
  • Programmatic tracing facilities

Note that to use the JDBC Universal Driver, the db2jcc.jar file must be included in the Java CLASSPATH environment variable. As you will notice as you look at our code, to use the Type 4 driver, the JDBC syntax you might be used to changes a bit.

We were eager to use the new driver because we want to use the Updateable ResultSet support. You can read more about the Type 4 JDBC driver and the other new offerings of DB2 V8 at http://www.ibm.com/developerworks/db2/library/techarticle/0209hutchison/0209hutchison.html


Prerequisites

This article is written with the assumption that you have a solid understanding of the following technologies:

  • Java Servlets
  • Java Server Pages (JSPs)
  • JDBC

Being able to move your cursor and being able to make updates to result sets are some of the powerful offerings of the JDBC 2.0 API. You might want to familiarize yourself with the associated syntax by taking a look at: http://java.sun.com/docs/books/tutorial/jdbc/jdbc2dot0/index.html.

For installing the application, we recommend the following prerequisites:

  • On the client side: Internet Explorer Browser 4.0 or above.
  • On the server side:
    • A Java Servlet Engine running JRE 1.3 which supports JSP specification 1.0 or above and Java Servlet Specification 2.2 or above.
    • DB2 Version 8.

The application is distributed as a WAR file.


Installing the application in WebSphere Application Server 4.0 on Windows NT®

  1. Download the WAR file.
  2. Go to Start>Programs>IBM WebSphere> WebSphere Admin Server 4.0> Start Admin Sever and open the Admin Console.
  3. Use the Install wizard to deploy the Enterprise Application as a stand-alone Web application.
  4. Select the downloaded WAR file, and choose a context for the application (such as DB2WebSQL).
  5. The wizard will ask you to select a virtual host (select default_host) and application server (select default application server).
  6. The wizard will ask you various EJB deployment descriptor-related questions; ignore those questions and click Next until you are done with the wizard. Click Finish.
  7. After installation, you will get a message indicating that the application is installed successfully.
  8. In the Admin Console under Enterprise Applications you will see the installed application. Right click on the application, and start the application.
  9. Right click on the node and regenerate the plug-in for the node.
  10. The application can be accessed using the URL: http://yourservername/application_context_name/login.html

Architecture of DB2 webSQL

Figure 1. WebSQL architecture
Architecture

The DB2 webSQL application is based on the Model-View-Controller (MVC) pattern. In the MVC pattern:

  • The Model represents enterprise data.
  • The View renders the contents of a model.
  • The Controller translates interactions with the view into actions to be performed by the model.

The Model in our application is the data residing in your DB2 database, which we want to access via HTTP. The Views consist of the JSPs (namely, choosetable.jsp, dberror.jsp, and manipulatetable.jsp) displayed by the client’s Web browser. Lastly, the Controller consists of the two servlets -- AuthenticateServlet.java and DBManipulateServlet.java -- that execute your requests and dispatch the results to the appropriate Views (that is, JSPs).

By applying the MVC pattern to the DB2 webSQL application, we separated the presentation and control logic from the data. A Web application developed in this manner comes with several advantages:

  • Reduced impact of change
  • Increased maintainability
  • Client independence.

In addition, the MVC architecture can give you a clean way to separate developer roles in the development of this application. In our application, there is a dichotomy between presentation logic (the JSP pages) from the data access and business logic (the two servlets and their helper classes). Why is this good? The clear separation lets you change the look and feel of our application; you can do so with relatively less effort than if all of the code was tightly integrated.

The IBM RedBook WebSphere Studio Application Developer Programming Guide, SG24-6585-00 available at http://www.redbooks.ibm.com/abstracts/sg246585.html has a an excellent chapter about developing with the MVC pattern using WebSphere Studio Application Developer.


Overview of the application

Just like its Swing- based sibling, DB2 webSQL allows you to accomplish four things with your IBM DB2 database from a Web browser:

  • View a table.
  • Add a row to a table.
  • Delete a row from a table.
  • Update a table.

A closer look at the application

The webSQL application lets you view and make changes to tables in a database. To guard against unauthorized access to the database, you must validate yourself with the database by logging in. Figure 2 is the login screen that users will see when they first access the application. The code for the login page is static HTML and resides in login.html.

Figure 2. Logging into WebSQL
logging in

The user is required to enter everything except for a schema into the login form. If you are using the application on your own box that also has DB2, your Web application server, and your client, then your server will be "localhost". Also, if you used the default installation options, then the port will be 50000.

When you enter the required information on the login screen and click Submit, the request is routed to AuthenticateServlet. This servlet takes the information entered at login and verifies whether you have the permission to access the database. Take a look at the code of AuthenticationServlet.java. In it, you will notice that a &quotDBInfo" object is instantiated. It is via this object that we establish our database connection and also are able to gather information about our database data (e.g., tables names, column names, etc.). If you are authorized, you are directed to the next screen rendered by choosetable.jsp (see Figure 3), which lets you choose from a list of tables you have access to. If you are unauthorized at login, you see error.jsp.

Figure 3. Choosing a table to edit
choosing a table

To gather the list of table names, the application issues a call to DBInfo’s getTables method. The getTables method gathers the table names via a query to the DB2 catalog table sysibm.tables.

After successfully logging in, you are now at a point where you can view and make changes to the contents of your DB2 database. All of these requests are handled by the DBManipulateServlet.java servlet.

DBManipuateServlet servlet is a controller servlet that handles your requests to insert a row, delete a row and update a row in a table. It uses a helper class DBHandler to do the dirty work. The meat and potatoes of the DBHandler class is the executeSQL() method, which carries out requests to modify your DB2 data.

A look at the code of the executeSQL() method reveals our use of a scrollable ResultSet object. Using this JDBC 2.0 API function, we can move our cursor to a particular row as needed. We also make our ResultSet object updatable in order to facilitate changes to our row data.

Viewing table data

To view the contents of specific table, select a specific table from the ‘Table Chooser’ drop-down list and click Change Table (see Figure 3), which brings up a screen like that shown in Figure 4.

This screen shows the contents of the table and includes a drop down list called Current Function, which lets you change data in the table. The page here is rendered by manipulatetable.jsp. As you look through the code of this JSP, note that it contains a large amount of JavaScript embedded within the Java Server Page scriptlets. The code is riddled with escape characters in order to facilitate the housing of the embedded JavaScript within the JSP, scriptlet code. This was JavaScript was necessary to be able to render the popup windows that control the Add and Update functions.

One important aspect about this code is how we establish which parameters we will pass to DBManipulateServlet. In essence, there is no way we can know beforehand what columns the table in question will have. Accordingly, we build these form variables on the fly. Using DBInfo’s getColumns method, we either concatenate a "pre" or a "post" to the column name, depending on the intended use of the request variable. To get a better grasp of what is going on in manipulatetable.jsp, we would suggest that you look at the HTML code generated by the JSP for a given table and compare it to the code of manipulatetable.jsp. This will give you a better idea of how the page dynamically generates the code needed to represent different tables and columns at page load time.

Figure 4. Choosing a function
choosing a function

Adding a row

To add a row, select Add Row from the Current Function drop-down list. A new JavaScript pop-up window appears that lets you insert a new row (Figure 5).

Figure 5. Inserting a row into a table
Insert popup

You can enter the desired values for the new row in the second column and click Add Row to persist the changes to the database. The row just added is reflected in the GUI (Figure 6).

Figure 6. The inserted row appears on the screen
Insert success

Deleting a row

To delete a row, select Delete Row from the Current Function drop-down list, and highlight the row you want deleted and left click. Note that this version of the code does not include an "Are you sure" message, so if you need this, you will need to add it.

Figure 7. Deleting a row
Delete process

Updating a row

To update an existing row, select Modify Row from the Current Function drop-down list, and highlight the row you want updated followed by a click on the row. A dialog box that lets you change the column values appears, as shown in Figure 8.

Figure 8. Updating a row
update popup

Enter the new values for the columns you want updated, then click Modify Row to persist the changes to the database. Again, there is no "Are you sure?" message. The updated row is reflected in the GUI, as shown in Figure 9.

Figure 9. Updating a row
changed row appears

Synchronization issues

Just like the Swing-based sibling presented in the previous article, the Web-based application presented here is not real time. Data is gathered from the DB2 tables and then manipulated by the Web user. It is quite possible that during this time period of manipulation, some other user or process could be manipulating the backend. This of course, could perform some anomalies that might perplex the user. We take a very innocent approach that during the time of database manipulation, we are the only ones manipulating the database.


Performance issues

This application takes into account that you are manipulating tables of relatively small size. As you will notice when you work with the application, table data is passed from the database to the Web server almost every time you perform a transaction. This can be very network and computer resource taxing if you have a number of rows in a table or your rows have a huge number of columns. It would be desirable to be able use pagination to break apart the data pulled from the database. Pagination could be made possible by tapping into the JDBC 2.0 API offerings of DB2 Version 8.


Conclusion

The Web-based application presented in this article allows you to manipulate your DB2 data remotely. Such functionality can be very valuable when you need to have access to data regardless of where you are. By using the MVC architecture we made it very easy to add new functionality to the application. The Type 4 JDBC Universal driver in DBC Version 8 provided us with the API we needed to pull off our project. Of course, each enterprise organization has its own particular needs. Accordingly, the code of DB2 webSQL has been made available so it can be extended and modified by you.

We welcome comments and suggestions for future releases of this tool.


Download

DescriptionNameSize
Code samplesource.zip  ( HTTP | FTP )33 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, WebSphere
ArticleID=14423
ArticleTitle=Writing a Web-based Table Editor GUI for DB2
publish-date=11142002