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