API tools faq
paste
Advertisement
Guest User

Untitled

a guest
May 18th, 2017
69
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 26.10 KB | None | 0 0
raw download clone embed print report
  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();
  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.  
  407. Vector<String> FlightsResult = new Vector();
  408. Vector<String> FlightsResultSortByPrice = new Vector();
  409. Vector<String> FlightsResultBusiness = new Vector();
  410. Vector<String> FlightsResultEconomy = new Vector();
  411.  
  412. Statement stmt1;
  413. Statement stmt2;
  414. Statement stmt3;
  415. Statement stmt4;
  416.  
  417. ResultSet rs1;
  418. ResultSet rs2;
  419. ResultSet rs3;
  420. ResultSet rs4;
  421.  
  422. int columnCount_1=0;
  423. int columnCount_2=0;
  424. int columnCount_3=0;
  425. int columnCount_4=0;
  426.  
  427. int rowCount1=0;
  428. int rowCount2=0;
  429. int rowCount3=0;
  430. int rowCount4=0;
  431.  
  432. try
  433. {
  434.  
  435. stmt1 = con.createStatement();
  436. stmt2 = con.createStatement();
  437. stmt3 = con.createStatement();
  438. stmt4 = con.createStatement();
  439.  
  440. /*QuickSort*/
  441. rs1 = stmt1.executeQuery("SELECT fd.Depart_Timestamp, fd. Airport_ID, fa.Arrive_Timestamp, fa.Airport_ID, DISTINCT t.ticket_price, s.IsBusinessClass"+
  442. "FROM Ticket t, TicketForSeat ts, Seat s, FlightDeparture fd, FlightArrival fa"+ // From
  443. "WHERE t.Ticket_ID = ts.Ticket_ID " + // Where
  444. "AND ts.Seat_No = s.Seat_No AND" +
  445. "s.Flight_No = fd.Flight_No AND " +
  446. "s.Flight_No = fa.Flight_No AND" +
  447. "fd.Airport_ID = "+DepartureAirport+" AND" +
  448. "fa.Airport_ID = "+ArrivalAirport+" AND"+
  449. "fa.Arrive_Timestamp > "+ ArriveFromTimeStamp+"AND"+
  450. "fa.Arrive_Timestamp < "+ArriveToTimeStamp+"AND"+
  451. "fd.Depart_Timestamp >"+DeptFromTimeStamp+"AND"+
  452. "fd.Depart_Timestamp < "+DeptToTimeStamp+"AND"+
  453. "s.IsTaken = 0");
  454.  
  455. /*Sort by Price */
  456. rs2 = stmt2.executeQuery("SELECT fd.Depart_Timestamp, fd. Airport_ID, fa.Arrive_Timestamp, fa.Airport_ID, DISTINCT t.ticket_price, s.IsBusinessClass"+
  457. "FROM Ticket t, TicketForSeat ts, Seat s, FlightDeparture fd, FlightArrival fa"+ // From
  458. "WHERE t.Ticket_ID = ts.Ticket_ID " + // Where
  459. "AND ts.Seat_No = s.Seat_No AND" +
  460. "s.Flight_No = fd.Flight_No AND " +
  461. "s.Flight_No = fa.Flight_No AND" +
  462. "fd.Airport_ID = "+DepartureAirport+" AND" +
  463. "fa.Airport_ID = "+ArrivalAirport+" AND"+
  464. "fa.Arrive_Timestamp > "+ ArriveFromTimeStamp+"AND"+
  465. "fa.Arrive_Timestamp < "+ArriveToTimeStamp+"AND"+
  466. "fd.Depart_Timestamp >"+DeptFromTimeStamp+"AND"+
  467. "fd.Depart_Timestamp < "+DeptToTimeStamp+"AND"+
  468. "s.IsTaken = 0 SORT BY t.ticket_price ");
  469.  
  470. /*Business Class Only */
  471. rs3 = stmt3.executeQuery("SELECT fd.Depart_Timestamp, fd. Airport_ID, fa.Arrive_Timestamp, fa.Airport_ID, DISTINCT t.ticket_price, s.IsBusinessClass"+
  472. "FROM Ticket t, TicketForSeat ts, Seat s, FlightDeparture fd, FlightArrival fa"+ // From
  473. "WHERE t.Ticket_ID = ts.Ticket_ID " + // Where
  474. "AND ts.Seat_No = s.Seat_No AND" +
  475. "s.Flight_No = fd.Flight_No AND " +
  476. "s.Flight_No = fa.Flight_No AND" +
  477. "fd.Airport_ID = "+DepartureAirport+" AND" +
  478. "fa.Airport_ID = "+ArrivalAirport+" AND"+
  479. "fa.Arrive_Timestamp > "+ ArriveFromTimeStamp+"AND"+
  480. "fa.Arrive_Timestamp < "+ArriveToTimeStamp+"AND"+
  481. "fd.Depart_Timestamp >"+DeptFromTimeStamp+"AND"+
  482. "fd.Depart_Timestamp < "+DeptToTimeStamp+"AND"+
  483. "s.IsTaken = 0 AND s.IsBusinessClass = 1 SORT BY t.ticket_price ");
  484.  
  485. /*Economy Class Only */
  486. rs4 = stmt4.executeQuery("SELECT fd.Depart_Timestamp, fd. Airport_ID, fa.Arrive_Timestamp, fa.Airport_ID, DISTINCT t.ticket_price, s.IsBusinessClass"+
  487. "FROM Ticket t, TicketForSeat ts, Seat s, FlightDeparture fd, FlightArrival fa"+ // From
  488. "WHERE t.Ticket_ID = ts.Ticket_ID " + // Where
  489. "AND ts.Seat_No = s.Seat_No AND" +
  490. "s.Flight_No = fd.Flight_No AND " +
  491. "s.Flight_No = fa.Flight_No AND" +
  492. "fd.Airport_ID = "+DepartureAirport+" AND" +
  493. "fa.Airport_ID = "+ArrivalAirport+" AND"+
  494. "fa.Arrive_Timestamp > "+ ArriveFromTimeStamp+"AND"+
  495. "fa.Arrive_Timestamp < "+ArriveToTimeStamp+"AND"+
  496. "fd.Depart_Timestamp >"+DeptFromTimeStamp+"AND"+
  497. "fd.Depart_Timestamp < "+DeptToTimeStamp+"AND"+
  498. "s.IsTaken = 0 AND s.IsBusinessClass = 0 SORT BY t.ticket_price ");
  499.  
  500. ResultSetMetaData rsmd1 = rs1.getMetaData();
  501. ResultSetMetaData rsmd2 = rs2.getMetaData();
  502. ResultSetMetaData rsmd3 = rs2.getMetaData();
  503. ResultSetMetaData rsmd4 = rs2.getMetaData();
  504. columnCount_1 = rsmd1.getColumnCount();
  505. columnCount_2 = rsmd2.getColumnCount();
  506. columnCount_3 = rsmd3.getColumnCount();
  507. columnCount_4 = rsmd4.getColumnCount();
  508.  
  509. while(rs1.next())
  510. {
  511. for(int i=1;i<columnCount_1+1;i++){
  512. FlightsResult.add(rs1.getString(i));
  513. rowCount1++;
  514. }
  515. }
  516.  
  517. while(rs2.next())
  518. {
  519. for(int i=1;i<columnCount_2+1;i++){
  520. FlightsResultSortByPrice.add(rs2.getString(i));
  521. rowCount2++;
  522. }
  523. }
  524.  
  525. while(rs3.next())
  526. {
  527. for(int i=1;i<columnCount_3+1;i++){
  528. FlightsResultSortByPrice.add(rs3.getString(i));
  529. rowCount3++;
  530. }
  531. }
  532.  
  533. while(rs4.next())
  534. {
  535. for(int i=1;i<columnCount_4+1;i++){
  536. FlightsResultSortByPrice.add(rs4.getString(i));
  537. rowCount4++;
  538. }
  539. }
  540.  
  541. }
  542. catch (SQLException ex)
  543. {
  544. System.out.println("Message: " + ex.getMessage());
  545.  
  546. try
  547. {
  548. con.rollback();
  549. }
  550. catch (SQLException ex2)
  551. {
  552. System.out.println("Message: " + ex2.getMessage());
  553. System.exit(-1);
  554. }
  555. }
  556.  
  557. if(SortByPrice==1){
  558. String tableName = "FlightsResultSortByPrice";
  559. tableData FlightsResutSortByPriceData= new tableData(tableName, columnCount_2, rowCount2, FlightsResultSortByPrice);
  560. return FlightsResutSortByPriceData;
  561. }
  562. else if(BusinessClassQuery==1){
  563. String tableName = "BusinessClassQuery";
  564. tableData BusinessClassQueryData= new tableData(tableName, columnCount_3, rowCount3, FlightsResultBusiness);
  565. return BusinessClassQueryData;
  566. }
  567. else if(EconomyClassQuery==1){
  568. String tableName = "EconomyClassQuery";
  569. tableData EconomyClassData = new tableData(tableName, columnCount_4, rowCount4, FlightsResultEconomy);
  570. return EconomyClassData;
  571. }
  572. else{
  573. String tableName = "FlightsResult";
  574. tableData FlightsResultData= new tableData(tableName, columnCount_1, rowCount1, FlightsResult);
  575. return FlightsResultData;
  576. }
  577. }
  578.  
  579. private void deleteFlight(int Flight_No) {
  580. PreparedStatement psDeleteFlight;
  581. PreparedStatement psUpdateTickets;
  582.  
  583. try
  584. {
  585. psDeleteFlight = con.prepareStatement("DELETE FROM Flight WHERE Flight_No ="+Flight_No);
  586.  
  587. psUpdateTickets = con.prepareStatement("UPDATE Ticket T SET T.Cancelled="+1+"WHERE T.TicketID= (SELECT T2.TicketID"+
  588. "FROM Ticket T2, TicketForSeat, Seat, FlightDeparture, FlightArrival"+
  589. "WHERE T2.TicketID = TicketForSeat.TicketID AND"+
  590. "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");
  591.  
  592. psDeleteFlight.executeUpdate();
  593. psUpdateTickets.executeUpdate();
  594.  
  595. con.commit();
  596. psDeleteFlight.close();
  597. psUpdateTickets.close();
  598. }
  599. catch (SQLException ex)
  600. {
  601. System.out.println("Message: " + ex.getMessage());
  602.  
  603. try
  604. {
  605. con.rollback();
  606. }
  607. catch (SQLException ex2)
  608. {
  609. System.out.println("Message: " + ex2.getMessage());
  610. System.exit(-1);
  611. }
  612. }
  613. }
  614.  
  615. public void FirstClassPassengers(){
  616.  
  617. }
  618.  
  619. private void insertPerson(int CID, String Name, String PhoneNum, String DOB, String Password, String Email, int isAdmin)
  620. {
  621.  
  622. PreparedStatement ps;
  623.  
  624. try
  625. {
  626. ps = con.prepareStatement("INSERT INTO Person VALUES (?,?,?,?,?,?,?)");
  627.  
  628. ps.setInt(1, CID); // This must be ps.setInt
  629. ps.setString(2, Name);
  630. ps.setString(3, PhoneNum);
  631. ps.setString(4, DOB);
  632. ps.setString(5, Password);
  633. ps.setString(6, Email);
  634. ps.setInt(6, isAdmin);
  635.  
  636. ps.executeUpdate();
  637. con.commit();
  638.  
  639. ps.close();
  640. }
  641. catch (SQLException ex)
  642. {
  643. System.out.println("Message: " + ex.getMessage());
  644. try
  645. {
  646. // undo the insert
  647. con.rollback();
  648. }
  649. catch (SQLException ex2)
  650. {
  651. System.out.println("Message: " + ex2.getMessage());
  652. System.exit(-1);
  653. }
  654. }
  655. }
  656.  
  657. /*Assuming we know Email, Password, and Flight_No */
  658.  
  659. private void insertReservation(int Ticket_ID, int CID, String Payment_date, int Payment_ID, int Seat_No, int Flight_No)
  660. {
  661.  
  662. PreparedStatement psNewTicketForPerson;
  663. PreparedStatement psNewPaysFor;
  664. PreparedStatement psNewTransactTicket;
  665. PreparedStatement psNewTicket;
  666. PreparedStatement psNewTicketForSeat;
  667. PreparedStatement psUpdateSeat;
  668.  
  669. try
  670. {
  671. psNewTicketForPerson = con.prepareStatement("INSERT INTO TicketForPerson VALUES (?,?)");
  672. psNewTicketForPerson.setInt(1, Ticket_ID); // int
  673. psNewTicketForPerson.setInt(2, CID); // int
  674.  
  675. psNewPaysFor = con.prepareStatement("INSERT INTO PaysFor VALUES (?,?,?)");
  676. psNewPaysFor.setString(1, Payment_date); // String
  677. psNewPaysFor.setInt(2, Payment_ID); // int
  678. psNewPaysFor.setInt(3,CID); // int
  679.  
  680. psNewTransactTicket = con.prepareStatement("INSERT INTO TransactTicket VALUES (?,?)");
  681. psNewTransactTicket.setInt(1,Ticket_ID);
  682. psNewTransactTicket.setInt(2, Payment_ID);
  683.  
  684. psNewTicket = con.prepareStatement("INSERT INTO Ticket VALUES (?)");
  685. psNewTicket.setInt(1,Ticket_ID);
  686.  
  687. psNewTicketForSeat = con.prepareStatement("INSERT INTO TicketForSeat VALUES (?,?,?)");
  688. psNewTicketForSeat.setInt(1,Ticket_ID);
  689. psNewTicketForSeat.setInt(2,Seat_No);
  690. psNewTicketForSeat.setInt(3,Flight_No);
  691.  
  692. psUpdateSeat = con.prepareStatement("UPDATE Seat S SET S.IsTaken = 1 WHERE S.Seat_No = "+Seat_No+" and S.Flight_No ="+Flight_No);
  693.  
  694. psNewTicketForPerson.executeUpdate();
  695. psNewPaysFor.executeUpdate();
  696. psNewTransactTicket.executeUpdate();
  697. psNewTicket.executeUpdate();
  698. psNewTicketForSeat.executeUpdate();
  699. psUpdateSeat.executeUpdate();
  700. // commit work
  701. con.commit();
  702.  
  703. psNewTicketForPerson.close();
  704. psNewPaysFor.close();
  705. psNewTransactTicket.close();
  706. psNewTicket.close();
  707. psNewTicketForSeat.close();
  708. psUpdateSeat.close();
  709.  
  710. }
  711. catch (SQLException ex)
  712. {
  713. System.out.println("Message: " + ex.getMessage());
  714. try
  715. {
  716. // undo the insert
  717. con.rollback();
  718.  
  719. }
  720. catch (SQLException ex2)
  721. {
  722. System.out.println("Message: " + ex2.getMessage());
  723. System.exit(-1);
  724. }
  725. }
  726. }
  727.  
  728.  
  729.  
  730.  
  731. /*
  732. * deletes a branch
  733. */
  734. private void deletePerson(String Email, String Password)
  735. {
  736.  
  737. PreparedStatement psDeletePerson;
  738.  
  739.  
  740. try
  741. {
  742. //psSearchForPerson = con.prepareStatement("SELECT FROM Person WHERE )
  743. psDeletePerson = con.prepareStatement("DELETE FROM Person WHERE Email LIKE '%" +Email + "%' and Password LIKE '%" +Password+"%'");
  744.  
  745. int rowCount = psDeletePerson.executeUpdate();
  746.  
  747. if (rowCount == 0)
  748. {
  749. System.out.println("\nPerson " + Email + " does not exist!");
  750. }
  751.  
  752. con.commit();
  753.  
  754. psDeletePerson.close();
  755. }
  756. catch (SQLException ex)
  757. {
  758. System.out.println("Message: " + ex.getMessage());
  759.  
  760. try
  761. {
  762. con.rollback();
  763. }
  764. catch (SQLException ex2)
  765. {
  766. System.out.println("Message: " + ex2.getMessage());
  767. System.exit(-1);
  768. }
  769. }
  770. }
  771.  
  772.  
  773. /*
  774. * updates the name of a branch
  775. */
  776. private void updatePerson(String Name, String OldEmail, String OldPassword, String Email, String Password, String PhoneNum, String DOB)
  777. {
  778. PreparedStatement ps;
  779. try
  780. {
  781. ps = con.prepareStatement("UPDATE Person P SET P.Name = ?, P.Email = ?, P.Password = ?, P.PhoneNum = ?, P.DOB = ? WHERE P.OldEmail = ? and P.OldPassword = ?");
  782. ps.setString(1, Name);
  783. ps.setString(2, Email);
  784. ps.setString(3, Password);
  785. ps.setString(4, PhoneNum);
  786. ps.setString(5, DOB);
  787. ps.setString(6, OldEmail);
  788. ps.setString(7, OldPassword);
  789.  
  790. con.commit();
  791.  
  792. ps.close();
  793. }
  794.  
  795. catch (SQLException ex)
  796. {
  797. System.out.println("Message: " + ex.getMessage());
  798.  
  799. try
  800. {
  801. con.rollback();
  802. }
  803. catch (SQLException ex2)
  804. {
  805. System.out.println("Message: " + ex2.getMessage());
  806. System.exit(-1);
  807. }
  808. }
  809. }
  810.  
  811.  
  812. /*
  813. * display information about branches
  814. */
  815. private tableData showReservations(String Email, String Password)
  816. {
  817. Vector<String> personvector = new Vector();
  818. int columnCount=4;
  819. int rowCount=0;
  820.  
  821. String FlightDepartsFrom__TimeStamp;
  822. String FlightArrivesAt__TimeStamp;
  823. String FlightDepartsFrom__Airport_ID;
  824. String FlightArrivesAt__Airport_ID;
  825. String FlightDepartsFrom__Flight_No;
  826. String Seat__Seat_No;
  827. String Seat__IsBusinessClass;
  828. String Ticket__Ticket_ID;
  829. String PaysFor__Payment_ID;
  830. String Person__CID;
  831. String Person__Email;
  832. String Person__PhoneNum;
  833. String Person__DOB;
  834. String Person__Name;
  835.  
  836. Statement stmt;
  837. ResultSet rs;
  838.  
  839. try
  840. {
  841. stmt = con.createStatement();
  842. rs = stmt.executeQuery(
  843. "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 " +
  844. "FROM Person, TicketForPerson, PaysFor, TransactTicket, Ticket, TicketForSeat, Seat, FlightDeparture, FlightArrival, Airport " +
  845. "WHERE Person.Password = "+Password+" AND Person.Email ="+Email+"AND TicketForPerson.CID = Person.CID AND " +
  846. "TicketForPerson.Ticket_ID = Ticket.Ticket_ID AND " +
  847. "PaysFor.CID = Person.CID AND " +
  848. "TransactTicket.Payment_ID = PaysFor.Payment_ID AND " +
  849. "TransactTicket.Ticket_ID = Ticket.Ticket_ID AND " +
  850. "TicketForSeat.Ticket_ID = Ticket.Ticket_ID AND " +
  851. "TicketForSeat.Seat_no = Seat.Seat_no AND " +
  852. "TicketForSeat.Flight_no = Seat.Flight_no AND " +
  853. "Seat.Flight_no = FlightDeparture.Flight_no AND " +
  854. "FlightDeparture.Tail_no = Plane.Tail_no AND " +
  855. "FlightDeparture.Airport_ID = Airport.Airport_ID AND " +
  856. "FlightArrival.Tail_no = Plane.Tail_no AND " +
  857. "FlightArrival.Airport_ID = Airport.Airport_ID");
  858.  
  859. ResultSetMetaData rsmd = rs.getMetaData();
  860. Parameters p = new Parameters();
  861.  
  862. while(rs.next())
  863. {
  864.  
  865. FlightDepartsFrom__TimeStamp = rs.getString("FlightDepartsFrom.TimeStamp");
  866. FlightArrivesAt__TimeStamp = rs.getString("FlightArrivesAt.TimeStamp");
  867. FlightDepartsFrom__Airport_ID = rs.getString("FlightDepartsFrom__Airport_ID");
  868. FlightArrivesAt__Airport_ID = rs.getString("FlightArrivesAt.Airport_ID");
  869. FlightDepartsFrom__Flight_No = rs.getString("FlightDepartsFrom.Flight_No");
  870. Seat__Seat_No = rs.getString("Seat.Seat_No");
  871. Seat__IsBusinessClass = rs.getString("Seat.IsBusinessClass");
  872. Ticket__Ticket_ID = rs.getString("Ticket.Ticket_ID");
  873. PaysFor__Payment_ID = rs.getString("PaysFor.Payment_ID");
  874. Person__CID = rs.getString("Person.CID");
  875. Person__Email = rs.getString("Person.Email");
  876. Person__PhoneNum = rs.getString("Person.PhoneNum");
  877. Person__DOB = rs.getString("Person.DOB");
  878. Person__Name = rs.getString("Person.Name");
  879.  
  880. personvector.add(FlightDepartsFrom__TimeStamp);
  881. personvector.add(FlightArrivesAt__TimeStamp);
  882. personvector.add(FlightDepartsFrom__Airport_ID);
  883. personvector.add(FlightArrivesAt__Airport_ID);
  884. personvector.add(FlightDepartsFrom__Flight_No);
  885. personvector.add(Seat__Seat_No);
  886. personvector.add(Seat__IsBusinessClass);
  887. personvector.add(Ticket__Ticket_ID);
  888. personvector.add(PaysFor__Payment_ID);
  889. personvector.add(Person__CID);
  890. personvector.add(Person__Email);
  891. personvector.add(Person__PhoneNum);
  892. personvector.add(Person__DOB);
  893. personvector.add(Person__Name);
  894.  
  895. rowCount++;
  896. stmt.close();
  897. }
  898. }
  899. catch (SQLException ex)
  900. {
  901. System.out.println("Message: " + ex.getMessage());
  902. }
  903.  
  904. String tableName = "QueryFlights";
  905. tableData queryFlightsData = new tableData(tableName, columnCount, rowCount, personvector);
  906. return queryFlightsData;
  907.  
  908. }
  909.  
  910.  
  911. public static void main(String args[])
  912. {
  913. new testtickles();
  914. }
  915. }
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!