Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- // to compile this program you need to include hb15.zip
- // in the CLASSPATH environment variable
- // We need to import the java.sql package to use JDBC
- import java.sql.*;
- import java.util.Vector;
- // for reading from the command line
- import java.io.*;
- // for the login window
- import javax.swing.*;
- import java.awt.*;
- import java.awt.event.*;
- // for Format.printf() and Parameters.add()
- import com.braju.format.*;
- /*
- * This class implements a graphical login window and a simple text
- * interface for interacting with the branch table
- */
- public class testtickles implements ActionListener
- {
- // command line reader
- private BufferedReader in = new BufferedReader(new InputStreamReader(System.in));
- private Connection con;
- // user is allowed 3 login attempts
- private int loginAttempts = 0;
- // components of the login window
- private JTextField usernameField;
- private JPasswordField passwordField;
- private JFrame mainFrame;
- /*
- * constructs login window and loads JDBC driver
- */
- public testtickles()
- {
- mainFrame = new JFrame("User Login");
- JLabel usernameLabel = new JLabel("Enter username: ");
- JLabel passwordLabel = new JLabel("Enter password: ");
- usernameField = new JTextField(10);
- passwordField = new JPasswordField(10);
- passwordField.setEchoChar('*');
- JButton loginButton = new JButton("Log In");
- JPanel contentPane = new JPanel();
- mainFrame.setContentPane(contentPane);
- // layout components using the GridBag layout manager
- GridBagLayout gb = new GridBagLayout();
- GridBagConstraints c = new GridBagConstraints();
- contentPane.setLayout(gb);
- contentPane.setBorder(BorderFactory.createEmptyBorder(10, 10, 10, 10));
- // place the username label
- c.gridwidth = GridBagConstraints.RELATIVE;
- c.insets = new Insets(10, 10, 5, 0);
- gb.setConstraints(usernameLabel, c);
- contentPane.add(usernameLabel);
- // place the text field for the username
- c.gridwidth = GridBagConstraints.REMAINDER;
- c.insets = new Insets(10, 0, 5, 10);
- gb.setConstraints(usernameField, c);
- contentPane.add(usernameField);
- // place password label
- c.gridwidth = GridBagConstraints.RELATIVE;
- c.insets = new Insets(0, 10, 10, 0);
- gb.setConstraints(passwordLabel, c);
- contentPane.add(passwordLabel);
- // place the password field
- c.gridwidth = GridBagConstraints.REMAINDER;
- c.insets = new Insets(0, 0, 10, 10);
- gb.setConstraints(passwordField, c);
- contentPane.add(passwordField);
- // place the login button
- c.gridwidth = GridBagConstraints.REMAINDER;
- c.insets = new Insets(5, 10, 10, 10);
- c.anchor = GridBagConstraints.CENTER;
- gb.setConstraints(loginButton, c);
- contentPane.add(loginButton);
- // register password field and OK button with action event handler
- passwordField.addActionListener(this);
- loginButton.addActionListener(this);
- // anonymous inner class for closing the window
- mainFrame.addWindowListener(new WindowAdapter()
- {
- public void windowClosing(WindowEvent e)
- {
- System.exit(0);
- }
- });
- // size the window to obtain a best fit for the components
- mainFrame.pack();
- // center the frame
- Dimension d = mainFrame.getToolkit().getScreenSize();
- Rectangle r = mainFrame.getBounds();
- mainFrame.setLocation( (d.width - r.width)/2, (d.height - r.height)/2 );
- // make the window visible
- mainFrame.setVisible(true);
- // place the cursor in the text field for the username
- usernameField.requestFocus();
- try
- {
- // Load the Oracle JDBC driver
- DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
- }
- catch (SQLException ex)
- {
- System.out.println("Message: " + ex.getMessage());
- System.exit(-1);
- }
- }
- /*
- * connects to Oracle database named ug using user supplied username and password
- */
- private boolean connect(String username, String password)
- {
- // String connectURL = "jdbc:oracle:thin:@dbhost.ugrad.cs.ubc.ca:1521:ug";
- String connectURL = "jdbc:oracle:thin:@localhost:1521:ug";
- try
- {
- con = DriverManager.getConnection(connectURL,username,password);
- System.out.println("\nConnected to Oracle!");
- return true;
- }
- catch (SQLException ex)
- {
- System.out.println("Message: " + ex.getMessage());
- return false;
- }
- }
- /*
- * event handler for login window
- */
- public void actionPerformed(ActionEvent e)
- {
- if ( connect(usernameField.getText(), String.valueOf(passwordField.getPassword())) )
- {
- // if the username and password are valid,
- // remove the login window and display a text menu
- mainFrame.dispose();
- showMenu();
- }
- else
- {
- loginAttempts++;
- if (loginAttempts >= 3)
- {
- mainFrame.dispose();
- System.exit(-1);
- }
- else
- {
- // clear the password
- passwordField.setText("");
- }
- }
- }
- /*
- * displays simple text interface
- */
- private void showMenu()
- {
- int choice;
- boolean quit;
- quit = false;
- try
- {
- // disable auto commit mode
- con.setAutoCommit(false);
- while (!quit)
- {
- System.out.print("\n\nPlease choose one of the following: \n");
- System.out.print("1. Insert Person\n");
- System.out.print("2. Delete Person\n");
- System.out.print("3. Update Person\n");
- System.out.print("4. showReservations\n");
- System.out.print("5. insertReservation\n");
- System.out.print("6. Quit\n>> ");
- choice = Integer.parseInt(in.readLine());
- System.out.println(" ");
- switch(choice)
- {
- }
- }
- con.close();
- in.close();
- System.out.println("\nGood Bye!\n\n");
- System.exit(0);
- }
- catch (IOException e)
- {
- System.out.println("IOException!");
- try
- {
- con.close();
- System.exit(-1);
- }
- catch (SQLException ex)
- {
- System.out.println("Message: " + ex.getMessage());
- }
- }
- catch (SQLException ex)
- {
- System.out.println("Message: " + ex.getMessage());
- }
- }
- /* Select the Date, Time and Price of the Cheapest Ticket from ___ to ___ that departs between ____ and ____.
- SELECT fd.Depart_Timestamp, t.ticket_price
- FROM Ticket t, TicketForSeat ts, Seat s, FlightDeparture fd, FlightArrival fa
- WHERE t.Ticket_ID = ts.Ticket_ID AND ts.Seat_No = s.Seat_No AND
- s.Flight_No = fd.Flight_No AND s.Flight_No = fa.Flight_No AND
- fd.Airport_ID = 'YVR' AND fa.Airport_ID = 'SEA' AND
- fd.Depart_Timestamp < '01-JAN-2012 12:59:59' AND
- fd.Depart_Timestamp > '01-JAN-2011 01:00:00' AND
- s.IsTaken = 0 AND t.ticket_price ( SELECT MIN(t1.ticket_price)
- FROM Ticket t1) ; */
- private tableData cheapestFlight(
- String DepartureAirport,
- String DeptFromTimeStamp,
- String DeptToTimeStamp,
- String ArrivalAirport){
- Vector<String> cheapestFlight = new Vector<String>();
- int columnCount=5;
- int rowCount=0;
- Statement stmt;
- ResultSet rs;
- try
- {
- stmt = con.createStatement();
- rs = stmt.executeQuery("SELECT fd.Depart_Timestamp, fd. Airport_ID, fa.Arrive_Timestamp, fa.Airport_ID, DISTINCT t.ticket_price, s.IsBusinessClass"+
- "FROM Ticket t, TicketForSeat ts, Seat s, FlightDeparture fd, FlightArrival fa"+ // From
- "WHERE t.Ticket_ID = ts.Ticket_ID " + // Where
- "AND ts.Seat_No = s.Seat_No AND" +
- "s.Flight_No = fd.Flight_No AND " +
- "s.Flight_No = fa.Flight_No AND" +
- "fd.Airport_ID = "+DepartureAirport+" AND" +
- "fa.Airport_ID = "+ArrivalAirport +" AND"+
- "fd.Depart_Timestamp >"+DeptToTimeStamp+"AND"+
- "fd.Depart_Timestamp < "+DeptToTimeStamp+"AND"+
- "s.IsTaken = 0 AND t.ticket_price ( SELECT MIN(t1.ticket_price)FROM Ticket t1") ;
- ResultSetMetaData rsmd1 = rs.getMetaData();
- while(rs.next())
- {
- for(int i=1;i<columnCount+1;i++){
- cheapestFlight .add(rs.getString(i));
- }
- rowCount++;
- }
- }
- catch (SQLException ex)
- {
- System.out.println("Message: " + ex.getMessage());
- try
- {
- con.rollback();
- }
- catch (SQLException ex2)
- {
- System.out.println("Message: " + ex2.getMessage());
- System.exit(-1);
- }
- }
- String tableName = "cheapestFlight";
- tableData cheapestFlightData= new tableData(tableName, columnCount, rowCount, cheapestFlight);
- return cheapestFlightData;
- }
- /* Deleting a Ticket actually sets the Ticket's Canceled status to True (1).
- *
- */
- private void deleteTicket(String Ticket_ID) {
- PreparedStatement psDeleteTicket;
- try
- {
- psDeleteTicket = con.prepareStatement("UPDATE Ticket T SET T.Cancelled="+1+"WHERE T.TicketID="+Ticket_ID);
- psDeleteTicket.executeUpdate();
- con.commit();
- psDeleteTicket.close();
- }
- catch (SQLException ex)
- {
- System.out.println("Message: " + ex.getMessage());
- try
- {
- con.rollback();
- }
- catch (SQLException ex2)
- {
- System.out.println("Message: " + ex2.getMessage());
- System.exit(-1);
- }
- }
- }
- /* Find the Number of Empty Seats Remaining of a Particular Class on a Particular Flight
- SELECT COUNT(Seat.Seat_No)
- FROM Seat
- WHERE Seat.Flight_No = 102 AND Seat.IsBusinessClass = 1 AND Seat.IsTaken = 0 */
- private int emptySeatsonFlight(int Flight_No, int isBusinessClass){
- int SeatsRemaining = 0;
- Statement stmt;
- ResultSet rs;
- try
- {
- stmt = con.createStatement();
- rs = stmt.executeQuery("SELECT COUNT(Seat.Seat_No) FROM Seat WHERE Seat.Flight_No ="+Flight_No+"( AND isBusinessClass ="+ isBusinessClass +" AND Seat.IsTaken = 0");
- ResultSetMetaData rsmd1 = rs.getMetaData();
- SeatsRemaining=Integer.parseInt(rs.getString(1));
- }
- catch (SQLException ex)
- {
- System.out.println("Message: " + ex.getMessage());
- try
- {
- con.rollback();
- }
- catch (SQLException ex2)
- {
- System.out.println("Message: " + ex2.getMessage());
- System.exit(-1);
- }
- }
- return SeatsRemaining;
- }
- /*Finished Above*/
- /*Returns the a String Array of results*/
- private tableData queryFlights(
- String DepartureAirport,
- String DeptFromTimeStamp, String DeptToTimeStamp,
- String ArrivalAirport, String ArriveFromTimeStamp, String ArriveToTimeStamp,
- int SortByPrice,
- int BusinessClassQuery,
- int EconomyClassQuery,
- int CheapestTicketQuery){
- Vector<String> FlightsResult = new Vector<String>();
- Vector<String> FlightsResultSortByPrice = new Vector<String>();
- Vector<String> FlightsResultBusiness = new Vector<String>();
- Vector<String> FlightsResultEconomy = new Vector<String>();
- Vector<String> FlightsResultCheapest = new Vector<String>();
- Statement stmt1;
- Statement stmt2;
- Statement stmt3;
- Statement stmt4;
- Statement stmt5;
- ResultSet rs1;
- ResultSet rs2;
- ResultSet rs3;
- ResultSet rs4;
- ResultSet rs5;
- int columnCount_1=0;
- int columnCount_2=0;
- int columnCount_3=0;
- int columnCount_4=0;
- int columnCount_5=0;
- int rowCount1=0;
- int rowCount2=0;
- int rowCount3=0;
- int rowCount4=0;
- int rowCount5=0;
- try
- {
- stmt1 = con.createStatement();
- stmt2 = con.createStatement();
- stmt3 = con.createStatement();
- stmt4 = con.createStatement();
- stmt5 = con.createStatement();
- /*QuickSort*/
- rs1 = stmt1.executeQuery("SELECT fd.Depart_Timestamp, fd. Airport_ID, fa.Arrive_Timestamp, fa.Airport_ID, DISTINCT t.ticket_price, s.IsBusinessClass"+
- "FROM Ticket t, TicketForSeat ts, Seat s, FlightDeparture fd, FlightArrival fa"+ // From
- "WHERE t.Ticket_ID = ts.Ticket_ID " + // Where
- "AND ts.Seat_No = s.Seat_No AND" +
- "s.Flight_No = fd.Flight_No AND " +
- "s.Flight_No = fa.Flight_No AND" +
- "fd.Airport_ID = "+DepartureAirport+" AND" +
- "fa.Airport_ID = "+ArrivalAirport+" AND"+
- "fa.Arrive_Timestamp > "+ ArriveFromTimeStamp+"AND"+
- "fa.Arrive_Timestamp < "+ArriveToTimeStamp+"AND"+
- "fd.Depart_Timestamp >"+DeptFromTimeStamp+"AND"+
- "fd.Depart_Timestamp < "+DeptToTimeStamp+"AND"+
- "s.IsTaken = 0");
- /*Sort by Price */
- rs2 = stmt2.executeQuery("SELECT fd.Depart_Timestamp, fd. Airport_ID, fa.Arrive_Timestamp, fa.Airport_ID, DISTINCT t.ticket_price, s.IsBusinessClass"+
- "FROM Ticket t, TicketForSeat ts, Seat s, FlightDeparture fd, FlightArrival fa"+ // From
- "WHERE t.Ticket_ID = ts.Ticket_ID " + // Where
- "AND ts.Seat_No = s.Seat_No AND" +
- "s.Flight_No = fd.Flight_No AND " +
- "s.Flight_No = fa.Flight_No AND" +
- "fd.Airport_ID = "+DepartureAirport+" AND" +
- "fa.Airport_ID = "+ArrivalAirport+" AND"+
- "fa.Arrive_Timestamp > "+ ArriveFromTimeStamp+"AND"+
- "fa.Arrive_Timestamp < "+ArriveToTimeStamp+"AND"+
- "fd.Depart_Timestamp >"+DeptFromTimeStamp+"AND"+
- "fd.Depart_Timestamp < "+DeptToTimeStamp+"AND"+
- "s.IsTaken = 0 SORT BY t.ticket_price ");
- /*Business Class Only */
- rs3 = stmt3.executeQuery("SELECT fd.Depart_Timestamp, fd. Airport_ID, fa.Arrive_Timestamp, fa.Airport_ID, DISTINCT t.ticket_price, s.IsBusinessClass"+
- "FROM Ticket t, TicketForSeat ts, Seat s, FlightDeparture fd, FlightArrival fa"+ // From
- "WHERE t.Ticket_ID = ts.Ticket_ID " + // Where
- "AND ts.Seat_No = s.Seat_No AND" +
- "s.Flight_No = fd.Flight_No AND " +
- "s.Flight_No = fa.Flight_No AND" +
- "fd.Airport_ID = "+DepartureAirport+" AND" +
- "fa.Airport_ID = "+ArrivalAirport+" AND"+
- "fa.Arrive_Timestamp > "+ ArriveFromTimeStamp+"AND"+
- "fa.Arrive_Timestamp < "+ArriveToTimeStamp+"AND"+
- "fd.Depart_Timestamp >"+DeptFromTimeStamp+"AND"+
- "fd.Depart_Timestamp < "+DeptToTimeStamp+"AND"+
- "s.IsTaken = 0 AND s.IsBusinessClass = 1 SORT BY t.ticket_price ");
- /*Economy Class Only */
- rs4 = stmt4.executeQuery("SELECT fd.Depart_Timestamp, fd. Airport_ID, fa.Arrive_Timestamp, fa.Airport_ID, DISTINCT t.ticket_price, s.IsBusinessClass"+
- "FROM Ticket t, TicketForSeat ts, Seat s, FlightDeparture fd, FlightArrival fa"+ // From
- "WHERE t.Ticket_ID = ts.Ticket_ID " + // Where
- "AND ts.Seat_No = s.Seat_No AND" +
- "s.Flight_No = fd.Flight_No AND " +
- "s.Flight_No = fa.Flight_No AND" +
- "fd.Airport_ID = "+DepartureAirport+" AND" +
- "fa.Airport_ID = "+ArrivalAirport+" AND"+
- "fa.Arrive_Timestamp > "+ ArriveFromTimeStamp+"AND"+
- "fa.Arrive_Timestamp < "+ArriveToTimeStamp+"AND"+
- "fd.Depart_Timestamp >"+DeptFromTimeStamp+"AND"+
- "fd.Depart_Timestamp < "+DeptToTimeStamp+"AND"+
- "s.IsTaken = 0 AND s.IsBusinessClass = 0 SORT BY t.ticket_price ");
- rs5 = stmt5.executeQuery("SELECT fd.Depart_Timestamp, fd. Airport_ID, fa.Arrive_Timestamp, fa.Airport_ID, DISTINCT t.ticket_price, s.IsBusinessClass" +
- "WHERE Ticket_Price = (SELECT MAX(ticket_price) FROM ticket");
- ResultSetMetaData rsmd1 = rs1.getMetaData();
- ResultSetMetaData rsmd2 = rs2.getMetaData();
- ResultSetMetaData rsmd3 = rs3.getMetaData();
- ResultSetMetaData rsmd4 = rs4.getMetaData();
- ResultSetMetaData rsmd5 = rs5.getMetaData();
- columnCount_1 = rsmd1.getColumnCount();
- columnCount_2 = rsmd2.getColumnCount();
- columnCount_3 = rsmd3.getColumnCount();
- columnCount_4 = rsmd4.getColumnCount();
- columnCount_5 = rsmd5.getColumnCount();
- while(rs1.next())
- {
- for(int i=1;i<columnCount_1+1;i++){
- FlightsResult.add(rs1.getString(i));
- rowCount1++;
- }
- }
- while(rs2.next())
- {
- for(int i=1;i<columnCount_2+1;i++){
- FlightsResultSortByPrice.add(rs2.getString(i));
- rowCount2++;
- }
- }
- while(rs3.next())
- {
- for(int i=1;i<columnCount_3+1;i++){
- FlightsResultBusiness.add(rs3.getString(i));
- rowCount3++;
- }
- }
- while(rs4.next())
- {
- for(int i=1;i<columnCount_4+1;i++){
- FlightsResultEconomy.add(rs4.getString(i));
- rowCount4++;
- }
- }
- while(rs5.next())
- {
- for(int i=1;i<columnCount_5+1;i++){
- FlightsResultCheapest.add(rs4.getString(i));
- rowCount5++;
- }
- }
- }
- catch (SQLException ex)
- {
- System.out.println("Message: " + ex.getMessage());
- try
- {
- con.rollback();
- }
- catch (SQLException ex2)
- {
- System.out.println("Message: " + ex2.getMessage());
- System.exit(-1);
- }
- }
- if(SortByPrice==1){
- String tableName = "FlightsResultSortByPrice";
- tableData FlightsResutSortByPriceData= new tableData(tableName, columnCount_2, rowCount2, FlightsResultSortByPrice);
- return FlightsResutSortByPriceData;
- }
- else if(BusinessClassQuery==1){
- String tableName = "BusinessClassQuery";
- tableData BusinessClassQueryData= new tableData(tableName, columnCount_3, rowCount3, FlightsResultBusiness);
- return BusinessClassQueryData;
- }
- else if(EconomyClassQuery==1){
- String tableName = "EconomyClassQuery";
- tableData EconomyClassData = new tableData(tableName, columnCount_4, rowCount4, FlightsResultEconomy);
- return EconomyClassData;
- }
- else if(EconomyClassQuery==1){
- String tableName = "EconomyClassQuery";
- tableData EconomyClassData = new tableData(tableName, columnCount_4, rowCount4, FlightsResultEconomy);
- return EconomyClassData;
- }
- else if(EconomyClassQuery==1){
- String tableName = "EconomyClassQuery";
- tableData EconomyClassData = new tableData(tableName, columnCount_4, rowCount4, FlightsResultEconomy);
- return EconomyClassData;
- }
- else{
- String tableName = "FlightsResult";
- tableData FlightsResultData= new tableData(tableName, columnCount_1, rowCount1, FlightsResult);
- return FlightsResultData;
- }
- }
- private void deleteFlight(int Flight_No) {
- PreparedStatement psDeleteFlight;
- PreparedStatement psUpdateTickets;
- try
- {
- psDeleteFlight = con.prepareStatement("DELETE FROM Flight WHERE Flight_No ="+Flight_No);
- psUpdateTickets = con.prepareStatement("UPDATE Ticket T SET T.Cancelled="+1+"WHERE T.TicketID= (SELECT T2.TicketID"+
- "FROM Ticket T2, TicketForSeat, Seat, FlightDeparture, FlightArrival"+
- "WHERE T2.TicketID = TicketForSeat.TicketID AND"+
- "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");
- psDeleteFlight.executeUpdate();
- psUpdateTickets.executeUpdate();
- con.commit();
- psDeleteFlight.close();
- psUpdateTickets.close();
- }
- catch (SQLException ex)
- {
- System.out.println("Message: " + ex.getMessage());
- try
- {
- con.rollback();
- }
- catch (SQLException ex2)
- {
- System.out.println("Message: " + ex2.getMessage());
- System.exit(-1);
- }
- }
- }
- /* SELECT Name, Email, PhoneNum, DOB
- FROM Person, TicketForPerson tp, TickerForSeat ts, Seat s
- WHERE Person.cid = tp.cid AND tp.ticket_ID = ts.ticket_ID AND ts.seat_no = s.seat_no AND s.isbusinessclass = 1 ;
- */
- public tableData firstClassPassengers(){
- Vector<String> firstClass = new Vector<String>();
- int columnCount = 0;
- int rowCount=0;
- Statement stmt;
- ResultSet rs;
- try
- {
- stmt = con.createStatement();
- rs = stmt.executeQuery("SELECT Name, Email, PhoneNum, DOB " +
- "FROM Person, TicketForPerson tp, TickerForSeat ts, Seat s " +
- "WHERE Person.cid = tp.cid AND tp.ticket_ID = ts.ticket_ID AND ts.seat_no = s.seat_no AND s.isbusinessclass = 1");
- ResultSetMetaData rsmd = rs.getMetaData();
- columnCount = rsmd.getColumnCount();
- while(rs.next())
- {
- for(int i=1;i<columnCount+1;i++){
- firstClass .add(rs.getString(i));
- }
- rowCount++;
- }
- }
- catch (SQLException ex)
- {
- System.out.println("Message: " + ex.getMessage());
- try
- {
- con.rollback();
- }
- catch (SQLException ex2)
- {
- System.out.println("Message: " + ex2.getMessage());
- System.exit(-1);
- }
- }
- String tableName = "firstClass";
- tableData firstClassData= new tableData(tableName, columnCount, rowCount, firstClass);
- return firstClassData;
- }
- }
- private void insertPerson(int CID, String Name, String PhoneNum, String DOB, String Password, String Email, int isAdmin)
- {
- PreparedStatement ps;
- try
- {
- ps = con.prepareStatement("INSERT INTO Person VALUES (?,?,?,?,?,?,?)");
- ps.setInt(1, CID); // This must be ps.setInt
- ps.setString(2, Name);
- ps.setString(3, PhoneNum);
- ps.setString(4, DOB);
- ps.setString(5, Password);
- ps.setString(6, Email);
- ps.setInt(6, isAdmin);
- ps.executeUpdate();
- con.commit();
- ps.close();
- }
- catch (SQLException ex)
- {
- System.out.println("Message: " + ex.getMessage());
- try
- {
- // undo the insert
- con.rollback();
- }
- catch (SQLException ex2)
- {
- System.out.println("Message: " + ex2.getMessage());
- System.exit(-1);
- }
- }
- }
- /*Assuming we know Email, Password, and Flight_No */
- private void insertReservation(int Ticket_ID, int CID, String Payment_date, int Payment_ID, int Seat_No, int Flight_No)
- {
- PreparedStatement psNewTicketForPerson;
- PreparedStatement psNewPaysFor;
- PreparedStatement psNewTransactTicket;
- PreparedStatement psNewTicket;
- PreparedStatement psNewTicketForSeat;
- PreparedStatement psUpdateSeat;
- try
- {
- psNewTicketForPerson = con.prepareStatement("INSERT INTO TicketForPerson VALUES (?,?)");
- psNewTicketForPerson.setInt(1, Ticket_ID); // int
- psNewTicketForPerson.setInt(2, CID); // int
- psNewPaysFor = con.prepareStatement("INSERT INTO PaysFor VALUES (?,?,?)");
- psNewPaysFor.setString(1, Payment_date); // String
- psNewPaysFor.setInt(2, Payment_ID); // int
- psNewPaysFor.setInt(3,CID); // int
- psNewTransactTicket = con.prepareStatement("INSERT INTO TransactTicket VALUES (?,?)");
- psNewTransactTicket.setInt(1,Ticket_ID);
- psNewTransactTicket.setInt(2, Payment_ID);
- psNewTicket = con.prepareStatement("INSERT INTO Ticket VALUES (?)");
- psNewTicket.setInt(1,Ticket_ID);
- psNewTicketForSeat = con.prepareStatement("INSERT INTO TicketForSeat VALUES (?,?,?)");
- psNewTicketForSeat.setInt(1,Ticket_ID);
- psNewTicketForSeat.setInt(2,Seat_No);
- psNewTicketForSeat.setInt(3,Flight_No);
- psUpdateSeat = con.prepareStatement("UPDATE Seat S SET S.IsTaken = 1 WHERE S.Seat_No = "+Seat_No+" and S.Flight_No ="+Flight_No);
- psNewTicketForPerson.executeUpdate();
- psNewPaysFor.executeUpdate();
- psNewTransactTicket.executeUpdate();
- psNewTicket.executeUpdate();
- psNewTicketForSeat.executeUpdate();
- psUpdateSeat.executeUpdate();
- // commit work
- con.commit();
- psNewTicketForPerson.close();
- psNewPaysFor.close();
- psNewTransactTicket.close();
- psNewTicket.close();
- psNewTicketForSeat.close();
- psUpdateSeat.close();
- }
- catch (SQLException ex)
- {
- System.out.println("Message: " + ex.getMessage());
- try
- {
- // undo the insert
- con.rollback();
- }
- catch (SQLException ex2)
- {
- System.out.println("Message: " + ex2.getMessage());
- System.exit(-1);
- }
- }
- }
- private void deletePerson(String Email, String Password)
- {
- PreparedStatement psDeletePerson;
- try
- {
- //psSearchForPerson = con.prepareStatement("SELECT FROM Person WHERE )
- psDeletePerson = con.prepareStatement("DELETE FROM Person WHERE Email LIKE '%" +Email + "%' and Password LIKE '%" +Password+"%'");
- int rowCount = psDeletePerson.executeUpdate();
- if (rowCount == 0)
- {
- System.out.println("\nPerson " + Email + " does not exist!");
- }
- con.commit();
- psDeletePerson.close();
- }
- catch (SQLException ex)
- {
- System.out.println("Message: " + ex.getMessage());
- try
- {
- con.rollback();
- }
- catch (SQLException ex2)
- {
- System.out.println("Message: " + ex2.getMessage());
- System.exit(-1);
- }
- }
- }
- /*
- * updates the name of a branch
- */
- private void updatePerson(String Name, String OldEmail, String OldPassword, String Email, String Password, String PhoneNum, String DOB)
- {
- PreparedStatement ps;
- try
- {
- ps = con.prepareStatement("UPDATE Person P SET P.Name = ?, P.Email = ?, P.Password = ?, P.PhoneNum = ?, P.DOB = ? WHERE P.OldEmail = ? and P.OldPassword = ?");
- ps.setString(1, Name);
- ps.setString(2, Email);
- ps.setString(3, Password);
- ps.setString(4, PhoneNum);
- ps.setString(5, DOB);
- ps.setString(6, OldEmail);
- ps.setString(7, OldPassword);
- con.commit();
- ps.close();
- }
- catch (SQLException ex)
- {
- System.out.println("Message: " + ex.getMessage());
- try
- {
- con.rollback();
- }
- catch (SQLException ex2)
- {
- System.out.println("Message: " + ex2.getMessage());
- System.exit(-1);
- }
- }
- }
- /*
- * display information about branches
- */
- private tableData showReservations(String Email, String Password)
- {
- Vector<String> personvector = new Vector<String>();
- int columnCount=4;
- int rowCount=0;
- String FlightDepartsFrom__TimeStamp;
- String FlightArrivesAt__TimeStamp;
- String FlightDepartsFrom__Airport_ID;
- String FlightArrivesAt__Airport_ID;
- String FlightDepartsFrom__Flight_No;
- String Seat__Seat_No;
- String Seat__IsBusinessClass;
- String Ticket__Ticket_ID;
- String PaysFor__Payment_ID;
- String Person__CID;
- String Person__Email;
- String Person__PhoneNum;
- String Person__DOB;
- String Person__Name;
- Statement stmt;
- ResultSet rs;
- try
- {
- stmt = con.createStatement();
- rs = stmt.executeQuery(
- "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 " +
- "FROM Person, TicketForPerson, PaysFor, TransactTicket, Ticket, TicketForSeat, Seat, FlightDeparture, FlightArrival, Airport " +
- "WHERE Person.Password = "+Password+" AND Person.Email ="+Email+"AND TicketForPerson.CID = Person.CID AND " +
- "TicketForPerson.Ticket_ID = Ticket.Ticket_ID AND " +
- "PaysFor.CID = Person.CID AND " +
- "TransactTicket.Payment_ID = PaysFor.Payment_ID AND " +
- "TransactTicket.Ticket_ID = Ticket.Ticket_ID AND " +
- "TicketForSeat.Ticket_ID = Ticket.Ticket_ID AND " +
- "TicketForSeat.Seat_no = Seat.Seat_no AND " +
- "TicketForSeat.Flight_no = Seat.Flight_no AND " +
- "Seat.Flight_no = FlightDeparture.Flight_no AND " +
- "FlightDeparture.Tail_no = Plane.Tail_no AND " +
- "FlightDeparture.Airport_ID = Airport.Airport_ID AND " +
- "FlightArrival.Tail_no = Plane.Tail_no AND " +
- "FlightArrival.Airport_ID = Airport.Airport_ID");
- ResultSetMetaData rsmd = rs.getMetaData();
- Parameters p = new Parameters();
- while(rs.next())
- {
- FlightDepartsFrom__TimeStamp = rs.getString("FlightDepartsFrom.TimeStamp");
- FlightArrivesAt__TimeStamp = rs.getString("FlightArrivesAt.TimeStamp");
- FlightDepartsFrom__Airport_ID = rs.getString("FlightDepartsFrom__Airport_ID");
- FlightArrivesAt__Airport_ID = rs.getString("FlightArrivesAt.Airport_ID");
- FlightDepartsFrom__Flight_No = rs.getString("FlightDepartsFrom.Flight_No");
- Seat__Seat_No = rs.getString("Seat.Seat_No");
- Seat__IsBusinessClass = rs.getString("Seat.IsBusinessClass");
- Ticket__Ticket_ID = rs.getString("Ticket.Ticket_ID");
- PaysFor__Payment_ID = rs.getString("PaysFor.Payment_ID");
- Person__CID = rs.getString("Person.CID");
- Person__Email = rs.getString("Person.Email");
- Person__PhoneNum = rs.getString("Person.PhoneNum");
- Person__DOB = rs.getString("Person.DOB");
- Person__Name = rs.getString("Person.Name");
- personvector.add(FlightDepartsFrom__TimeStamp);
- personvector.add(FlightArrivesAt__TimeStamp);
- personvector.add(FlightDepartsFrom__Airport_ID);
- personvector.add(FlightArrivesAt__Airport_ID);
- personvector.add(FlightDepartsFrom__Flight_No);
- personvector.add(Seat__Seat_No);
- personvector.add(Seat__IsBusinessClass);
- personvector.add(Ticket__Ticket_ID);
- personvector.add(PaysFor__Payment_ID);
- personvector.add(Person__CID);
- personvector.add(Person__Email);
- personvector.add(Person__PhoneNum);
- personvector.add(Person__DOB);
- personvector.add(Person__Name);
- rowCount++;
- stmt.close();
- }
- }
- catch (SQLException ex)
- {
- System.out.println("Message: " + ex.getMessage());
- }
- String tableName = "QueryFlights";
- tableData queryFlightsData = new tableData(tableName, columnCount, rowCount, personvector);
- return queryFlightsData;
- }
- public static void main(String args[])
- {
- new testtickles();
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement