Internet Programming I

Assignment 9

Html forms and databases

Due: November 14, 2006

Html forms are usually used in accompaniment with databases. The data is sent from the form to the server. There a SQL statement is constructed that sends a query to a database. The response from the database is then included in another web page and sent back to the user.

In assignment 7 we sent queries to a database using the Java programming language. In assignment 8, we collected data from a form and returned a response to the user. We used a simple server for this, but actual servers work very much the same way. In this assignment, we will add database queries to assignment 8.

1. Either open an existing database or create a new one. Close it and register it with the operating system. The one below is an example from a library. This table is called books.

2. Next use JCreator to create a web page with a form that will display the contents of the database. This form contains only a single input statement, since no data will be sent.

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

<html><head><title>Display Form</title></head>

<body>

<h3>To see the books in the library database, click the Send button.</h3>

</form>

</body></html>

3. Now use JCreator to create a Java program to receive the request from the form and send the query to the database.

package client_server;

/**

* DisplayProcessor processes a request from a web page. It responds to the request by sending

a query to a database, and returning the result.

**/

import java.io.*;

import java.sql.*;

public class DisplayProcessor extends WebRequestProcessor

{

public void process (Request request, Response response)

{

try

{

// Get a connection to the database.

Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver");

Connection con = DriverManager.getConnection ("jdbc:odbc:library");

// Create a statement and a query.

Statement stmt = con.createStatement ();

String query = "Select * From books";

// Execute the query and retrieve a ResultSet.

ResultSet rs = stmt.executeQuery (query);

// Get a PrintWriter object and respond to the request.

PrintWriter out = response.getWriter ();

Page.createHeader (out, "Book List");

out.println ("<h3>Book List</h3>");

// Display the data in the ResultSet.

while (rs.next ())

{

out.println ("<p>ISBN: " + rs.getString ("isbn"));

out.println ("<br />Author: " + rs.getString ("author"));

out.println ("<br />Title: " + rs.getString ("title"));

out.println ("<br />Publisher: " + rs.getString ("publisher"));

out.println ("<br />Date: " + rs.getString ("date") + "</p>");

}

Page.createFooter (out);

con.close (); // Close the connection to the database.

} catch (SQLException e) {System.out.println ("SQL Exception");}

catch (ClassNotFoundException e) {System.out.println ("Driver not found");}

} // process

} // DisplayProcessor

// Class with static method that add standard lines to the html output page.

class Page

{

public static void createHeader (PrintWriter out, String title)

{

out.println ("<!DOCTYPE HTML PUBLIC '-//W3C//DTD HTML 4.0 Transitional//EN'>");

out.println ("<html>");

out.println ("<head>");

out.println ("<title>" + title + "</title>");

out.println ("</head>");

out.println ("<body>");

} // createHeader

public static void createFooter (PrintWriter out){out.println ("</body></html>");}

} // class Page

4. When done, load WebServer.java into JCreator, click on the two blue dots and run it. When the console screen comes up, press the enter key. Go back to Internet Explorer and open DisplayForm.html. Click on the button. This should send the request to the server. The server then starts the DisplayProcessor program. It opens a connect to the database, sends a Select query, receives the response, and creates a web page with the data from the database. It then sends this back to the user. If everything works the result should look like the page below.

5. While the form is very simple, the processor program is not. It contains a number of sections.

· The package containing SQL commands must be imported: import java.sql.*;

As before, the name of the class must be exactly the same as the name of the file it is stored in.
The program must open a connection to the database:

// Get a connection to the database.

Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver");

Connection con = DriverManager.getConnection ("jdbc:odbc:library");

Next it has to create and execute a query to the database:

// Create a statement and a query.

Statement stmt = con.createStatement ();

String query = "Select * From books";

// Execute the query and retrieve a ResultSet.

ResultSet rs = stmt.executeQuery (query);

The following step is to get a PrintWriter object to write a web page for the user and then call the Page class to get the web page started:

// Get a PrintWriter object and respond to the request.

PrintWriter out = response.getWriter ();

Page.createHeader (out, "Book List");

out.println ("<h3>Book List</h3>");

After this is finished, the program uses a while loop to write all the data returned by the query to the web page:

// Display the data in the ResultSet.

while (rs.next ())

{

out.println ("<p>ISBN: " + rs.getString ("isbn"));

out.println ("<br />Author: " + rs.getString ("author"));

out.println ("<br />Title: " + rs.getString ("title"));

out.println ("<br />Publisher: " + rs.getString ("publisher"));

out.println ("<br />Date: " + rs.getString ("date") + "</p>");

}

Note the Html tags in the output statements.

Finally the program attaches a few more lines to the web page and closes the connection to the database.

6. Now create an Html page and a processor program for your database. You will be able to copy most of the lines in the form and program above. The example below shows how to change the program above to display the list of borrowers in the library.

The Page class doesn’t change, so it isn’t shown. The changes are shown in bold face. The only change to the Html page is the name of the processor.

<h3>To see the borrowers in the library database, click the Send button.</h3>

</form>

package client_server;

/**

* BorrowersProcessor processes a request from a web page. It responds to the

* request by sending a query to a database, and returning the result.

**/

import java.io.*;

import java.sql.*;

public class BorrowersProcessor extends WebRequestProcessor

{

public void process (Request request, Response response)

{

try

{

// Get a connection to the database.

Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver");

Connection con = DriverManager.getConnection ("jdbc:odbc:library");

// Create a statement and a query.

Statement stmt = con.createStatement ();

String query = "Select * From borrowers";

// Execute the query and retrieve a ResultSet.

ResultSet rs = stmt.executeQuery (query);

// Get a PrintWriter object and respond to the request.

PrintWriter out = response.getWriter ();

Page.createHeader (out, "Borrowers List");

out.println ("<h3>Borrowers List</h3>");

// Display the data in the ResultSet.

while (rs.next ())

{

out.println ("<p>ID: " + rs.getString ("id"));

out.println ("<br />Name: " + rs.getString ("name"));

out.println ("<br />Address: " + rs.getString ("address"));

out.println ("<br />Telephone: " + rs.getString ("phone"));

out.println ("<br />Email: " + rs.getString ("email") + "</p>");

}

Page.createFooter (out);

con.close (); // Close the connection to the database.

} catch (SQLException e) {System.out.println ("SQL Exception");}

catch (ClassNotFoundException e) {System.out.println ("Driver not found");}

} // process

} // BorrowersProcessor

7. Create an Html page and processor programs for the tables in the database you created for assignment five. Put everything in a folder, including the database. Zip up the entire folder and send it to me.