Internet Programming I

Assignment 10

Searching and updating a database

Due: November 21, 2006

In assignment 9, we displayed all the data in a database. However, if the database is large, that is impractical. Instead, it is necessary to restrict the amount of data retrieved to a specific item or to a range of items. We can also update a database by adding new rows, deleting rows, or modifying specific fields.

1. We will first use the library database discussed before.

2. If we want to retrieve all the information about one of the books, we can use the query,

"Select * From books Where title = '" + title + "'"

The asterisk (*) says to get all the data from the books table. But the ‘Where’ clause restricts the data retrieved to that for a specific title. If we knew the title ahead of time, the query would have been something like

"Select * From books Where title = 'Emma'";

3. As before, a web page is used to send the request to the server, and the server then uses a program to send the query on to the database. The following web page contains two forms, one for displaying the entire database and another to find a specific title. Case here is very important. The name used in the form, name="title", must have exactly the same case as that used in the program on the server.

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

<body>

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

</form>

<h3>To find a book in the library database, enter the title and click the Send button.</h3>

<input type="text" name="title" value="" size="30" /> Title

</form>

</body></html>

4. The program on the server has to receive the request from the web page and use the data in order to create a query. This query will provide the book’s title, a string. And all strings in queries must be surrounded by quotation marks. Since the entire query has to be in quotation marks as well, the solution is to use single quotes inside of the double quotes. The program uses the Page class, as before. If you run this, you should copy it in at the end.

package client_server;

/**

FindBook 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 FindBook extends WebRequestProcessor

{

public void process (Request request, Response response)

{

try

{

/* Get the title from the html page. The case used in the web page must be

the same as in the request. */

String title = request.getParameter ("title");

// Get a connection to the database. This code is the same as before.

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

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

// Create a statement and a query. Note the single quotes inside of the double quotes.

Statement stmt = con.createStatement ();

String query = "Select * From books Where title = '" + title + "'";

// Execute the query and retrieve a ResultSet.

ResultSet rs = stmt.executeQuery (query);

// Get a PrintWriter object and create a heading for the response page.

PrintWriter out = response.getWriter ();

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

/* If the ResultSet is not empty, display the results. Note that this uses an if

statement rather than a while statement. */

if (rs.next ())

{

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

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>");

}

else // Otherwise, if the result is empty display an error message.

{

out.println ("<h4>The title was not in the database</h4>");

}

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

} // FindBook

5. The next program can be used to find a borrower in the borrower table. The places where it differs from the FindBook program are shown in bold face. As you can see, the changes are minor. However, they are important. If they are not all made, the program will not work.

package client_server;

/**

* FindBorrower 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 FindBorrower extends WebRequestProcessor

{

public void process (Request request, Response response)

{

try

{

// Get the title from the html page.

String name = request.getParameter ("name");

// 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 Where name = '" + name + "'";

// 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 Requested");

// Display the data in the ResultSet.

if (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>");

}

else

{

out.println ("<h4>The name was not in the database</h4>");

}

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

} // FindBorrower

6. To find a borrower, we need another form in the web page. It looks quite similar to the previous ones.

<h3>To find a borrower in the library database, enter the title and click the Send button.</h3>

<input type="text" name="name" value="" size="30" /> Name

</form>

7. Now add a find form to the web page you created for assignment 9 and write and compile a program to process it. Test it out and when it is working, send everything to me. If you want, you may include one of the following update methods as well. This would be for extra credit.

8. Adding an item to the database requires a longer query. We have to include all the fields in the table that is to be modified. So to add a new book, we have a query with five parts:

String query =

"Insert Into books Values ('" + isbn + "', '" + author + "', '" + title + "', '" + publisher + "', '"+ date + "')";

It is very easy to make a mistake here. If a field is a string, and all of these are, then it has to be in quotes. And the quotes must themselves be between double quotes. It is very easy to leave off one or more of the quotes. If this query had constant values in it instead of variables, it might look like:

"Insert Into books Values ('023146587', 'Shakespeare', 'Hamlet', 'Penguin', '1605')";

Make a note of where the quotation marks go. Update queries return an integer value. If it is 0, the update failed, but if it is anything else, the update was performed.

9. The insert program follows. The new code is in bold, as before.

package client_server;

import java.sql.*;

import java.io.*;

// InsertBook adds a book to the database.

public class InsertBook extends WebRequestProcessor

{

public void process (Request request, Response response)

{

try

{

PrintWriter out = response.getWriter ();

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

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

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

String isbn = request.getParameter ("isbn");

String author = request.getParameter ("author");

String title = request.getParameter ("title");

String publisher = request.getParameter ("publisher");

String date = request.getParameter ("date");

Statement stmt = con.createStatement ();

String query =

"Insert Into books Values ('"

+ isbn + "', '"

+ author + "', '"

+ title + "', '"

+ publisher + "', '"

+ date + "')";

int success = stmt.executeUpdate (query);

stmt.close ();

if (success == 0) out.println ("<h3>Insert error.</h3>");

else out.println ("<h3>The data has been successfully inserted.");

Page.createFooter (out);

} catch (ClassNotFoundException e) {System.out.println ("Class Not Found Exception.");}

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

} // process

} // class InsertBook

10. The form to handle this insertion looks like the following:

<h3>Click to insert a book into the database.</h3>

<input name="author" type="text" value="" size="20" /> Author<br />

<input name="title" type="text" value="" size="20" /> Title<br />

<input name="publisher" type="text" value="" size="20" /> Publisher<br />

</form>

11. There are also delete and update commands. The query to delete a book looks like

String query = "Delete From books Where isbn = '" + isbn + "'";

or String query = "Delete From books Where isbn = '0684837889'"; which uses the isbn for A Farewell to Arms.

There is also a query to update (modify) data in a specific field.

String query = "Update books Set date = '" + date + "' Where title = '" + title + "'";

or String query = "Update books Set date = '1605' Where title = 'Hamlet'";