Java Programming, Lecture Notes # 688, Revised 8/22/99.
Students in Prof. Baldwin's Advanced Java Programming classes at ACC will be responsible for knowing and understanding all of the material in this lesson beginning with the spring semester of 1999.
This lesson was originally written on January 9, 1999. The sample servlet was tested using the JDK 1.2 download package from JavaSoft along with the Java Servlet Development Kit (JSDK) 2.0 from JavaSoft. All tests were performed under Win95.
The servlet was tested using the servletrunner program that is included in the JSDK running as localhost on the same machine. The servlet was also tested using the JavaSoft Java Web Server 1.1.1 program running on a different machine on the network.
All tests required the use of the Windows version of the mSQL 2.0 database program from http://blnet.com/msqlpc/downloads.htm#win and the JDBC interface classes for mSQL and JDK 1.2 from http://www.Imaginary.com/Java/.
The primary purpose of this lesson is to introduce you to the use of the JDBC ResultSetMetaData Interface. The lesson also illustrates the use of a servlet to access a database to retrieve data and send it back to a client.
The name of the sample program is Servlet05. The primary purpose of this program is to illustrate the use of the JDBC ResultSetMetaData interface, which provides a way to get information about the underlying structure of a ResultSet object at runtime.
This servlet performs a query on a database and then displays the contents of the resulting ResultSet object in an HTML table. This is accomplished without the servlet knowing anything about the contents of the ResultSet object in advance.
The raw text output from the servlet is shown below. Note that I manually inserted line breaks at certain critical points to make this material easier to read. The HTML doesn't care about line breaks in the raw text as long as the break appears at an appropriate place in the text.
|
<HTML> <HEAD><TITLE=Servlet05</TITLE></HEAD> <BODY> com.imaginary.sql.msql.MsqlException: Table "servlet05Table" exists <br> Unable to create table<br><br> Contents of Database<BR><BR> <TABLE BORDER=2> <TR><TH>firstName<TH>middleInitial<TH>lastName</TR> <TR><TD>Michael<TD>J.<TD>Mouse</TR> <TR><TD>John<TD>Q.<TD>Public</TR> <TR><TD>Jane<TD>M.<TD>Doe</TR> <TR><TD>Im<TD>A.<TD>Student</TR> <TR><TD>Ur<TD>A.<TD>Student</TR> </TABLE> </BODY> </HTML> |
This servlet first produced the following preliminary information in the browser window.
|
com.imaginary.sql.msql.MsqlException: Table "servlet05Table" exists Contents of Database |
After displaying the preliminary information, the servlet obtained a list of names from the database and formatted them into an HTML table as shown below.
|
firstName |
middleInitial |
lastName |
|
Michael |
J. |
Mouse |
|
John |
Q. |
Public |
|
Jane |
M. |
Doe |
|
Im |
A. |
Student |
|
Ur |
A. |
Student |
This is an upgrade to servlet named Servlet02 discussed in an earlier lesson. The upgrade is described by item 4 in following list.
This servlet performs the following operations:
The client has the ability to either cause the existing table to be deleted and add data to a new table, or to simply add new data to an existing table. The data provided by the client consists of three strings, one representing a first name, one representing a middle initial, and the third representing a last name. Each time the client submits data, all of the data is retrieved from the table, formatted into an HTML table, and sent back to be displayed on the client screen.
The program was tested using three computers on the network, all running JDK 1.2 under Win95.
For this test, the HTTP server program from JavaSoft named Java Web Server 1.1.1 was running on one machine. The database server program named mSQL was running on a second machine. A browser was running on the third machine
An HTML form file named Servlet05.html containing four fields and one submit button was loaded into the browser. The four fields were:
The servlet was activated and data was sent to the server by the highlighted tags from the HTML file shown below. If you aren't familiar with the HTML syntax for creating and processing forms, you will need to look this up in an appropriate HTML book or tutorial. Many such documents are freely available on the web. (Note that this version of the HTML file makes use of the servletrunner program running on the localhost.)
<FORM METHOD=GET ACTION="http://localhost:8080/servlet/Servlet05"> Enter your name and press the button<P> Clear Database, y or n: <INPUT TYPE=TEXT NAME="clear"><P> First Name: <INPUT TYPE=TEXT NAME="firstName"><P> Middle Initial: <INPUT TYPE=TEXT NAME="middleInitial"><P> Last Name: <INPUT TYPE=TEXT NAME="lastName"><P> <INPUT TYPE=SUBMIT> |
The first line shown above caused a GET request to be sent to the server and caused the server to call the servlet named Servlet05. The request from the client was handled by an overridden doGet() method in the servlet.
The servlet got a connection to a specified database server on the machine named node3 using the following URL:
jdbc:msql://node3:1114/JunkDB |
Note that the // is part of the URL and is not a comment indicator in this case.
Then, the code in the servlet obtained the value of the parameter named clear from the client. If the value was either Y or y, and a table named servlet05Table already existed, it was deleted.
Next, the servlet created a new table named servlet05Table if a table of that name didn't already exist
The servlet then got the values for the parameters named firstName, middleInitial, and lastName from the client and inserted them into the database table.
After this, the servlet
The program was also successfully tested using the JavaSoft servletrunner program running on localhost as a substitute for the HTTP server. In this case, only two different machines were involved: one for the browser and servletrunner and one for the database server..
The first fragment is shown here simply to remind you of the requirement to import special packages dealing with servlets and JDBC.
import java.io.*; import javax.servlet.*; import javax.servlet.http.*; import java.sql.*; |
The next fragment shows the beginning of the overridden doGet() method, and should remind you that each of the service() methods receive two objects as parameters. One object passes data from the client to the servlet. The other object passes data from the servlet to the client.
public class Servlet05 extends HttpServlet{
public void doGet(HttpServletRequest req,
HttpServletResponse res)
throws ServletException, IOException{ |
The next fragment declares two local reference variables that you will need to know about when they are used later.
try {
Statement stmt;//used for database access
ResultSet resultSet;//used for database access |
The next fragment contains code that you have seen in several earlier lessons.
As discussed in earlier lessons, it is necessary to set the content type of the data to be returned to the client before accessing the writer or stream that will be used to return that data. This is illustrated in the following fragment that sets the content type to the standard for HTML text.
The fragment gets an output writer object that will be used to send data back to the client. It also begins constructing the tags and other information that is required to construct an HTML page on the fly.
Finally, the fragment
res.setContentType("text/html");
PrintWriter out = res.getWriter();
out.println("<HTML>");
out.println("<HEAD><TITLE=Servlet05</TITLE></HEAD>");
out.println("<BODY>");
Class.forName("com.imaginary.sql.msql.MsqlDriver");
String url = "jdbc:msql://node3:1114/JunkDB";
Connection con = DriverManager.getConnection(url,
"baldwin", ""); |
The next fragment also contains code that you have seen before. First, it gets a Statement object linked to the database connection to be used later for manipulating the database.
This fragment gets the incoming parameter named clear from the client. If the value of the parameter is either Y or y, an attempt is made to delete the existing database table. Note that clear is the name of a local reference variable in the method, and "clear" as a parameter to the getParameter() method is a string that matches the name of the data-input field on the HTML form. There is no requirement for these two names to match.
Then the servlet tries to create a new database table each time it is invoked. If a table with the same name already exists, the attempt to create a new table is aborted and an exception is thrown. The exception is essentially ignored.
Since you have seen code of this type before, I omitted the code from the exception handlers for brevity. You can view it in the complete listing of the program near the end of this lesson.
stmt = con.createStatement();
String clear = req.getParameter("clear");
if(clear.toUpperCase().equals("Y")){//Delete table
try{
stmt.executeUpdate("DROP TABLE servlet05Table");
}catch(Exception e){//code omitted for brevity}
}//end if(clear.toUpperCase().equals("Y")
try{
stmt.executeUpdate("CREATE TABLE servlet05Table ("
+ "firstName char(15),"
+ "middleInitial char(2),"
+ "lastName char(15)"
+ ")");
}catch(Exception e){//code omitted for brevity} |
The next fragment also contains code that you have seen before. This fragment uses the getParameter() method of the incoming request object to obtain the values of the firstName, middleInitial, and lastName fields of the HTML form. Then this new data is inserted into (added to) the data already in the database table.
Pay particular attention to the requirement to surround the new data with single quotes to satisfy the SQL syntax requirement for string data.
The fragment also creates a Statement object and uses that object to execute a query on the database. The result of the query is encapsulated in an object of type ResultSet that is referred to by the reference variable named resultSet.
String firstName = req.getParameter("firstName");
String middleInitial = req.getParameter(
"middleInitial");
String lastName = req.getParameter("lastName");
stmt.executeUpdate("INSERT INTO servlet05Table ("
+ "firstName,middleInitial,lastName)"
+ " VALUES('"
+ firstName + "','"
+ middleInitial + "','"
+ lastName + "'"
+ ")");
stmt = con.createStatement();
resultSet = stmt.executeQuery(
"SELECT * from servlet05Table"); |
The objective of this servlet is to extract all of the data from the database without prior knowledge of the database structure, format the data into an HTML table, and send the table back to the client for display on the client screen.
The next fragment simply displays some text on the client screen.
out.println("Contents of Database<BR><BR>"); |
Finally, the remaining fragments provide information that is new to this lesson. An object of type ResultSetMetaData can be used to represent a ResultSet object and provide information about that object. The next fragment gets a ResultSetMetaData object that represents the ResultSet object produced by the query in the previous fragment.
The getColumnCount() method of the ResultSetMetaData object is used to obtain the number of columns in the ResultSet object. This information will be used in the conditional clause of loop structures in subsequent code.
The ResultSetMetaData interface declares about twenty different methods that can be called to obtain various types of information about a ResultSet object. getColumnCount() is only one of them.
ResultSetMetaData resultSetMetaData =
resultSet.getMetaData();
int numColumns = resultSetMetaData.getColumnCount(); |
This code constructs a String object that contains the entire HTML table, including tags and data. The contents of the String object are then sent to the client. An object of the StringBuffer class is used to construct the String on the fly.
The next fragment begins the construction of the StringBuffer object. First, the object is instantiated. Then the append() method is used to append an HTML tag. The HTML tag specifies a table with a border two pixels in width. If you aren't familiar with the HTML tag syntax used to construct a table, you will need to look it up in another source.
The fragment also appends the HTML tag that begins the first row of data in the table.
StringBuffer strBuf = new StringBuffer();
strBuf.append("<TABLE BORDER=2>\n");
strBuf.append("<TR>");//table row tag |
The next fragment iterates on the number of columns in the table. During each iteration, it appends a table header tag followed by the string from the column label for that column in the ResultSet table. Thus, the column labels in the ResultSet table are used as column headers in the resulting HTML table.
After the loop is exhausted, a tag is appended indicating the end of the row.
for(int i = 1; i <= numColumns; i++){
strBuf.append("<TH>"//table header tag
+ resultSetMetaData.getColumnLabel(i));
}//end for loop
strBuf.append("</TR>\n");//end table row |
The next fragment is a little more complicated. This fragment contains an outer while loop that iterates once for each row of data in the ResultSet table. It also contains an inner nested for loop that iterates once for each column in the ResultSet table.
The behavior of the outer loop is simply to provide the HTML tags that begin and end successive rows in the HTML table.
During each iteration of the inner loop (one iteration for each column), a table data tag is appended to the StringBuffer object.
Then the code gets the object that represents the cell in the ResultSet table for that column and that row. The object is tested for null. If it is not null, the toString() method is used to convert it to a String which is appended to the StringBuffer object.
If it is null, an HTML non-breaking space is appended to the StringBuffer object. This effectively produces an empty cell in the resulting HTML table.
while(resultSet.next()) {
strBuf.append("<TR>");//new row
for(int i = 1; i <= numColumns; i++){
strBuf.append("<TD>");//table data element tag
Object obj = resultSet.getObject(i);
if(obj != null){
strBuf.append(obj.toString());
}//end if(obj != null)
else{
strBuf.append(" ");//non-breaking space
}//end else
}//end for loop
strBuf.append("</TR>");//end table row
}//end while loop |
The next fragment appends the HTML tag to specify the end of the table and then sends the entire string representation of the StringBuffer object to the client. This causes the HTML table to be displayed on the client screen.
strBuf.append("</TABLE>");//end table
out.println(strBuf);//send table to client |
The next fragment constructs the remaining tags required by the HTML format and closes the connection to the database.
out.println("</BODY></HTML>");
con.close(); |
This is followed by some routine wrap-up code that is not shown here, but which you can view in the complete listing of the program in the next section.
A complete listing of the program follows.
/*File Servlet05.java, Copyright 1999, R.G.Baldwin
Rev 1/8/99
The primary purpose of this program is to illustrate the
use of the JDBC ResultSetMetaData interface, which provides
a way to get information about the underlying structure of
a ResultSet object at runtime.
This servlet performs a query on a database and then
displays the resulting table in an HTML table without
knowing anything about the contents of the ResultSet object
in advance.
This is an upgrade to the servlet named Servlet02. The
upgrade is described by item 4 in the following list.
This servlet performs the following operations:
1. Use a servlet to get data from a client form.
2, Use the servlet to access a database to store
data obtained from the client form.
3. Use the servlet to access the database to retrieve
data.
4. Use the servlet to automatically format the data
into an HTML table and send it back to the client.
The program was tested using three computers on the
network, all running JDK 1.2 under Win95.
For this test, the HTTP server program from JavaSoft
named Java Web Server 1.1.1 was running on one machine.
The database server program named mSQL was running on a
second machine.
A browser was running on the third machine
An HTML file containing four fields and one submit button
was loaded into the browser. The four fields were:
clear (y or n)
firstName
middleInitial
lastName
The servlet was activated by the following tag in the
HTML file.
<FORM METHOD=GET
ACTION="http://webserver:8080/servlet/Servlet05">
This tag caused a GET request to be sent to the server and
caused the server to call the servlet named Servlet05. The
request from the client was handled by an overridden
doGet() method in the servlet.
The servlet got a connection to a specified database
server on the machine named node3 using the following URL:
jdbc:msql://node3:1114/JunkDB
Then, the code in the servlet obtained the value of the
parameter named clear from the client. If the value was
either Y or y, and a table named servlet05Table already
existed, it was deleted.
Next, the servlet created a new table named servlet05Table
if a table of that name didn't already exist
The servlet then got the values for the parameters named
firstName, middleInitial, and lastName from the client and
inserted them into the database table.
After this, the servlet
- Accessed the database server and got a ResultSet table
containing all of the data in the database
- Used ResultSetMetaData to get information about the
ResultSet table
- Used that information to format the data in the ResultSet
table into an HTML table and returned the HTML table to
the browser where it appeared in the browser window.
The program was also successfully tested using the JavaSoft
servletrunner program running on localhost as a substitute
for the HTTP server. In this case, only two different
machines were involved: one for the browser and
servletrunner and one for the database server.
**********************************************************/
import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.*;
public class Servlet05 extends HttpServlet{
public void doGet(HttpServletRequest req,
HttpServletResponse res)
throws ServletException, IOException{
try {
Statement stmt;//used for database access
ResultSet resultSet;//used for database access
//Set the content type of the data to be sent back
// to the client.
res.setContentType("text/html");
//Get an output writer object to send data back to
// the client.
PrintWriter out = res.getWriter();
//Begin constructing the HTML page to send back to
// the client.
out.println("<HTML>");
out.println("<HEAD><TITLE=Servlet05</TITLE></HEAD>");
out.println("<BODY>");
//Register the JDBC driver
Class.forName("com.imaginary.sql.msql.MsqlDriver");
//Construct URL for database on node3
String url = "jdbc:msql://node3:1114/JunkDB";
//Get a connection to the database
Connection con = DriverManager.getConnection(url,
"baldwin", "");
//Get a Statement object
stmt = con.createStatement();
//Get the incoming parameter named clear from the
// client. If it is Y or y, delete the existing
// table.
String clear = req.getParameter("clear");
if(clear.toUpperCase().equals("Y")){//Delete table
try{
stmt.executeUpdate("DROP TABLE servlet05Table");
}catch(Exception e){
out.println(e + "<br>");
out.println("Unable to delete table<br><br>");
}//end catch
}//end if(clear.toUpperCase().equals("Y")
//Create a new table named servlet05Table in the
// database if it doesn't already exist
try{
stmt.executeUpdate("CREATE TABLE servlet05Table ("
+ "firstName char(15),"
+ "middleInitial char(2),"
+ "lastName char(15)"
+ ")");
}catch(Exception e){
out.println(e + "<br>");
out.println("Unable to create table<br><br>");
}//end catch
//Get the incoming data parameters from the client
String firstName = req.getParameter("firstName");
String middleInitial = req.getParameter(
"middleInitial");
String lastName = req.getParameter("lastName");
//Put the data into the database table. Don't forget
// to enclose the string data in single quotes to
// satisfy the SQL syntax requirement.
stmt.executeUpdate("INSERT INTO servlet05Table ("
+ "firstName,middleInitial,lastName)"
+ " VALUES('"
+ firstName + "','"
+ middleInitial + "','"
+ lastName + "'"
+ ")");
//Create a statement object linked to the database
// connection.
stmt = con.createStatement();
//Use the statement object to execute a query on the
// database.
resultSet = stmt.executeQuery(
"SELECT * from servlet05Table");
out.println("Contents of Database<BR><BR>");
//Get info about the ResultSet
ResultSetMetaData resultSetMetaData =
resultSet.getMetaData();
int numColumns = resultSetMetaData.getColumnCount();
//Start an output string that is an HTML table
StringBuffer strBuf = new StringBuffer();
//begin HTML table
strBuf.append("<TABLE BORDER=2>\n");
//Use column labels from ResultSet for table titles
strBuf.append("<TR>");//table row tag
for(int i = 1; i <= numColumns; i++){
strBuf.append("<TH>"//table header tag
+ resultSetMetaData.getColumnLabel(i));
}//end for loop
strBuf.append("</TR>\n");//end table row
while(resultSet.next()) {
strBuf.append("<TR>");//new row
for(int i = 1; i <= numColumns; i++){
strBuf.append("<TD>");//table data element tag
Object obj = resultSet.getObject(i);
if(obj != null){
strBuf.append(obj.toString());
}//end if(obj != null)
else{
strBuf.append(" ");//non-breaking space
}//end else
}//end for loop
strBuf.append("</TR>");//end table row
}//end while loop
strBuf.append("</TABLE>");//end table
out.println(strBuf);//send table to client
//Finish the construction of the html page
out.println("</BODY></HTML>");
//Enable the following statement to delete the table
// when the servlet terminates.
//stmt.executeUpdate("DROP TABLE servlet05Table");
//Close the connection to the database
con.close();
}catch( Exception e ) {
e.printStackTrace();
}//end catch
}//end doGet()
}//end class Servlet05 |
-end-