booking.java

oracle data base booking system (JAVA)

import java.io.*;
import java.util.*;
import java.sql.*;
import java.sql.Date;
import java.text.*;



class Admin_Database {
    Connection con = null;
    Statement stmt = null;

    public Admin_Database() {
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            con = DriverManager.getConnection("jdbc:oracle:thin:@db00.cse.cuhk.edu.hk:1521:db00", "db037", "a123b123"); // may not work
            stmt = con.createStatement();
        }
        catch(ClassNotFoundException e) {
            System.out.println("Class not found: " + e);
        } catch(SQLException e)	{
            System.out.println("SQL Exception: " + e);
        }
    }

    public void create() {
    	try {
    		stmt.executeUpdate("CREATE TABLE Flight (FlightID VARCHAR(10), Airline VARCHAR(200), DepartCity VARCHAR(100), ArrivalCity VARCHAR(100), DepartTime DATE, ArrivalTime DATE, Price FLOAT, No_Of_Seats INT, PRIMARY KEY(FlightID))");
    		stmt.executeUpdate("CREATE TABLE Booking (BookingID VARCHAR(10), PassengerName VARCHAR(100), FromCity VARCHAR(100), ToCity VARCHAR(100), DepartAfter DATE, ArrivalBefore DATE, PassengerPhone VARCHAR(20), CreditCardNo VARCHAR(50), PRIMARY KEY(BookingID))");
    		stmt.executeUpdate("CREATE TABLE Ticket (BookingID VARCHAR(10), FlightID VARCHAR(10), IssueDate DATE, Fee FLOAT, PRIMARY KEY(FlightID, BookingID), FOREIGN KEY(FlightID) references Flight(FlightID), FOREIGN KEY(BookingID) references Booking(BookingID))");
            System.out.println("Tables are created successfully!!!");   		
    	} catch (SQLException e){
    		System.out.println("SQL Exception: " + e);
    		System.out.println("NO tables are created!");
    	}
    	
    	
    }
    
    public void insert() throws Exception {
		 PreparedStatement pstmt1 = con.prepareStatement("INSERT INTO Flight VALUES (?, ?, ?, ?, to_date(?, 'yyyy-mm-dd  hh24:mi'), to_date(?, 'yyyy-mm-dd  hh24:mi'), ?, ?)");
		 String[] ss1=new String[10];
		 try{
				FileReader fr1 = new FileReader("Flights.txt"); 
				BufferedReader br2 = new BufferedReader(fr1); 
				String line = br2.readLine();
				while(line != null){	
					ss1 = line.split("\t", 8);
					pstmt1.setString(1, ss1[0]);
				   	pstmt1.setString(2, ss1[1]);
				   	pstmt1.setString(3, ss1[2]);
				  	pstmt1.setString(4, ss1[3]);
				   	pstmt1.setString(5, ss1[4]);
				   	pstmt1.setString(6, ss1[5]);
					pstmt1.setFloat(7, Float.parseFloat(ss1[6]));
				  	pstmt1.setInt(8, Integer.parseInt(ss1[7]));
				   	pstmt1.executeUpdate();
				   	line = br2.readLine();
				}
				br2.close();
				fr1.close();	
				pstmt1.close();
		 } catch (IOException e) {
				System.out.println("IO Error:" + e.getMessage());
		 }
		 PreparedStatement pstmt2 = con.prepareStatement("INSERT INTO Booking VALUES (?, ?, ?, ?, to_date(?, 'yyyy-mm-dd  hh24:mi'), to_date(?, 'yyyy-mm-dd  hh24:mi'), ?, ?)");
		 String[] ss2=new String[10];
		 try{
				FileReader fr2 = new FileReader("Bookings.txt"); 
				BufferedReader br2 = new BufferedReader(fr2); 
				String line = br2.readLine();
				while(line != null){	
					ss2 = line.split("\t", 8);
					pstmt2.setString(1, ss2[0]);
				   	pstmt2.setString(2, ss2[1]);
				   	pstmt2.setString(3, ss2[2]);
				  	pstmt2.setString(4, ss2[3]);
				   	pstmt2.setString(5, ss2[4]);
				   	pstmt2.setString(6, ss2[5]);
					pstmt2.setString(7, ss2[6]);
				  	pstmt2.setString(8, ss2[7]);
				   	pstmt2.executeUpdate();
				   	line = br2.readLine();
				}
				br2.close();
				fr2.close();	
				pstmt2.close();
		 } catch (IOException e) {
				System.out.println("IO Error:" + e.getMessage());
		 }
		 PreparedStatement pstmt3 = con.prepareStatement("INSERT INTO Ticket VALUES (?, ?, to_date(?, 'yyyy-mm-dd  hh24:mi'), ?)");
		 String[] ss3=new String[10];
		 try{
				FileReader fr3 = new FileReader("Books.txt"); 
				BufferedReader br3 = new BufferedReader(fr3); 
				String line = br3.readLine();
				while(line != null){	
					ss3 = line.split("\t", 4);
					pstmt3.setString(1, ss3[0]);
				   	pstmt3.setString(2, ss3[1]);
				   	pstmt3.setString(3, ss3[2]);
				   	pstmt3.setFloat(4, Float.parseFloat(ss3[3]));
				   	pstmt3.executeUpdate();
				   	line = br3.readLine();
				}
				br3.close();
				fr3.close();	
				pstmt3.close();
		 } catch (IOException e) {
				System.out.println("IO Error:" + e.getMessage());
		 }
    }
    
    
    public void drop() {
    	try	{
    		stmt.executeUpdate("DROP TABLE Ticket");
    		stmt.executeUpdate("DROP TABLE Booking");
    		stmt.executeUpdate("DROP TABLE Flight");
    		System.out.println("Tables are dropped successfully!!!");
    	}
    	catch (SQLException e) {
    		System.out.println("SQL Exception: " + e);
    		System.out.println("NO tables are dropped!");
    	}
    }
    

    public ResultSet exeQuery(String Query) throws SQLException {
        ResultSet rs=null;
        rs=stmt.executeQuery(Query);
        return rs;
    }

    
   public void closeConn() {
        try {
            stmt.close();
            con.close();
        } catch(SQLException e)	{
            System.out.println("SQL Exception");
        }
    }
}

public class Booking
{
	public static void main(String[] args) throws SQLException 
	{
		Admin_Database Admin_Database = new Admin_Database();
		Scanner in = new Scanner(System.in);
		for(;;) {
			System.out.println();
			System.out.print("Welcome to our travel agency!!!\n");
			System.out.print("1. Administrator Interface\n");
			System.out.print("2. Booking Interface\n");
			System.out.print("3. Evaluation Interface\n");
			System.out.print("Please select the interface you need: ");
		
			int num = in.nextInt();
		
			if (num==1) {
				for(;;) {
					System.out.println();
					System.out.print("Welcome to Administrator Interface!!!\n");
					System.out.print("1. Create table schemas in the database\n");
					System.out.print("2. Delete table schemas in the database\n");
					System.out.print("3. Return to the last selection page\n");
					System.out.print("Please select your choice: ");
					
					int num1 = in.nextInt();
					
					if (num1==1) {
						for(;;) {
							System.out.println();
							System.out.print("1. Create table schemas in the database\n");
							System.out.print("2. Insert data into table schemas in the database\n");			
							System.out.print("3. Return to the last selection page\n");
							System.out.print("Please select your choice: ");
							int num11 = in.nextInt();
							if (num11==1)			
								Admin_Database.create();
							if (num11==2) {
								try {
									Admin_Database.insert();
									System.out.println("Data are inserted successfully!!!");
								} catch (Exception e) {
									System.out.println("IO Error:" + e.getMessage());
									System.out.println("No data inserted into tables!");
								}
							} 
							if (num11==3)
								break;
							else {
								System.out.print("You entered a wrong number!\n");
								continue;
								}
							}
						}
					if (num1==2) {
						Admin_Database.drop();
						}
					if (num1==3)
						break;
						}
				}
		
			if (num==2){
				Scanner in2 = new Scanner(System.in);
				System.out.println();
				System.out.print("Welcome to the booking system!!!\n");
				System.out.print("Please enter the names of places and time duration of the flight you wanted:\n");
				System.out.print("From City: ");
				String DepartCity = in2.nextLine();
				System.out.print("To City: ");
				String ArrivalCity = in2.nextLine();
				System.out.print("Depart After(YYYY-MM-DD HH:mm): ");
				String DepartTime = in2.nextLine();
				System.out.print("Arrival Before(YYYY-MM-DD HH:mm): ");
				String ArrivalTime = in2.nextLine();
				
				System.out.println();
				System.out.println("1. Table of itinerary sorted by fee");
				System.out.println("2. Table of itinerary sorted by duration");
				System.out.print("Please select one of the sorted tables of itinerary above: ");
				int num2 = in.nextInt();
				int ino=0;
				if (num2==1) {
					try {
						String query = ("SELECT FlightID, FlightID2, DepartCity, ArrivalCity, to_char(DepartTime, ('yyyy-mm-dd hh24:mi')) as DepartTime,  to_char(ArrivalTime, ('yyyy-mm-dd hh24:mi')) as ArrivalTime, price , (ArrivalTime-departtime)*24 as duration from(" + 
				        		"SELECT A.FlightID, B.FlightID as FlightID2, A.DepartCity, A.ArrivalCity, A.DepartTime, A.ArrivalTime, A.price" +
				        		" FROM Flight A, Flight B WHERE  A.DepartCity = '" + DepartCity + "' AND A.ArrivalCity = '" + ArrivalCity+"' AND A.DepartTime between to_date('" + DepartTime + "', 'yyyy-mm-dd  hh24:mi') AND to_date('"+ ArrivalTime + "', 'yyyy-mm-dd  hh24:mi') AND A.ArrivalTime between to_date('" + DepartTime + "', 'yyyy-mm-dd  hh24:mi') AND to_date('"+ ArrivalTime + "', 'yyyy-mm-dd  hh24:mi') " +
				        			"AND A.FlightID = B.FlightID UNION  \n" +
				                "SELECT A.FlightID, B.FlightID as FlightID2, A.DepartCity, B.ArrivalCity, A.DepartTime, \n"+
				        		"B.ArrivalTime, (A.price + B.price) as price FROM Flight A, Flight B WHERE A.Arrivalcity = B.DepartCity \n" + 
				        		"AND A.DepartCity = '" + DepartCity + "' AND B.ArrivalCity = '" + ArrivalCity +"' AND A.DepartTime between to_date('" + DepartTime + "', 'yyyy-mm-dd  hh24:mi') AND to_date('" + ArrivalTime + "', 'yyyy-mm-dd  hh24:mi')" +
				        		"AND B.ArrivalTime between to_date('" + DepartTime + "', 'yyyy-mm-dd  hh24:mi') AND to_date('" + ArrivalTime + "', 'yyyy-mm-dd  hh24:mi')" +
				        		"AND A.ArrivalTime Between A.Departtime AND B.Departtime) ORDER BY PRICE");
				        ResultSet rs = Admin_Database.exeQuery(query);
				        System.out.println();
				        while (rs.next()) {ino++;
								if (rs.getInt(1) == rs.getInt(2)) {
									System.out.print("Itinerary " + ino);