· 9 years ago · Jul 21, 2017, 11:20 PM
1package JDBCProject1;
2
3import java.sql.*;
4import edu.uci.ics.pattis.introlib.*;
5import java.util.*;
6import java.awt.event.ActionEvent;
7import java.awt.event.ActionListener;
8import java.awt.event.KeyEvent;
9import java.io.*;
10import javax.swing.*;
11
12
13
14
15public class JDBCProject1 {
16 private static char menuPrompt () {
17 System.out.println("FabFlix Commands: ");
18 System.out.println(" p - Print out movies featuring a star");
19 System.out.println(" s - Insert a new star into database");
20 System.out.println(" c - Insert a customer into database");
21 System.out.println(" d - Delete a customer from database");
22 System.out.println(" t - Add a movie into database");
23 System.out.println(" m - Provide metadata of database");
24 System.out.println(" x - Provide a SQL command");
25 System.out.println(" o - Provide database + user access information");
26 System.out.println(" k - Create Fabflix Error Report");
27 System.out.println(" e - Edit user access rights/Create or Delete Users");
28 System.out.println(" u - Exit the menu");
29 System.out.println(" z - Exit the program");
30
31 return Prompt.forChar("\nEnter Command","pscdtkmoxuez");
32 }
33 static Connection conn = null;
34
35
36 @SuppressWarnings({ "restriction", "unused" })
37
38 public static void main(String[] args) throws Exception {
39 Statement stmt = null;
40 Statement stmt1 = null;
41 boolean loggedIn = false;
42 ResultSet rs = null;
43 ResultSet rsType = null;
44 String usernameDatabase;
45 String passwordDatabase;
46 String employeeEmail;
47 String employeePassword;
48
49 String url = "jdbc:mysql:///moviedb";
50
51
52 Class.forName("com.mysql.jdbc.Driver").newInstance();
53 if (loggedIn == false) {
54 while(loggedIn == false) {
55 System.out.println("Welcome to FabFlix! Please input user information below:");
56 usernameDatabase = "root";
57 passwordDatabase = "lakers";
58 employeeEmail = Prompt.forString("Enter email");
59 employeePassword = Prompt.forString("Enter password");
60 String queryLogin = "SELECT count(*) from employees where email = '" + employeeEmail + "' and password = '" + employeePassword +"'";
61 conn = DriverManager.getConnection(url, usernameDatabase, passwordDatabase);
62 stmt = conn.createStatement();
63 stmt1 = conn.createStatement();
64 ResultSet rsLogin = stmt.executeQuery(queryLogin);
65
66 while (rsLogin.next()){
67 if (rsLogin.getString(1).equals("1")) {
68 // conn = DriverManager.getConnection(url, usernameDatabase, passwordDatabase);
69 // stmt = conn.createStatement();
70 // stmt1 = conn.createStatement();
71 loggedIn = true;
72 usernameDatabase = null;
73 passwordDatabase = null;
74 break;
75 }
76 else
77 System.out.println("Wrong username/password combination. Please try again.");
78 }
79 }
80 }
81
82 for(;;) try {
83 if (loggedIn == true) {
84 char selection = menuPrompt();
85 if (selection == 'p') {
86 String firstName = Prompt.forString("Please enter first name");
87 String lastName = Prompt.forString("Please enter last name");
88 String identification = Prompt.forString("Please enter ID");
89 if (firstName.equals("") && !lastName.isEmpty()) {
90 rs = stmt.executeQuery("SELECT m.id, m.title, m.year, m.director, m.banner_url, m.trailer_url FROM movies as m, stars as s, stars_in_movies as t" +
91 " WHERE m.id = t.movie_id and s.id = t.star_id and s.last_name = " + "'" + lastName + "'");
92 ResultSetMetaData rsmd = rs.getMetaData();
93 for (int i = 1; i <= rsmd.getColumnCount(); i++) {
94 System.out.print(rsmd.getColumnName(i) + "\t");
95 }
96 System.out.println();
97
98 while (rs.next()) {
99 int id = rs.getInt("id");
100 String title = rs.getString("title");
101 int year = rs.getInt("year");
102 String director = rs.getString("director");
103 String banner = rs.getString("banner_url");
104 String trailer = rs.getString("trailer_url");
105 System.out.println(id + "\t" + title + "\t" + year + "\t" + director + "\t" + banner + "\t" + trailer);
106 }
107 }
108 else if (lastName.equals("") && !firstName.isEmpty()){ //Have: First name/Don't Have: Last name or ID
109 rs = stmt.executeQuery("SELECT m.id, m.title, m.year, m.director, m.banner_url, m.trailer_url FROM movies as m, stars as s, stars_in_movies as t" +
110 " WHERE m.id = t.movie_id and s.id = t.star_id and s.first_name = " + "'" + firstName + "'");
111 ResultSetMetaData rsmd = rs.getMetaData();
112 for (int i = 1; i <= rsmd.getColumnCount(); i++) {
113 System.out.print(rsmd.getColumnName(i) + "\t");
114 }
115 System.out.println();
116 while (rs.next()) {
117 int id = rs.getInt("id");
118 String title = rs.getString("title");
119 int year = rs.getInt("year");
120 String director = rs.getString("director");
121 String banner = rs.getString("banner_url");
122 String trailer = rs.getString("trailer_url");
123 System.out.println(id + "\t" + title + "\t" + year + "\t" + director + "\t" + banner + "\t" + trailer);
124 }
125 }
126 else if (firstName.equals("") && lastName.equals("")) {
127 int newID = Integer.parseInt(identification);
128 rs = stmt.executeQuery("SELECT m.id, m.title, m.year, m.director, m.banner_url, m.trailer_url FROM movies as m, stars as s, stars_in_movies as t " +
129 " WHERE m.id=t.movie_id and s.id=t.star_id and s.id = " + newID);
130 System.out.println("got here 1");
131 ResultSetMetaData rsmd = rs.getMetaData();
132 for (int i = 1; i <= rsmd.getColumnCount(); i++) {
133 System.out.print(rsmd.getColumnName(i) + "\t");
134 }
135 System.out.println();
136
137 while (rs.next()) {
138 int id = rs.getInt("id");
139 String title = rs.getString("title");
140 int year = rs.getInt("year");
141 String director = rs.getString("director");
142 String banner = rs.getString("banner_url");
143 String trailer = rs.getString("trailer_url");
144 System.out.println(id + "\t" + title + "\t" + year + "\t" + director + "\t" + banner + "\t" + trailer);
145 }
146 }
147 else {
148 rs = stmt.executeQuery("SELECT m.id, m.title, m.year, m.director, m.banner_url, m.trailer_url FROM movies as m, stars as s, stars_in_movies as t" +
149 " WHERE m.id = t.movie_id and s.id = t.star_id and s.first_name = " + "'" + firstName + "'" + " and s.last_name = " + "'" +lastName +"'");
150 ResultSetMetaData rsmd = rs.getMetaData();
151 for (int i = 1; i <= rsmd.getColumnCount(); i++) {
152 System.out.print(rsmd.getColumnName(i) + "\t");
153 }
154 System.out.println();
155
156 while (rs.next()) {
157 int id = rs.getInt("id");
158 String title = rs.getString("title");
159 int year = rs.getInt("year");
160 String director = rs.getString("director");
161 String banner = rs.getString("banner_url");
162 String trailer = rs.getString("trailer_url");
163 System.out.println(id + "\t" + title + "\t" + year + "\t" + director + "\t" + banner + "\t" + trailer);
164 }
165 }
166 }
167 else if (selection == 's') {
168 Boolean singleName = Prompt.forBoolean("Does he/she have a single name?");
169 if (singleName == true) {
170 int id = Prompt.forInt("Enter ID number");
171 String lastName = Prompt.forString("Enter name");
172 String firstName = "";
173 String dob = Prompt.forString("Enter DOB");
174 String photo_url = Prompt.forString("Enter photo_url");
175 int insert = stmt.executeUpdate("INSERT INTO stars (id, first_name, last_name, dob, photo_url) " +
176 "VALUES('"+ id +"', '"+ firstName +"','"+
177 lastName +"','"+ dob + "', '"+ photo_url +"');");
178 System.out.println("1 record added into the Stars table.");
179 }
180 else {
181 int id = Prompt.forInt("Enter ID number");
182 String firstName = Prompt.forString("Enter first name");
183 String lastName = Prompt.forString("Enter last name");
184 String dob = Prompt.forString("Enter DOB");
185 String photo_url = Prompt.forString("Enter photo_url");
186 int insert = stmt.executeUpdate("INSERT INTO stars (id, first_name, last_name, dob, photo_url) " +
187 "VALUES('"+ id +"', '"+ firstName +"','"+
188 lastName +"','"+ dob + "', '"+ photo_url +"');");
189 System.out.println("1 record added into the Stars table.");
190 }
191
192 }
193 else if (selection == 't') {
194 String title = Prompt.forString("Enter movie title");
195 String titleQuery = "SELECT title FROM movies WHERE title = '"+title+"'";
196 Statement statement1 = conn.createStatement();
197 ResultSet rs1 = statement1.executeQuery(titleQuery);
198 boolean changes = false;
199
200 if (rs1.next()) {
201 System.out.println("Movie title already exists. No other movie information is needed as no new movie title will be inserted.");
202 System.out.println();
203 }
204 else {
205 changes = true;
206 System.out.println("New movie title has been detected!!!");
207 System.out.println();
208
209 int year = Prompt.forInt("Enter movie year");
210 String director = Prompt.forString("Enter Director Name (first & last name)");
211 String bannerURL = Prompt.forString("Enter Banner URL");
212 String trailerURL = Prompt.forString("Enter Trailer URL");
213 String procedure = "call add_movie('"+title+"',"+year+", '"+director+"', '"+bannerURL+"', '"+trailerURL+"')";
214 Statement statement11 = conn.createStatement();
215 boolean addMovieUpdate = statement11.execute(procedure);
216 System.out.println("Movie title had been added to database.");
217 System.out.println();
218
219 }
220
221
222
223 String firstName = Prompt.forString("Enter a star's first name");
224 String lastName = Prompt.forString("Enter the same star's last name");
225 String starQuery = "SELECT id FROM stars WHERE first_name = '"+firstName+"' and last_name = '"+lastName+"'";
226 Statement statement2 = conn.createStatement();
227 ResultSet rs2 = statement2.executeQuery(starQuery);
228
229 if (rs2.next()) {
230 System.out.println("Star already exists. No new star will be inserted.");
231 System.out.println();
232
233 String starID = rs2.getString("id");
234 String titleID = "SELECT id FROM movies WHERE title = '"+title+"'";
235 Statement statement21 = conn.createStatement();
236 ResultSet rs21 = statement21.executeQuery(titleID);
237 rs21.next();
238 String movieID = rs21.getString("id");
239 String checkQuery = "SELECT * from stars_in_movies where star_id ="+starID+" and movie_id ="+movieID;
240 ResultSet rs22 = statement21.executeQuery(checkQuery);
241 if (rs22.next()) {
242 System.out.println("This star is already associated with the movie title you have entered above. No change is necessary. You will be re-directed to the genre input section.");
243 System.out.println();
244
245 }
246 else {
247 String insertSIM = "INSERT INTO stars_in_movies (star_id, movie_id) VALUES("+starID+","+movieID+")";
248 int update = statement21.executeUpdate(insertSIM);
249 changes = true;
250 System.out.println("Stars_in_movies has be updated successfully!");
251 System.out.println();
252
253 }
254
255
256 }
257 else {
258 changes = true;
259 System.out.println("New star detected!!!");
260 System.out.println();
261
262 String dob = Prompt.forString("Enter DOB (YYYY-MM-DD)");
263 String photoURL = Prompt.forString("Enter photo_url");
264 int insert = stmt.executeUpdate("INSERT INTO stars (first_name, last_name, dob, photo_url) " +
265 "VALUES('"+ firstName +"','"+
266 lastName +"','"+ dob + "', '"+ photoURL +"')");
267 System.out.println("New star added successfully!");
268 System.out.println();
269
270 String starIDQuery = "SELECT id FROM stars WHERE first_name = '"+firstName+"' and last_name = '"+lastName+"'";
271 Statement statement22 = conn.createStatement();
272 ResultSet rs22 = statement22.executeQuery(starIDQuery);
273 rs22.next();
274 String starID = rs22.getString("id");
275 String titleID = "SELECT id FROM movies WHERE title = '"+title+"'";
276 Statement statement21 = conn.createStatement();
277 ResultSet rs21 = statement21.executeQuery(titleID);
278 rs21.next();
279 String movieID = rs21.getString("id");
280 String insertSIM = "INSERT INTO stars_in_movies (star_id, movie_id) VALUES("+starID+","+movieID+")";
281 int update = statement22.executeUpdate(insertSIM);
282 System.out.println("Stars_in_movies has be updated succesfully!");
283 System.out.println();
284
285
286
287 }
288
289 String genreName = Prompt.forString("Enter a genre of this movie");
290 String genreQuery = "SELECT id FROM genres WHERE name = '"+genreName+"'";
291 Statement statement3 = conn.createStatement();
292 ResultSet rs3 = statement3.executeQuery(genreQuery);
293
294 if (rs3.next()) {
295 System.out.println("Genre already exists. No new genre will be inserted.");
296 System.out.println();
297
298 String genreID = rs3.getString("id");
299 String titleID = "SELECT id FROM movies WHERE title = '"+title+"'";
300 Statement statement21 = conn.createStatement();
301 ResultSet rs21 = statement21.executeQuery(titleID);
302 rs21.next();
303 String movieID = rs21.getString("id");
304 String checkQuery = "SELECT * from genres_in_movies where genre_id ="+genreID+" and movie_id ="+movieID;
305 ResultSet rs22 = statement21.executeQuery(checkQuery);
306 if (rs22.next()) {
307 System.out.println("This genre is already associated with the movie title you have entered above. No change(s) are necessary.");
308 System.out.println();
309
310 }
311 else {
312 String insertGIM = "INSERT INTO genres_in_movies (genre_id, movie_id) VALUES("+genreID+","+movieID+")";
313 int update = statement21.executeUpdate(insertGIM);
314 changes = true;
315 System.out.println("genres_in_movies has be updated successfully!");
316 System.out.println();
317
318 }
319
320 }
321 else {
322 System.out.println("New genre is detected!!");
323 System.out.println();
324
325 changes = true;
326 int insert = stmt.executeUpdate("INSERT INTO genres (name) " +
327 "VALUES('"+ genreName +"')");
328 System.out.println("New genre added successfully!");
329 System.out.println();
330
331 String genreIDQuery = "SELECT id FROM genres WHERE name = '"+genreName+"'";
332 Statement statement22 = conn.createStatement();
333 ResultSet rs22 = statement22.executeQuery(genreIDQuery);
334 rs22.next();
335 String genreID = rs22.getString("id");
336 String titleID = "SELECT id FROM movies WHERE title = '"+title+"'";
337 Statement statement21 = conn.createStatement();
338 ResultSet rs21 = statement21.executeQuery(titleID);
339 rs21.next();
340 String movieID = rs21.getString("id");
341 String insertGIM = "INSERT INTO genres_in_movies (genre_id, movie_id) VALUES("+genreID+","+movieID+")";
342 int update = statement22.executeUpdate(insertGIM);
343 System.out.println("genres_in_movies has be updated succesfully!");
344 System.out.println();
345 }
346 if (changes) {
347 System.out.println("Congratulations. You have successfully added a movie and/or modified the data related to a movie! " +
348 "You will now be re-directed to the main menu.");
349 System.out.println();
350 }
351
352 else {
353 System.out.println("Sorry. All of the data you have inputted is already reflected in our database so no changes were made. You will now be re-directed to the main menu.");
354 System.out.println();
355 }
356 }
357 else if (selection == 'o') {
358 Statement dbStatement = conn.createStatement();
359 ResultSet resultDB = dbStatement.executeQuery("show databases");
360 String dbName;
361 while (resultDB.next()) {
362 dbName = resultDB.getString(1);
363 // Only get information inside the "moviedb" database
364 if (dbName.compareTo("moviedb")==0) {
365 Statement moviedbStatement = conn.createStatement();
366 moviedbStatement.execute("use moviedb");
367 // Create and execute an SQL statement to get all the table names in moviedb
368 Statement tableStatement = conn.createStatement();
369 ResultSet resultTB = tableStatement.executeQuery("show tables");
370 // XXXXXXXXXXX
371 String tb1Name;
372 ResultSet columnData;
373 Statement columnStatement;
374 while (resultTB.next()) {
375 tb1Name = resultTB.getString(1);
376 System.out.println("\n**Table Name:** " + tb1Name + "\n");
377 System.out.println("Metadata about columns in this table:\n");
378 System.out.println("==== Field Name ==== Field Type ===== Null Allowed ?");
379 System.out.println("----------------------------------------------------");
380 columnStatement = conn.createStatement();
381 // Create and execute an SQL statement to get all the column names for this table
382 columnData = columnStatement.executeQuery("describe "+tb1Name);
383 while (columnData.next()) {
384 System.out.print("==== "+columnData.getString(1));
385 System.out.print("==== "+columnData.getString(2));
386 System.out.println("==== "+columnData.getString(3));
387 }
388 }
389 }
390 }
391 // Displaying user privileges
392 System.out.println("\n**Users and their respective privileges:** " + "\n");
393 ResultSet userprivRS = stmt.executeQuery("select host, user from mysql.user");
394 ResultSetMetaData rsmdUserPriv = userprivRS.getMetaData();
395 for (int i = 1; i <= rsmdUserPriv.getColumnCount(); i++) {
396 System.out.print("==== "+rsmdUserPriv.getColumnName(i));
397 }
398 System.out.println();
399 System.out.println("------------------------------------------------------------------------------");
400 while (userprivRS.next()) {
401 String hostString = userprivRS.getString(1);
402 String userString = userprivRS.getString(2);
403 Statement stmt2 = conn.createStatement();
404 ResultSet rs2 = stmt2.executeQuery("show grants for '"+userString+"'@'"+hostString+"'");
405 ResultSetMetaData rsmd2 = rs2.getMetaData();
406 for (int i = 1; i <= rsmd2.getColumnCount(); i++) {
407 System.out.print("====== "+rsmd2.getColumnName(i)+" ======");
408 while (rs2.next()) {
409 String grants = rs2.getString(1);
410 System.out.println("\n"+ grants);
411 }
412 }
413 System.out.println();
414 }
415 }
416 else if (selection == 'c') {
417 Boolean singleName = Prompt.forBoolean("Does he/she have a single name?");
418 if (singleName == true) {
419 int id = Prompt.forInt("Enter customer ID");
420 String firstName = "";
421 String lastName = Prompt.forString("Enter name");
422 String cc_id = Prompt.forString("Enter credit card number");
423 String address = Prompt.forString("Enter address");
424 String email = Prompt.forString("Enter email");
425 String password1 = Prompt.forString("Set a password");
426
427 // Checking with credit card data table
428 rs = stmt.executeQuery("SELECT c.id FROM creditcards as c");
429 while (rs.next()) {
430 String credID = rs.getString("id");
431 if (credID.equals(cc_id)) {
432 int insert = stmt.executeUpdate("INSERT INTO customers (id, first_name, last_name, cc_id, address, email, password) " +
433 "VALUES('"+ id +"', '"+ firstName +"','"+
434 lastName +"','"+ cc_id + "','"+ address +"','"+ email +"','"+ password1+"');");
435 System.out.println("1 row added to customers table.");
436 break;
437 }
438
439 }
440 }
441 else
442 {
443 int id = Prompt.forInt("Enter customer ID");
444 String firstName = Prompt.forString("Enter first name");
445 String lastName = Prompt.forString("Enter last name");
446 String cc_id = Prompt.forString("Enter credit card number");
447 String address = Prompt.forString("Enter address");
448 String email = Prompt.forString("Enter email");
449 String password2 = Prompt.forString("Set a password");
450 // Checking with credit card data table
451 rs = stmt.executeQuery("SELECT c.id FROM creditcards as c");
452 while (rs.next()) {
453 String credID = rs.getString("id");
454 if (credID.equals((String)cc_id)) {
455 int insert = stmt.executeUpdate("INSERT INTO customers (id, first_name, last_name, cc_id, address, email, password) " +
456 "VALUES('"+ id +"', '"+ firstName +"','"+
457 lastName +"','"+ cc_id + "','"+ address +"','"+ email +"','"+ password2+"');");
458 System.out.println("1 row added to customers table.");
459 System.out.println();
460 break;
461 }
462
463 }
464 }
465 }
466 else if (selection == 'd') {
467 int id = Prompt.forInt("Enter ID number for customer removal");
468 int delete= stmt.executeUpdate("delete from customers WHERE id = " + id);
469 System.out.println("1 row affected");
470 }
471 else if (selection == 'e') {
472 System.out.println("Before you begin, please ensure that you have reviewed the database and user access information by pressing 'o'.");
473 System.out.println("Please do so if you have not already.");
474 System.out.println();
475 String edit = Prompt.forString("Will you be granting/revoking existing user(s) more privileges? (Y/N)");
476 if (edit.equals("Y")) {
477 System.out.println("Grant existing user(s) more access rights");
478 final JFrame grantFrame = new JFrame("Grant/Revoke User Privleges");
479 grantFrame.setVisible(true);
480 grantFrame.setSize(300,100);
481 final JPanel panel = new JPanel();
482 grantFrame.add(panel);
483 final JTextField userName = new JTextField("Enter user name");
484 panel.add(userName);
485 final JTextField hostName = new JTextField("Enter host name");
486 panel.add(hostName);
487 JButton submit = new JButton("Submit");
488 panel.add(submit);
489 submit.addActionListener(new ActionListener() {
490 public void actionPerformed(ActionEvent e)
491 {
492 try {
493 //checks if user exists
494 String query = "select host, user from mysql.user where host = '"+hostName.getText()+"' and user = '"+userName.getText()+"'";
495 Statement statement = conn.createStatement();
496 ResultSet rs1 = statement.executeQuery(query);
497 if (! rs1.next()) {
498 final JPanel noUserPanel = new JPanel();
499 panel.setVisible(false);
500 grantFrame.add(noUserPanel);
501 grantFrame.setSize(500, 200);
502 noUserPanel.setVisible(true);
503 noUserPanel.add(new JLabel("This information you have inputted does not match any users in the database."));
504 JButton back = new JButton("Go Back to Previous Screen");
505 back.addActionListener(new ActionListener() {
506 public void actionPerformed (ActionEvent e) {
507 noUserPanel.setVisible(false);
508 panel.setVisible(true);
509 grantFrame.setSize(300,100);
510 }});
511 noUserPanel.add(back);
512
513 }
514
515 else {
516
517 final JPanel actionPanel = new JPanel();
518 panel.setVisible(false);
519 actionPanel.setVisible(true);
520 grantFrame.add(actionPanel);
521 grantFrame.setSize(800, 350);
522 JLabel info2 = new JLabel("Enter the database in this format: database_name.table or database_name.*");
523 JLabel info1 = new JLabel("For both forms you can grant/revoke certain privileges in the following format: SELECT, INSERT, DELETE, CREATE, DROP, etc.");
524 JLabel grant = new JLabel("This portion is for GRANTING privileges only:");
525 final JTextField grantPriv = new JTextField("Privileges to GRANT");
526
527 JLabel revoke = new JLabel("This portion is for REVOKING privileges only:");
528 JLabel selectDatabase = new JLabel ("Grant/Revoke privileges on the following database:");
529 final JTextField revokePriv = new JTextField("Privileges to REVOKE");
530 final JTextField selectDB = new JTextField("Enter Database");
531 JButton submit2 = new JButton("Submit");
532 actionPanel.add(info1);
533 actionPanel.add(info2);
534 actionPanel.add(grant);
535 actionPanel.add(grantPriv);
536 actionPanel.add(revoke);
537 actionPanel.add(revokePriv);
538 actionPanel.add(selectDatabase);
539 actionPanel.add(selectDB);
540 actionPanel.add(submit2);
541 submit2.addActionListener(new ActionListener() {
542 public void actionPerformed (ActionEvent e) {
543 try {
544 String username = userName.getText();
545 String hostname = hostName.getText();
546 String grantpriv = grantPriv.getText();
547 String revokepriv = revokePriv.getText();
548 if ((grantpriv.equals("") && revokepriv.equals("")) ||
549 (grantpriv.equals("Privileges to GRANT") && revokepriv.equals("Privileges to REVOKE"))) {
550 actionPanel.setVisible(false);
551 final JPanel grantRevokePanel = new JPanel();
552 grantRevokePanel.setVisible(true);
553 grantFrame.add(grantRevokePanel);
554 grantRevokePanel.setSize(400,400);
555 JLabel errorLabel = new JLabel("You failed to insert any information at all. Please use the back button to enter information");
556
557 grantRevokePanel.add(errorLabel);
558 JButton back = new JButton("Go back to previous screen");
559
560 grantRevokePanel.add(back);
561 back.addActionListener(new ActionListener() {
562 public void actionPerformed (ActionEvent e) {
563 actionPanel.setVisible(true);
564 actionPanel.setSize(800,350);
565
566 grantRevokePanel.setVisible(false);
567 }});
568 }
569
570 else if (grantpriv.equals("")|| grantpriv.equals("Privledges to GRANT")) {
571 Statement stmt = conn.createStatement();
572 boolean rs = stmt.execute("revoke "+revokepriv+" on mysql from '"+username+"'@'"+hostname+"'");
573 System.out.println("got to grantpriv if");
574 actionPanel.setVisible(false);
575 final JPanel confirmPanel = new JPanel();
576 grantFrame.add(confirmPanel);
577 grantFrame.setSize(500, 150);
578 final JLabel confirmLabel = new JLabel("You have succesfully revoked/granted access. Please refresh the MySQL server");
579 confirmPanel.add(confirmLabel);
580 confirmPanel.add(new JLabel("You may now close this window."));
581
582 }
583 else if (revokepriv.equals("") || revokepriv.equals("Privledges to REVOKE")) {
584 Statement stmt = conn.createStatement();
585 boolean rs = stmt.execute("grant "+grantpriv+" on mysql to '"+username+"'@'"+hostname+"'");
586 System.out.println("got to revoke priv if");
587 actionPanel.setVisible(false);
588 final JPanel confirmPanel = new JPanel();
589 grantFrame.add(confirmPanel);
590 grantFrame.setSize(500, 150);
591 final JLabel confirmLabel = new JLabel("You have succesfully revoked/granted access. Please refresh the MySQL server");
592 confirmPanel.add(confirmLabel);
593 confirmPanel.add(new JLabel("You may now close this window."));
594
595
596 }
597
598
599 else {
600 Statement stmt = conn.createStatement();
601 boolean rs = stmt.execute("grant "+grantpriv+" on moviedb.* to '"+username+"'@'"+hostname+"'");
602 Statement stmt2 = conn.createStatement();
603 boolean rs2 = stmt2.execute("revoke "+revokepriv+" on moviedb.* from '"+username+"'@'"+hostname+"'");
604 actionPanel.setVisible(false);
605 final JPanel confirmPanel = new JPanel();
606 grantFrame.add(confirmPanel);
607 grantFrame.setSize(500, 150);
608 final JLabel confirmLabel = new JLabel("You have succesfully revoked/granted access. Please refresh the MySQL server");
609 confirmPanel.add(confirmLabel);
610 confirmPanel.add(new JLabel("You may now close this window."));
611 }
612
613
614 }
615 catch (Exception e2) {
616 e2.printStackTrace();
617
618 }
619 }
620 });
621
622
623 }
624 // Student s = new Student(studentFirstName.getText(), studentLastName.getText(), menuID.getText(), pin.getText());
625 // menuPlus.getStudentCollection().addStudent(s);
626 // changes = true;
627
628
629
630 }
631 catch (Exception e2) {
632 e2.printStackTrace();
633
634 }
635 }
636
637 });
638 }
639
640 String addUsers = Prompt.forString("Would you like to add a user to the MySQL database? (Y/N)");
641 if (addUsers.equals("Y")){
642 final JFrame addFrame = new JFrame("Create MySQL user account");
643 addFrame.setVisible(true);
644 addFrame.setSize(300,300);
645 final JPanel addpanel = new JPanel();
646 addFrame.add(addpanel);
647 final JTextField adduserName = new JTextField("Enter user name");
648 addpanel.add(adduserName);
649 final JTextField addhostName = new JTextField("Enter host name");
650 addpanel.add(addhostName);
651 final JTextField addpassword = new JTextField("Enter password");
652 addpanel.add(addpassword);
653 JButton addsubmit = new JButton("Submit");
654 addpanel.add(addsubmit);
655 addsubmit.addActionListener(new ActionListener() {
656 public void actionPerformed(ActionEvent e) {
657 try {
658 Boolean duplicate = false;
659 String addUserNameString = adduserName.getText();
660 String addHostNameString = addhostName.getText();
661 String addPasswordString = addpassword.getText();
662
663 // Check to see if there are any users with the same account.
664 String queryCheck = "select count(*) from mysql.user where user = '" +addUserNameString+ "' and host = '" +addHostNameString+ "'";
665 Statement stmt00 = conn.createStatement();
666 ResultSet rsCheck = stmt00.executeQuery(queryCheck);
667 while (rsCheck.next()) {
668 if (rsCheck.getString(1).equals("1")) {
669 addpanel.setVisible(false);
670 final JPanel denyPanel = new JPanel();
671 addFrame.add(denyPanel);
672 final JLabel confirmLabel = new JLabel("Sorry, the user you have tried to add is already in the database.");
673 denyPanel.add(confirmLabel);
674 duplicate = true;
675 JButton back = new JButton("Go back to previous screen");
676 denyPanel.add(back);
677 back.addActionListener(new ActionListener() {
678 public void actionPerformed (ActionEvent e) {
679 addpanel.setVisible(true);
680
681
682 denyPanel.setVisible(false);
683 }});
684 }
685
686 }
687
688
689 // Time to make a SQL query/command
690 if (duplicate == false) {
691 String sqlCommand = "create user '" +addUserNameString+ "' @'"+addHostNameString+"' identified by '" + addPasswordString+"'";
692 Statement addStmt = conn.createStatement();
693 Boolean rsAdd = addStmt.execute(sqlCommand);
694 addpanel.setVisible(false);
695 final JPanel confirmPanel = new JPanel();
696 addFrame.add(confirmPanel);
697 final JLabel confirmLabel = new JLabel("Your entry has been confirmed. Please refresh the MySQL server");
698 confirmPanel.add(confirmLabel);
699 }
700
701 }
702 catch (Exception e3){
703 e3.printStackTrace();
704 }
705 }
706 }); }
707 }
708 else if (selection == 'u') {
709 loggedIn = false;
710
711
712 if (rs != null) {
713 try {
714 rs.close();
715 } catch (SQLException sqlEx) {
716 } // ignore
717
718 rs = null;
719 }
720
721 if (rsType != null) {
722 try {
723 rsType.close();
724 } catch (SQLException sqlEx) {
725 } // ignore
726
727 rsType = null;
728 }
729
730
731 if (stmt != null) {
732 try {
733 stmt.close();
734 } catch (SQLException sqlEx) {
735 } // ignore
736
737 stmt = null;
738 }
739
740 if (conn != null) {
741 try {
742 conn.close();
743 } catch (SQLException sqlEx) {
744 } // ignore
745
746 conn = null;
747 }
748
749 Class.forName("com.mysql.jdbc.Driver").newInstance();
750
751 while(loggedIn == false) {
752 System.out.println("Welcome to FabFlix! Please input user information below:");
753 //String username3 = Prompt.forString("Enter username");
754 //String password3 = Prompt.forString("Enter password");
755 String usernameDatabase1 = "root";
756 String passwordDatabase1 = "lakers";
757 String employeeEmail1 = Prompt.forString("Enter email");
758 String employeePassword1 = Prompt.forString("Enter password");
759 String queryLogin1 = "SELECT count(*) from employees where email = '" + employeeEmail1 + "' and password = '" + employeePassword1 +"'";
760 conn = DriverManager.getConnection(url, usernameDatabase1, passwordDatabase1);
761 stmt = conn.createStatement();
762 stmt1 = conn.createStatement();
763 ResultSet rsLogin1 = stmt.executeQuery(queryLogin1);
764
765 while(rsLogin1.next()) {
766 if (rsLogin1.getString(1).equals("1")) {
767 conn = DriverManager.getConnection(url, usernameDatabase1, passwordDatabase1);
768 stmt = conn.createStatement();
769 stmt1 = conn.createStatement();
770 loggedIn = true;
771 employeeEmail1 = null;
772 employeePassword1 = null;
773 break;
774 }
775
776 else
777 System.out.println("Wrong username/password combination. Please try again.");
778
779 }
780 }
781
782
783 }
784
785 else if (selection == 'x') {
786 boolean output = Prompt.forBoolean("Will this command return an output? (true or false)");
787 if (output == true) {
788 String select = Prompt.forString("SELECT");
789 String from = Prompt.forString("FROM");
790 String where = Prompt.forString("WHERE");
791 if (!where.isEmpty()){
792 rs = stmt.executeQuery("select " + select + " from " + from + " where " + where);
793 }
794 else {
795 rs = stmt.executeQuery("select " + select + " from " + from);
796 }
797
798 ResultSetMetaData rsmd = rs.getMetaData();
799 for (int i = 1; i <= rsmd.getColumnCount(); i++) {
800 System.out.print(rsmd.getColumnName(i) + "\t");
801 }
802 System.out.println();
803 System.out.println();
804 String t = "";
805 for (int i = 1; i<= rsmd.getColumnCount(); i++) {
806 String s = rsmd.getColumnName(i) + ", ";
807 t = t + s;
808 }
809 String s2 = t;
810 StringTokenizer st = new StringTokenizer(s2, ", "); // To grab the "new" attributes
811
812 while (rs.next()) {
813 while (st.hasMoreTokens()) {
814 Object o = rs.getObject(st.nextToken());
815 System.out.print(o + "\t");
816 }
817 st = new StringTokenizer(s2, ", ");
818 System.out.println();
819 }
820 }
821 else {
822 String option = Prompt.forString("Enter update, insert, or delete");
823 if (option.equals("update")) {
824 String update = Prompt.forString("UPDATE");
825 String set = Prompt.forString("SET");
826 String where = Prompt.forString("WHERE");
827 int updateExecute = stmt.executeUpdate("update " + update + " set " + set + " where " + where);
828 System.out.println("The information has been updated in the " + update+ " table");
829 }
830 else if (option.equals("insert")) {
831 String insertInto = Prompt.forString("INSERT INTO:");
832 rsType = stmt1.executeQuery("desc "+ insertInto);
833 String attributes ="(";
834 while(rsType.next()) {
835 String field = rsType.getString("field");
836 if (rsType.isLast())
837 attributes = attributes + field;
838 else
839 attributes = attributes + field + ", ";
840
841 }
842 String values = Prompt.forString("VALUES:");
843 int insertExecute = stmt.executeUpdate("insert into "+ insertInto+ attributes+")"+ " values" + values);
844 System.out.println("The information has been inserted into the " + insertInto+ " table");
845 }
846
847 else if (option.equals("delete")) {
848 String deleteFrom = Prompt.forString("DELETE FROM");
849 String where = Prompt.forString("WHERE");
850 int deleteExecute = stmt.executeUpdate("delete from " + deleteFrom + " where " + where);
851 System.out.println("The information inputted has deleted the corresponding records");
852 }
853 else
854 System.out.println("Error: You have entered a nonvalid option");
855 }
856 }
857
858
859 else if (selection == 'm') {
860 // Print Movie Metadata
861 System.out.println("Movies :");
862 rs = stmt.executeQuery("SELECT * FROM movies");
863 rsType = stmt1.executeQuery("desc movies"); // Data table about movie table
864 ResultSetMetaData rsmd = rs.getMetaData();
865 for (int i = 1; i <= rsmd.getColumnCount(); i++) {
866 System.out.print(rsmd.getColumnName(i));
867 if (rsType.next()) {
868 String type = rsType.getString("type");
869 System.out.println( " "+ "("+ type +")" + "\t");
870 }
871 }
872 System.out.println();
873
874 // Print Stars Metadata
875 System.out.println("Stars :");
876 rs = stmt.executeQuery("SELECT * FROM stars");
877 rsType = stmt1.executeQuery("desc stars");
878 ResultSetMetaData rsmd1 = rs.getMetaData();
879 for (int i = 1; i <= rsmd1.getColumnCount(); i++) {
880 System.out.print(rsmd1.getColumnName(i));
881 if (rsType.next()) {
882 String type = rsType.getString("type");
883 System.out.println( " "+ "("+ type +")" + "\t");
884 }
885 }
886 System.out.println();
887
888 // Print stars_in_movies metadata
889 System.out.println("stars_in_movies");
890 rs = stmt.executeQuery("SELECT * FROM stars_in_movies");
891 rsType = stmt1.executeQuery("desc stars_in_movies");
892 ResultSetMetaData rsmd2 = rs.getMetaData();
893 for (int i = 1; i <= rsmd2.getColumnCount(); i++) {
894 System.out.print(rsmd2.getColumnName(i));
895 if (rsType.next()) {
896 String type = rsType.getString("type");
897 System.out.println( " "+ "("+ type +")" + "\t");
898 }
899 }
900 System.out.println();
901
902 // Print genres metadata
903 System.out.println("genres");
904 rs = stmt.executeQuery("SELECT * FROM genres");
905 rsType = stmt1.executeQuery("desc genres");
906 ResultSetMetaData rsmd3 = rs.getMetaData();
907 for (int i = 1; i <= rsmd3.getColumnCount(); i++) {
908 System.out.print(rsmd3.getColumnName(i));
909 if (rsType.next()) {
910 String type = rsType.getString("type");
911 System.out.println( " "+ "("+ type +")" + "\t");
912 }
913 }
914 System.out.println();
915
916 // Print genres_in_movies metadata
917 System.out.println("genres_in_movies");
918 rs = stmt.executeQuery("SELECT * FROM genres_in_movies");
919 rsType = stmt1.executeQuery("desc genres_in_movies");
920 ResultSetMetaData rsmd4 = rs.getMetaData();
921 for (int i = 1; i <= rsmd4.getColumnCount(); i++) {
922 System.out.print(rsmd4.getColumnName(i));
923 if (rsType.next()) {
924 String type = rsType.getString("type");
925 System.out.println( " "+ "("+ type +")" + "\t");
926 }
927 }
928 System.out.println();
929
930 // Print customers metadata
931 System.out.println("customers");
932 rs = stmt.executeQuery("SELECT * FROM customers");
933 rsType = stmt1.executeQuery("desc customers");
934 ResultSetMetaData rsmd5 = rs.getMetaData();
935 for (int i = 1; i <= rsmd5.getColumnCount(); i++) {
936 System.out.print(rsmd5.getColumnName(i));
937 if (rsType.next()) {
938 String type = rsType.getString("type");
939 System.out.println( " "+ "("+ type +")" + "\t");
940 }
941 }
942 System.out.println();
943
944 // Print sales metadata
945 System.out.println("sales");
946 rs = stmt.executeQuery("SELECT * FROM sales");
947 rsType = stmt1.executeQuery("desc sales");
948 ResultSetMetaData rsmd6 = rs.getMetaData();
949 for (int i = 1; i <= rsmd6.getColumnCount(); i++) {
950 System.out.print(rsmd6.getColumnName(i));
951 if (rsType.next()) {
952 String type = rsType.getString("type");
953 System.out.println( " "+ "("+ type +")" + "\t");
954 }
955 }
956 System.out.println();
957
958 // Print creditcards metadata
959 System.out.println("creditcards");
960 rs = stmt.executeQuery("SELECT * FROM creditcards");
961 rsType = stmt1.executeQuery("desc creditcards");
962 ResultSetMetaData rsmd7 = rs.getMetaData();
963 for (int i = 1; i <= rsmd7.getColumnCount(); i++) {
964 System.out.print(rsmd7.getColumnName(i));
965 if (rsType.next()) {
966 String type = rsType.getString("type");
967 System.out.println( " "+ "("+ type +")" + "\t");
968 }
969 }
970 System.out.println();
971 }
972
973 else if (selection == 'z') {
974 break;
975 }
976 else if (selection == 'k') {
977 try{
978
979 // Create file
980
981 FileWriter fstream = new FileWriter("Error Report.html");
982
983 BufferedWriter out = new BufferedWriter(fstream);
984
985 out.write("<HTML> <HEAD> <TITLE>FabFlix Error Report</TITLE> </HEAD> <center> <BODY BGCOLOR=\"#FDF5E6\"> <H1 ALIGN=\"CENTER\">FabFlix Error Report</H1>");
986
987 //Query 1 is to find movies with no stars
988
989 String query1 = "SELECT m.id, m.title FROM movies as m LEFT JOIN stars_in_movies as s ON s.movie_id = m.id WHERE s.movie_id IS NULL";
990
991
992
993 Statement statementq1 = conn.createStatement();
994
995 ResultSet rsq1 = statementq1.executeQuery(query1);
996
997 out.write("The following movie IDs and titles have no stars associated with them:");
998
999 out.write("<TABLE border>");
1000
1001 out.write("<tr> +" +
1002
1003 "<td> Number </td>" +
1004
1005 "<td> ID </td>" +
1006
1007 "<td> Title </td> " +
1008
1009 "</tr>" +
1010
1011 "");
1012
1013 while (rsq1.next()) {
1014
1015 String id = rsq1.getString("id");
1016
1017 String title = rsq1.getString("title");
1018
1019 out.write("<tr>" +
1020
1021 "<td>" + rsq1.getRow() + "</td>" +
1022
1023 "<td>" + id + "</td>" +
1024
1025 "<td>" + title + "</td>" +
1026
1027
1028
1029 "</tr>");
1030
1031 }
1032
1033 out.write("</TABLE>");
1034
1035 //Query 2 is to find stars without movies
1036
1037 String query2 = "SELECT s.id, s.first_name, s.last_name FROM stars as s LEFT JOIN stars_in_movies as sm ON s.id = sm.star_id WHERE sm.star_id IS NULL";
1038
1039 Statement statementq2 = conn.createStatement();
1040
1041 ResultSet rsq2 = statementq2.executeQuery(query2);
1042
1043 out.write("<br> The following star IDs and names have no movies associated with them:");
1044
1045 out.write("<TABLE border>");
1046
1047 out.write("<tr> +" +
1048
1049 "<td> Number </td>" +
1050
1051 "<td> ID </td>" +
1052
1053 "<td> First Name </td> " +
1054
1055 "<td> Last Name </td> " +
1056
1057 "</tr>");
1058
1059 while (rsq2.next()) {
1060
1061 String id = rsq2.getString("id");
1062
1063 String firstName = rsq2.getString("first_name");
1064
1065 String lastName = rsq2.getString("last_name");
1066
1067 out.write("<tr>" +
1068
1069 "<td>" + rsq2.getRow() + "</td>" +
1070
1071 "<td>" + id + "</td>" +
1072
1073 "<td>" + firstName + "</td>" +
1074
1075 "<td>" + lastName + "</td>" +
1076
1077
1078
1079
1080
1081 "</tr>");
1082
1083 }
1084
1085 out.write("</table>");
1086
1087 //Query 3 is to find genres without movies
1088
1089 String query3 = "SELECT g.id, g.name FROM genres as g LEFT JOIN genres_in_movies as gm ON gm.genre_id = g.id WHERE gm.genre_id IS NULL";
1090
1091 Statement statementq3 = conn.createStatement();
1092
1093 ResultSet rsq3 = statementq3.executeQuery(query3);
1094
1095 out.write("<br> The following genre IDs and names have no movies associated with them:");
1096
1097 out.write("<TABLE border>");
1098
1099 out.write("<tr> +" +
1100
1101 "<td> Number </td>" +
1102
1103 "<td> ID </td>" +
1104
1105 "<td> Genre Name </td> " +
1106
1107 "</tr>");
1108
1109 System.out.println();
1110
1111 while (rsq3.next()) {
1112
1113 String id = rsq3.getString("id");
1114
1115 String name = rsq3.getString("name");
1116
1117
1118
1119 out.write("<tr>" +
1120
1121 "<td>" + rsq3.getRow() + "</td>" +
1122
1123 "<td>" + id + "</td>" +
1124
1125 "<td>" + name + "</td>" +
1126
1127 "</tr>");
1128
1129 }
1130
1131 out.write("</table>");
1132
1133
1134
1135 //Query4 is to find stars with either no last name or no first name or both.
1136
1137 String query4 = "SELECT s.id, s.first_name, s.last_name FROM stars as s WHERE (s.last_name ='' or s.first_name = '')";
1138
1139 Statement statementq4 = conn.createStatement();
1140
1141 ResultSet rsq4 = statementq4.executeQuery(query4);
1142
1143 out.write("<br> The following star IDs have no first name or last name associated with them");
1144
1145 out.write("<TABLE border>");
1146
1147 out.write("<tr> +" +
1148
1149 "<td> Number </td>" +
1150
1151 "<td> ID </td>" +
1152
1153 "</tr>");
1154
1155 while (rsq4.next()) {
1156
1157 String id = rsq4.getString("id");
1158
1159 out.write("<tr>" +
1160
1161 "<td>" + rsq3.getRow() + "</td>" +
1162
1163 "<td>" + id + "</td>" +
1164
1165 "</tr>");
1166
1167 }
1168
1169 out.write("</table>");
1170
1171 //Query 5 will all the expired creditcards of existing customers
1172
1173 String query5 = "select c.first_name, c.last_name, cc.id, cc.expiration from customers as c, creditcards as cc where cc.first_name = c.first_name and cc.last_name = c.last_name and cc.expiration <'2011-01-01'";
1174
1175 Statement statementq5 = conn.createStatement();
1176
1177 ResultSet rsq5 = statementq4.executeQuery(query5);
1178
1179 out.write("<br> The following customers have creditcards that have expired:");
1180
1181 out.write("<TABLE border>");
1182
1183 out.write("<tr> +" +
1184
1185 "<td> Number </td>" +
1186
1187 "<td> First Name </td> " +
1188
1189 "<td> Last Name </td> " +
1190
1191 "<td> ID </td>" +
1192
1193 "<td> Expiration Date </td>" +
1194
1195 "</tr>");
1196
1197 System.out.println();
1198
1199 while (rsq5.next()) {
1200
1201 String firstName = rsq5.getString("first_name");
1202
1203 String lastName = rsq5.getString("last_name");
1204
1205 String ccNum = rsq5.getString("id");
1206
1207 String exp = rsq5.getString("expiration");
1208
1209
1210
1211 out.write("<tr>" +
1212
1213 "<td>" + rsq5.getRow() + "</td>" +
1214
1215 "<td>" + firstName + "</td>" +
1216
1217 "<td>" + lastName + "</td>" +
1218
1219 "<td>" + ccNum + "</td>" +
1220
1221 "<td>" + exp + "</td>" +
1222
1223
1224
1225 "</tr>");
1226
1227 }
1228
1229 out.write("</table>");
1230
1231 //Query 6 will find all the duplicate movies
1232
1233 String query6 = " select m2.id, m.id, m.title, m.year from movies as m, movies as m2 where m.year=m2.year and m.title=m2.title and m.id !=m2.id";
1234
1235 Statement statement6 = conn.createStatement();
1236
1237 ResultSet rs6 = statement6.executeQuery(query6);
1238
1239 out.write("<br> The following shows duplicate movies along with their duplicate movie IDs:");
1240
1241 out.write("<TABLE border>");
1242
1243 out.write("<tr> +" +
1244
1245 "<td> Number </td>" +
1246
1247 "<td> Original ID </td>" +
1248
1249 "<td> Duplicate ID</td> " +
1250
1251 "<td> Movie Title </td> " +
1252
1253 "<td> Year </td>" +
1254
1255 "</tr>");
1256
1257 System.out.println("");
1258
1259 while (rs6.next()) {
1260
1261 String id1 = rs6.getString(1);
1262
1263 String id2 = rs6.getString(2);
1264
1265 String title = rs6.getString(3);
1266
1267 String year = rs6.getString(4);
1268
1269
1270
1271
1272
1273 out.write("<tr>" +
1274
1275 "<td>" + rs6.getRow() + "</td>" +
1276
1277 "<td>" + id1 + "</td>" +
1278
1279 "<td>" + id2 + "</td>" +
1280
1281 "<td>" + title + "</td>" +
1282
1283 "<td>" + year + "</td>" +
1284
1285 "</tr>");
1286
1287 }
1288
1289 out.write("</table>");
1290
1291
1292 //Query 7 will return duplicate stars
1293
1294 String query7 = " select s2.id, s.id, s.first_name, s.last_name, s.dob from stars as s, stars as s2 where s.first_name=s2.first_name and s.last_name=s2.last_name and s.dob = s2.dob and s.id !=s2.id";
1295
1296 Statement statement7 = conn.createStatement();
1297
1298 ResultSet rs7 = statement7.executeQuery(query7);
1299
1300 out.write("<br> The following shows duplicate stars and their duplicate star IDs:");
1301
1302 out.write("<TABLE border>");
1303
1304 out.write("<tr> +" +
1305
1306 "<td> Number </td>" +
1307
1308 "<td> Original ID </td>" +
1309
1310 "<td> Duplicate ID </td> " +
1311
1312 "<td> First Name </td>" +
1313
1314 "<td> Last Name </td> " +
1315
1316 "<td> DOB </td>"+
1317
1318 "</tr>");
1319
1320 while (rs7.next()) {
1321
1322 String id1 = rs7.getString(1);
1323
1324 String id2 = rs7.getString(2);
1325
1326 String firstname = rs7.getString(3);
1327
1328 String lastname = rs7.getString(4);
1329
1330 String dob = rs7.getString(5);
1331
1332
1333
1334 out.write("<tr>" +
1335
1336 "<td>" + rs7.getRow() + "</td>" +
1337
1338 "<td>" + id1 + "</td>" +
1339
1340 "<td>" + id2 + "</td>" +
1341
1342 "<td>" + firstname + "</td>" +
1343
1344 "<td>" + lastname + "</td>" +
1345
1346 "<td>" + dob + "</td>" +
1347
1348 "</tr>");
1349
1350 }
1351
1352 out.write("</table>");
1353
1354 //Query 9 will return stars with birth dates greater than today's date or less than the year 1900
1355
1356 String query9 = "select dob, id, first_name, last_name from stars where (dob < '1900-12-31' or dob > '2011-02-10')";
1357
1358 Statement statement9 = conn.createStatement();
1359
1360 ResultSet rsq9 = statement9.executeQuery(query9);
1361
1362 out.write("<br> The following displays stars with a DOB greater than today's date or less than the year 1900:");
1363
1364 out.write("<TABLE border>");
1365
1366 out.write("<tr> +" +
1367
1368 "<td> Number </td>" +
1369
1370 "<td> DOB </td>" +
1371
1372 "<td> Star ID </td>" +
1373
1374 "<td> First Name </td> " +
1375
1376 "<td> Last Name </td> " +
1377
1378
1379
1380 "</tr>");
1381
1382
1383 while (rsq9.next()) {
1384
1385 String dob = rsq9.getString(1);
1386
1387 String id = rsq9.getString(2);
1388
1389 String firstName = rsq9.getString(3);
1390
1391 String lastName = rsq9.getString(4);
1392
1393
1394
1395
1396
1397 out.write("<tr>" +
1398
1399 "<td>" + rsq9.getRow() + "</td>" +
1400
1401 "<td>" + dob + "</td>" +
1402
1403 "<td>" + id + "</td>" +
1404
1405 "<td>" + firstName + "</td>" +
1406
1407 "<td>" + lastName + "</td>" +
1408
1409
1410
1411 "</tr>");
1412
1413 }
1414
1415 out.write("</table>");
1416
1417 //Query 8 will return duplicate genres
1418
1419 String query8 = "select m2.id, m.id, m.name from genres as m, genres as m2 where m.name=m2.name and m.id !=m2.id";
1420
1421 Statement statementq8 = conn.createStatement();
1422
1423 ResultSet rsq8 = statementq3.executeQuery(query8);
1424
1425 out.write("<br> The following displays duplicate genres and their duplicate IDs:");
1426
1427 out.write("<TABLE border>");
1428
1429 out.write("<tr> +" +
1430
1431 "<td> Number </td>" +
1432
1433 "<td> Original ID </td>" +
1434
1435 "<td> Duplicate ID </td>" +
1436
1437 "<td> Genre Name </td> " +
1438
1439 "</tr>");
1440
1441
1442 while (rsq8.next()) {
1443
1444 String id1 = rsq8.getString(1);
1445
1446 String id2 = rsq8.getString(2);
1447
1448 String name = rsq8.getString(3);
1449
1450
1451
1452 out.write("<tr>" +
1453
1454 "<td>" + rsq8.getRow() + "</td>" +
1455
1456 "<td>" + id1 + "</td>" +
1457
1458 "<td>" + id2 + "</td>" +
1459
1460 "<td>" + name + "</td>" +
1461
1462 "</tr>");
1463
1464 }
1465
1466 out.write("</table>");
1467 out.write(" </CENTER> </FORM> </BODY> </HTML>");
1468
1469 System.out.println("You can find the Error Report in HTML format in the source folder of this project.");
1470
1471 System.out.println("The file name is Error Report.html.");
1472
1473 }catch (Exception e){//Catch exception if any
1474
1475 System.err.println("Error: " + e.getMessage());
1476
1477 }
1478
1479 }
1480 else
1481 System.out.println(selection + " is an unknown command") ;
1482
1483 } }
1484
1485 catch (Exception e) {
1486 e.printStackTrace();
1487
1488 }
1489
1490 }}