© 2002 International Business Machines Corporation. All rights reserved.
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
This article is written with the assumption that you have a solid understanding of the following technologies:
- Java Servlets
- Java Server Pages (JSPs)
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®
- Download the WAR file.
- Go to Start>Programs>IBM WebSphere> WebSphere Admin Server 4.0> Start Admin Sever and open the Admin Console.
- Use the Install wizard to deploy the Enterprise Application as a stand-alone Web application.
- Select the downloaded WAR file, and choose a context for the application (such as DB2WebSQL).
- The wizard will ask you to select a virtual host (select default_host) and application server (select default application server).
- 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.
- After installation, you will get a message indicating that the application is installed successfully.
- In the Admin Console under Enterprise Applications you will see the installed application. Right click on the application, and start the application.
- Right click on the node and regenerate the plug-in for the node.
- The application can be accessed using the URL: http://yourservername/application_context_name/login.html
Architecture of DB2 webSQL
Figure 1. WebSQL 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,
manipulatetable.jsp) displayed by the clientâs Web browser. Lastly, the Controller consists of the two servlets --
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
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 "DBInfo" 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
Figure 3. Choosing a table to edit
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
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
Adding a row
Figure 5. Inserting a row into a table
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
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
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
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
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.
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.
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.
|Code sample||source.zip ( HTTP | FTP )||33 KB|
Dig deeper into Information management on developerWorks
Get samples, articles, product docs, and community resources to help build, deploy, and manage your cloud apps.
Experiment with new directions in software development.
Software development in the cloud. Register today to create a project.
Evaluate IBM software and solutions, and transform challenges into opportunities.