· 9 years ago · Jul 21, 2017, 10:42 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");
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, select_priv, insert_priv, update_priv, delete_priv, create_priv, drop_priv 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 String select_priv = userprivRS.getString(3);
404 String insert_priv = userprivRS.getString(4);
405 String update_priv = userprivRS.getString(5);
406 String delete_priv = userprivRS.getString(6);
407 String create_priv = userprivRS.getString(7);
408 String drop_priv = userprivRS.getString(8);
409 System.out.println(hostString+" "+userString + " " + select_priv + " " + insert_priv + " " + update_priv + " " + delete_priv + " " + create_priv + " " + drop_priv);
410 }
411 System.out.println();
412 }
413 else if (selection == 'c') {
414 Boolean singleName = Prompt.forBoolean("Does he/she have a single name?");
415 if (singleName == true) {
416 int id = Prompt.forInt("Enter customer ID");
417 String firstName = "";
418 String lastName = Prompt.forString("Enter name");
419 String cc_id = Prompt.forString("Enter credit card number");
420 String address = Prompt.forString("Enter address");
421 String email = Prompt.forString("Enter email");
422 String password1 = Prompt.forString("Set a password");
423
424 // Checking with credit card data table
425 rs = stmt.executeQuery("SELECT c.id FROM creditcards as c");
426 while (rs.next()) {
427 String credID = rs.getString("id");
428 if (credID.equals(cc_id)) {
429 int insert = stmt.executeUpdate("INSERT INTO customers (id, first_name, last_name, cc_id, address, email, password) " +
430 "VALUES('"+ id +"', '"+ firstName +"','"+
431 lastName +"','"+ cc_id + "','"+ address +"','"+ email +"','"+ password1+"');");
432 System.out.println("1 row added to customers table.");
433 break;
434 }
435
436 }
437 }
438 else
439 {
440 int id = Prompt.forInt("Enter customer ID");
441 String firstName = Prompt.forString("Enter first name");
442 String lastName = Prompt.forString("Enter last name");
443 String cc_id = Prompt.forString("Enter credit card number");
444 String address = Prompt.forString("Enter address");
445 String email = Prompt.forString("Enter email");
446 String password2 = Prompt.forString("Set a password");
447 // Checking with credit card data table
448 rs = stmt.executeQuery("SELECT c.id FROM creditcards as c");
449 while (rs.next()) {
450 String credID = rs.getString("id");
451 if (credID.equals((String)cc_id)) {
452 int insert = stmt.executeUpdate("INSERT INTO customers (id, first_name, last_name, cc_id, address, email, password) " +
453 "VALUES('"+ id +"', '"+ firstName +"','"+
454 lastName +"','"+ cc_id + "','"+ address +"','"+ email +"','"+ password2+"');");
455 System.out.println("1 row added to customers table.");
456 System.out.println();
457 break;
458 }
459
460 }
461 }
462 }
463 else if (selection == 'd') {
464 int id = Prompt.forInt("Enter ID number for customer removal");
465 int delete= stmt.executeUpdate("delete from customers WHERE id = " + id);
466 System.out.println("1 row affected");
467 }
468 else if (selection == 'e') {
469 System.out.println("Before you begin, please ensure that you have reviewed the database and user access information by pressing 'o'. Please do so if you have not already.");
470 System.out.println();
471 Boolean edit = Prompt.forBoolean("Will you be granting existing user(s) more privileges? (True/False)");
472 if (edit == true) {
473 System.out.println("Grant existing user(s) more access rights");
474 final JFrame grantFrame = new JFrame("Grant existing users more access rights");
475 grantFrame.setVisible(true);
476 grantFrame.setSize(300,300);
477 final JPanel panel = new JPanel();
478 grantFrame.add(panel);
479 final JTextField userName = new JTextField("Enter user name");
480 panel.add(userName);
481 final JTextField hostName = new JTextField("Enter host name");
482 panel.add(hostName);
483 JButton submit = new JButton("Submit");
484 panel.add(submit);
485 submit.addActionListener(new ActionListener() {
486 public void actionPerformed(ActionEvent e)
487 {
488 try {
489
490 System.out.println(hostName.getText());
491 //checks if user exists
492 String query = "select host, user from mysql.user where host = '"+hostName.getText()+"' and user = '"+userName.getText()+"'";
493 Statement statement = conn.createStatement();
494 ResultSet rs1 = statement.executeQuery(query);
495 while (rs1.next()){
496 System.out.println("user exists");
497 final JPanel actionPanel = new JPanel();
498 panel.setVisible(false);
499 grantFrame.add(actionPanel);
500 grantFrame.setSize(650, 350);
501 // JLabel info1 = new JLabel("Please fill out only ONE (1) of the following forms.");
502 JLabel info2 = new JLabel("For both forms you can grant/revoke certain privledges in the following format: SELECT, INSERT, DELETE, CREATE, DROP");
503 // actionPanel.add(info1);
504 JLabel grant = new JLabel("This portion is for GRANTING privledges only:" +
505 " ");
506 final JTextField grantPriv = new JTextField("Enter Privledges to GRANT");
507
508 JLabel revoke = new JLabel("This portion is for REVOKING privledges only:" +
509 " ");
510 final JTextField revokePriv = new JTextField("Enter Privledges to REVOKE");
511 JButton submit2 = new JButton("Submit");
512
513 actionPanel.add(info2);
514 actionPanel.add(grant);
515 actionPanel.add(grantPriv);
516 actionPanel.add(revoke);
517 actionPanel.add(revokePriv);
518 actionPanel.add(submit2);
519 submit2.addActionListener(new ActionListener() {
520 public void actionPerformed (ActionEvent e) {
521 try {
522 String username = userName.getText();
523 String hostname = hostName.getText();
524 String grantpriv = grantPriv.getText();
525 String revokepriv = revokePriv.getText();
526
527 if (grantpriv.equals("")|| grantpriv.equals("Enter Privledges to GRANT")) {
528 Statement stmt = conn.createStatement();
529 boolean rs = stmt.execute("revoke "+revokepriv+" on mysql from '"+username+"'@'"+hostname+"'");
530 System.out.println("got to grantpriv if");
531
532 }
533 else if (revokepriv.equals("") || revokepriv.equals("Enter Privledges to REVOKE")) {
534 Statement stmt = conn.createStatement();
535 boolean rs = stmt.execute("grant "+grantpriv+" on mysql to '"+username+"'@'"+hostname+"'");
536 System.out.println("got to revoke priv if");
537
538 }
539 else if (grantpriv.equals("") && revokepriv.equals("")) {
540 actionPanel.setVisible(false);
541 final JPanel grantRevokePanel = new JPanel();
542 grantFrame.add(grantRevokePanel);
543 grantFrame.setSize(400,400);
544 JLabel errorLabel = new JLabel("You failed to insert any information at all. Please use the back button to enter information");
545 JButton back = new JButton("Go back to previous screen");
546 back.addActionListener(new ActionListener() {
547 public void actionPerformed (ActionEvent e) {
548 actionPanel.setVisible(true);
549
550 grantRevokePanel.setVisible(false);
551 }});
552 }
553
554 else {
555 Statement stmt = conn.createStatement();
556 int rs = stmt.executeUpdate("grant "+grantpriv+" on moviedb.* to '"+username+"'@'"+hostname+"'");
557 Statement stmt2 = conn.createStatement();
558 int rs2 = stmt2.executeUpdate("revoke "+revokepriv+" on moviedb.* from '"+username+"'@'"+hostname+"'");
559 }
560
561
562 }
563 catch (Exception e2) {
564 e2.printStackTrace();
565
566 }
567 }
568 });
569
570
571 }
572 // Student s = new Student(studentFirstName.getText(), studentLastName.getText(), menuID.getText(), pin.getText());
573 // menuPlus.getStudentCollection().addStudent(s);
574 // changes = true;
575
576
577 }
578 catch (Exception e2) {
579 e2.printStackTrace();
580
581 }
582 }
583
584 });
585 }
586
587
588 else{
589 System.out.println("Revoke certain access rights to existing user(s)");
590 }
591 }
592 else if (selection == 'u') {
593 loggedIn = false;
594
595
596 if (rs != null) {
597 try {
598 rs.close();
599 } catch (SQLException sqlEx) {
600 } // ignore
601
602 rs = null;
603 }
604
605 if (rsType != null) {
606 try {
607 rsType.close();
608 } catch (SQLException sqlEx) {
609 } // ignore
610
611 rsType = null;
612 }
613
614
615 if (stmt != null) {
616 try {
617 stmt.close();
618 } catch (SQLException sqlEx) {
619 } // ignore
620
621 stmt = null;
622 }
623
624 if (conn != null) {
625 try {
626 conn.close();
627 } catch (SQLException sqlEx) {
628 } // ignore
629
630 conn = null;
631 }
632
633 Class.forName("com.mysql.jdbc.Driver").newInstance();
634
635 while(loggedIn == false) {
636 System.out.println("Welcome to FabFlix! Please input user information below:");
637 //String username3 = Prompt.forString("Enter username");
638 //String password3 = Prompt.forString("Enter password");
639 String usernameDatabase1 = "root";
640 String passwordDatabase1 = "lakers";
641 String employeeEmail1 = Prompt.forString("Enter email");
642 String employeePassword1 = Prompt.forString("Enter password");
643 String queryLogin1 = "SELECT count(*) from employees where email = '" + employeeEmail1 + "' and password = '" + employeePassword1 +"'";
644 conn = DriverManager.getConnection(url, usernameDatabase1, passwordDatabase1);
645 stmt = conn.createStatement();
646 stmt1 = conn.createStatement();
647 ResultSet rsLogin1 = stmt.executeQuery(queryLogin1);
648
649 while(rsLogin1.next()) {
650 if (rsLogin1.getString(1).equals("1")) {
651 conn = DriverManager.getConnection(url, usernameDatabase1, passwordDatabase1);
652 stmt = conn.createStatement();
653 stmt1 = conn.createStatement();
654 loggedIn = true;
655 employeeEmail1 = null;
656 employeePassword1 = null;
657 break;
658 }
659
660 else
661 System.out.println("Wrong username/password combination. Please try again.");
662
663 }
664 }
665
666
667 }
668
669 else if (selection == 'x') {
670 boolean output = Prompt.forBoolean("Will this command return an output? (true or false)");
671 if (output == true) {
672 String select = Prompt.forString("SELECT");
673 String from = Prompt.forString("FROM");
674 String where = Prompt.forString("WHERE");
675 if (!where.isEmpty()){
676 rs = stmt.executeQuery("select " + select + " from " + from + " where " + where);
677 }
678 else {
679 rs = stmt.executeQuery("select " + select + " from " + from);
680 }
681
682 ResultSetMetaData rsmd = rs.getMetaData();
683 for (int i = 1; i <= rsmd.getColumnCount(); i++) {
684 System.out.print(rsmd.getColumnName(i) + "\t");
685 }
686 System.out.println();
687 System.out.println();
688 String t = "";
689 for (int i = 1; i<= rsmd.getColumnCount(); i++) {
690 String s = rsmd.getColumnName(i) + ", ";
691 t = t + s;
692 }
693 String s2 = t;
694 StringTokenizer st = new StringTokenizer(s2, ", "); // To grab the "new" attributes
695
696 while (rs.next()) {
697 while (st.hasMoreTokens()) {
698 Object o = rs.getObject(st.nextToken());
699 System.out.print(o + "\t");
700 }
701 st = new StringTokenizer(s2, ", ");
702 System.out.println();
703 }
704 }
705 else {
706 String option = Prompt.forString("Enter update, insert, or delete");
707 if (option.equals("update")) {
708 String update = Prompt.forString("UPDATE");
709 String set = Prompt.forString("SET");
710 String where = Prompt.forString("WHERE");
711 int updateExecute = stmt.executeUpdate("update " + update + " set " + set + " where " + where);
712 System.out.println("The information has been updated in the " + update+ " table");
713 }
714 else if (option.equals("insert")) {
715 String insertInto = Prompt.forString("INSERT INTO:");
716 rsType = stmt1.executeQuery("desc "+ insertInto);
717 String attributes ="(";
718 while(rsType.next()) {
719 String field = rsType.getString("field");
720 if (rsType.isLast())
721 attributes = attributes + field;
722 else
723 attributes = attributes + field + ", ";
724
725 }
726 String values = Prompt.forString("VALUES:");
727 int insertExecute = stmt.executeUpdate("insert into "+ insertInto+ attributes+")"+ " values" + values);
728 System.out.println("The information has been inserted into the " + insertInto+ " table");
729 }
730
731 else if (option.equals("delete")) {
732 String deleteFrom = Prompt.forString("DELETE FROM");
733 String where = Prompt.forString("WHERE");
734 int deleteExecute = stmt.executeUpdate("delete from " + deleteFrom + " where " + where);
735 System.out.println("The information inputted has deleted the corresponding records");
736 }
737 else
738 System.out.println("Error: You have entered a nonvalid option");
739 }
740 }
741
742
743 else if (selection == 'm') {
744 // Print Movie Metadata
745 System.out.println("Movies :");
746 rs = stmt.executeQuery("SELECT * FROM movies");
747 rsType = stmt1.executeQuery("desc movies"); // Data table about movie table
748 ResultSetMetaData rsmd = rs.getMetaData();
749 for (int i = 1; i <= rsmd.getColumnCount(); i++) {
750 System.out.print(rsmd.getColumnName(i));
751 if (rsType.next()) {
752 String type = rsType.getString("type");
753 System.out.println( " "+ "("+ type +")" + "\t");
754 }
755 }
756 System.out.println();
757
758 // Print Stars Metadata
759 System.out.println("Stars :");
760 rs = stmt.executeQuery("SELECT * FROM stars");
761 rsType = stmt1.executeQuery("desc stars");
762 ResultSetMetaData rsmd1 = rs.getMetaData();
763 for (int i = 1; i <= rsmd1.getColumnCount(); i++) {
764 System.out.print(rsmd1.getColumnName(i));
765 if (rsType.next()) {
766 String type = rsType.getString("type");
767 System.out.println( " "+ "("+ type +")" + "\t");
768 }
769 }
770 System.out.println();
771
772 // Print stars_in_movies metadata
773 System.out.println("stars_in_movies");
774 rs = stmt.executeQuery("SELECT * FROM stars_in_movies");
775 rsType = stmt1.executeQuery("desc stars_in_movies");
776 ResultSetMetaData rsmd2 = rs.getMetaData();
777 for (int i = 1; i <= rsmd2.getColumnCount(); i++) {
778 System.out.print(rsmd2.getColumnName(i));
779 if (rsType.next()) {
780 String type = rsType.getString("type");
781 System.out.println( " "+ "("+ type +")" + "\t");
782 }
783 }
784 System.out.println();
785
786 // Print genres metadata
787 System.out.println("genres");
788 rs = stmt.executeQuery("SELECT * FROM genres");
789 rsType = stmt1.executeQuery("desc genres");
790 ResultSetMetaData rsmd3 = rs.getMetaData();
791 for (int i = 1; i <= rsmd3.getColumnCount(); i++) {
792 System.out.print(rsmd3.getColumnName(i));
793 if (rsType.next()) {
794 String type = rsType.getString("type");
795 System.out.println( " "+ "("+ type +")" + "\t");
796 }
797 }
798 System.out.println();
799
800 // Print genres_in_movies metadata
801 System.out.println("genres_in_movies");
802 rs = stmt.executeQuery("SELECT * FROM genres_in_movies");
803 rsType = stmt1.executeQuery("desc genres_in_movies");
804 ResultSetMetaData rsmd4 = rs.getMetaData();
805 for (int i = 1; i <= rsmd4.getColumnCount(); i++) {
806 System.out.print(rsmd4.getColumnName(i));
807 if (rsType.next()) {
808 String type = rsType.getString("type");
809 System.out.println( " "+ "("+ type +")" + "\t");
810 }
811 }
812 System.out.println();
813
814 // Print customers metadata
815 System.out.println("customers");
816 rs = stmt.executeQuery("SELECT * FROM customers");
817 rsType = stmt1.executeQuery("desc customers");
818 ResultSetMetaData rsmd5 = rs.getMetaData();
819 for (int i = 1; i <= rsmd5.getColumnCount(); i++) {
820 System.out.print(rsmd5.getColumnName(i));
821 if (rsType.next()) {
822 String type = rsType.getString("type");
823 System.out.println( " "+ "("+ type +")" + "\t");
824 }
825 }
826 System.out.println();
827
828 // Print sales metadata
829 System.out.println("sales");
830 rs = stmt.executeQuery("SELECT * FROM sales");
831 rsType = stmt1.executeQuery("desc sales");
832 ResultSetMetaData rsmd6 = rs.getMetaData();
833 for (int i = 1; i <= rsmd6.getColumnCount(); i++) {
834 System.out.print(rsmd6.getColumnName(i));
835 if (rsType.next()) {
836 String type = rsType.getString("type");
837 System.out.println( " "+ "("+ type +")" + "\t");
838 }
839 }
840 System.out.println();
841
842 // Print creditcards metadata
843 System.out.println("creditcards");
844 rs = stmt.executeQuery("SELECT * FROM creditcards");
845 rsType = stmt1.executeQuery("desc creditcards");
846 ResultSetMetaData rsmd7 = rs.getMetaData();
847 for (int i = 1; i <= rsmd7.getColumnCount(); i++) {
848 System.out.print(rsmd7.getColumnName(i));
849 if (rsType.next()) {
850 String type = rsType.getString("type");
851 System.out.println( " "+ "("+ type +")" + "\t");
852 }
853 }
854 System.out.println();
855 }
856
857 else if (selection == 'z') {
858 break;
859 }
860 else if (selection == 'k') {
861 try{
862
863 // Create file
864
865 FileWriter fstream = new FileWriter("Error Report.html");
866
867 BufferedWriter out = new BufferedWriter(fstream);
868
869 out.write("<HTML> <HEAD> <TITLE>FabFlix Error Report</TITLE> </HEAD> <center> <BODY BGCOLOR=\"#FDF5E6\"> <H1 ALIGN=\"CENTER\">FabFlix Error Report</H1>");
870
871 //Query 1 is to find movies with no stars
872
873 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";
874
875
876
877 Statement statementq1 = conn.createStatement();
878
879 ResultSet rsq1 = statementq1.executeQuery(query1);
880
881 out.write("The following movie IDs and titles have no stars associated with them:");
882
883 out.write("<TABLE border>");
884
885 out.write("<tr> +" +
886
887 "<td> Number </td>" +
888
889 "<td> ID </td>" +
890
891 "<td> Title </td> " +
892
893 "</tr>" +
894
895 "");
896
897 while (rsq1.next()) {
898
899 String id = rsq1.getString("id");
900
901 String title = rsq1.getString("title");
902
903 out.write("<tr>" +
904
905 "<td>" + rsq1.getRow() + "</td>" +
906
907 "<td>" + id + "</td>" +
908
909 "<td>" + title + "</td>" +
910
911
912
913 "</tr>");
914
915 }
916
917 out.write("</TABLE>");
918
919 //Query 2 is to find stars without movies
920
921 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";
922
923 Statement statementq2 = conn.createStatement();
924
925 ResultSet rsq2 = statementq2.executeQuery(query2);
926
927 out.write("<br> The following star IDs and names have no movies associated with them:");
928
929 out.write("<TABLE border>");
930
931 out.write("<tr> +" +
932
933 "<td> Number </td>" +
934
935 "<td> ID </td>" +
936
937 "<td> First Name </td> " +
938
939 "<td> Last Name </td> " +
940
941 "</tr>");
942
943 while (rsq2.next()) {
944
945 String id = rsq2.getString("id");
946
947 String firstName = rsq2.getString("first_name");
948
949 String lastName = rsq2.getString("last_name");
950
951 out.write("<tr>" +
952
953 "<td>" + rsq2.getRow() + "</td>" +
954
955 "<td>" + id + "</td>" +
956
957 "<td>" + firstName + "</td>" +
958
959 "<td>" + lastName + "</td>" +
960
961
962
963
964
965 "</tr>");
966
967 }
968
969 out.write("</table>");
970
971 //Query 3 is to find genres without movies
972
973 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";
974
975 Statement statementq3 = conn.createStatement();
976
977 ResultSet rsq3 = statementq3.executeQuery(query3);
978
979 out.write("<br> The following genre IDs and names have no movies associated with them:");
980
981 out.write("<TABLE border>");
982
983 out.write("<tr> +" +
984
985 "<td> Number </td>" +
986
987 "<td> ID </td>" +
988
989 "<td> Genre Name </td> " +
990
991 "</tr>");
992
993 System.out.println();
994
995 while (rsq3.next()) {
996
997 String id = rsq3.getString("id");
998
999 String name = rsq3.getString("name");
1000
1001
1002
1003 out.write("<tr>" +
1004
1005 "<td>" + rsq3.getRow() + "</td>" +
1006
1007 "<td>" + id + "</td>" +
1008
1009 "<td>" + name + "</td>" +
1010
1011 "</tr>");
1012
1013 }
1014
1015 out.write("</table>");
1016
1017
1018
1019 //Query4 is to find stars with either no last name or no first name or both.
1020
1021 String query4 = "SELECT s.id, s.first_name, s.last_name FROM stars as s WHERE (s.last_name ='' or s.first_name = '')";
1022
1023 Statement statementq4 = conn.createStatement();
1024
1025 ResultSet rsq4 = statementq4.executeQuery(query4);
1026
1027 out.write("<br> The following star IDs have no first name or last name associated with them");
1028
1029 out.write("<TABLE border>");
1030
1031 out.write("<tr> +" +
1032
1033 "<td> Number </td>" +
1034
1035 "<td> ID </td>" +
1036
1037 "</tr>");
1038
1039 while (rsq4.next()) {
1040
1041 String id = rsq4.getString("id");
1042
1043 out.write("<tr>" +
1044
1045 "<td>" + rsq3.getRow() + "</td>" +
1046
1047 "<td>" + id + "</td>" +
1048
1049 "</tr>");
1050
1051 }
1052
1053 out.write("</table>");
1054
1055 //Query 5 will all the expired creditcards of existing customers
1056
1057 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'";
1058
1059 Statement statementq5 = conn.createStatement();
1060
1061 ResultSet rsq5 = statementq4.executeQuery(query5);
1062
1063 out.write("<br> The following customers have creditcards that have expired:");
1064
1065 out.write("<TABLE border>");
1066
1067 out.write("<tr> +" +
1068
1069 "<td> Number </td>" +
1070
1071 "<td> First Name </td> " +
1072
1073 "<td> Last Name </td> " +
1074
1075 "<td> ID </td>" +
1076
1077 "<td> Expiration Date </td>" +
1078
1079 "</tr>");
1080
1081 System.out.println();
1082
1083 while (rsq5.next()) {
1084
1085 String firstName = rsq5.getString("first_name");
1086
1087 String lastName = rsq5.getString("last_name");
1088
1089 String ccNum = rsq5.getString("id");
1090
1091 String exp = rsq5.getString("expiration");
1092
1093
1094
1095 out.write("<tr>" +
1096
1097 "<td>" + rsq5.getRow() + "</td>" +
1098
1099 "<td>" + firstName + "</td>" +
1100
1101 "<td>" + lastName + "</td>" +
1102
1103 "<td>" + ccNum + "</td>" +
1104
1105 "<td>" + exp + "</td>" +
1106
1107
1108
1109 "</tr>");
1110
1111 }
1112
1113 out.write("</table>");
1114
1115 //Query 6 will find all the duplicate movies
1116
1117 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";
1118
1119 Statement statement6 = conn.createStatement();
1120
1121 ResultSet rs6 = statement6.executeQuery(query6);
1122
1123 out.write("<br> The following shows duplicate movies along with their duplicate movie IDs:");
1124
1125 out.write("<TABLE border>");
1126
1127 out.write("<tr> +" +
1128
1129 "<td> Number </td>" +
1130
1131 "<td> Original ID </td>" +
1132
1133 "<td> Duplicate ID</td> " +
1134
1135 "<td> Movie Title </td> " +
1136
1137 "<td> Year </td>" +
1138
1139 "</tr>");
1140
1141 System.out.println("");
1142
1143 while (rs6.next()) {
1144
1145 String id1 = rs6.getString(1);
1146
1147 String id2 = rs6.getString(2);
1148
1149 String title = rs6.getString(3);
1150
1151 String year = rs6.getString(4);
1152
1153
1154
1155
1156
1157 out.write("<tr>" +
1158
1159 "<td>" + rs6.getRow() + "</td>" +
1160
1161 "<td>" + id1 + "</td>" +
1162
1163 "<td>" + id2 + "</td>" +
1164
1165 "<td>" + title + "</td>" +
1166
1167 "<td>" + year + "</td>" +
1168
1169 "</tr>");
1170
1171 }
1172
1173 out.write("</table>");
1174
1175
1176 //Query 7 will return duplicate stars
1177
1178 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";
1179
1180 Statement statement7 = conn.createStatement();
1181
1182 ResultSet rs7 = statement7.executeQuery(query7);
1183
1184 out.write("<br> The following shows duplicate stars and their duplicate star IDs:");
1185
1186 out.write("<TABLE border>");
1187
1188 out.write("<tr> +" +
1189
1190 "<td> Number </td>" +
1191
1192 "<td> Original ID </td>" +
1193
1194 "<td> Duplicate ID </td> " +
1195
1196 "<td> First Name </td>" +
1197
1198 "<td> Last Name </td> " +
1199
1200 "<td> DOB </td>"+
1201
1202 "</tr>");
1203
1204 while (rs7.next()) {
1205
1206 String id1 = rs7.getString(1);
1207
1208 String id2 = rs7.getString(2);
1209
1210 String firstname = rs7.getString(3);
1211
1212 String lastname = rs7.getString(4);
1213
1214 String dob = rs7.getString(5);
1215
1216
1217
1218 out.write("<tr>" +
1219
1220 "<td>" + rs7.getRow() + "</td>" +
1221
1222 "<td>" + id1 + "</td>" +
1223
1224 "<td>" + id2 + "</td>" +
1225
1226 "<td>" + firstname + "</td>" +
1227
1228 "<td>" + lastname + "</td>" +
1229
1230 "<td>" + dob + "</td>" +
1231
1232 "</tr>");
1233
1234 }
1235
1236 out.write("</table>");
1237
1238 //Query 9 will return stars with birth dates greater than today's date or less than the year 1900
1239
1240 String query9 = "select dob, id, first_name, last_name from stars where (dob < '1900-12-31' or dob > '2011-02-10')";
1241
1242 Statement statement9 = conn.createStatement();
1243
1244 ResultSet rsq9 = statement9.executeQuery(query9);
1245
1246 out.write("<br> The following displays stars with a DOB greater than today's date or less than the year 1900:");
1247
1248 out.write("<TABLE border>");
1249
1250 out.write("<tr> +" +
1251
1252 "<td> Number </td>" +
1253
1254 "<td> DOB </td>" +
1255
1256 "<td> Star ID </td>" +
1257
1258 "<td> First Name </td> " +
1259
1260 "<td> Last Name </td> " +
1261
1262
1263
1264 "</tr>");
1265
1266
1267 while (rsq9.next()) {
1268
1269 String dob = rsq9.getString(1);
1270
1271 String id = rsq9.getString(2);
1272
1273 String firstName = rsq9.getString(3);
1274
1275 String lastName = rsq9.getString(4);
1276
1277
1278
1279
1280
1281 out.write("<tr>" +
1282
1283 "<td>" + rsq9.getRow() + "</td>" +
1284
1285 "<td>" + dob + "</td>" +
1286
1287 "<td>" + id + "</td>" +
1288
1289 "<td>" + firstName + "</td>" +
1290
1291 "<td>" + lastName + "</td>" +
1292
1293
1294
1295 "</tr>");
1296
1297 }
1298
1299 out.write("</table>");
1300
1301 //Query 8 will return duplicate genres
1302
1303 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";
1304
1305 Statement statementq8 = conn.createStatement();
1306
1307 ResultSet rsq8 = statementq3.executeQuery(query8);
1308
1309 out.write("<br> The following displays duplicate genres and their duplicate IDs:");
1310
1311 out.write("<TABLE border>");
1312
1313 out.write("<tr> +" +
1314
1315 "<td> Number </td>" +
1316
1317 "<td> Original ID </td>" +
1318
1319 "<td> Duplicate ID </td>" +
1320
1321 "<td> Genre Name </td> " +
1322
1323 "</tr>");
1324
1325
1326 while (rsq8.next()) {
1327
1328 String id1 = rsq8.getString(1);
1329
1330 String id2 = rsq8.getString(2);
1331
1332 String name = rsq8.getString(3);
1333
1334
1335
1336 out.write("<tr>" +
1337
1338 "<td>" + rsq8.getRow() + "</td>" +
1339
1340 "<td>" + id1 + "</td>" +
1341
1342 "<td>" + id2 + "</td>" +
1343
1344 "<td>" + name + "</td>" +
1345
1346 "</tr>");
1347
1348 }
1349
1350 out.write("</table>");
1351 out.write(" </CENTER> </FORM> </BODY> </HTML>");
1352
1353 System.out.println("You can find the Error Report in HTML format in the source folder of this project.");
1354
1355 System.out.println("The file name is Error Report.html.");
1356
1357 }catch (Exception e){//Catch exception if any
1358
1359 System.err.println("Error: " + e.getMessage());
1360
1361 }
1362
1363 }
1364 else
1365 System.out.println(selection + " is an unknown command") ;
1366
1367 } }
1368
1369 catch (Exception e) {
1370 e.printStackTrace();
1371
1372 }
1373
1374 }}