1 rizwank 1.1 //Rizwan Kassim - 602 784 143 (c) Rizwan Kassim 2004
2 import java.io.*;
3 import java.text.*;
4 import java.util.*;
5 import java.sql.*;
6 import javax.servlet.*;
7 import javax.servlet.http.*;
8
9 public class P4 extends HttpServlet {
10 PrintWriter out;
11
12 // define query Strings for params
13 String page;
14 String param1;
15 String param2;
16 String param3;
17 String param4;
18 String param5;
19 String param6;
20 String param7;
21 String param8;
22 rizwank 1.1 String query_text;
23
24 Connection con;
25
26 String debugloc;
27
28 // GOT FORM VALIDATION HELP FROM http://javascript.about.com/library/weekly/aa070901a.htm
29 public void doGet(HttpServletRequest req, HttpServletResponse resp)
30 throws IOException, ServletException
31 {
32 try {
33 Class.forName("COM.ibm.db2.jdbc.app.DB2Driver");
34 } catch (Exception e) {
35 e.printStackTrace();
36 }
37
38 resp.setContentType("text/html");
39 out = resp.getWriter();
40
41 // define query Strings for params
42 page = "";
43 rizwank 1.1 param1 = "";
44 param2 = "";
45 param3 = "";
46 param4 = "";
47 param5 = "";
48 param6 = "";
49 param7 = "";
50 param8 = "";
51 query_text = "";
52
53 // get params ( str = req.getParameter("paramname");)
54 page = req.getParameter("page");
55 param1 = req.getParameter("param1");
56 param2 = req.getParameter("param2");
57 param3 = req.getParameter("param3");
58 param4 = req.getParameter("param4");
59 param5 = req.getParameter("param5");
60 param6 = req.getParameter("param6");
61 param7 = req.getParameter("param7");
62 param8 = req.getParameter("param8");
63
64 rizwank 1.1 con = null;
65 String url ="jdbc:db2:CS143";
66
67 try {
68 con = DriverManager.getConnection(url);
69 }
70 catch ( SQLException ex ) {
71 out.println( "<p><hr><b>SQLException caught<br>---<br>" );
72 out.println( "Message : " + ex.getMessage( ) + "<br>");
73 out.println( "ErrorCode : " + ex.getErrorCode( ) + "<br>---<br></b>");
74 }
75 out.println("<html><head>");
76 String title = "Rizwan Kassim's Project 4 - Internet Movie Database";
77 out.println("<title>" + title + "</title>");
78 //out.println("<script language='JavaScript' src='../FormValid.js'></script>");
79 javascript();
80 out.println("</head><body bgcolor=white>");
81 out.println("<center><h1>" + title + "</h1><hr>");
82 out.println("<a href='P4?page=B1'>Actor Information</a> - <a href='P4?page=B2'>Movie Information</a><br><a href='P4?page=I1'>Actor Input</a> - <a href='P4?page=I2'>Comment Input</a> - <a href='P4?page=I3'>Movie Input</a><br><a href='P4?page=S1'>Search Query</a><br><hr>");
83 //out.println("<SELECT NAME='page'><option selected><option>B1<option>B2<option>S1<option>I1<option>I2<option>I3</select>");
84 if ( page != null ) {
85 rizwank 1.1 if ( page.startsWith("B1") ) { page_B1(); }
86 if ( page.startsWith("B2") ) { page_B2(); }
87 if ( page.startsWith("S1") ) { page_S1(); }
88 if ( page.startsWith("I1") ) { page_I1(); }
89 if ( page.startsWith("I2") ) { page_I2(); }
90 if ( page.startsWith("I3") ) { page_I3(); }
91 } else {
92 out.println("<hr>");
93 }
94 try {
95 con.close();
96 }
97 catch ( SQLException ex ) {
98 out.println( "<p><hr><b>SQLException caught<br>---<br>" );
99 out.println( "Message : " + ex.getMessage( ) + "<br>");
100 out.println( "ErrorCode : " + ex.getErrorCode( ) + "<br>---<br></b>");
101 }
102 }
103
104 public void page_B1() {
105 try {
106 rizwank 1.1 out.println("<form method=get action=P4>");
107 out.println("On which actor do you want information?<br>");
108 // TODO : ADD DIRECTOR
109 actor_droplist(true,1,param1);
110 out.println("<br><input type=hidden name=page value=B1><input type=submit value='Submit Query'><hr>");
111
112 if (param1 != null ) {
113 out.println("</center>");
114 Statement query_actor_data = con.createStatement();
115 query_text = "SELECT * FROM ACTOR WHERE ID=" + param1;
116 ResultSet actor_data = query_actor_data.executeQuery(query_text);
117 out.println("<table border='0' cellpadding='1' cellspacing='0' style='border-collapse: collapse' bordercolor='#111111' width='100%'><tr>");
118 out.println("<td><b>Actor's ID</b><td><b>Last Name</b><td><b>First Name</b><td><b>Sex</b><td><b>Date of Birth</b><td><b>Date of Death</b><tr>");
119 while (actor_data.next()) {
120 String id = actor_data.getString(1);
121 String last = actor_data.getString(2);
122 String first = actor_data.getString(3);
123 String sex = actor_data.getString(4);
124 String dob = actor_data.getString(5);
125 String dod = actor_data.getString(6);
126 out.println("<td>"+id+"<td>"+last+"<td>"+first+"<td>"+sex+"<td>"+dob+"<td>"+dod+"</tr></table><br>");
127 rizwank 1.1 }
128 out.println("<p>Roles:<br>");
129 Statement query_actor_role = con.createStatement();
130 query_text = "SELECT mid,role FROM MovieActor WHERE AID=" + param1;
131 ResultSet actor_role = query_actor_role.executeQuery(query_text);
132 while (actor_role.next()) {
133 String mid = actor_role.getString(1);
134 String role = actor_role.getString(2);
135 out.println("Played <i>" + role + "</i> in <b><a href='P4?param1="+mid+"&page=B2'>");
136 Statement query_movie_name = con.createStatement();
137 query_text = "SELECT title,year,id FROM Movie WHERE ID=" + mid;
138 ResultSet movie_name = query_movie_name.executeQuery(query_text);
139 while (movie_name.next()) {
140 String moviename = movie_name.getString(1);
141 String year = movie_name.getString(2);
142 String id = movie_name.getString(3);
143 out.println(moviename+"</a></b> ("+year+")<br>");
144 }
145 }
146 }
147 }
148 rizwank 1.1 catch ( SQLException ex ) {
149 out.println( "<p><hr><b>SQLException caught<br>" );
150 out.println( "---<br>" );
151 while ( ex != null ) {
152 out.println( "Message : " + ex.getMessage( ) + "<br>");
153 out.println( "SQLState : " + ex.getSQLState( ) + "<br>");
154 out.println( "ErrorCode : " + ex.getErrorCode( ) + "<br>");
155 out.println( "Query Passa: " + query_text + "<br>");
156 out.println( "---<br></b>" );
157 }
158 ex = ex.getNextException( );
159 } // close catch
160 }
161
162
163
164
165
166 public void page_B2() {
167 try {
168
169 rizwank 1.1 out.println("<form method=get action=P4>");
170 out.println("On which movie do you want information?<br>");
171 movie_droplist(true,1,param1);
172
173 out.println("<br><input type=hidden name=page value=B2><input type=submit value='Submit Query'><hr>");
174
175 if (param1 != null ) {
176 out.println("</center>");
177 Statement query_movie_data = con.createStatement();
178 query_text = "SELECT * FROM MOVIE WHERE ID=" + param1;
179 ResultSet movie_data = query_movie_data.executeQuery(query_text);
180 out.println("<table border='0' cellpadding='1' cellspacing='0' style='border-collapse: collapse' bordercolor='#111111' width='100%'><tr>");
181 out.println("<td><b>Movie's ID</b><td><b>Title</b><td><b>Year</b><td><b>Rating</b><td><b>Production Company</b><td><b>Average Rating</b><td><b>Genre(s)</b><tr>");
182 while (movie_data.next()) {
183 String id = movie_data.getString(1);
184 String title = movie_data.getString(2);
185 String year = movie_data.getString(3);
186 String rating = movie_data.getString(4);
187 String company = movie_data.getString(5);
188 String avgrating = "";
189 Statement query_movie_rating = con.createStatement();
190 rizwank 1.1 query_text = "SELECT AVG(RATING) FROM REVIEW WHERE MID=" + param1;
191 ResultSet movie_rating = query_movie_rating.executeQuery(query_text);
192 while (movie_rating.next()) {
193 avgrating = movie_rating.getString(1);
194 }
195 out.println("<td>"+id+"<td>"+title+"<td>"+year+"<td>"+rating+"<td>"+company+"<td>"+avgrating+"<td>");
196 String genre = "";
197 Statement query_movie_genre = con.createStatement();
198 query_text = "SELECT genre FROM moviegenre WHERE mid=" + param1;
199 ResultSet movie_genre = query_movie_genre.executeQuery(query_text);
200 while (movie_genre.next()) {
201 genre = movie_genre.getString(1);
202 }
203 out.println(genre+"</tr></table><br>");
204 }
205 out.println("<p>Roles:<br>");
206 Statement query_movie_role = con.createStatement();
207 query_text = "SELECT aid,role FROM MovieActor WHERE MID=" + param1;
208 ResultSet movie_role = query_movie_role.executeQuery(query_text);
209 while (movie_role.next()) {
210 String aid = movie_role.getString(1);
211 rizwank 1.1 String role = movie_role.getString(2);
212 Statement query_actor_name = con.createStatement();
213 query_text = "SELECT last,first,sex FROM Actor WHERE ID=" + aid;
214 ResultSet actor_name = query_actor_name.executeQuery(query_text);
215 out.println("<a href='P4?param1="+aid+"&page=B1'><b>");
216 while (actor_name.next()) {
217 String last = actor_name.getString(1);
218 String first = actor_name.getString(2);
219 String sex = actor_name.getString(3);
220 out.println(last+", "+first+" ("+sex+")");
221 }
222 out.println("</b></a> played as <i>" + role + "</i><br>");
223 }
224 Statement query_movie_comment = con.createStatement();
225 query_text = "SELECT * FROM Review WHERE MID=" + param1;
226 ResultSet movie_comment = query_movie_comment.executeQuery(query_text);
227 out.println("<br><table border='2' cellpadding='1' cellspacing='1' style='border-collapse: collapse' width='100%'>");
228 out.println("<caption><B>Comments</b></caption><tr><th>Name<th>Date Entered<th>Rating<th>Review");
229 while (movie_comment.next()) {
230 String name = movie_comment.getString(1);
231 String when = movie_comment.getString(2);
232 rizwank 1.1 String rating = movie_comment.getString(4);
233 String comment = movie_comment.getString(5);
234 out.println("<tr VALIGN=top><td NOWRAP ><b>"+name+"</b><td NOWRAP ><i>"+when+"</i><td NOWRAP ><b>"+rating+"</b><td>" + comment );
235 }
236 // TODO : Assign different CSS values for each rating! (colors)
237 out.println("</table><br><a href='P4?param1=" + param1 + "&page=I2'>Add a comment</a>" );
238 out.println("</html>" );
239 }
240 }
241 catch ( SQLException ex ) {
242 out.println( "<p><hr><b>SQLException caught<br>" );
243 out.println( "---<br>" );
244 while ( ex != null ) {
245 out.println( "Message : " + ex.getMessage( ) + "<br>");
246 out.println( "SQLState : " + ex.getSQLState( ) + "<br>");
247 out.println( "ErrorCode : " + ex.getErrorCode( ) + "<br>");
248 out.println( "Query Passb " + query_text + "<br>");
249 out.println( "---<br></b>" );
250 }
251 ex = ex.getNextException( );
252 } // close catch
253 rizwank 1.1 }
254
255 public void page_S1() {
256 try {
257 out.println("<form method=get action=P4 onsubmit='return validateForm4(this)'>");
258 out.println("<table><tr><td><br><input type=radio name ='param2' value='movie'");
259 if ( param2 != null) {
260 if ( param2.startsWith("movie") ) {
261 out.println(" checked ");
262 }
263 }
264 out.println(">Search Movies</td><td></td><tr>");
265 out.println("<td><input type=radio name ='param2' value='actor' ");
266 if ( param2 == null) {
267 out.println(" checked ");
268 } else if ( param2.startsWith("actor") ) {
269 out.println(" checked ");
270 }
271
272 out.println(">Search Actors </td><td><select name='param3'><option value='last'>Last Name<option value='first'>First Name</select></td></table><br>");
273 out.println("Partial Search Parameter (Case-Sensitive Permitted) : <input type=text name ='param1' size = 35");
274 rizwank 1.1 if ( param1 != null) {
275 out.println(" value='" + param1 + "' ");
276 }
277 out.println("><br>");
278 out.println("<input type=hidden name=page value=S1><input type=submit value='Submit Query'><hr>");
279
280 if (param1 != null) {
281 out.println("</center>");
282 if ( param2.startsWith("movie") ) {
283 Statement query_movie_data = con.createStatement();
284 query_text = "SELECT * FROM MOVIE WHERE TITLE LIKE '%" + param1 + "%' ORDER BY TITLE";
285 ResultSet movie_data = query_movie_data.executeQuery(query_text);
286 out.println("<table border='0' cellpadding='1' cellspacing='0' style='border-collapse: collapse' bordercolor='#111111' width='100%'><tr>");
287 out.println("<td><b>Movie's ID</b><td><b>Title</b><td><b>Year</b><td><b>Rating</b><td><b>Company</b>");
288 while (movie_data.next()) {
289 out.println("<tr>");
290 String id = movie_data.getString(1);
291 String title = movie_data.getString(2);
292 String year = movie_data.getString(3);
293 String rating = movie_data.getString(4);
294 String company= movie_data.getString(5);
295 rizwank 1.1 out.println("<td><a href='P4?param1="+id+"&page=B2'>"+id+"</a>");
296 out.println("<td>"+title+"<td>"+year+"<td>"+rating+"<td>"+company+"</tr>");
297 }
298 out.println("</table><br>");
299 }
300 if ( param2.startsWith("actor") ) {
301 Statement query_actor_data = con.createStatement();
302 if ( param3 != null ) {
303 if ( param3.startsWith("first") ) {
304 query_text = "SELECT * FROM ACTOR WHERE FIRST Like '%" + param1 + "%' ORDER BY FIRST";
305 } else {
306 query_text = "SELECT * FROM ACTOR WHERE LAST LIKE '%" + param1 + "%' ORDER BY LAST";
307 }
308 }
309 else {
310 query_text = "SELECT * FROM ACTOR WHERE LAST LIKE '%" + param1 + "%' ORDER BY LAST";
311 }
312 ResultSet actor_data = query_actor_data.executeQuery(query_text);
313 out.println("<table border='0' cellpadding='1' cellspacing='0' style='border-collapse: collapse' bordercolor='#111111' width='100%'><tr>");
314 out.println("<td><b>Actor's ID</b><td><b>Last Name</b><td><b>First Name</b><td><b>Sex</b><td><b>Date of Birth</b><td><b>Date of Death</b>");
315 while (actor_data.next()) {
316 rizwank 1.1 out.println("<tr>");
317 String id = actor_data.getString(1);
318 String last = actor_data.getString(2);
319 String first = actor_data.getString(3);
320 String sex = actor_data.getString(4);
321 String dob = actor_data.getString(5);
322 String dod = actor_data.getString(6);
323 out.println("<td><a href='P4?param1="+id+"&page=B1'>"+id+"</a>");
324 out.println("<td>"+last+"<td>"+first+"<td>"+sex+"<td>"+dob+"<td>"+dod+"</tr>");
325 }
326 out.println("</table><br>");
327 }
328 }
329
330
331
332 }
333 catch ( SQLException ex ) {
334 out.println( "<p><hr><b>SQLException caught<br>" );
335 out.println( "---<br>" );
336 while ( ex != null ) {
337 rizwank 1.1 out.println( "Message : " + ex.getMessage( ) + "<br>");
338 out.println( "SQLState : " + ex.getSQLState( ) + "<br>");
339 out.println( "ErrorCode : " + ex.getErrorCode( ) + "<br>");
340 out.println( "Query Passc: " + query_text + "<br>");
341 out.println( "---<br></b>" );
342 }
343 ex = ex.getNextException( );
344 } // close catch
345 }
346
347 public void page_I1() {
348 try {
349 out.println("<form method=get action=P4 onsubmit='return validateForm(this)' >");
350 out.println("Insert a new <input type=radio name ='param2' value='actor' checked>Actor <input type=radio name ='param2' value='director'>Director<br>");
351 out.println("Required fields in bold,<br>DOB/DOD must be in MM/DD/YYYY form.<br>Sex is ignored for Directors.<br>");
352 out.println("<table border=1><td><b>First Name:</b><td><input type=text name='param3' size=20><tr>");
353 out.println("<td><b>Last Name:</b><td><input type=text name='param4' size=20><tr>");
354 out.println("<td>Sex:<td><select name='param5'><option value='male'>Male<option value='female'>Female</select><tr>");
355 out.println("<td>Date of Birth:<td><input type=text name='param6' size=10><tr>");
356 out.println("<td>Date of Death:<td><input type=text name='param7' size=10><tr></table>");
357 out.println("<input type=hidden name=page value=I1><input type=submit value='Submit Query'><hr>");
358 rizwank 1.1
359 if (param2 != null & param3 != null & param4 != null) {
360 String query_text_a = "INSERT INTO ";
361 String query_text_b;
362 if (param2.startsWith("director") ) {
363 query_text_b = "DIRECTOR (ID,LAST,FIRST,DOB,DOD) VALUES (";
364 } else {
365 query_text_b = "ACTOR (ID,LAST,FIRST,SEX,DOB,DOD) VALUES (";
366 }
367 // fetch MAXPERSONID
368 String query_text_c;
369 query_text_c = "";
370 query_text = "SELECT * FROM MAXPERSONID";
371 Statement max_person_s = con.createStatement();
372 ResultSet max_person = max_person_s.executeQuery(query_text);
373 while (max_person.next()) {
374 query_text_c = max_person.getString(1);
375 }
376 query_text = "update maxpersonid set id=id+1";
377 Statement query_s = con.createStatement();
378 query_s.executeUpdate(query_text);
379 rizwank 1.1 query_s.close();
380 String query_text_d = ",'"+param4+"','"+param3+"',";
381 String query_text_e;
382 if (param2.startsWith("director") ) {
383 query_text_e = "";
384 } else {
385 query_text_e = "'"+param5 + "',";
386 }
387 String query_text_f;
388 String query_text_g;
389 if (param6.length() > 2) {
390 query_text_f = "'"+param6+"',";
391 } else {
392 query_text_f = "NULL,";
393 }
394 if (param7.length() > 2) {
395 query_text_g = "'"+param7+"')";
396 } else {
397 query_text_g = "NULL)";
398 }
399 query_text = query_text_a + query_text_b + query_text_c + query_text_d + query_text_e + query_text_f + query_text_g;
400 rizwank 1.1 String query_text_z = query_text_c + query_text_d + query_text_e + query_text_f + query_text_g;
401 Statement insert_query_s = con.createStatement();
402 insert_query_s.executeUpdate(query_text);
403 out.println("Executed Query : <br><b>" + query_text_a + query_text_b + "<br>" + query_text_z + "</b>,<br> updating the maxperson count to " + query_text_c + "+1<br>");
404 out.println("<br>Inserted Data was:<br><table><tr><td>Type<td>"+param2+"<tr><td>ID<td>"+query_text_c+"<tr><td>First Name<td>"+param3+"<tr><td>Last Name<td>"+param4+"<tr><td>Sex (if App)<td>"+param5+"<tr><td>Date of Birth<td>"+param6+"<tr><td>Date of Death<td>"+param7+"</table>");
405 }
406 }
407 catch ( SQLException ex ) {
408 out.println( "<p><hr><b>SQLException caught<br>" );
409 out.println( "---<br>" );
410 while ( ex != null ) {
411 out.println( "Message : " + ex.getMessage( ) + "<br>");
412 out.println( "SQLState : " + ex.getSQLState( ) + "<br>");
413 out.println( "ErrorCode : " + ex.getErrorCode( ) + "<br>");
414 out.println( "Query Passd: " + query_text + "<br>");
415 out.println( "---<br></b>" );
416 }
417 ex = ex.getNextException( );
418 } // close catch
419 }
420
421 rizwank 1.1 public void page_I2() {
422 try {
423 out.println("<form method=get action=P4 onsubmit='return validateForm2(this)' >");
424 out.println("Required fields in bold,<br>");
425 out.println("<table border=1><b><td>Review which movie?</b><td>");
426 movie_droplist(true,1,param1);
427 out.println("<tr><td><b>Your Name:</b><td><input type=text name='param2' size=20 ");
428 if (param2 != null) {
429 out.println("value='"+param2+"'");
430 }
431 out.println("><tr><td><b>Rating</b>:<td><select name='param3'><option value='5'>*****<option value='4'>****<option value='3'>***<option value='2'>**<option value='1'>*</select><tr>");
432 out.println("<td><b>Comment:</b><td><textarea name='param4' cols=80 rows=7>Enter your comment here!</textarea></table>");
433 out.println("<input type=hidden name=page value=I2><input type=submit value='Submit Query'><hr>");
434
435 if (param2 != null & param3 != null & param4 != null) {
436 String query_text_a = "INSERT INTO REVIEW (NAME,TIME,MID,RATING,COMMENT) VALUES ";
437 String query_text_b = "('" + param2 + "','";
438 Timestamp ts = new java.sql.Timestamp(System.currentTimeMillis());
439 String query_text_c = new String(ts.toString());
440 //outputBuffer.append(timestr.substring(timestr.indexOf(' ',2)));
441 //https://lists.xcf.berkeley.edu/lists/advanced-java/2000-March/007661.html
442 rizwank 1.1 String query_text_d = "',"+param1+","+param3+",'";
443 String query_text_e = param4 + "' )";
444 query_text = query_text_a + query_text_b + query_text_c + query_text_d + query_text_e;
445 String query_text_z = query_text_b + query_text_c + query_text_d + query_text_e;
446 Statement insert_query_s = con.createStatement();
447 insert_query_s.executeUpdate(query_text);
448 out.println("Executed Query : <br><b>" + query_text_a + "<br>" + query_text_z + "</b>.<br>");
449 out.println("<br>Inserted Data was:<br><table><tr><td>Type<td>"+"Comment"+"<tr><td>MID<td><a href='P4?page=B2¶m1="+param1+"'>"+param1+"</a><tr><td>Name<td>"+param2+"<tr><td>Rating<td>"+param3+"<tr><td>Comment<td>"+param4+"<tr><td>Timestamp<td>"+query_text_c+"</table>");
450 }
451 }
452 catch ( SQLException ex ) {
453 out.println( "<p><hr><b>SQLException caught<br>" );
454 out.println( "---<br>" );
455 while ( ex != null ) {
456 out.println( "Message : " + ex.getMessage( ) + "<br>");
457 out.println( "SQLState : " + ex.getSQLState( ) + "<br>");
458 out.println( "ErrorCode : " + ex.getErrorCode( ) + "<br>");
459 out.println( "Query Passe: " + query_text + "<br>");
460 out.println( "---<br></b>" );
461 }
462 ex = ex.getNextException( );
463 rizwank 1.1 } // close catch
464 }
465 public void page_I3() {
466 try {
467 out.println("<br>Required fields in bold,<br>");
468 out.println("<form method=get action=P4 onsubmit='return validateForm3(this)' ><table border=1><b><td>Add a movie</b><td>OR<td>Add actors to a movie<tr>");
469 out.println("<td>");
470 out.println("<table border=1><td><b>Title:</b><td><input type=text name='param1' size=25><tr>");
471 out.println("<td><b>Year:</b><td><input type=text name='param2' size=6><tr>");
472 out.println("<td>Rating:<td><select name='param3'><option value='G'>G<option value='PG'>PG<option value='PG13'>PG13<option value='R'>R<option value='NC17'>NC17<option value='X'>X<option value='NR'>NR</select><tr>");
473 out.println("<td>Production Company:<td><input type=text name='param4' size=25><tr><td>Genre:<td><input type=text name='param5' size=25></table>");
474 out.println("<td><td>");
475 out.println("<b>Add Actor:</b><br>");
476 actor_droplist(false,6,param6);
477 out.println("<br><b>to Movie:</b><br>");
478 movie_droplist(true,7,param7);
479 out.println("<br><b>as Role:</b><br><input type=text name='param8' size=20><br>");
480 out.println("</table><input type=hidden name=page value=I3><br><input type=submit value='Submit Query'>");
481
482 // INSERT DIRECTOR NOT IMPLEMENTED
483
484 rizwank 1.1 if (param1 != null & param3 != null & param2 != null) {
485 if (param1 .length() > 2 & param2 .length() >2) {
486 String query_text_a = "INSERT INTO MOVIE (ID,TITLE,YEAR,RATING,COMPANY) VALUES (";
487 // fetch MAXPERSONID
488 String query_text_b = "";
489 query_text = "SELECT * FROM MaxMovieID";
490 Statement max_movie_s = con.createStatement();
491 ResultSet max_movie = max_movie_s.executeQuery(query_text);
492 while (max_movie.next()) {
493 query_text_b = max_movie.getString(1);
494 }
495 query_text = "update MaxMovieID set id=id+1";
496 Statement query_s = con.createStatement();
497 query_s.executeUpdate(query_text);
498 query_s.close();
499 String query_text_c = ",'"+param1+"',"+param2+",";
500 String query_text_d = "'"+param3+"','"+param4+"')";
501 query_text = query_text_a + query_text_b + query_text_c + query_text_d;
502 String query_text_z = query_text_b + query_text_c + query_text_d;
503 Statement insert_query_s = con.createStatement();
504 insert_query_s.executeUpdate(query_text);
505 rizwank 1.1 out.println("<hr>Executed Query : <br><b>" + query_text_a + "<br>" + query_text_z + "</b>,<br> updating the maxmovie count to " + query_text_b + "+1<br>");
506 out.println("<br>Inserted Data was:<br><table><tr><td>ID<td>"+ query_text_b +"<tr><td>Title<td>"+param1+"<tr><td>Year<td>"+param2+"<tr><td>Rating<td>"+param3+"<tr><td>Company<td>"+param4+"</table>");
507 if (param5.length() > 2) {
508 String query_text = "INSERT INTO MovieGenre (MID,GENRE) VALUES ("+query_text_b+",'"+param5+"')";
509 Statement insert_query_s2 = con.createStatement();
510 insert_query_s2.executeUpdate(query_text);
511 out.println("<br>Executed Query : <br><b>" + query_text + "</b><br>");
512 out.println("<br>Inserted Data was:<br><table><tr><td>MID<td>"+query_text_b+"<tr><td>Genre<td>"+param5+"</table>");
513 }
514 }
515 }
516
517 if (param6 != null & param7 != null & param8 != null) {
518 if (param8 .length() >2) {
519 String query_text_a = "INSERT INTO MovieActor (MID,AID,ROLE) VALUES ";
520 String query_text_b = "(" + param7 + ",";
521 String query_text_c = param6+",'"+param8+"')";
522 query_text = query_text_a + query_text_b + query_text_c;
523 String query_text_z = query_text_b + query_text_c;
524 Statement insert_query_s = con.createStatement();
525 insert_query_s.executeUpdate(query_text);
526 rizwank 1.1 out.println("<br>Executed Query : <br><b>" + query_text_a + "<br>" + query_text_z + "</b>.<br>");
527 out.println("<br>Inserted Data was:<br><table><tr><TD>MID<td><a href='P4?page=B2¶m1="+param7+"'>"+param7+"</a><tr><TD>AID<td><a href='P4?page=B1¶m1="+param6+"'>"+param6+"</a><tr><td>Role<td>"+param8+"</table>");
528 }
529 }
530 }
531 catch ( SQLException ex ) {
532 out.println( "<p><hr><b>SQLException caught<br>" );
533 out.println( "---<br>" );
534 while ( ex != null ) {
535 out.println( "Message : " + ex.getMessage( ) + "<br>");
536 out.println( "SQLState : " + ex.getSQLState( ) + "<br>");
537 out.println( "ErrorCode : " + ex.getErrorCode( ) + "<br>");
538 out.println( "Query Pass: " + query_text + "<br>");
539 out.println( "---<br></b>" );
540 }
541 ex = ex.getNextException( );
542 } // close catch
543 }
544
545 public void actor_droplist(boolean paramtest,int whichselect,String param){
546 try {
547 rizwank 1.1 Statement Actorlist_s = con.createStatement();
548 //ResultSet Actorlist = Actorlist_s.executeQuery("SELECT * FROM Actor WHERE ID > 12100 AND ID < 12200 ORDER BY LAST ");
549 ResultSet Actorlist = Actorlist_s.executeQuery("SELECT * FROM Actor ORDER BY LAST ");
550 // CROPPED FIX TODO
551 // all the IDs
552
553 out.println("<SELECT NAME='param"+whichselect+"'><option selected>");
554
555 debugloc = "Actorlist";
556 // TODO : DIVIDE INTO ALPHA SORTS?
557 while (Actorlist.next()) {
558 String id = Actorlist.getString(1);
559 out.println("<option value='"+id+"'");
560 if (paramtest) {
561 if ( param != null ) {
562 if ( param.startsWith(id) ) {
563 out.println(" selected");
564 }
565 }
566 }
567 out.println(">");
568 rizwank 1.1 String last = Actorlist.getString(2);
569 String first = Actorlist.getString(3);
570 String sex = Actorlist.getString(4);
571 out.println(last+", "+first+" ("+sex+")");
572 } // close while
573 out.println("</SELECT>");
574 }
575 catch ( SQLException ex ) {
576 out.println( "<p><hr><b>SQLException caught<br>" );
577 out.println( "---<br>" );
578 while ( ex != null ) {
579 out.println( "Message : " + ex.getMessage( ) + "<br>");
580 out.println( "SQLState : " + ex.getSQLState( ) + "<br>");
581 out.println( "ErrorCode : " + ex.getErrorCode( ) + "<br>");
582 out.println( "Query Passf: " + query_text + "<br>");
583 out.println( "---<br></b>" );
584 }
585 ex = ex.getNextException( );
586 } // close catch
587
588 }
589 rizwank 1.1
590 public void movie_droplist(boolean paramtest,int whichselect,String param){
591 try {
592 Statement Movielist_s = con.createStatement();
593 //ResultSet Movielist = Movielist_s.executeQuery("SELECT ID,TITLE,YEAR FROM Movie WHERE ID > 1750 AND ID < 1800 ORDER BY TITLE ");
594 ResultSet Movielist = Movielist_s.executeQuery("SELECT ID,TITLE,YEAR FROM Movie ORDER BY TITLE ");
595 // CROPPED FIX TODO
596 // all the IDs
597
598 out.println("<SELECT NAME='param"+whichselect+"'><option selected>");
599 debugloc = "Movielist";
600 while (Movielist.next()) {
601 String id = Movielist.getString(1);
602 out.println("<option value='"+id+"'");
603 if (paramtest) {
604 if ( param != null ) {
605 if ( param.startsWith(id) ) {
606 out.println(" selected");
607 }
608 }
609 }
610 rizwank 1.1 out.println(">");
611 String title = Movielist.getString(2);
612 String year = Movielist.getString(3);
613 out.println(title+" ("+year+")");
614 } // close while
615 out.println("</SELECT>");
616 }
617 catch ( SQLException ex ) {
618 out.println( "<p><hr><b>SQLException caught<br>" );
619 out.println( "---<br>" );
620 while ( ex != null ) {
621 out.println( "Message : " + ex.getMessage( ) + "<br>");
622 out.println( "SQLState : " + ex.getSQLState( ) + "<br>");
623 out.println( "ErrorCode : " + ex.getErrorCode( ) + "<br>");
624 out.println( "Query Passg: " + query_text + "<br>");
625 out.println( "---<br></b>" );
626 }
627 ex = ex.getNextException( );
628 } // close catch
629
630 }
631 rizwank 1.1
632 public void javascript(){
633 out.println("<script Language='JavaScript'> ");
634 out.println("function validRequired(formField,fieldLabel)");
635 out.println("{");
636 out.println(" var result = true;");
637 out.println(" ");
638 out.println(" if (formField.value == '"+"')");
639 out.println(" {");
640 out.println(" alert('Please enter a value for the ' + fieldLabel +' field.');");
641 out.println(" formField.focus();");
642 out.println(" result = false;");
643 out.println(" }");
644 out.println(" ");
645 out.println(" return result;");
646 out.println("}");
647 out.println("function validLength(formField,fieldLabel,length)");
648 out.println("{");
649 out.println(" var result = true;");
650 out.println(" ");
651 out.println(" if (formField.value.length > 500)");
652 rizwank 1.1 out.println(" {");
653 out.println(" alert('Please make the ' + fieldLabel +' field less than 500 characters.');");
654 out.println(" formField.focus();");
655 out.println(" result = false;");
656 out.println(" }");
657 out.println(" ");
658 out.println(" return result;");
659 out.println("}");
660 out.println("function validNum(formField,fieldLabel,required)");
661 out.println("{");
662 out.println(" var result = true;");
663 out.println(" if (required && !validRequired(formField,fieldLabel))");
664 out.println(" result = false;");
665 out.println(" ");
666 out.println(" if (result)");
667 out.println(" {");
668 out.println(" var num = parseInt(formField.value,10);");
669 out.println(" if (isNaN(num))");
670 out.println(" {");
671 out.println(" alert('Please enter a number for the ' + fieldLabel +' field.');");
672 out.println(" formField.focus(); ");
673 rizwank 1.1 out.println(" result = false;");
674 out.println(" }");
675 out.println(" } ");
676 out.println(" ");
677 out.println(" return result;");
678 out.println("}");
679 out.println("function validDate(formField,fieldLabel,required)");
680 out.println("{");
681 out.println(" var result = true;");
682 // out.println(" if (required && !validRequired(formField,fieldLabel))");
683 // out.println(" result = false;");
684 out.println(" ");
685 out.println(" if (fieldLabel)");
686 out.println(" {");
687 out.println(" var elems = formField.value.split('/');");
688 out.println(" ");
689 out.println(" result = (elems.length == 3 || elems.length == 1 ); // should be three components");
690 out.println(" ");
691 out.println(" if (result)");
692 out.println(" {");
693 out.println(" var month = parseInt(elems[0],10);");
694 rizwank 1.1 out.println(" var day = parseInt(elems[1],10);");
695 out.println(" var year = parseInt(elems[2],10);");
696 out.println(" result = !isNaN(month) && (month > 0) && (month < 13) &&");
697 out.println(" !isNaN(day) && (day > 0) && (day < 32) &&");
698 out.println(" !isNaN(year) && (elems[2].length == 4);");
699 out.println(" result = result || isNaN(month); }");
700 out.println(" //riz added few liens hre ");
701 out.println(" if (!result)");
702 out.println(" {");
703 out.println(" alert('Please enter a date in the format MM/DD/YYYY for the ' + fieldLabel +' field.');");
704 out.println(" formField.focus(); ");
705 out.println(" }");
706 out.println(" } ");
707 out.println(" ");
708 out.println(" return result;");
709 out.println("}");
710 out.println("function validateForm(theForm)");
711 out.println("{");
712 out.println(" // Customize these calls for your form");
713 out.println(" // Start ------->");
714 out.println(" if (!validRequired(theForm.param3,'First Name'))");
715 rizwank 1.1 out.println(" return false;");
716 out.println(" if (!validRequired(theForm.param4,'Last Name'))");
717 out.println(" return false;");
718 out.println(" if (!validDate(theForm.param6,'Date of Birth',false))");
719 out.println(" return false;");
720 out.println("if (!validDate(theForm.param7,'Date of Death',false))");
721 out.println(" return false; ");
722 out.println(" // <--------- End");
723 out.println(" ");
724 out.println(" return true;");
725 out.println("}");
726 out.println("function validateForm2(theForm)");
727 out.println("{");
728 out.println(" // Customize these calls for your form");
729 out.println(" // Start ------->");
730 out.println(" if (!validRequired(theForm.param2,'Name'))");
731 out.println(" return false;");
732 out.println(" if (!validRequired(theForm.param1,'Movie'))");
733 out.println(" return false;");
734 out.println(" if (!validRequired(theForm.param4,'Comment'))");
735 out.println(" return false;");
736 rizwank 1.1 out.println(" if (!validLength(theForm.param4,'Comment'))");
737 out.println(" return false;");
738 out.println(" // <--------- End");
739 out.println(" ");
740 out.println(" return true;");
741 out.println("}");
742 out.println("function validateForm3(theForm)");
743 out.println("{");
744 out.println(" // Customize these calls for your form");
745 out.println(" // Start ------->");
746 out.println(" if (theForm.param1.value != '"+"') {");
747 out.println(" if (!validRequired(theForm.param1,'Title'))");
748 out.println(" return false;");
749 out.println(" if (!validNum(theForm.param2,'Year',true))");
750 out.println(" return false;");
751 out.println(" } else if (theForm.param8.value != '"+"') {");
752 out.println(" // Customize these calls for your form");
753 out.println(" // Start ------->");
754 out.println(" if (!validRequired(theForm.param7,'Actor'))");
755 out.println(" return false;");
756 out.println(" if (!validRequired(theForm.param6,'Movie'))");
757 rizwank 1.1 out.println(" return false;");
758 out.println(" if (!validRequired(theForm.param8,'Role'))");
759 out.println(" return false;");
760 out.println(" // <--------- End");
761 out.println(" } else { alert('Please enter something in either movie title field.');");
762 out.println(" return false; }");
763 out.println("}");
764 out.println("function validateForm4(theForm)");
765 out.println("{");
766 out.println(" // Customize these calls for your form");
767 out.println(" // Start ------->");
768 out.println(" if (!validRequired(theForm.param1,'Movie or Actor'))");
769 out.println(" return false;");
770 out.println(" // <--------- End");
771 out.println(" ");
772 out.println(" return true;");
773 out.println("}");
774 out.println("</script>");
775 }
776 }
777
778 rizwank 1.1
779
780 /*
781
782
783
784 out.println("Insert a Tuple!<br><form method=get action=P2B>Name:<input type=text name ='actor' size = 35><br>Movie:<input type=text name ='movie' size = 35><br>Role:<input type=text name ='character' size = 35><br>Year:<input type=text name ='sorted' size = 5><br>");
785 out.println("<input type=submit name=special value=INSERT_FINAL></form></body></html>");
786
787
788 out.println("<p>Roles:<br>");
789 Statement query_actor_role = con.createStatement();
790 query_text = "SELECT mid,role FROM MovieActor WHERE AID=" + param1;
791 ResultSet actor_role = query_actor_role.executeQuery(query_text);
792 while (actor_role.next()) {
793 String mid = actor_role.getString(1);
794 String role = actor_role.getString(2);
795 // TODO : Make this link to movie once we have movie working
796 out.println("Played <i>" + role + "</i> in <b>");
797 // NESTING BAAAAAAAAAAAAAAAD
798 Statement query_movie_name = con.createStatement();
799 rizwank 1.1 query_text = "SELECT title,year FROM Movie WHERE ID=" + mid;
800 ResultSet movie_name = query_movie_name.executeQuery(query_text);
801 while (movie_name.next()) {
802 String moviename = movie_name.getString(1);
803 String year = movie_name.getString(2);
804 out.println(moviename+"</b> ("+year+")<br>");
805 }
806 }
807 */
808
809
810
811 /*
812
813 String movie_query;
814 String actor_query;
815 String character_query;
816 String sorted_query;
817 String special_query;
818 String total_query = "";
819
820 rizwank 1.1 movie_query = req.getParameter("movie");
821 actor_query = req.getParameter("actor");
822 character_query = req.getParameter("character");
823 sorted_query = req.getParameter("sorted");
824 special_query = req.getParameter("special");
825
826 // since form is driven WITH data from DB we've had to initalize all the way back here
827
828
829 Statement movielist_s = con.createStatement();
830
831 // print out the result
832
833
834 out.println("<a href='P2B'>Reset Query</a><br><br>");
835 out.println("On what movie do you want information?<br>");
836 out.println("<SELECT NAME='movie'>");
837 if ( movie_query != null) {
838 if ( movie_query.length() > 4) {
839 out.println("<OPTION SELECTED>"+movie_query);
840 out.println("<OPTION>ALL");
841 rizwank 1.1 }
842 else {
843 out.println("<OPTION SELECTED>ALL");
844 }
845 }
846 else {
847 out.println("<OPTION SELECTED>ALL");
848 }
849 debugloc = "Movielist";
850
851 ResultSet movielist = movielist_s.executeQuery("SELECT DISTINCT MOVIE FROM Actors");
852
853 while (movielist.next()) {
854 out.println("<option>");
855 String moviename = movielist.getString(1);
856 out.println(moviename);
857 } // close while
858
859 out.println("</SELECT>");
860
861 movielist.close();
862 rizwank 1.1 movielist_s.close();
863
864 out.println("<br>For which actor? (Partial Search Case-Sensitive Permitted) : <input type=text name ='actor' size = 35 ");
865 if ( actor_query != null) {
866 out.println("value='" + actor_query + "' ");
867 }
868 out.println(">");
869 out.println("<br>Playing which character? (Partial Search Case-Sensitive Permitted) : <input type=text name ='character' size = 35 ");
870 if ( character_query != null) {
871 out.println("value='" + character_query + "' ");
872 }
873 out.println(">");
874 out.println("<br>Sorted by : <input type=radio name = 'sorted' value='M' ");
875 if (sorted_query != null) {
876 if (sorted_query.length() < 2) {
877 out.println("CHECKED ");
878 }
879 if (sorted_query.length() == 4) {
880 out.println("CHECKED ");
881 }
882
883 rizwank 1.1 out.println(" >Movies <input type=radio name = 'sorted' value='AA'");
884 if (sorted_query.length() == 2) {
885 out.println(" CHECKED ");
886 }
887 out.println(">Actors <input type=radio name = 'sorted' value='CCC'");
888 if (sorted_query.length() == 3) {
889 out.println(" CHECKED ");
890 }
891 }
892 else {
893 out.println("CHECKED ");
894 out.println(" >Movies <input type=radio name = 'sorted' value='AA'");
895 out.println(">Actors <input type=radio name = 'sorted' value='CCC'");
896 }
897
898 out.println(">Characters");
899 out.println("<br><input type=submit name=optional value=Submit><input type=submit name=special value=INSERT><br><hr>");
900 out.println("</form>");
901
902 debugloc = "Response";
903 if (special_query != null) {
904 rizwank 1.1 if ( special_query.length() == 4 ) {
905 out.println("Modify Not Implemented (Yet)!<br>");
906 out.println("What would be here is something similar to the Insert Function, I'd put another form like this:<br>");
907 out.println("<form method=get action=P2B>Name:<input type=text name ='actor2' size = 35><br>Movie:<input type=text name ='movie2' size = 35><br>Role:<input type=text name ='character2' size = 35><br>Year:<input type=text name ='sorted2' size = 5><br>");
908 out.println("I'd repass the old passed data as well as the 'actor2' data from this form into a second form like Insert<br>");
909 }
910 if ( special_query.length() == 5 ) {
911 out.println("Deleting Tuple!");
912 Statement query_s = con.createStatement();
913 out.println("DELETE Query : <b>" + "DELETE FROM Actors WHERE Name='"+actor_query+"' AND Movie='"+movie_query+"' AND Role='"+character_query+"' " + "</b>" );
914 total_query = "DELETE FROM Actors WHERE Name='"+actor_query+"' AND Movie='"+movie_query+"' AND Role='"+character_query+"'";
915 query_s.executeUpdate(total_query);
916 query_s.close();
917 out.println("<a href='P2B'>Reset Query</a><br><br>");
918 }
919 if ( special_query.length() == 6 ) {
920 out.println("Insert a Tuple!<br><form method=get action=P2B>Name:<input type=text name ='actor' size = 35><br>Movie:<input type=text name ='movie' size = 35><br>Role:<input type=text name ='character' size = 35><br>Year:<input type=text name ='sorted' size = 5><br>");
921 out.println("<input type=submit name=special value=INSERT_FINAL></form></body></html>");
922 // INSERT
923 }
924 if ( special_query.length() > 10 ) {
925 rizwank 1.1 out.println("Done! Insert another Tuple!<br><form method=get action=P2B>Name:<input type=text name ='actor' size = 35><br>Movie:<input type=text name ='movie' size = 35><br>Role:<input type=text name ='character' size = 35><br>Year:<input type=text name ='sorted' size = 5><br>");
926 out.println("<input type=submit name=special value=INSERT_FINAL></form>");
927 // INSERT confirm
928 Statement query_s = con.createStatement();
929 out.println("Insert Query : <b>" + "INSERT INTO Actors (Name, Role, Movie, Year) VALUES ('"+actor_query+"','"+movie_query+"','"+sorted_query+"','"+character_query+"')" + "</b>" );
930 total_query = "INSERT INTO Actors VALUES ('"+actor_query+"','"+movie_query+"',"+sorted_query+",'"+character_query+"')";
931 query_s.executeUpdate(total_query);
932 query_s.close();
933 }
934 } else if (sorted_query==null) {
935 out.println("</body></html>");
936 }
937 else {
938 Statement query_s = con.createStatement();
939 String the_query_a = "SELECT * ";
940 // the_query_b is sorted_query - well not really
941 String the_query_c = "FROM Actors ";
942 String the_query_d = " ";
943 String the_query_e = ""; String the_query_f = "";
944 String the_query_g = ""; String the_query_h = "";
945 String the_query_i = ""; String the_query_j = "";
946 rizwank 1.1 if (sorted_query.length() == 1) {
947 the_query_j = " ORDER BY Movie";
948 }
949 if (sorted_query.length() == 2) {
950 the_query_j = " ORDER BY Name";
951 }
952 if (sorted_query.length() == 3) {
953 the_query_j = " ORDER BY Role";
954 }
955
956 int past = 0;
957 if ( actor_query.length() > 0 ) {
958 the_query_d ="WHERE ";
959 the_query_e = "Name Like '%" + actor_query + "%'";
960 past = 1;
961 }
962 if ( character_query.length() > 0 ) {
963 if (past==1)
964 { the_query_f = " AND "; }
965 else
966 { the_query_f = " "; }
967 rizwank 1.1 the_query_d = "WHERE ";
968 the_query_g = "Role Like '%" + character_query + "%'";
969 past = 1;
970 }
971 if ( movie_query.length() > 4) {
972 if (past==1)
973 { the_query_h = " AND "; }
974 else
975 { the_query_h = " "; }
976 the_query_d = "WHERE ";
977 the_query_i = "Movie='" + movie_query + "'";
978 }
979 total_query = the_query_a + the_query_c + the_query_d + the_query_e + the_query_f + the_query_g + the_query_h + the_query_i + the_query_j;
980 out.println("<br>The processed query was : <b>" + total_query + "</b><br>");
981
982 // could have used String.appen but this works too :)
983 // doc here http://java.sun.com/j2se/1.4.2/docs/api/java/lang/String.html
984
985 ResultSet final_query = query_s.executeQuery(total_query);
986 out.println("<table border='0' cellpadding='1' cellspacing='0' style='border-collapse: collapse' bordercolor='#111111' width='100%'><tr>");
987
988 rizwank 1.1 int counter = 0;
989 out.println("<p>");
990 while (final_query.next()) {
991 out.println("<td align='left' width='80%'>");
992 String sql_name = final_query.getString(1);
993 String sql_movie = final_query.getString(2);
994 String sql_year = final_query.getString(3);
995 String sql_role = final_query.getString(4);
996
997 if (sorted_query.length() == 1) {
998 out.println("<b>" + sql_movie + "</b> (" + sql_year + ") starring ");
999 out.println("<a href='P2B?movie=ALL&character=&sorted=BB&actor=" + sql_name + "'>"+sql_name+"</a>");
1000 out.println(" as " + sql_role + ".<br>");
1001 }
1002 if (sorted_query.length() == 2) {
1003 out.println("<b>" + sql_name + "</b> as " + sql_role + " in ");
1004 out.println("<a href='P2B?character=&sorted=A&actor=&movie=" + sql_movie + "'>"+sql_movie+"</a>");
1005 out.println(" (" + sql_year + ").<br>");
1006 }
1007 if (sorted_query.length() == 3) {
1008 out.println("<b>" + sql_role + "</b> played by ");
1009 rizwank 1.1 out.println("<a href='P2B?movie=ALL&character=&sorted=BB&actor=" + sql_name + "'>"+sql_name+"</a>");
1010 out.println("in ");
1011 out.println("<a href='P2B?character=&sorted=A&actor=&movie=" + sql_movie + "'>"+sql_movie+"</a>");
1012 out.println(" (" + sql_year + ").<br>");
1013 }
1014 out.println("</td><td align='center' width='10%'>");
1015 out.println("<a href='P2B?movie=" + sql_movie + "&actor=" + sql_name + "&character=" + sql_role + "&special=delet'>");
1016 out.println("Delete</a>");
1017 out.println("</td><td align='center' width='10%'>");
1018 out.println("<a href='P2B?movie=" + sql_movie + "&actor=" + sql_name + "&character=" + sql_role + "&special=mdfy'>");
1019 out.println("Update</a>");
1020 out.println("</td></tr>");
1021 counter++;
1022 } // close while
1023 out.println("</table>");
1024 out.println("<br>" + counter + " rows returned.");
1025 final_query.close();
1026 query_s.close();
1027 } // end else
1028
1029
1030 rizwank 1.1 */
1031
1032
1033
1034
1035
1036
|
Return to
P4.java
CVS log
Up to [RizwankCVS] / geekymedia_web / code