statement. Then within each row of the HTML table the for loop steps through each column of the resultset row and creates a column within the HTML row with the | statement. Then the heart of this process is accomplished by placing the content of column from the resultset into the column within the row of the HTML table with the statement <%= results.getString(i) %>. The code <%= places the following value, in this case results.getString(i) onto the HTML Web page.
Adding a record to a table within the database
Now to the heart of the interaction with the database where records will be added, changed or deleted. In the following example a record will be added to the Catalog table.
The process of adding a record to a database table entails the use of two Web pages. The first page contains an HTML form where data is entered. The form also contains a submit button that when clicked submits the values of the objects on the form into a buffer which is available to the posted page. Below is the code for the HTML form.
In this example, when the submit button is clicked the three fields - id, description, and prices are posted to the jsp page named addUpdate.jsp. The values actually go into a buffer, which is available to the jsp page.
The next step is then for the jsp page, in this case addUpdat.jsp, to get the values from the buffer and insert a record into the database with the values.
As in the case of the query from the database, a connection to the database must be made. Once that connection is made the values posted from the Web page with the form are then accessed and placed into variables. The below code explains that process.
<%
// Get the values from the form and place into variables
String tmpID = " ";
int cnvID = 0;
tmpID = request.getParameter("id");
cnvID = Integer.parseInt(tmpID);
String tmpDescr = " ";
tmpDescr = request.getParameter("description");
String tmpPrice = " ";
double cnvPrice = 0;
tmpPrice = request.getParameter("price");
Double tmpValue = Double.valueOf(tmpPrice);
cnvPrice =tmpValue.doubleValue();
%>
The three fields are accessed with the request.getParameter( ) method. The request object references the buffer area. Then content of each field is String. Therefore the number fields must be converted from string to either int or double, the explanation of which is outside the scope of this paper.
Now that the values are in variables on the Web page, and the database is connected, a record can now be inserted into the database table. First a determination is made as to whether a record with the same id is already in the table as follows.
<%
// Verify if the record is already on file
sql = "Select * From Catalog Where Cat_ID = " + tID;
stmt = cnn.createStatement();
results = stmt.executeQuery(sql);
// If the record is on file go to invalid page
if (results.next()) {
results.close();
%>
<%;
} // end if
A resultset is created that will either contain one record or no records. If the resultset contains one record then the record is already in the table and the add process is terminated by closing the resultset and forwarding to a notification page.
If the record is not on file then a record is inserted into the table, and the resultset is closed.
<%
Statement insert = cnn.createStatement();
int rowInserted = 0;
// Insert the record into the table
sql = "Insert Into Catalog Values (";
sql = sql + tID + ", ";
sql = sql + "'" + tDescription + "', ";
sql = sql + tPrice + ")";
rowInserted = insert.executeUpdate(sql);
// Close the recordset
results.close();
%>
A create statement must be created, in this case named insert. Also, an int variable that will receive the result of the insert, which is named rowInserted, must be created. Then a String variable named sql is built with the SQL command that will insert the three fields into table. The row is actually inserted with the executeUpdate method using the sql variable as the argument. Since the absence of a duplicate record was already determined the use of rowInserted is not necessary.
Changing a record in a table within the database
The process of changing a record in a database table is similar to that of adding a record. The values, which are posted from the Web page with the form, are in a buffer available to the jsp page. The example for the add process would apply to the change except the jsp page will now be changeUpdate.jsp.
Again a connection to the database must be made, and the values from the form are then accessed and placed into variables. As in the case of the add process, a determination is made as to whether the record exists or not. In the case of the change if the record does not exist the process is terminated. If the record is on file then the record is modified and the resultset is closed.
<%
int rowModified = 0;
Statement modify = cnn.createStatement();
// Modify the record in the table
sql = "Update Catalog Set Description = '" + tDescription + "'";
sql = sql + Price = " + tPrice;
sql = sql + " Where Cat_ID = " + tID;
rowModified = modify.executeUpdate(sql);
%>
A create statement must be created, in this case named modify. Also, an int variable that will receive the result of the update, which is named rowModified, must be created. Then a String variable named sql is built with the SQL command that will modify the three fields in the row of the table. The row is actually modified with the executeUpdate method using the sql variable as the argument. Since the presence of the record was already determined the use of rowModified is not necessary.
Deleting a record from a table within the database
The process of deleting a record from a database table is almost identical to that of changing a record. The most notable exception is the sql command that either updates or deletes a record. The other exception is that the only value from the buffer is the id field, which is the primary key in the database table. The name of the jsp page will now be deleteUpdate.jsp.
Once again a connection to the database must be made. As in the case of the change process, a determination is made as to whether the record exists or not. If the record does not exist the process is terminated. If the record is on file then the record is deleted and the resultset is closed.
<%
int rowDeleted = 0;
Statement delete = cnn.createStatement();
// Delete the record from the table
sql = "Delete From Catalog Where Cat_ID = " + tID;
rowDeleted = delete.executeUpdate(sql);
%>
A create statement must be created, in this case named delete. And an int variable that will receive the result of the delete, which is named rowDeleted, must also be created. Then a String variable named sql is built with the SQL command that will delete the row from the table. The row is actually deleted with the executeUpdate method using the sql variable as the argument. Since the presence of the record was already determined the use of rowDeleted is not necessary.
3. CONCLUSIONS
When developing interactive Web page applications several options are available. One of those options is Java Server Pages. This paper has given examples of the fundamental processes for interactive Web page development - querying from a database, adding records to a database, and changing or deleting records. In each of these processes the type of scripting must be identified, in this case Java. A connection must also be made to the database.
In the case of the query, data is extracted from the database and placed into a resultset. The content of this result set is then displayed on the Web page. Although not necessary, for clarity of display the placement of the data into an HTML table is preferable.
For add, change, and delete processes two Web pages are necessary. The first page contains a form, which is used for data entry, and the values from the objects on the form are then posted into a buffer area, which is available to the JSP page that will interact with the database. On the JSP page the values from the buffer area is placed into variables on the Web page. These values are then used to add a record, change a record, or delete a record.
4. REFERENCES
Avila, John, 2001, Server-Side Java Programming for Web Developers. Scott/Jones Inc., El Granada, CA.
Annunziato, Jose, & Stephanie Fesler Kaminaris, 2001, Sams Teach Yourself JavaServer Pages in 24 Hours. Sams Publishing, Indianapolis.
Ben-Natan, Ron, & Ori Sasson, 2000, WebSphere Starter Kit. McGraw-Hill, New York.
Bhamidipati, Kishore, 1998, SQL, Programmer's Reference. McGraw-Hill, New York.
Fields, Duane K. & Mark A. Kolb, 2000, Web Development with Java Server Pages, A practical guide for designing and building dynamic Web services. Manning, Greenwich.
Gittleman, Art, 2001, Internet Applications with the Java 2 Platform. Scott/Jones Inc., El Granada, CA.
Hougland, Damon, & Aaron Tavistock, 2001, Core JSP. Prentice Hall, Upper Saddle River, NJ.
Powell, Thomas A., 1998, HTML, the Complete Reference. McGraw-Hill, New York.
Sharma, Vivek and Rajiv Sharma, 2000, Developing e-Commerce Sites, an Integrated Approach. Addison Wesley, Boston.
Staugaard, Andrew C., Jr., 1999, Java for Computer Information Systems. Prentice-Hall, Upper Saddle River, NJ.
Tremblett, Paul, 2000, Instant JavaServer Pages. McGraw-Hill, New York.
1
|