Advertisement
Guest User

Untitled

a guest
May 18th, 2017
95
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 28.17 KB | None | 0 0
  1. // to compile this program you need to include hb15.zip
  2. // in the CLASSPATH environment variable
  3.  
  4. // We need to import the java.sql package to use JDBC
  5. import java.sql.*;
  6. import java.util.Vector;
  7.  
  8. // for reading from the command line
  9. import java.io.*;
  10.  
  11. // for the login window
  12. import javax.swing.*;
  13. import java.awt.*;
  14. import java.awt.event.*;
  15.  
  16. // for Format.printf() and Parameters.add()
  17. import com.braju.format.*;
  18.  
  19.  
  20. /*
  21. * This class implements a graphical login window and a simple text
  22. * interface for interacting with the branch table
  23. */
  24. public class testtickles implements ActionListener
  25. {
  26. // command line reader
  27. private BufferedReader in = new BufferedReader(new InputStreamReader(System.in));
  28.  
  29. private Connection con;
  30.  
  31. // user is allowed 3 login attempts
  32. private int loginAttempts = 0;
  33.  
  34. // components of the login window
  35. private JTextField usernameField;
  36. private JPasswordField passwordField;
  37. private JFrame mainFrame;
  38.  
  39.  
  40. /*
  41. * constructs login window and loads JDBC driver
  42. */
  43. public testtickles()
  44. {
  45. mainFrame = new JFrame("User Login");
  46.  
  47. JLabel usernameLabel = new JLabel("Enter username: ");
  48. JLabel passwordLabel = new JLabel("Enter password: ");
  49.  
  50. usernameField = new JTextField(10);
  51. passwordField = new JPasswordField(10);
  52. passwordField.setEchoChar('*');
  53.  
  54. JButton loginButton = new JButton("Log In");
  55.  
  56. JPanel contentPane = new JPanel();
  57. mainFrame.setContentPane(contentPane);
  58.  
  59.  
  60. // layout components using the GridBag layout manager
  61.  
  62. GridBagLayout gb = new GridBagLayout();
  63. GridBagConstraints c = new GridBagConstraints();
  64.  
  65. contentPane.setLayout(gb);
  66. contentPane.setBorder(BorderFactory.createEmptyBorder(10, 10, 10, 10));
  67.  
  68. // place the username label
  69. c.gridwidth = GridBagConstraints.RELATIVE;
  70. c.insets = new Insets(10, 10, 5, 0);
  71. gb.setConstraints(usernameLabel, c);
  72. contentPane.add(usernameLabel);
  73.  
  74. // place the text field for the username
  75. c.gridwidth = GridBagConstraints.REMAINDER;
  76. c.insets = new Insets(10, 0, 5, 10);
  77. gb.setConstraints(usernameField, c);
  78. contentPane.add(usernameField);
  79.  
  80. // place password label
  81. c.gridwidth = GridBagConstraints.RELATIVE;
  82. c.insets = new Insets(0, 10, 10, 0);
  83. gb.setConstraints(passwordLabel, c);
  84. contentPane.add(passwordLabel);
  85.  
  86. // place the password field
  87. c.gridwidth = GridBagConstraints.REMAINDER;
  88. c.insets = new Insets(0, 0, 10, 10);
  89. gb.setConstraints(passwordField, c);
  90. contentPane.add(passwordField);
  91.  
  92. // place the login button
  93. c.gridwidth = GridBagConstraints.REMAINDER;
  94. c.insets = new Insets(5, 10, 10, 10);
  95. c.anchor = GridBagConstraints.CENTER;
  96. gb.setConstraints(loginButton, c);
  97. contentPane.add(loginButton);
  98.  
  99. // register password field and OK button with action event handler
  100. passwordField.addActionListener(this);
  101. loginButton.addActionListener(this);
  102.  
  103. // anonymous inner class for closing the window
  104. mainFrame.addWindowListener(new WindowAdapter()
  105. {
  106. public void windowClosing(WindowEvent e)
  107. {
  108. System.exit(0);
  109. }
  110. });
  111.  
  112. // size the window to obtain a best fit for the components
  113. mainFrame.pack();
  114.  
  115. // center the frame
  116. Dimension d = mainFrame.getToolkit().getScreenSize();
  117. Rectangle r = mainFrame.getBounds();
  118. mainFrame.setLocation( (d.width - r.width)/2, (d.height - r.height)/2 );
  119.  
  120. // make the window visible
  121. mainFrame.setVisible(true);
  122.  
  123. // place the cursor in the text field for the username
  124. usernameField.requestFocus();
  125.  
  126. try
  127. {
  128. // Load the Oracle JDBC driver
  129. DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
  130. }
  131. catch (SQLException ex)
  132. {
  133. System.out.println("Message: " + ex.getMessage());
  134. System.exit(-1);
  135. }
  136. }
  137.  
  138.  
  139. /*
  140. * connects to Oracle database named ug using user supplied username and password
  141. */
  142. private boolean connect(String username, String password)
  143. {
  144. // String connectURL = "jdbc:oracle:thin:@dbhost.ugrad.cs.ubc.ca:1521:ug";
  145. String connectURL = "jdbc:oracle:thin:@localhost:1521:ug";
  146.  
  147. try
  148. {
  149. con = DriverManager.getConnection(connectURL,username,password);
  150.  
  151. System.out.println("\nConnected to Oracle!");
  152. return true;
  153. }
  154. catch (SQLException ex)
  155. {
  156. System.out.println("Message: " + ex.getMessage());
  157. return false;
  158. }
  159. }
  160.  
  161.  
  162. /*
  163. * event handler for login window
  164. */
  165. public void actionPerformed(ActionEvent e)
  166. {
  167. if ( connect(usernameField.getText(), String.valueOf(passwordField.getPassword())) )
  168. {
  169. // if the username and password are valid,
  170. // remove the login window and display a text menu
  171. mainFrame.dispose();
  172. showMenu();
  173. }
  174. else
  175. {
  176. loginAttempts++;
  177.  
  178. if (loginAttempts >= 3)
  179. {
  180. mainFrame.dispose();
  181. System.exit(-1);
  182. }
  183. else
  184. {
  185. // clear the password
  186. passwordField.setText("");
  187. }
  188. }
  189.  
  190. }
  191.  
  192.  
  193. /*
  194. * displays simple text interface
  195. */
  196. private void showMenu()
  197. {
  198. int choice;
  199. boolean quit;
  200.  
  201. quit = false;
  202.  
  203. try
  204. {
  205. // disable auto commit mode
  206. con.setAutoCommit(false);
  207.  
  208. while (!quit)
  209. {
  210. System.out.print("\n\nPlease choose one of the following: \n");
  211. System.out.print("1. Insert Person\n");
  212. System.out.print("2. Delete Person\n");
  213. System.out.print("3. Update Person\n");
  214. System.out.print("4. showReservations\n");
  215. System.out.print("5. insertReservation\n");
  216. System.out.print("6. Quit\n>> ");
  217.  
  218. choice = Integer.parseInt(in.readLine());
  219.  
  220. System.out.println(" ");
  221.  
  222. switch(choice)
  223. {
  224.  
  225.  
  226. }
  227. }
  228.  
  229. con.close();
  230. in.close();
  231. System.out.println("\nGood Bye!\n\n");
  232. System.exit(0);
  233. }
  234. catch (IOException e)
  235. {
  236. System.out.println("IOException!");
  237.  
  238. try
  239. {
  240. con.close();
  241. System.exit(-1);
  242. }
  243. catch (SQLException ex)
  244. {
  245. System.out.println("Message: " + ex.getMessage());
  246. }
  247. }
  248. catch (SQLException ex)
  249. {
  250. System.out.println("Message: " + ex.getMessage());
  251. }
  252. }
  253.  
  254. /* Select the Date, Time and Price of the Cheapest Ticket from ___ to ___ that departs between ____ and ____.
  255. SELECT fd.Depart_Timestamp, t.ticket_price
  256. FROM Ticket t, TicketForSeat ts, Seat s, FlightDeparture fd, FlightArrival fa
  257. WHERE t.Ticket_ID = ts.Ticket_ID AND ts.Seat_No = s.Seat_No AND
  258. s.Flight_No = fd.Flight_No AND s.Flight_No = fa.Flight_No AND
  259. fd.Airport_ID = 'YVR' AND fa.Airport_ID = 'SEA' AND
  260. fd.Depart_Timestamp < '01-JAN-2012 12:59:59' AND
  261. fd.Depart_Timestamp > '01-JAN-2011 01:00:00' AND
  262. s.IsTaken = 0 AND t.ticket_price ( SELECT MIN(t1.ticket_price)
  263. FROM Ticket t1) ; */
  264.  
  265. private tableData cheapestFlight(
  266. String DepartureAirport,
  267. String DeptFromTimeStamp,
  268. String DeptToTimeStamp,
  269. String ArrivalAirport){
  270.  
  271. Vector<String> cheapestFlight = new Vector<String>();
  272. int columnCount=5;
  273. int rowCount=0;
  274.  
  275. Statement stmt;
  276. ResultSet rs;
  277.  
  278. try
  279. {
  280.  
  281. stmt = con.createStatement();
  282.  
  283. rs = stmt.executeQuery("SELECT fd.Depart_Timestamp, fd. Airport_ID, fa.Arrive_Timestamp, fa.Airport_ID, DISTINCT t.ticket_price, s.IsBusinessClass"+
  284. "FROM Ticket t, TicketForSeat ts, Seat s, FlightDeparture fd, FlightArrival fa"+ // From
  285. "WHERE t.Ticket_ID = ts.Ticket_ID " + // Where
  286. "AND ts.Seat_No = s.Seat_No AND" +
  287. "s.Flight_No = fd.Flight_No AND " +
  288. "s.Flight_No = fa.Flight_No AND" +
  289. "fd.Airport_ID = "+DepartureAirport+" AND" +
  290. "fa.Airport_ID = "+ArrivalAirport +" AND"+
  291. "fd.Depart_Timestamp >"+DeptToTimeStamp+"AND"+
  292. "fd.Depart_Timestamp < "+DeptToTimeStamp+"AND"+
  293. "s.IsTaken = 0 AND t.ticket_price ( SELECT MIN(t1.ticket_price)FROM Ticket t1") ;
  294.  
  295.  
  296. ResultSetMetaData rsmd1 = rs.getMetaData();
  297.  
  298. while(rs.next())
  299. {
  300. for(int i=1;i<columnCount+1;i++){
  301. cheapestFlight .add(rs.getString(i));
  302. }
  303. rowCount++;
  304. }
  305.  
  306. }
  307. catch (SQLException ex)
  308. {
  309. System.out.println("Message: " + ex.getMessage());
  310.  
  311. try
  312. {
  313. con.rollback();
  314. }
  315. catch (SQLException ex2)
  316. {
  317. System.out.println("Message: " + ex2.getMessage());
  318. System.exit(-1);
  319. }
  320. }
  321.  
  322. String tableName = "cheapestFlight";
  323.  
  324. tableData cheapestFlightData= new tableData(tableName, columnCount, rowCount, cheapestFlight);
  325. return cheapestFlightData;
  326. }
  327.  
  328. /* Deleting a Ticket actually sets the Ticket's Canceled status to True (1).
  329. *
  330. */
  331. private void deleteTicket(String Ticket_ID) {
  332.  
  333. PreparedStatement psDeleteTicket;
  334. try
  335. {
  336. psDeleteTicket = con.prepareStatement("UPDATE Ticket T SET T.Cancelled="+1+"WHERE T.TicketID="+Ticket_ID);
  337. psDeleteTicket.executeUpdate();
  338. con.commit();
  339. psDeleteTicket.close();
  340. }
  341. catch (SQLException ex)
  342. {
  343. System.out.println("Message: " + ex.getMessage());
  344.  
  345. try
  346. {
  347. con.rollback();
  348. }
  349. catch (SQLException ex2)
  350. {
  351. System.out.println("Message: " + ex2.getMessage());
  352. System.exit(-1);
  353. }
  354. }
  355.  
  356. }
  357.  
  358. /* Find the Number of Empty Seats Remaining of a Particular Class on a Particular Flight
  359. SELECT COUNT(Seat.Seat_No)
  360. FROM Seat
  361. WHERE Seat.Flight_No = 102 AND Seat.IsBusinessClass = 1 AND Seat.IsTaken = 0 */
  362.  
  363. private int emptySeatsonFlight(int Flight_No, int isBusinessClass){
  364. int SeatsRemaining = 0;
  365.  
  366. Statement stmt;
  367. ResultSet rs;
  368. try
  369. {
  370.  
  371. stmt = con.createStatement();
  372. rs = stmt.executeQuery("SELECT COUNT(Seat.Seat_No) FROM Seat WHERE Seat.Flight_No ="+Flight_No+"( AND isBusinessClass ="+ isBusinessClass +" AND Seat.IsTaken = 0");
  373.  
  374. ResultSetMetaData rsmd1 = rs.getMetaData();
  375. SeatsRemaining=Integer.parseInt(rs.getString(1));
  376.  
  377.  
  378. }
  379. catch (SQLException ex)
  380. {
  381. System.out.println("Message: " + ex.getMessage());
  382.  
  383. try
  384. {
  385. con.rollback();
  386. }
  387. catch (SQLException ex2)
  388. {
  389. System.out.println("Message: " + ex2.getMessage());
  390. System.exit(-1);
  391. }
  392. }
  393. return SeatsRemaining;
  394. }
  395.  
  396.  
  397. /*Finished Above*/
  398. /*Returns the a String Array of results*/
  399. private tableData queryFlights(
  400. String DepartureAirport,
  401. String DeptFromTimeStamp, String DeptToTimeStamp,
  402. String ArrivalAirport, String ArriveFromTimeStamp, String ArriveToTimeStamp,
  403. int SortByPrice,
  404. int BusinessClassQuery,
  405. int EconomyClassQuery,
  406. int CheapestTicketQuery){
  407.  
  408. Vector<String> FlightsResult = new Vector<String>();
  409. Vector<String> FlightsResultSortByPrice = new Vector<String>();
  410. Vector<String> FlightsResultBusiness = new Vector<String>();
  411. Vector<String> FlightsResultEconomy = new Vector<String>();
  412. Vector<String> FlightsResultCheapest = new Vector<String>();
  413.  
  414. Statement stmt1;
  415. Statement stmt2;
  416. Statement stmt3;
  417. Statement stmt4;
  418. Statement stmt5;
  419.  
  420. ResultSet rs1;
  421. ResultSet rs2;
  422. ResultSet rs3;
  423. ResultSet rs4;
  424. ResultSet rs5;
  425.  
  426. int columnCount_1=0;
  427. int columnCount_2=0;
  428. int columnCount_3=0;
  429. int columnCount_4=0;
  430. int columnCount_5=0;
  431.  
  432. int rowCount1=0;
  433. int rowCount2=0;
  434. int rowCount3=0;
  435. int rowCount4=0;
  436. int rowCount5=0;
  437.  
  438. try
  439. {
  440.  
  441. stmt1 = con.createStatement();
  442. stmt2 = con.createStatement();
  443. stmt3 = con.createStatement();
  444. stmt4 = con.createStatement();
  445. stmt5 = con.createStatement();
  446.  
  447. /*QuickSort*/
  448. rs1 = stmt1.executeQuery("SELECT fd.Depart_Timestamp, fd. Airport_ID, fa.Arrive_Timestamp, fa.Airport_ID, DISTINCT t.ticket_price, s.IsBusinessClass"+
  449. "FROM Ticket t, TicketForSeat ts, Seat s, FlightDeparture fd, FlightArrival fa"+ // From
  450. "WHERE t.Ticket_ID = ts.Ticket_ID " + // Where
  451. "AND ts.Seat_No = s.Seat_No AND" +
  452. "s.Flight_No = fd.Flight_No AND " +
  453. "s.Flight_No = fa.Flight_No AND" +
  454. "fd.Airport_ID = "+DepartureAirport+" AND" +
  455. "fa.Airport_ID = "+ArrivalAirport+" AND"+
  456. "fa.Arrive_Timestamp > "+ ArriveFromTimeStamp+"AND"+
  457. "fa.Arrive_Timestamp < "+ArriveToTimeStamp+"AND"+
  458. "fd.Depart_Timestamp >"+DeptFromTimeStamp+"AND"+
  459. "fd.Depart_Timestamp < "+DeptToTimeStamp+"AND"+
  460. "s.IsTaken = 0");
  461.  
  462. /*Sort by Price */
  463. rs2 = stmt2.executeQuery("SELECT fd.Depart_Timestamp, fd. Airport_ID, fa.Arrive_Timestamp, fa.Airport_ID, DISTINCT t.ticket_price, s.IsBusinessClass"+
  464. "FROM Ticket t, TicketForSeat ts, Seat s, FlightDeparture fd, FlightArrival fa"+ // From
  465. "WHERE t.Ticket_ID = ts.Ticket_ID " + // Where
  466. "AND ts.Seat_No = s.Seat_No AND" +
  467. "s.Flight_No = fd.Flight_No AND " +
  468. "s.Flight_No = fa.Flight_No AND" +
  469. "fd.Airport_ID = "+DepartureAirport+" AND" +
  470. "fa.Airport_ID = "+ArrivalAirport+" AND"+
  471. "fa.Arrive_Timestamp > "+ ArriveFromTimeStamp+"AND"+
  472. "fa.Arrive_Timestamp < "+ArriveToTimeStamp+"AND"+
  473. "fd.Depart_Timestamp >"+DeptFromTimeStamp+"AND"+
  474. "fd.Depart_Timestamp < "+DeptToTimeStamp+"AND"+
  475. "s.IsTaken = 0 SORT BY t.ticket_price ");
  476.  
  477. /*Business Class Only */
  478. rs3 = stmt3.executeQuery("SELECT fd.Depart_Timestamp, fd. Airport_ID, fa.Arrive_Timestamp, fa.Airport_ID, DISTINCT t.ticket_price, s.IsBusinessClass"+
  479. "FROM Ticket t, TicketForSeat ts, Seat s, FlightDeparture fd, FlightArrival fa"+ // From
  480. "WHERE t.Ticket_ID = ts.Ticket_ID " + // Where
  481. "AND ts.Seat_No = s.Seat_No AND" +
  482. "s.Flight_No = fd.Flight_No AND " +
  483. "s.Flight_No = fa.Flight_No AND" +
  484. "fd.Airport_ID = "+DepartureAirport+" AND" +
  485. "fa.Airport_ID = "+ArrivalAirport+" AND"+
  486. "fa.Arrive_Timestamp > "+ ArriveFromTimeStamp+"AND"+
  487. "fa.Arrive_Timestamp < "+ArriveToTimeStamp+"AND"+
  488. "fd.Depart_Timestamp >"+DeptFromTimeStamp+"AND"+
  489. "fd.Depart_Timestamp < "+DeptToTimeStamp+"AND"+
  490. "s.IsTaken = 0 AND s.IsBusinessClass = 1 SORT BY t.ticket_price ");
  491.  
  492. /*Economy Class Only */
  493. rs4 = stmt4.executeQuery("SELECT fd.Depart_Timestamp, fd. Airport_ID, fa.Arrive_Timestamp, fa.Airport_ID, DISTINCT t.ticket_price, s.IsBusinessClass"+
  494. "FROM Ticket t, TicketForSeat ts, Seat s, FlightDeparture fd, FlightArrival fa"+ // From
  495. "WHERE t.Ticket_ID = ts.Ticket_ID " + // Where
  496. "AND ts.Seat_No = s.Seat_No AND" +
  497. "s.Flight_No = fd.Flight_No AND " +
  498. "s.Flight_No = fa.Flight_No AND" +
  499. "fd.Airport_ID = "+DepartureAirport+" AND" +
  500. "fa.Airport_ID = "+ArrivalAirport+" AND"+
  501. "fa.Arrive_Timestamp > "+ ArriveFromTimeStamp+"AND"+
  502. "fa.Arrive_Timestamp < "+ArriveToTimeStamp+"AND"+
  503. "fd.Depart_Timestamp >"+DeptFromTimeStamp+"AND"+
  504. "fd.Depart_Timestamp < "+DeptToTimeStamp+"AND"+
  505. "s.IsTaken = 0 AND s.IsBusinessClass = 0 SORT BY t.ticket_price ");
  506.  
  507. rs5 = stmt5.executeQuery("SELECT fd.Depart_Timestamp, fd. Airport_ID, fa.Arrive_Timestamp, fa.Airport_ID, DISTINCT t.ticket_price, s.IsBusinessClass" +
  508. "WHERE Ticket_Price = (SELECT MAX(ticket_price) FROM ticket");
  509.  
  510. ResultSetMetaData rsmd1 = rs1.getMetaData();
  511. ResultSetMetaData rsmd2 = rs2.getMetaData();
  512. ResultSetMetaData rsmd3 = rs3.getMetaData();
  513. ResultSetMetaData rsmd4 = rs4.getMetaData();
  514. ResultSetMetaData rsmd5 = rs5.getMetaData();
  515.  
  516. columnCount_1 = rsmd1.getColumnCount();
  517. columnCount_2 = rsmd2.getColumnCount();
  518. columnCount_3 = rsmd3.getColumnCount();
  519. columnCount_4 = rsmd4.getColumnCount();
  520. columnCount_5 = rsmd5.getColumnCount();
  521.  
  522. while(rs1.next())
  523. {
  524. for(int i=1;i<columnCount_1+1;i++){
  525. FlightsResult.add(rs1.getString(i));
  526. rowCount1++;
  527. }
  528. }
  529.  
  530. while(rs2.next())
  531. {
  532. for(int i=1;i<columnCount_2+1;i++){
  533. FlightsResultSortByPrice.add(rs2.getString(i));
  534. rowCount2++;
  535. }
  536. }
  537.  
  538. while(rs3.next())
  539. {
  540. for(int i=1;i<columnCount_3+1;i++){
  541. FlightsResultBusiness.add(rs3.getString(i));
  542. rowCount3++;
  543. }
  544. }
  545.  
  546. while(rs4.next())
  547. {
  548. for(int i=1;i<columnCount_4+1;i++){
  549. FlightsResultEconomy.add(rs4.getString(i));
  550. rowCount4++;
  551. }
  552. }
  553.  
  554. while(rs5.next())
  555. {
  556. for(int i=1;i<columnCount_5+1;i++){
  557. FlightsResultCheapest.add(rs4.getString(i));
  558. rowCount5++;
  559. }
  560. }
  561.  
  562. }
  563. catch (SQLException ex)
  564. {
  565. System.out.println("Message: " + ex.getMessage());
  566.  
  567. try
  568. {
  569. con.rollback();
  570. }
  571. catch (SQLException ex2)
  572. {
  573. System.out.println("Message: " + ex2.getMessage());
  574. System.exit(-1);
  575. }
  576. }
  577.  
  578. if(SortByPrice==1){
  579. String tableName = "FlightsResultSortByPrice";
  580. tableData FlightsResutSortByPriceData= new tableData(tableName, columnCount_2, rowCount2, FlightsResultSortByPrice);
  581. return FlightsResutSortByPriceData;
  582. }
  583. else if(BusinessClassQuery==1){
  584. String tableName = "BusinessClassQuery";
  585. tableData BusinessClassQueryData= new tableData(tableName, columnCount_3, rowCount3, FlightsResultBusiness);
  586. return BusinessClassQueryData;
  587. }
  588. else if(EconomyClassQuery==1){
  589. String tableName = "EconomyClassQuery";
  590. tableData EconomyClassData = new tableData(tableName, columnCount_4, rowCount4, FlightsResultEconomy);
  591. return EconomyClassData;
  592. }
  593. else if(EconomyClassQuery==1){
  594. String tableName = "EconomyClassQuery";
  595. tableData EconomyClassData = new tableData(tableName, columnCount_4, rowCount4, FlightsResultEconomy);
  596. return EconomyClassData;
  597. }
  598. else if(EconomyClassQuery==1){
  599. String tableName = "EconomyClassQuery";
  600. tableData EconomyClassData = new tableData(tableName, columnCount_4, rowCount4, FlightsResultEconomy);
  601. return EconomyClassData;
  602. }
  603. else{
  604. String tableName = "FlightsResult";
  605. tableData FlightsResultData= new tableData(tableName, columnCount_1, rowCount1, FlightsResult);
  606. return FlightsResultData;
  607. }
  608. }
  609.  
  610. private void deleteFlight(int Flight_No) {
  611. PreparedStatement psDeleteFlight;
  612. PreparedStatement psUpdateTickets;
  613.  
  614. try
  615. {
  616. psDeleteFlight = con.prepareStatement("DELETE FROM Flight WHERE Flight_No ="+Flight_No);
  617.  
  618. psUpdateTickets = con.prepareStatement("UPDATE Ticket T SET T.Cancelled="+1+"WHERE T.TicketID= (SELECT T2.TicketID"+
  619. "FROM Ticket T2, TicketForSeat, Seat, FlightDeparture, FlightArrival"+
  620. "WHERE T2.TicketID = TicketForSeat.TicketID AND"+
  621. "TicketForSeat.Seat_No = Seat.Seat_No AND TicketForSeat.Flight_No = Seat.Flight_No AND AND Seat.Flight_No = "+Flight_No+" AND Seat.Flight_No = FlightDeparture.Flight_No AND Seat.Flight_No = FlightArrival.Flight_No");
  622.  
  623. psDeleteFlight.executeUpdate();
  624. psUpdateTickets.executeUpdate();
  625.  
  626. con.commit();
  627. psDeleteFlight.close();
  628. psUpdateTickets.close();
  629. }
  630. catch (SQLException ex)
  631. {
  632. System.out.println("Message: " + ex.getMessage());
  633.  
  634. try
  635. {
  636. con.rollback();
  637. }
  638. catch (SQLException ex2)
  639. {
  640. System.out.println("Message: " + ex2.getMessage());
  641. System.exit(-1);
  642. }
  643. }
  644. }
  645.  
  646. /* SELECT Name, Email, PhoneNum, DOB
  647. FROM Person, TicketForPerson tp, TickerForSeat ts, Seat s
  648. WHERE Person.cid = tp.cid AND tp.ticket_ID = ts.ticket_ID AND ts.seat_no = s.seat_no AND s.isbusinessclass = 1 ;
  649. */
  650. public tableData firstClassPassengers(){
  651.  
  652. Vector<String> firstClass = new Vector<String>();
  653. int columnCount = 0;
  654. int rowCount=0;
  655.  
  656. Statement stmt;
  657. ResultSet rs;
  658.  
  659. try
  660. {
  661.  
  662. stmt = con.createStatement();
  663. rs = stmt.executeQuery("SELECT Name, Email, PhoneNum, DOB " +
  664. "FROM Person, TicketForPerson tp, TickerForSeat ts, Seat s " +
  665. "WHERE Person.cid = tp.cid AND tp.ticket_ID = ts.ticket_ID AND ts.seat_no = s.seat_no AND s.isbusinessclass = 1");
  666.  
  667. ResultSetMetaData rsmd = rs.getMetaData();
  668. columnCount = rsmd.getColumnCount();
  669.  
  670. while(rs.next())
  671. {
  672. for(int i=1;i<columnCount+1;i++){
  673. firstClass .add(rs.getString(i));
  674. }
  675. rowCount++;
  676. }
  677.  
  678. }
  679. catch (SQLException ex)
  680. {
  681. System.out.println("Message: " + ex.getMessage());
  682.  
  683. try
  684. {
  685. con.rollback();
  686. }
  687. catch (SQLException ex2)
  688. {
  689. System.out.println("Message: " + ex2.getMessage());
  690. System.exit(-1);
  691. }
  692. }
  693.  
  694. String tableName = "firstClass";
  695. tableData firstClassData= new tableData(tableName, columnCount, rowCount, firstClass);
  696. return firstClassData;
  697. }
  698.  
  699.  
  700. }
  701.  
  702. private void insertPerson(int CID, String Name, String PhoneNum, String DOB, String Password, String Email, int isAdmin)
  703. {
  704.  
  705. PreparedStatement ps;
  706.  
  707. try
  708. {
  709. ps = con.prepareStatement("INSERT INTO Person VALUES (?,?,?,?,?,?,?)");
  710.  
  711. ps.setInt(1, CID); // This must be ps.setInt
  712. ps.setString(2, Name);
  713. ps.setString(3, PhoneNum);
  714. ps.setString(4, DOB);
  715. ps.setString(5, Password);
  716. ps.setString(6, Email);
  717. ps.setInt(6, isAdmin);
  718.  
  719. ps.executeUpdate();
  720. con.commit();
  721.  
  722. ps.close();
  723. }
  724. catch (SQLException ex)
  725. {
  726. System.out.println("Message: " + ex.getMessage());
  727. try
  728. {
  729. // undo the insert
  730. con.rollback();
  731. }
  732. catch (SQLException ex2)
  733. {
  734. System.out.println("Message: " + ex2.getMessage());
  735. System.exit(-1);
  736. }
  737. }
  738. }
  739.  
  740. /*Assuming we know Email, Password, and Flight_No */
  741.  
  742. private void insertReservation(int Ticket_ID, int CID, String Payment_date, int Payment_ID, int Seat_No, int Flight_No)
  743. {
  744.  
  745. PreparedStatement psNewTicketForPerson;
  746. PreparedStatement psNewPaysFor;
  747. PreparedStatement psNewTransactTicket;
  748. PreparedStatement psNewTicket;
  749. PreparedStatement psNewTicketForSeat;
  750. PreparedStatement psUpdateSeat;
  751.  
  752. try
  753. {
  754. psNewTicketForPerson = con.prepareStatement("INSERT INTO TicketForPerson VALUES (?,?)");
  755. psNewTicketForPerson.setInt(1, Ticket_ID); // int
  756. psNewTicketForPerson.setInt(2, CID); // int
  757.  
  758. psNewPaysFor = con.prepareStatement("INSERT INTO PaysFor VALUES (?,?,?)");
  759. psNewPaysFor.setString(1, Payment_date); // String
  760. psNewPaysFor.setInt(2, Payment_ID); // int
  761. psNewPaysFor.setInt(3,CID); // int
  762.  
  763. psNewTransactTicket = con.prepareStatement("INSERT INTO TransactTicket VALUES (?,?)");
  764. psNewTransactTicket.setInt(1,Ticket_ID);
  765. psNewTransactTicket.setInt(2, Payment_ID);
  766.  
  767. psNewTicket = con.prepareStatement("INSERT INTO Ticket VALUES (?)");
  768. psNewTicket.setInt(1,Ticket_ID);
  769.  
  770. psNewTicketForSeat = con.prepareStatement("INSERT INTO TicketForSeat VALUES (?,?,?)");
  771. psNewTicketForSeat.setInt(1,Ticket_ID);
  772. psNewTicketForSeat.setInt(2,Seat_No);
  773. psNewTicketForSeat.setInt(3,Flight_No);
  774.  
  775. psUpdateSeat = con.prepareStatement("UPDATE Seat S SET S.IsTaken = 1 WHERE S.Seat_No = "+Seat_No+" and S.Flight_No ="+Flight_No);
  776.  
  777. psNewTicketForPerson.executeUpdate();
  778. psNewPaysFor.executeUpdate();
  779. psNewTransactTicket.executeUpdate();
  780. psNewTicket.executeUpdate();
  781. psNewTicketForSeat.executeUpdate();
  782. psUpdateSeat.executeUpdate();
  783. // commit work
  784. con.commit();
  785.  
  786. psNewTicketForPerson.close();
  787. psNewPaysFor.close();
  788. psNewTransactTicket.close();
  789. psNewTicket.close();
  790. psNewTicketForSeat.close();
  791. psUpdateSeat.close();
  792.  
  793. }
  794. catch (SQLException ex)
  795. {
  796. System.out.println("Message: " + ex.getMessage());
  797. try
  798. {
  799. // undo the insert
  800. con.rollback();
  801.  
  802. }
  803. catch (SQLException ex2)
  804. {
  805. System.out.println("Message: " + ex2.getMessage());
  806. System.exit(-1);
  807. }
  808. }
  809. }
  810.  
  811. private void deletePerson(String Email, String Password)
  812. {
  813.  
  814. PreparedStatement psDeletePerson;
  815.  
  816.  
  817. try
  818. {
  819. //psSearchForPerson = con.prepareStatement("SELECT FROM Person WHERE )
  820. psDeletePerson = con.prepareStatement("DELETE FROM Person WHERE Email LIKE '%" +Email + "%' and Password LIKE '%" +Password+"%'");
  821.  
  822. int rowCount = psDeletePerson.executeUpdate();
  823.  
  824. if (rowCount == 0)
  825. {
  826. System.out.println("\nPerson " + Email + " does not exist!");
  827. }
  828.  
  829. con.commit();
  830.  
  831. psDeletePerson.close();
  832. }
  833. catch (SQLException ex)
  834. {
  835. System.out.println("Message: " + ex.getMessage());
  836.  
  837. try
  838. {
  839. con.rollback();
  840. }
  841. catch (SQLException ex2)
  842. {
  843. System.out.println("Message: " + ex2.getMessage());
  844. System.exit(-1);
  845. }
  846. }
  847. }
  848.  
  849.  
  850. /*
  851. * updates the name of a branch
  852. */
  853. private void updatePerson(String Name, String OldEmail, String OldPassword, String Email, String Password, String PhoneNum, String DOB)
  854. {
  855. PreparedStatement ps;
  856. try
  857. {
  858. ps = con.prepareStatement("UPDATE Person P SET P.Name = ?, P.Email = ?, P.Password = ?, P.PhoneNum = ?, P.DOB = ? WHERE P.OldEmail = ? and P.OldPassword = ?");
  859. ps.setString(1, Name);
  860. ps.setString(2, Email);
  861. ps.setString(3, Password);
  862. ps.setString(4, PhoneNum);
  863. ps.setString(5, DOB);
  864. ps.setString(6, OldEmail);
  865. ps.setString(7, OldPassword);
  866.  
  867. con.commit();
  868.  
  869. ps.close();
  870. }
  871.  
  872. catch (SQLException ex)
  873. {
  874. System.out.println("Message: " + ex.getMessage());
  875.  
  876. try
  877. {
  878. con.rollback();
  879. }
  880. catch (SQLException ex2)
  881. {
  882. System.out.println("Message: " + ex2.getMessage());
  883. System.exit(-1);
  884. }
  885. }
  886. }
  887.  
  888.  
  889. /*
  890. * display information about branches
  891. */
  892. private tableData showReservations(String Email, String Password)
  893. {
  894. Vector<String> personvector = new Vector<String>();
  895. int columnCount=4;
  896. int rowCount=0;
  897.  
  898. String FlightDepartsFrom__TimeStamp;
  899. String FlightArrivesAt__TimeStamp;
  900. String FlightDepartsFrom__Airport_ID;
  901. String FlightArrivesAt__Airport_ID;
  902. String FlightDepartsFrom__Flight_No;
  903. String Seat__Seat_No;
  904. String Seat__IsBusinessClass;
  905. String Ticket__Ticket_ID;
  906. String PaysFor__Payment_ID;
  907. String Person__CID;
  908. String Person__Email;
  909. String Person__PhoneNum;
  910. String Person__DOB;
  911. String Person__Name;
  912.  
  913. Statement stmt;
  914. ResultSet rs;
  915.  
  916. try
  917. {
  918. stmt = con.createStatement();
  919. rs = stmt.executeQuery(
  920. "SELECT FlightDepartsFrom.TimeStamp, FlightArrivesAt.Timestamp, FlightDepartsFrom.Airport_ID, FlightArrivesAt.Airport_ID, FlightDepartsFrom.Flight_No, Seat.Seat_No, Seat.IsBusinessClass, Ticket.Ticket_ID, PaysFor.Payment_ID, Person.CID, Person.Email, Person.PhoneNum, Person.DOB, Person.Name " +
  921. "FROM Person, TicketForPerson, PaysFor, TransactTicket, Ticket, TicketForSeat, Seat, FlightDeparture, FlightArrival, Airport " +
  922. "WHERE Person.Password = "+Password+" AND Person.Email ="+Email+"AND TicketForPerson.CID = Person.CID AND " +
  923. "TicketForPerson.Ticket_ID = Ticket.Ticket_ID AND " +
  924. "PaysFor.CID = Person.CID AND " +
  925. "TransactTicket.Payment_ID = PaysFor.Payment_ID AND " +
  926. "TransactTicket.Ticket_ID = Ticket.Ticket_ID AND " +
  927. "TicketForSeat.Ticket_ID = Ticket.Ticket_ID AND " +
  928. "TicketForSeat.Seat_no = Seat.Seat_no AND " +
  929. "TicketForSeat.Flight_no = Seat.Flight_no AND " +
  930. "Seat.Flight_no = FlightDeparture.Flight_no AND " +
  931. "FlightDeparture.Tail_no = Plane.Tail_no AND " +
  932. "FlightDeparture.Airport_ID = Airport.Airport_ID AND " +
  933. "FlightArrival.Tail_no = Plane.Tail_no AND " +
  934. "FlightArrival.Airport_ID = Airport.Airport_ID");
  935.  
  936. ResultSetMetaData rsmd = rs.getMetaData();
  937. Parameters p = new Parameters();
  938.  
  939. while(rs.next())
  940. {
  941.  
  942. FlightDepartsFrom__TimeStamp = rs.getString("FlightDepartsFrom.TimeStamp");
  943. FlightArrivesAt__TimeStamp = rs.getString("FlightArrivesAt.TimeStamp");
  944. FlightDepartsFrom__Airport_ID = rs.getString("FlightDepartsFrom__Airport_ID");
  945. FlightArrivesAt__Airport_ID = rs.getString("FlightArrivesAt.Airport_ID");
  946. FlightDepartsFrom__Flight_No = rs.getString("FlightDepartsFrom.Flight_No");
  947. Seat__Seat_No = rs.getString("Seat.Seat_No");
  948. Seat__IsBusinessClass = rs.getString("Seat.IsBusinessClass");
  949. Ticket__Ticket_ID = rs.getString("Ticket.Ticket_ID");
  950. PaysFor__Payment_ID = rs.getString("PaysFor.Payment_ID");
  951. Person__CID = rs.getString("Person.CID");
  952. Person__Email = rs.getString("Person.Email");
  953. Person__PhoneNum = rs.getString("Person.PhoneNum");
  954. Person__DOB = rs.getString("Person.DOB");
  955. Person__Name = rs.getString("Person.Name");
  956.  
  957. personvector.add(FlightDepartsFrom__TimeStamp);
  958. personvector.add(FlightArrivesAt__TimeStamp);
  959. personvector.add(FlightDepartsFrom__Airport_ID);
  960. personvector.add(FlightArrivesAt__Airport_ID);
  961. personvector.add(FlightDepartsFrom__Flight_No);
  962. personvector.add(Seat__Seat_No);
  963. personvector.add(Seat__IsBusinessClass);
  964. personvector.add(Ticket__Ticket_ID);
  965. personvector.add(PaysFor__Payment_ID);
  966. personvector.add(Person__CID);
  967. personvector.add(Person__Email);
  968. personvector.add(Person__PhoneNum);
  969. personvector.add(Person__DOB);
  970. personvector.add(Person__Name);
  971.  
  972. rowCount++;
  973. stmt.close();
  974. }
  975. }
  976. catch (SQLException ex)
  977. {
  978. System.out.println("Message: " + ex.getMessage());
  979. }
  980.  
  981. String tableName = "QueryFlights";
  982. tableData queryFlightsData = new tableData(tableName, columnCount, rowCount, personvector);
  983. return queryFlightsData;
  984.  
  985. }
  986.  
  987.  
  988. public static void main(String args[])
  989. {
  990. new testtickles();
  991. }
  992. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement