import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;
import java.util.*;
import java.sql.*;
// This class demonstrates the use of a visual application
// to produce and display a query. In this case, the
// user can enter the name of a customer, and the application
// will query the database to find their orders.
public class ACMECustomer extends HttpServlet
implements SingleThreadModel {
public void doPost(HttpServletRequest request,
HttpServletResponse response)
throws ServletException, IOException {
int quantity;
double price, total;
String item;
int day, month, year;
String dateString;
// Full package name to avoid confusion between sql and util Date classes
java.sql.Date date;
Connection con;
PrintWriter out = response.getWriter();
try {
// Register the driver (or the bridge).
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
//Class.forName("com.mysql.jdbc.Driver");
// Make the connection.
con = DriverManager.getConnection("jdbc:odbc:ACME");
// con = DriverManager.getConnection("jdbc:mysql://localhost/ACME");
}
// If there is a problem connecting, return an error message.
catch(Exception ex) {
out.println("Error -- Database unreachable!
");
return;
}
// Get the customer name from the request
String who = (request.getParameter("customer")).trim();
// If there was no name entered, notify the user.
if (who.equals("")) {
out.println("No customer name entered!
");
return;
}
// Construct a query, based on the item name entered by
// the user. We will get the name of the person who bought
// the item and the number they bought (from the Purchases
// table), and the price (from the stock table). This will
// be used to compute the total costs.
String queryString =
"SELECT Purchases.Item, Purchases.Quantity, Purchases.When, Stock.Price " +
"FROM Purchases, Stock " +
"WHERE Purchases.Name = '" + who + "' " +
"AND Purchases.Item = Stock.Item";
try {
// Create a new statement object.
Statement s = con.createStatement();
// Execute the query on it.
ResultSet rs = s.executeQuery(queryString);
boolean notEmpty = false;
boolean headerPrinted = false;
total = 0;
// Cycle through the result set until no more records, and
// build a simple html table to display the data.
while (rs.next()) {
// Print header of table if not printed yet
if (!headerPrinted) {
out.println("Orders for " + who + "
");
out.println("");
out.println("| Item Purchased | ");
out.println("Date Purchased | ");
out.println("Number Purchased | ");
out.println("Price per Item |
");
headerPrinted = true;
notEmpty = true;
}
// Extract the named fields from the current record
item = rs.getString("Item");
quantity = rs.getInt("Quantity");
price = rs.getDouble("Price");
total = total + price;
date = rs.getDate("When");
// To extract information from a Date object, we will need to
// build a GregorianClaendar object from it, and then extract
// its data using Calendar constants.
GregorianCalendar gc = new GregorianCalendar();
gc.setTime(date);
day = gc.get(Calendar.DAY_OF_MONTH);
month = gc.get(Calendar.MONTH) + 1;
year = gc.get(Calendar.YEAR);
dateString = "" + month + "/" + day + "/" + year;
// Write as a new table row
out.println("| " + item + " | ");
out.println("" + dateString + " | ");
out.println("" + quantity + " | ");
out.println("$" + price + " |
");
}
// Print total and close table
if (notEmpty) {
out.println("| Total Purchases: $" + total);
out.println(" |
");
}
// If no records found, send simple message instead
else {
out.println("No records for that customer.