· 9 years ago · Jul 21, 2017, 11:32 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 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") || edit.equals("y")) {
477 System.out.println("Look to upper left for auxilary window for grant/revoke options");
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(510, 250);
522 JLabel info2 = new JLabel("Enter the database in the following format:");
523 JLabel info3 = new JLabel("database_name.table_name or database_name.*");
524 JLabel info1 = new JLabel("For both forms you can grant/revoke certain privileges in the following format:");
525 JLabel info4 = new JLabel("SELECT, INSERT, DELETE, CREATE, DROP, etc.");
526 JLabel grant = new JLabel("This portion is for GRANTING privileges only:");
527 final JTextField grantPriv = new JTextField("Privileges to GRANT");
528
529 JLabel revoke = new JLabel("This portion is for REVOKING privileges only:");
530 JLabel selectDatabase = new JLabel ("Grant/Revoke privileges on the following database:");
531 final JTextField revokePriv = new JTextField("Privileges to REVOKE");
532 final JTextField selectDB = new JTextField("Enter Database");
533 JButton submit2 = new JButton("Submit");
534 actionPanel.add(info1);
535 actionPanel.add(info4);
536 actionPanel.add(info2);
537 actionPanel.add(info3);
538 actionPanel.add(grant);
539 actionPanel.add(grantPriv);
540 actionPanel.add(revoke);
541 actionPanel.add(revokePriv);
542 actionPanel.add(selectDatabase);
543 actionPanel.add(selectDB);
544 actionPanel.add(submit2);
545 submit2.addActionListener(new ActionListener() {
546 public void actionPerformed (ActionEvent e) {
547 try {
548 String username = userName.getText();
549 String hostname = hostName.getText();
550 String grantpriv = grantPriv.getText();
551 String revokepriv = revokePriv.getText();
552 String selectdb = selectDB.getText();
553 if ((grantpriv.equals("") && revokepriv.equals("")) && selectdb.equals("")||
554 (grantpriv.equals("Privileges to GRANT") && revokepriv.equals("Privileges to REVOKE") && selectdb.equals("Enter Database"))) {
555 actionPanel.setVisible(false);
556 final JPanel grantRevokePanel = new JPanel();
557 grantRevokePanel.setVisible(true);
558 grantFrame.add(grantRevokePanel);
559 grantRevokePanel.setSize(400,400);
560 JLabel errorLabel = new JLabel("You failed to insert any information. Please use the back button to enter information.");
561
562 grantRevokePanel.add(errorLabel);
563 JButton back = new JButton("Go back to previous screen");
564
565 grantRevokePanel.add(back);
566 back.addActionListener(new ActionListener() {
567 public void actionPerformed (ActionEvent e) {
568 actionPanel.setVisible(true);
569 actionPanel.setSize(800,350);
570
571 grantRevokePanel.setVisible(false);
572 }});
573 }
574
575 else if (grantpriv.equals("")|| grantpriv.equals("Privledges to GRANT")) {
576 Statement stmt = conn.createStatement();
577 boolean rs = stmt.execute("revoke "+revokepriv+" on "+selectdb+" from '"+username+"'@'"+hostname+"'");
578 System.out.println("got to grantpriv if");
579 actionPanel.setVisible(false);
580 final JPanel confirmPanel = new JPanel();
581 grantFrame.add(confirmPanel);
582 grantFrame.setSize(500, 150);
583 final JLabel confirmLabel = new JLabel("You have succesfully revoked/granted access. Please refresh the MySQL server");
584 confirmPanel.add(confirmLabel);
585 confirmPanel.add(new JLabel("You may now close this window."));
586
587 }
588 else if (revokepriv.equals("") || revokepriv.equals("Privledges to REVOKE")) {
589 Statement stmt = conn.createStatement();
590 boolean rs = stmt.execute("grant "+grantpriv+" on "+selectdb+" to '"+username+"'@'"+hostname+"'");
591 System.out.println("got to revoke priv if");
592 actionPanel.setVisible(false);
593 final JPanel confirmPanel = new JPanel();
594 grantFrame.add(confirmPanel);
595 grantFrame.setSize(500, 150);
596 final JLabel confirmLabel = new JLabel("You have succesfully revoked/granted access. Please refresh the MySQL server");
597 confirmPanel.add(confirmLabel);
598 confirmPanel.add(new JLabel("You may now close this window."));
599
600
601 }
602
603
604 else {
605 Statement stmt = conn.createStatement();
606 boolean rs = stmt.execute("grant "+grantpriv+" on "+selectdb+" to '"+username+"'@'"+hostname+"'");
607 Statement stmt2 = conn.createStatement();
608 boolean rs2 = stmt2.execute("revoke "+revokepriv+" on "+selectdb+" from '"+username+"'@'"+hostname+"'");
609 actionPanel.setVisible(false);
610 final JPanel confirmPanel = new JPanel();
611 grantFrame.add(confirmPanel);
612 grantFrame.setSize(500, 150);
613 final JLabel confirmLabel = new JLabel("You have succesfully revoked/granted access. Please refresh the MySQL server");
614 confirmPanel.add(confirmLabel);
615 confirmPanel.add(new JLabel("You may now close this window."));
616 }
617
618
619 }
620 catch (Exception e2) {
621 e2.printStackTrace();
622
623 }
624 }
625 });
626
627
628 }
629 // Student s = new Student(studentFirstName.getText(), studentLastName.getText(), menuID.getText(), pin.getText());
630 // menuPlus.getStudentCollection().addStudent(s);
631 // changes = true;
632
633
634
635 }
636 catch (Exception e2) {
637 e2.printStackTrace();
638
639 }
640 }
641
642 });
643 }
644
645 String addUsers = Prompt.forString("Would you like to add a user to the MySQL database? (Y/N)");
646 if (addUsers.equals("Y") || addUsers.equals("y")){
647 System.out.println("Look to upper left for auxilary window to add users");
648 final JFrame addFrame = new JFrame("Create MySQL user account");
649 addFrame.setVisible(true);
650 addFrame.setSize(300,300);
651 final JPanel addpanel = new JPanel();
652 addFrame.add(addpanel);
653 final JTextField adduserName = new JTextField("Enter user name");
654 addpanel.add(adduserName);
655 final JTextField addhostName = new JTextField("Enter host name");
656 addpanel.add(addhostName);
657 final JTextField addpassword = new JTextField("Enter password");
658 addpanel.add(addpassword);
659 JButton addsubmit = new JButton("Create User");
660 addpanel.add(addsubmit);
661 addsubmit.addActionListener(new ActionListener() {
662 public void actionPerformed(ActionEvent e) {
663 try {
664 Boolean duplicate = false;
665 String addUserNameString = adduserName.getText();
666 String addHostNameString = addhostName.getText();
667 String addPasswordString = addpassword.getText();
668
669 // Check to see if there are any users with the same account.
670 String queryCheck = "select count(*) from mysql.user where user = '" +addUserNameString+ "' and host = '" +addHostNameString+ "'";
671 Statement stmt00 = conn.createStatement();
672 ResultSet rsCheck = stmt00.executeQuery(queryCheck);
673 while (rsCheck.next()) {
674 if (rsCheck.getString(1).equals("1")) {
675 addpanel.setVisible(false);
676 final JPanel denyPanel = new JPanel();
677 addFrame.add(denyPanel);
678 final JLabel confirmLabel = new JLabel("Sorry, the user you have tried to add is already in the database.");
679 denyPanel.add(confirmLabel);
680 duplicate = true;
681 JButton back = new JButton("Go back to previous screen");
682 denyPanel.add(back);
683 back.addActionListener(new ActionListener() {
684 public void actionPerformed (ActionEvent e) {
685 addpanel.setVisible(true);
686
687
688 denyPanel.setVisible(false);
689 }});
690 }
691
692 }
693
694
695 // Time to make a SQL query/command
696 if (duplicate == false) {
697 String sqlCommand = "create user '" +addUserNameString+ "' @'"+addHostNameString+"' identified by '" + addPasswordString+"'";
698 Statement addStmt = conn.createStatement();
699 Boolean rsAdd = addStmt.execute(sqlCommand);
700 addpanel.setVisible(false);
701 final JPanel confirmPanel = new JPanel();
702 addFrame.add(confirmPanel);
703 final JLabel confirmLabel = new JLabel("Your entry has been confirmed. Please refresh the MySQL server");
704 confirmPanel.add(confirmLabel);
705 }
706
707 }
708 catch (Exception e3){
709 e3.printStackTrace();
710 }
711 }
712 });
713 }
714 else {
715 System.out.println("\nYou have selected none of the options.");
716 System.out.println("\nTherefore, you will be redirected to the main menu. \n");
717 }
718 }
719 else if (selection == 'u') {
720 loggedIn = false;
721
722
723 if (rs != null) {
724 try {
725 rs.close();
726 } catch (SQLException sqlEx) {
727 } // ignore
728
729 rs = null;
730 }
731
732 if (rsType != null) {
733 try {
734 rsType.close();
735 } catch (SQLException sqlEx) {
736 } // ignore
737
738 rsType = null;
739 }
740
741
742 if (stmt != null) {
743 try {
744 stmt.close();
745 } catch (SQLException sqlEx) {
746 } // ignore
747
748 stmt = null;
749 }
750
751 if (conn != null) {
752 try {
753 conn.close();
754 } catch (SQLException sqlEx) {
755 } // ignore
756
757 conn = null;
758 }
759
760 Class.forName("com.mysql.jdbc.Driver").newInstance();
761
762 while(loggedIn == false) {
763 System.out.println("Welcome to FabFlix! Please input user information below:");
764 //String username3 = Prompt.forString("Enter username");
765 //String password3 = Prompt.forString("Enter password");
766 String usernameDatabase1 = "root";
767 String passwordDatabase1 = "lakers";
768 String employeeEmail1 = Prompt.forString("Enter email");
769 String employeePassword1 = Prompt.forString("Enter password");
770 String queryLogin1 = "SELECT count(*) from employees where email = '" + employeeEmail1 + "' and password = '" + employeePassword1 +"'";
771 conn = DriverManager.getConnection(url, usernameDatabase1, passwordDatabase1);
772 stmt = conn.createStatement();
773 stmt1 = conn.createStatement();
774 ResultSet rsLogin1 = stmt.executeQuery(queryLogin1);
775
776 while(rsLogin1.next()) {
777 if (rsLogin1.getString(1).equals("1")) {
778 conn = DriverManager.getConnection(url, usernameDatabase1, passwordDatabase1);
779 stmt = conn.createStatement();
780 stmt1 = conn.createStatement();
781 loggedIn = true;
782 employeeEmail1 = null;
783 employeePassword1 = null;
784 break;
785 }
786
787 else
788 System.out.println("Wrong username/password combination. Please try again.");
789
790 }
791 }
792
793
794 }
795
796 else if (selection == 'x') {
797 boolean output = Prompt.forBoolean("Will this command return an output? (true or false)");
798 if (output == true) {
799 String select = Prompt.forString("SELECT");
800 String from = Prompt.forString("FROM");
801 String where = Prompt.forString("WHERE");
802 if (!where.isEmpty()){
803 rs = stmt.executeQuery("select " + select + " from " + from + " where " + where);
804 }
805 else {
806 rs = stmt.executeQuery("select " + select + " from " + from);
807 }
808
809 ResultSetMetaData rsmd = rs.getMetaData();
810 for (int i = 1; i <= rsmd.getColumnCount(); i++) {
811 System.out.print(rsmd.getColumnName(i) + "\t");
812 }
813 System.out.println();
814 System.out.println();
815 String t = "";
816 for (int i = 1; i<= rsmd.getColumnCount(); i++) {
817 String s = rsmd.getColumnName(i) + ", ";
818 t = t + s;
819 }
820 String s2 = t;
821 StringTokenizer st = new StringTokenizer(s2, ", "); // To grab the "new" attributes
822
823 while (rs.next()) {
824 while (st.hasMoreTokens()) {
825 Object o = rs.getObject(st.nextToken());
826 System.out.print(o + "\t");
827 }
828 st = new StringTokenizer(s2, ", ");
829 System.out.println();
830 }
831 }
832 else {
833 String option = Prompt.forString("Enter update, insert, or delete");
834 if (option.equals("update")) {
835 String update = Prompt.forString("UPDATE");
836 String set = Prompt.forString("SET");
837 String where = Prompt.forString("WHERE");
838 int updateExecute = stmt.executeUpdate("update " + update + " set " + set + " where " + where);
839 System.out.println("The information has been updated in the " + update+ " table");
840 }
841 else if (option.equals("insert")) {
842 String insertInto = Prompt.forString("INSERT INTO:");
843 rsType = stmt1.executeQuery("desc "+ insertInto);
844 String attributes ="(";
845 while(rsType.next()) {
846 String field = rsType.getString("field");
847 if (rsType.isLast())
848 attributes = attributes + field;
849 else
850 attributes = attributes + field + ", ";
851
852 }
853 String values = Prompt.forString("VALUES:");
854 int insertExecute = stmt.executeUpdate("insert into "+ insertInto+ attributes+")"+ " values" + values);
855 System.out.println("The information has been inserted into the " + insertInto+ " table");
856 }
857
858 else if (option.equals("delete")) {
859 String deleteFrom = Prompt.forString("DELETE FROM");
860 String where = Prompt.forString("WHERE");
861 int deleteExecute = stmt.executeUpdate("delete from " + deleteFrom + " where " + where);
862 System.out.println("The information inputted has deleted the corresponding records");
863 }
864 else
865 System.out.println("Error: You have entered a nonvalid option");
866 }
867 }
868
869
870 else if (selection == 'm') {
871 // Print Movie Metadata
872 System.out.println("Movies :");
873 rs = stmt.executeQuery("SELECT * FROM movies");
874 rsType = stmt1.executeQuery("desc movies"); // Data table about movie table
875 ResultSetMetaData rsmd = rs.getMetaData();
876 for (int i = 1; i <= rsmd.getColumnCount(); i++) {
877 System.out.print(rsmd.getColumnName(i));
878 if (rsType.next()) {
879 String type = rsType.getString("type");
880 System.out.println( " "+ "("+ type +")" + "\t");
881 }
882 }
883 System.out.println();
884
885 // Print Stars Metadata
886 System.out.println("Stars :");
887 rs = stmt.executeQuery("SELECT * FROM stars");
888 rsType = stmt1.executeQuery("desc stars");
889 ResultSetMetaData rsmd1 = rs.getMetaData();
890 for (int i = 1; i <= rsmd1.getColumnCount(); i++) {
891 System.out.print(rsmd1.getColumnName(i));
892 if (rsType.next()) {
893 String type = rsType.getString("type");
894 System.out.println( " "+ "("+ type +")" + "\t");
895 }
896 }
897 System.out.println();
898
899 // Print stars_in_movies metadata
900 System.out.println("stars_in_movies");
901 rs = stmt.executeQuery("SELECT * FROM stars_in_movies");
902 rsType = stmt1.executeQuery("desc stars_in_movies");
903 ResultSetMetaData rsmd2 = rs.getMetaData();
904 for (int i = 1; i <= rsmd2.getColumnCount(); i++) {
905 System.out.print(rsmd2.getColumnName(i));
906 if (rsType.next()) {
907 String type = rsType.getString("type");
908 System.out.println( " "+ "("+ type +")" + "\t");
909 }
910 }
911 System.out.println();
912
913 // Print genres metadata
914 System.out.println("genres");
915 rs = stmt.executeQuery("SELECT * FROM genres");
916 rsType = stmt1.executeQuery("desc genres");
917 ResultSetMetaData rsmd3 = rs.getMetaData();
918 for (int i = 1; i <= rsmd3.getColumnCount(); i++) {
919 System.out.print(rsmd3.getColumnName(i));
920 if (rsType.next()) {
921 String type = rsType.getString("type");
922 System.out.println( " "+ "("+ type +")" + "\t");
923 }
924 }
925 System.out.println();
926
927 // Print genres_in_movies metadata
928 System.out.println("genres_in_movies");
929 rs = stmt.executeQuery("SELECT * FROM genres_in_movies");
930 rsType = stmt1.executeQuery("desc genres_in_movies");
931 ResultSetMetaData rsmd4 = rs.getMetaData();
932 for (int i = 1; i <= rsmd4.getColumnCount(); i++) {
933 System.out.print(rsmd4.getColumnName(i));
934 if (rsType.next()) {
935 String type = rsType.getString("type");
936 System.out.println( " "+ "("+ type +")" + "\t");
937 }
938 }
939 System.out.println();
940
941 // Print customers metadata
942 System.out.println("customers");
943 rs = stmt.executeQuery("SELECT * FROM customers");
944 rsType = stmt1.executeQuery("desc customers");
945 ResultSetMetaData rsmd5 = rs.getMetaData();
946 for (int i = 1; i <= rsmd5.getColumnCount(); i++) {
947 System.out.print(rsmd5.getColumnName(i));
948 if (rsType.next()) {
949 String type = rsType.getString("type");
950 System.out.println( " "+ "("+ type +")" + "\t");
951 }
952 }
953 System.out.println();
954
955 // Print sales metadata
956 System.out.println("sales");
957 rs = stmt.executeQuery("SELECT * FROM sales");
958 rsType = stmt1.executeQuery("desc sales");
959 ResultSetMetaData rsmd6 = rs.getMetaData();
960 for (int i = 1; i <= rsmd6.getColumnCount(); i++) {
961 System.out.print(rsmd6.getColumnName(i));
962 if (rsType.next()) {
963 String type = rsType.getString("type");
964 System.out.println( " "+ "("+ type +")" + "\t");
965 }
966 }
967 System.out.println();
968
969 // Print creditcards metadata
970 System.out.println("creditcards");
971 rs = stmt.executeQuery("SELECT * FROM creditcards");
972 rsType = stmt1.executeQuery("desc creditcards");
973 ResultSetMetaData rsmd7 = rs.getMetaData();
974 for (int i = 1; i <= rsmd7.getColumnCount(); i++) {
975 System.out.print(rsmd7.getColumnName(i));
976 if (rsType.next()) {
977 String type = rsType.getString("type");
978 System.out.println( " "+ "("+ type +")" + "\t");
979 }
980 }
981 System.out.println();
982 }
983
984 else if (selection == 'z') {
985 break;
986 }
987 else if (selection == 'k') {
988 try{
989
990 // Create file
991
992 FileWriter fstream = new FileWriter("Error Report.html");
993 BufferedWriter out = new BufferedWriter(fstream);
994 out.write("<HTML> <HEAD> <TITLE>FabFlix Error Report</TITLE> </HEAD> <center> <BODY BGCOLOR=\"#FDF5E6\"> <H1 ALIGN=\"CENTER\">FabFlix Error Report</H1>");
995
996 //Query 1 is to find movies with no stars
997 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";
998
999 Statement statementq1 = conn.createStatement();
1000 ResultSet rsq1 = statementq1.executeQuery(query1);
1001 out.write("The following movie IDs and titles have no stars associated with them:");
1002 out.write("<TABLE border>");
1003 out.write("<tr> +" +
1004 "<td> Number </td>" +
1005 "<td> ID </td>" +
1006 "<td> Title </td> " +
1007 "</tr>" +
1008 "");
1009
1010 while (rsq1.next()) {
1011 String id = rsq1.getString("id");
1012 String title = rsq1.getString("title");
1013 out.write("<tr>" +
1014 "<td>" + rsq1.getRow() + "</td>" +
1015 "<td>" + id + "</td>" +
1016 "<td>" + title + "</td>" +
1017 "</tr>");
1018 }
1019
1020 out.write("</TABLE>");
1021 //Query 2 is to find stars without movies
1022 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";
1023 Statement statementq2 = conn.createStatement();
1024 ResultSet rsq2 = statementq2.executeQuery(query2);
1025 out.write("<br> The following star IDs and names have no movies associated with them:");
1026 out.write("<TABLE border>");
1027 out.write("<tr> +" +
1028 "<td> Number </td>" +
1029 "<td> ID </td>" +
1030 "<td> First Name </td> " +
1031 "<td> Last Name </td> " +
1032 "</tr>");
1033
1034 while (rsq2.next()) {
1035 String id = rsq2.getString("id");
1036 String firstName = rsq2.getString("first_name");
1037 String lastName = rsq2.getString("last_name");
1038 out.write("<tr>" +
1039 "<td>" + rsq2.getRow() + "</td>" +
1040 "<td>" + id + "</td>" +
1041 "<td>" + firstName + "</td>" +
1042 "<td>" + lastName + "</td>" +
1043 "</tr>");
1044 }
1045
1046 out.write("</table>");
1047 //Query 3 is to find genres without movies
1048 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";
1049 Statement statementq3 = conn.createStatement();
1050 ResultSet rsq3 = statementq3.executeQuery(query3);
1051 out.write("<br> The following genre IDs and names have no movies associated with them:");
1052 out.write("<TABLE border>");
1053 out.write("<tr> +" +
1054 "<td> Number </td>" +
1055 "<td> ID </td>" +
1056 "<td> Genre Name </td> " +
1057 "</tr>");
1058
1059 System.out.println();
1060 while (rsq3.next()) {
1061 String id = rsq3.getString("id");
1062 String name = rsq3.getString("name");
1063 out.write("<tr>" +
1064 "<td>" + rsq3.getRow() + "</td>" +
1065 "<td>" + id + "</td>" +
1066 "<td>" + name + "</td>" +
1067 "</tr>");
1068 }
1069 out.write("</table>");
1070
1071 //Query4 is to find stars with either no last name or no first name or both.
1072 String query4 = "SELECT s.id, s.first_name, s.last_name FROM stars as s WHERE (s.last_name ='' or s.first_name = '')";
1073 Statement statementq4 = conn.createStatement();
1074 ResultSet rsq4 = statementq4.executeQuery(query4);
1075 out.write("<br> The following star IDs have no first name or last name associated with them");
1076 out.write("<TABLE border>");
1077 out.write("<tr> +" +
1078 "<td> Number </td>" +
1079 "<td> ID </td>" +
1080 "</tr>");
1081
1082 while (rsq4.next()) {
1083 String id = rsq4.getString("id");
1084 out.write("<tr>" +
1085 "<td>" + rsq3.getRow() + "</td>" +
1086 "<td>" + id + "</td>" +
1087 "</tr>");
1088 }
1089 out.write("</table>");
1090 //Query 5 will all the expired creditcards of existing customers
1091 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'";
1092 Statement statementq5 = conn.createStatement();
1093 ResultSet rsq5 = statementq4.executeQuery(query5);
1094 out.write("<br> The following customers have creditcards that have expired:");
1095 out.write("<TABLE border>");
1096 out.write("<tr> +" +
1097 "<td> Number </td>" +
1098 "<td> First Name </td> " +
1099 "<td> Last Name </td> " +
1100 "<td> ID </td>" +
1101 "<td> Expiration Date </td>" +
1102 "</tr>");
1103
1104 System.out.println();
1105 while (rsq5.next()) {
1106 String firstName = rsq5.getString("first_name");
1107 String lastName = rsq5.getString("last_name");
1108 String ccNum = rsq5.getString("id");
1109 String exp = rsq5.getString("expiration");
1110 out.write("<tr>" +
1111 "<td>" + rsq5.getRow() + "</td>" +
1112 "<td>" + firstName + "</td>" +
1113 "<td>" + lastName + "</td>" +
1114 "<td>" + ccNum + "</td>" +
1115 "<td>" + exp + "</td>" +
1116 "</tr>");
1117 }
1118
1119 out.write("</table>");
1120 //Query 6 will find all the duplicate movies
1121 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";
1122 Statement statement6 = conn.createStatement();
1123 ResultSet rs6 = statement6.executeQuery(query6);
1124 out.write("<br> The following shows duplicate movies along with their duplicate movie IDs:");
1125 out.write("<TABLE border>");
1126 out.write("<tr> +" +
1127 "<td> Number </td>" +
1128 "<td> Original ID </td>" +
1129 "<td> Duplicate ID</td> " +
1130 "<td> Movie Title </td> " +
1131 "<td> Year </td>" +
1132 "</tr>");
1133 System.out.println("");
1134 while (rs6.next()) {
1135 String id1 = rs6.getString(1);
1136 String id2 = rs6.getString(2);
1137 String title = rs6.getString(3);
1138 String year = rs6.getString(4);
1139 out.write("<tr>" +
1140 "<td>" + rs6.getRow() + "</td>" +
1141 "<td>" + id1 + "</td>" +
1142 "<td>" + id2 + "</td>" +
1143 "<td>" + title + "</td>" +
1144 "<td>" + year + "</td>" +
1145 "</tr>");
1146 }
1147
1148 out.write("</table>");
1149 //Query 7 will return duplicate stars
1150 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";
1151 Statement statement7 = conn.createStatement();
1152 ResultSet rs7 = statement7.executeQuery(query7);
1153 out.write("<br> The following shows duplicate stars and their duplicate star IDs:");
1154 out.write("<TABLE border>");
1155 out.write("<tr> +" +
1156 "<td> Number </td>" +
1157 "<td> Original ID </td>" +
1158 "<td> Duplicate ID </td> " +
1159 "<td> First Name </td>" +
1160 "<td> Last Name </td> " +
1161 "<td> DOB </td>"+
1162 "</tr>");
1163
1164 while (rs7.next()) {
1165 String id1 = rs7.getString(1);
1166 String id2 = rs7.getString(2);
1167 String firstname = rs7.getString(3);
1168 String lastname = rs7.getString(4);
1169 String dob = rs7.getString(5);
1170
1171 out.write("<tr>" +
1172 "<td>" + rs7.getRow() + "</td>" +
1173 "<td>" + id1 + "</td>" +
1174 "<td>" + id2 + "</td>" +
1175 "<td>" + firstname + "</td>" +
1176 "<td>" + lastname + "</td>" +
1177 "<td>" + dob + "</td>" +
1178 "</tr>");
1179
1180 }
1181
1182 out.write("</table>");
1183 //Query 9 will return stars with birth dates greater than today's date or less than the year 1900
1184 String query9 = "select dob, id, first_name, last_name from stars where (dob < '1900-12-31' or dob > '2011-02-10')";
1185 Statement statement9 = conn.createStatement();
1186 ResultSet rsq9 = statement9.executeQuery(query9);
1187 out.write("<br> The following displays stars with a DOB greater than today's date or less than the year 1900:");
1188 out.write("<TABLE border>");
1189 out.write("<tr> +" +
1190 "<td> Number </td>" +
1191 "<td> DOB </td>" +
1192 "<td> Star ID </td>" +
1193 "<td> First Name </td> " +
1194 "<td> Last Name </td> " +
1195 "</tr>");
1196
1197
1198 while (rsq9.next()) {
1199 String dob = rsq9.getString(1);
1200 String id = rsq9.getString(2);
1201 String firstName = rsq9.getString(3);
1202 String lastName = rsq9.getString(4);
1203 out.write("<tr>" +
1204 "<td>" + rsq9.getRow() + "</td>" +
1205 "<td>" + dob + "</td>" +
1206 "<td>" + id + "</td>" +
1207 "<td>" + firstName + "</td>" +
1208 "<td>" + lastName + "</td>" +
1209 "</tr>");
1210
1211 }
1212
1213 out.write("</table>");
1214
1215 //Query 8 will return duplicate genres
1216 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";
1217 Statement statementq8 = conn.createStatement();
1218 ResultSet rsq8 = statementq3.executeQuery(query8);
1219 out.write("<br> The following displays duplicate genres and their duplicate IDs:");
1220 out.write("<TABLE border>");
1221 out.write("<tr> +" +
1222 "<td> Number </td>" +
1223 "<td> Original ID </td>" +
1224 "<td> Duplicate ID </td>" +
1225 "<td> Genre Name </td> " +
1226 "</tr>");
1227
1228
1229 while (rsq8.next()) {
1230 String id1 = rsq8.getString(1);
1231 String id2 = rsq8.getString(2);
1232 String name = rsq8.getString(3);
1233 out.write("<tr>" +
1234 "<td>" + rsq8.getRow() + "</td>" +
1235 "<td>" + id1 + "</td>" +
1236 "<td>" + id2 + "</td>" +
1237 "<td>" + name + "</td>" +
1238 "</tr>");
1239
1240 }
1241
1242 out.write("</table>");
1243 out.write(" </CENTER> </FORM> </BODY> </HTML>");
1244 System.out.println("You can find the Error Report in HTML format in the source folder of this project.");
1245 System.out.println("The file name is Error Report.html.");
1246
1247 }catch (Exception e){//Catch exception if any
1248
1249 System.err.println("Error: " + e.getMessage());
1250
1251 }
1252
1253 }
1254 else
1255 System.out.println(selection + " is an unknown command") ;
1256
1257 } }
1258
1259 catch (Exception e) {
1260 e.printStackTrace();
1261
1262 }
1263
1264 }}