API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Jul 21st, 2017
128
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 45.75 KB | None | 0 0
raw download clone embed print report
  1. package JDBCProject1;
  2.  
  3. import java.sql.*;
  4. import edu.uci.ics.pattis.introlib.*;
  5. import java.util.*;
  6. import java.awt.event.ActionEvent;
  7. import java.awt.event.ActionListener;
  8. import java.awt.event.KeyEvent;
  9. import java.io.*;
  10. import javax.swing.*;
  11.  
  12.  
  13.  
  14.  
  15. public 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. }}
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!