Building the Web Site with Java
The remainder of this chapter builds the Sales Tracking Web site as defined in Chapter 8 in Figure 8.7 (reprinted here). Figure 10.22 illustrates that Java can build the same Web site as was built in Chapter 9. Notice the URL, in Figure 10.22, makes reference to the simplej virtual path and Java cartridge (as defined in Figures 10.16 and 10.18).
Figure 10.22. Sales Tracking Web site.
The final example for this chapter illustrates two servlets—stcarsforsale and stcarsforsalebuy—to match the two Web pages of the Web site. Each will use the stcarsutil.banner to provide a consistent banner. These Java servlets also will select and update the ST_CARS_FOR_SALE table object, illustrated in Chapter 8 (refer to Figures 8.5 and 8.6).
The Java code will be listed and can be accessed and compiled using the Oracle JDeveloper v3.0 environment, discussed earlier in this chapter. This chapter also discusses the Java examples and the specific code necessary to build the Web site. Java is a complex code, and it is beyond the scope of this one chapter to discuss the intricacies of the Java language.
Figure 8.7. These four windows (starting at the upper left) show the navigation of the actual Sales Tracking Web site.
TIP
Many good books about the Java language are available, including Java from Scratch by Que.
The stcarsforsale Java servlet builds the Web page as illustrated in Figure 10.22. Lines 15 and 16 retrieve specific parameters coming in from the URL. If no parameters are passed, the inv_id and direction variables are set to null.
Lines 20–22 begin the HTML output. Notice in line 23 the call to stcarsutil.banner (from Listing 10.4) to display the Metro Motors banner page.
Line 31 connects to the database using the JDBC thin driver and connects to an Oracle database with a Net8 name of test, using the sales_tracking user ID and password. Remember from Chapter 8 that the thin connection will be maintained only for a single session initiated by a single Web browser access.
Some tasks, such as cursor management (retrieving a group of rows from the database and moving forward and backward through the rows), are best handled by PL/SQL. This Java servlet shows interaction between Java and PL/SQL (see line 33). The get_car PL/SQL procedure is similar to the auto_display PL/SQL developed in Chapter 9 (refer to Listing 9.9) in how it selects rows to be displayed. In Java, the CallableStatement routine is used to build the call statement and process any parameters. The six question marks (?) in line 33 are parameter placeholders: There are two input variables to this procedure and four variables returned. Lines 34–37 define the parameters that will be returned from the PL/SQL procedure, and lines 39–45 do some checking for the existence of URL input parameters. If they exist, they will be passed to the PL/SQL routine. Line 46 calls the PL/SQL routine.
Lines 48–84 prepare the bulk of the Web page illustrated in Figure 10.22. Notice how line 50 displays the automobile photo (its path was returned in variable 6 from the PL/SQL routine: line 66 of Listing 10.7) by defining an image IMG and making the source SRC reference the return variable from the PL/SQL get_car procedure.
The Previous and Next buttons are defined in lines 59–62 and lines 66–69, respectively. The URL is set to this Java servlet, whereas the Buy This Car button's URL (lines 73–75) is set to the other Java servlet, stcarsforsalebuy. This is how access to either Web page is defined.
The remainder of the code in this servlet, lines 87–99, is used to catch and handle any error conditions, and line 101 completes the HTML output.
Listing 10.6. stcarsforsale Java Servlet
1: import java.io.*;
2: import java.sql.*;
3: import javax.servlet.*;
4: import javax.servlet.http.*;
5: import java.util.*;
6:
7: public class stcarsforsale extends HttpServlet {
8:
9: public void doGet(HttpServletRequest req, HttpServletResponse res)
10: throws ServletException, IOException{
11: Connection con = null;
12: Statement stmt = null;
13: ResultSet rs = null;
14:
15: String inv_id = req.getParameter("p_inv_id");
16: String direction = req.getParameter("p_direction");
17:
18: res.setContentType("text/html");
19: PrintWriter out = res.getWriter();
20: out.println("<HTML>");
21: out.println("<HEAD><TITLE>Metro Motors Web Site</TITLE></HEAD>");
22: out.println("<BODY>");
23: stcarsutil.banner(out,"Metro Motors Web Site");
24:
25: try {
26: //Register Oracle Driver
27: Class.forName("oracle.jdbc.driver.OracleDriver");
28:
29: //Get a Connection to the database
30: con = DriverManager.getConnection(
31:"jdbc:oracle:thin:@localhost:1521:test", "sales_tracking","sales_tracking");
32:
33: CallableStatement cstmt = con.prepareCall("{call get_car(?,?,?,?,?,?)}");
34: cstmt.registerOutParameter(3,java.sql.Types.FLOAT);
35: cstmt.registerOutParameter(4,java.sql.Types.VARCHAR);
36: cstmt.registerOutParameter(5,java.sql.Types.FLOAT);
37: cstmt.registerOutParameter(6,java.sql.Types.VARCHAR);
38: cstmt.setString(1,direction);
39: if (inv_id != null) {
40: cstmt.setInt(2,Integer.parseInt(inv_id));
41: }
42: else
43: {
44: cstmt.setInt(2,0);
45: }
46: cstmt.execute();
47:
48: out.println("<TABLE><TR>");
49: out.println("<TD ROWSPAN=3>");
50: out.println("<IMG WIDTH=300 SRC=" + cstmt.getString(6) +">");
51: out.println("</TD>");
52: out.println("<TD COLSPAN=3>");
53: out.println("Description:"+cstmt.getString(4));
54: out.println("</TD></TR><TD COLSPAN=3>");
55: out.println("Suggested Price: "+ cstmt.getInt(5));
56: out.println("</TD></TR>");
57: out.println("<TR>");
58: out.println("<TD>");
59: out.println("<FORM URL=stcarsforsale METHOD=get>");
60: out.println("<INPUT TYPE=hidden NAME=p_direction VALUE=DISPLAY_PREV>");
61: out.println("<INPUT TYPE=hidden NAME=p_inv_id VALUE="+cstmt.getInt(3)+">");
62: out.println("<INPUT TYPE=SUBMIT VALUE=Previous>");
63: out.println("</FORM>");
64: out.println("</TD>");
65: out.println("<TD>");
66: out.println("<FORM URL=stcarsforsale METHOD=get>");
67: out.println("<INPUT TYPE=hidden NAME=p_direction VALUE=DISPLAY_NEXT>");
68: out.println("<INPUT TYPE=hidden NAME=p_inv_id VALUE="+cstmt.getInt(3)+">");
69: out.println("<INPUT TYPE=SUBMIT VALUE=Next>");
70: out.println("</FORM>");
71: out.println("</TD>");
72: out.println("<TD>");
73: out.println("<FORM URL=stcarsforsalebuy METHOD=get>");
74: out.println("<INPUT TYPE=hidden NAME=p_inv_id VALUE="+cstmt.getInt(3)+">");
75: out.println("<INPUT TYPE=SUBMIT VALUE='Buy This Car'>");
76: out.println("</FORM>");
77: out.println("</TD>");
78:
79: out.println("");
80: out.println("");
81: out.println("");
82: out.println("");
83: out.println("</TR>");
84: out.println("</TABLE>");
85:
86:
87: }
88: catch(ClassNotFoundException e) {
89: out.println("Could not load db driver" + e.getMessage());
90: }
91: catch(SQLException e) {
92: out.println("SQL Error: " + e.getMessage());
93: }
94: finally {
95: try {
96: if (con != null) con.close();
97: }
98: catch (SQLException e) {}
99: }
100:
101: out.println("</BODY></HTML>");
102: }
103: }
|
The PL/SQL procedure get_car, Listing 10.7, is similar to Listing 9.9 in how it selects cars from the ST_CARS_FOR_SALE table object. It is passed two input variables. In line 3, the p_direction variable has a default value, in case no input values were passed from the Java servlet (Listing 10.6, line 33).
There is no connect string to the database. The Java servlet is already connected to the database and can call this procedure. The output of this procedure is in lines 63–66 and returns information from the current row in the open cursor.
Listing 10.7. get_car PL/SQL Procedure stcarsforsale Java Servlet
1: CREATE OR REPLACE PROCEDURE get_car 2: ( 3: p_direction IN VARCHAR2 DEFAULT 'DISPLAY_NEXT' 4: , p_inv_id IN NUMBER DEFAULT NULL 5: , x_inv_id OUT NUMBER 6: , x_auto_description OUT VARCHAR2 7: , x_sale_amt OUT NUMBER 8: , x_photo_location OUT VARCHAR2 9: ) IS 10: CURSOR next_CUR 11: ( 12: p_inv_id IN NUMBER 13: ) IS 14: SELECT inv_id, auto_description 15: , sale_amt, sale_photo_location 16: FROM st_cars_for_sale 17: WHERE inv_id > NVL(p_inv_id,0) 18: ORDER BY inv_id ASC; 19: CURSOR prev_CUR 20: ( 21: p_inv_id IN NUMBER 22: ) IS 23: SELECT inv_id, auto_description 24: , sale_amt, sale_photo_location 25: FROM st_cars_for_sale 26: WHERE inv_id < NVL(p_inv_id,10000000) 27: ORDER BY inv_id DESC; 28: sale_row prev_CUR%ROWTYPE; 29: BEGIN 30: IF p_direction = 'DISPLAY_NEXT' THEN 31: OPEN next_CUR 32: ( 33: p_inv_id 34: ); 35: FETCH next_CUR INTO sale_row; 36: IF next_CUR%NOTFOUND THEN 37: OPEN prev_CUR 38: ( 39: p_inv_id 40: ); 41: FETCH prev_CUR INTO sale_row; 42: CLOSE prev_CUR; 43: END IF; — next_CUR%NOTFOUND 44: CLOSE next_CUR; 45: ELSE 46: OPEN prev_CUR 47: ( 48: p_inv_id 49: ); 50: FETCH prev_CUR INTO sale_row; 51: IF prev_CUR%NOTFOUND THEN 52: OPEN next_CUR 53: ( 54: p_inv_id 55: ); 56: FETCH next_CUR INTO sale_row; 57: CLOSE next_CUR; 58: END IF; — prev_CUR%NOTFOUND 59: CLOSE prev_CUR; 60: END IF; — p_direction = 'DISPLAY_NEXT' 61: 62: — Set return values 63: x_inv_id := sale_row.inv_id; 64: x_auto_description := sale_row.auto_description; 65: x_sale_amt := sale_row.sale_amt; 66: x_photo_location := sale_row.sale_photo_location; 67: END; 68: / |
The stcarsforsalebuy Java servlet (see Listing 10.8) displays the Web page as illustrated in Figure 8.9 of Chapter 8. This Web page enables the Web browser user to make an offer on a car and record the information in the ST_CARS_FOR_SALE table object. The UPDATE statement that performs this task can be found in lines 43–47. If the UPDATE was successful (see line 48), the offer information is displayed on the Web page.
The SELECT statement (lines 54–56) reread the information being displayed on the original Web page (the stcarsforsale Java servlet). The remainder of the code (beginning at line 58) builds the Web page and directs the Web browser to the appropriate servlet for the selections made by the Web browser user, similar to Listing 10.6 (lines 48–84).
Listing 10.8. stcarsforsalebuy Java Servlet
1: import java.io.*;
2: import java.sql.*;
3: import javax.servlet.*;
4: import javax.servlet.http.*;
5: import java.util.*;
6:
7:
8:
9:
10: public class stcarsforsalebuy extends HttpServlet {
11:
12: public void doGet(HttpServletRequest req, HttpServletResponse res)
13: throws ServletException, IOException{
14: Connection con = null;
15: Statement stmt = null;
16: ResultSet rs = null;
17: int counter = 0;
18:
19: String inv_id = req.getParameter("p_inv_id");
20: String buyer_name = req.getParameter("p_buyer_name");
21: String buyer_phone = req.getParameter("p_buyer_phone");
22: String buyer_offer = req.getParameter("p_buyer_offer");
23:
24: res.setContentType("text/html");
25: PrintWriter out = res.getWriter();
26: out.println("<HTML>");
27:out.println("<HEAD><TITLE>Metro Motors Web Site Make Offer</TITLE></HEAD>");
28: out.println("<BODY>");
29: stcarsutil.banner(out,"Metro Motors Web Site - Make An Offer");
30:
31: try {
32: //Register Oracle Driver
33: Class.forName("oracle.jdbc.driver.OracleDriver");
34:
35: //Get a Connection to the database
36: con = DriverManager.getConnection(
37:"jdbc:oracle:thin:@localhost:1521:test", "sales_tracking","sales_tracking");
38:
39: stmt = con.createStatement();
40:
41: if (buyer_name != null) {
42: // update the record with the offer
43: counter = stmt.executeUpdate(
44: "UPDATE st_cars_for_sale SET buyer_name='" + buyer_name
45: + "', buyer_phone='" + buyer_phone + "', buyer_offer="
46: + buyer_offer + ", buyer_date=SYSDATE WHERE inv_id = " + inv_id
47: );
48: if (counter > 0) {
49: out.println("Offer for $" + buyer_offer + " recorded<BR>");
50: }
51: counter = 0;
52: }
53:
54: rs = stmt.executeQuery("SELECT auto_description, sale_amt, sale_photo_location, inv_id "
55: + "FROM st_cars_for_sale WHERE inv_id="+inv_id);
56:
57: while (rs.next()) {
58: counter++;
59: out.println("<TABLE><TR>");
60: out.println("<TD ROWSPAN=6>");
61: out.println("<IMG WIDTH=300 SRC=" + rs.getString ("sale_photo_location") +">");
62: out.println("</TD>");
63: out.println("<TD COLSPAN=2>");
64: out.println("Description:"+rs.getString ("auto_description"));
65: out.println("</TD></TR>");
66: out.println("<TR>");
67: out.println("<TD>");
68: out.println("<FORM URL=stcarsforsale METHOD=get>");
69: out.println("<INPUT TYPE=hidden NAME=p_direction VALUE=DISPLAY_PREV>");
70: out.println("<INPUT TYPE=hidden NAME=p_inv_id VALUE="+rs.getInt("inv_id")+">");
71: out.println("Buyer Name:");
72: out.println("</TD><TD>");
73: out.println("<INPUT TYPE=TEXT NAME=p_buyer_name maxlength=20>");
74: out.println("</TD><TD></TR>");
75: out.println("<TR><TD>");
76: out.println("Buyer Phone:");
77: out.println("</TD><TD>");
78: out.println("<INPUT TYPE=TEXT NAME=p_buyer_phone maxlength=10>");
79: out.println("</TD></TR>");
80: out.println("<TR><TD>");
81: out.println("Offer:");
82: out.println("</TD><TD>");
83: out.println("<INPUT TYPE=TEXT NAME=p_buyer_offer maxlength=10>");
84: out.println("</TD></TR>");
85:
86: out.println("<TR>");
87: out.println("<TD COLSPAN=2 ALIGN=CENTER>");
88: out.println("<INPUT TYPE=SUBMIT VALUE='Make Offer'>");
89: out.println("</FORM>");
90: out.println("</TD></TR>");
91: out.println("<TR><TD COLSPAN=2 ALIGN=CENTER>");
92: out.println("<FORM URL=stcarsforsale METHOD=get>");
93: out.println("<INPUT TYPE=hidden NAME=p_inv_id VALUE="+rs.getInt("inv_id")+">");
94: out.println("<INPUT TYPE=SUBMIT VALUE='Browse Inventory'>");
95: out.println("</FORM>");
96: out.println("</TD></TR>");
97: out.println("</TABLE>");
98: }
99: if (counter==0) {
100: out.println("Car Not found");
101: }
102:
103: }
104: catch(ClassNotFoundException e) {
105: out.println("Could not load db driver" + e.getMessage());
106: }
107: catch(SQLException e) {
108: out.println("SQL Error: " + e.getMessage());
109: }
110: finally {
111: try {
112: if (con != null) con.close();
113: }
114: catch (SQLException e) {}
115: }
116:
117: out.println("</BODY></HTML>");
118: }
119:
120:
121: }
|