SHARE
TWEET

Untitled

a guest Dec 2nd, 2019 148 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /*
  2.  * Template JAVA User Interface
  3.  * =============================
  4.  *
  5.  * Database Management Systems
  6.  * Department of Computer Science & Engineering
  7.  * University of California - Riverside
  8.  *
  9.  * Target DBMS: 'Postgres'
  10.  *
  11.  */
  12.  
  13.  
  14. import java.sql.DriverManager;
  15. import java.sql.Connection;
  16. import java.sql.Statement;
  17. import java.sql.ResultSet;
  18. import java.sql.ResultSetMetaData;
  19. import java.sql.SQLException;
  20. import java.io.File;
  21. import java.io.FileReader;
  22. import java.io.BufferedReader;
  23. import java.io.InputStreamReader;
  24. import java.text.SimpleDateFormat;
  25. import java.util.Calendar;
  26. import java.util.Date;
  27. import java.text.DateFormat;
  28.  
  29.  
  30. /**
  31.  * This class defines a simple embedded SQL utility class that is designed to
  32.  * work with PostgreSQL JDBC drivers.
  33.  *
  34.  */
  35. public class DBProject {
  36.  
  37.    // reference to physical database connection.
  38.    private Connection _connection = null;
  39.  
  40.    // handling the keyboard inputs through a BufferedReader
  41.    // This variable can be global for convenience.
  42.    static BufferedReader in = new BufferedReader(
  43.                                 new InputStreamReader(System.in));
  44.  
  45.    /**
  46.     * Creates a new instance of DBProject
  47.     *
  48.     * @param hostname the MySQL or PostgreSQL server hostname
  49.     * @param database the name of the database
  50.     * @param username the user name used to login to the database
  51.     * @param password the user login password
  52.     * @throws java.sql.SQLException when failed to make a connection.
  53.     */
  54.    public DBProject (String dbname, String dbport, String user, String passwd) throws SQLException {
  55.  
  56.       System.out.print("Connecting to database...");
  57.       try{
  58.          // constructs the connection URL
  59.          String url = "jdbc:postgresql://localhost:" + dbport + "/" + dbname;
  60.          System.out.println ("Connection URL: " + url + "\n");
  61.  
  62.          // obtain a physical connection
  63.          this._connection = DriverManager.getConnection(url, user, passwd);
  64.          System.out.println("Done");
  65.       }catch (Exception e){
  66.          System.err.println("Error - Unable to Connect to Database: " + e.getMessage() );
  67.          System.out.println("Make sure you started postgres on this machine");
  68.          System.exit(-1);
  69.       }//end catch
  70.    }//end DBProject
  71.  
  72.    /**
  73.     * Method to execute an update SQL statement.  Update SQL instructions
  74.     * includes CREATE, INSERT, UPDATE, DELETE, and DROP.
  75.     *
  76.     * @param sql the input SQL string
  77.     * @throws java.sql.SQLException when update failed
  78.     */
  79.    public void executeUpdate (String sql) throws SQLException {
  80.       // creates a statement object
  81.       Statement stmt = this._connection.createStatement ();
  82.  
  83.       // issues the update instruction
  84.       stmt.executeUpdate (sql);
  85.  
  86.       // close the instruction
  87.       stmt.close ();
  88.    }//end executeUpdate
  89.  
  90.    /**
  91.     * Method to execute an input query SQL instruction (i.e. SELECT).  This
  92.     * method issues the query to the DBMS and outputs the results to
  93.     * standard out.
  94.     *
  95.     * @param query the input query string
  96.     * @return the number of rows returned
  97.     * @throws java.sql.SQLException when failed to execute the query
  98.     */
  99.    public int executeQuery (String query) throws SQLException {
  100.       // creates a statement object
  101.       Statement stmt = this._connection.createStatement ();
  102.  
  103.       // issues the query instruction
  104.       ResultSet rs = stmt.executeQuery (query);
  105.  
  106.       /*
  107.        ** obtains the metadata object for the returned result set.  The metadata
  108.        ** contains row and column info.
  109.        */
  110.       ResultSetMetaData rsmd = rs.getMetaData ();
  111.       int numCol = rsmd.getColumnCount ();
  112.       int rowCount = 0;
  113.  
  114.       // iterates through the result set and output them to standard out.
  115.       boolean outputHeader = true;
  116.       while (rs.next()){
  117.      if(outputHeader){
  118.         for(int i = 1; i <= numCol; i++){
  119.         System.out.print(rsmd.getColumnName(i) + "\t");
  120.         }
  121.         System.out.println();
  122.         outputHeader = false;
  123.      }
  124.          for (int i=1; i<=numCol; ++i)
  125.             System.out.print (rs.getString (i) + "\t");
  126.          System.out.println ();
  127.          ++rowCount;
  128.       }//end while
  129.       stmt.close ();
  130.       return rowCount;
  131.    }//end executeQuery
  132.  
  133.    /**
  134.     * Method to close the physical connection if it is open.
  135.     */
  136.    public void cleanup(){
  137.       try{
  138.          if (this._connection != null){
  139.             this._connection.close ();
  140.          }//end if
  141.       }catch (SQLException e){
  142.          // ignored.
  143.       }//end try
  144.    }//end cleanup
  145.  
  146.    /**
  147.     * The main execution method
  148.     *
  149.     * @param args the command line arguments this inclues the <mysql|pgsql> <login file>
  150.     */
  151.    public static void main (String[] args) {
  152.       if (args.length != 3) {
  153.          System.err.println (
  154.             "Usage: " +
  155.             "java [-classpath <classpath>] " +
  156.             DBProject.class.getName () +
  157.             " <dbname> <port> <user>");
  158.          return;
  159.       }//end if
  160.      
  161.       Greeting();
  162.       DBProject esql = null;
  163.       try{
  164.          // use postgres JDBC driver.
  165.          Class.forName ("org.postgresql.Driver").newInstance ();
  166.          // instantiate the DBProject object and creates a physical
  167.          // connection.
  168.          String dbname = args[0];
  169.          String dbport = args[1];
  170.          String user = args[2];
  171.          esql = new DBProject (dbname, dbport, user, "");
  172.  
  173.          boolean keepon = true;
  174.          while(keepon) {
  175.             // These are sample SQL statements
  176.                 System.out.println("MAIN MENU");
  177.                 System.out.println("---------");
  178.                 System.out.println("1. Add new customer");
  179.                 System.out.println("2. Add new room");
  180.                 System.out.println("3. Add new maintenance company");
  181.                 System.out.println("4. Add new repair");
  182.                 System.out.println("5. Add new Booking");
  183.                 System.out.println("6. Assign house cleaning staff to a room");
  184.                 System.out.println("7. Raise a repair request");
  185.                 System.out.println("8. Get number of available rooms");
  186.                 System.out.println("9. Get number of booked rooms");
  187.                 System.out.println("10. Get hotel bookings for a week");
  188.                 System.out.println("11. Get top k rooms with highest price for a date range");
  189.                 System.out.println("12. Get top k highest booking price for a customer");
  190.                 System.out.println("13. Get customer total cost occurred for a give date range");
  191.                 System.out.println("14. List the repairs made by maintenance company");
  192.                 System.out.println("15. Get top k maintenance companies based on repair count");
  193.                 System.out.println("16. Get number of repairs occurred per year for a given hotel room");
  194.                 System.out.println("17. < EXIT");
  195.  
  196.             switch (readChoice()){
  197.                    case 1: addCustomer(esql); break;
  198.                    case 2: addRoom(esql); break;
  199.                    case 3: addMaintenanceCompany(esql); break;
  200.                    case 4: addRepair(esql); break;
  201.                    case 5: bookRoom(esql); break;
  202.                    case 6: assignHouseCleaningToRoom(esql); break;
  203.                    case 7: repairRequest(esql); break;
  204.                    case 8: numberOfAvailableRooms(esql); break;
  205.                    case 9: numberOfBookedRooms(esql); break;
  206.                    case 10: listHotelRoomBookingsForAWeek(esql); break;
  207.                    case 11: topKHighestRoomPriceForADateRange(esql); break;
  208.                    case 12: topKHighestPriceBookingsForACustomer(esql); break;
  209.                    case 13: totalCostForCustomer(esql); break;
  210.                    case 14: listRepairsMade(esql); break;
  211.                    case 15: topKMaintenanceCompany(esql); break;
  212.                    case 16: numberOfRepairsForEachRoomPerYear(esql); break;
  213.                    case 17: keepon = false; break;
  214.                    default : System.out.println("Unrecognized choice!"); break;
  215.             }//end switch
  216.          }//end while
  217.       }catch(Exception e) {
  218.          System.err.println (e.getMessage ());
  219.       }finally{
  220.          // make sure to cleanup the created table and close the connection.
  221.          try{
  222.             if(esql != null) {
  223.                System.out.print("Disconnecting from database...");
  224.                esql.cleanup ();
  225.                System.out.println("Done\n\nBye !");
  226.             }//end if
  227.          }catch (Exception e) {
  228.             // ignored.
  229.          }//end try
  230.       }//end try
  231.    }//end main
  232.    
  233.    public static void Greeting(){
  234.       System.out.println(
  235.          "\n\n*******************************************************\n" +
  236.          "              User Interface                         \n" +
  237.          "*******************************************************\n");
  238.    }//end Greeting
  239.  
  240.    /*
  241.     * Reads the users choice given from the keyboard
  242.     * @int
  243.     **/
  244.    public static int readChoice() {
  245.       int input;
  246.       // returns only if a correct value is given.
  247.       do {
  248.          System.out.print("Please make your choice: ");
  249.          try { // read the integer, parse it and break.
  250.             input = Integer.parseInt(in.readLine());
  251.             break;
  252.          }catch (Exception e) {
  253.             System.out.println("Your input is invalid!");
  254.             continue;
  255.          }//end try
  256.       }while (true);
  257.       return input;
  258.    }//end readChoice
  259.  
  260.    public int getNextId(DBProject esql, String fieldName, String tableName ) {
  261.        String query = "Select Max(" + fieldName + ") from " + tableName;
  262.        int next = 0;
  263.        try {
  264.            // creates a statement object
  265.            Statement stmt = this._connection.createStatement ();
  266.            // issues the query instruction
  267.            ResultSet rs = stmt.executeQuery (query);
  268.            while (rs.next()){
  269.                next = rs.getInt(1) + 1;
  270.            }//end while
  271.            stmt.close ();
  272.         }
  273.       catch(Exception e) {
  274.             System.out.println(e);
  275.       }
  276.       return next;
  277.    }
  278.        
  279.        
  280.        
  281.    public static void addCustomer(DBProject esql){
  282.       // Given customer details add the customer in the DB
  283.       // Your code goes here.
  284.       // ...
  285.       // ...
  286.    }//end addCustomer
  287.  
  288.    public static void addRoom(DBProject esql){
  289.       // Given room details add the room in the DB
  290.       try{
  291.           System.out.print("\tEnter hotelID: ");
  292.           String hotelid = in.readLine();
  293.  
  294.           System.out.print("\tEnter room type: ");
  295.           String roomType = in.readLine();
  296.  
  297.           String sql = String.format("INSERT INTO Room VALUES ( %s, %s, '%s')", hotelid, esql.getNextId(esql, "roomno", "room"), roomType);
  298.  
  299.        
  300.           esql.executeQuery(sql);
  301.           System.out.println("The room was succesfully added!");
  302.           }
  303.        catch(Exception e){
  304.          System.err.println (e.getMessage());
  305.       }
  306.    }//end addRoom
  307.  
  308.    public static void addMaintenanceCompany(DBProject esql){
  309.       // Given maintenance Company details add the maintenance company in the DB
  310.       // ...
  311.       // ...
  312.    }//end addMaintenanceCompany
  313.  
  314.    public static void addRepair(DBProject esql){
  315.        
  316.       // Given repair details add repair in the DB
  317.      
  318.        try{
  319.            
  320.            System.out.print("\tEnter hotelID: ");
  321.            String hotelid = in.readLine();
  322.  
  323.            System.out.print("\tEnter room number: ");
  324.            String roomNo = in.readLine();
  325.  
  326.            System.out.print("\tEnter maintenance company id: ");
  327.            String compID = in.readLine();
  328.  
  329.            System.out.print("\tEnter repair date: ");
  330.            String repDate = in.readLine();
  331.  
  332.            System.out.print("\tEnter repair description: ");
  333.            String repairDescr = in.readLine();
  334.              
  335.            System.out.print("\tEnter repair type: ");
  336.            String repairType = in.readLine();
  337.  
  338.            String sql = String.format("INSERT INTO Repair VALUES ( %s, %s, %s, %s, '%s', '%s', '%s')",
  339.                 esql.getNextId(esql, "rID", "Repair"), hotelid, roomNo, compID, repDate, repairDescr, repairType );
  340.  
  341.  
  342.           esql.executeQuery(sql);
  343.           System.out.println("The repair was succesfully added!");
  344.           }
  345.       catch(Exception e){
  346.         System.err.println (e.getMessage());
  347.       }
  348.  
  349.    }//end addRepair
  350.  
  351.    public static void bookRoom(DBProject esql){
  352.       // Given hotelID, roomNo and customer Name create a booking in the DB
  353.       // Your code goes here.
  354.       // ...
  355.       // ...
  356.    }//end bookRoom
  357.  
  358.    public static void assignHouseCleaningToRoom(DBProject esql){
  359.       // Given Staff SSN, HotelID, roomNo Assign the staff to the room
  360.       // Your code goes here.
  361.        try{
  362.            
  363.           System.out.print("\tEnter staff id: ");
  364.           String staffID = in.readLine();
  365.  
  366.           System.out.print("\tEnter hotelID: ");
  367.           String hotelid = in.readLine();
  368.  
  369.           System.out.print("\tEnter room number: ");
  370.           String roomNo = in.readLine();
  371.  
  372.           String sql = String.format("INSERT INTO Assigned VALUES ('%s','%s','%s','%s')", esql.getNextId(esql, "asgID", "Assigned"), staffID, hotelid, roomNo);
  373.  
  374.      
  375.           esql.executeQuery(sql);
  376.          
  377.           }
  378.       catch(Exception e){
  379.         System.err.println (e.getMessage());
  380.       }
  381.      
  382.      
  383.    }//end assignHouseCleaningToRoom
  384.    
  385.    public static void repairRequest(DBProject esql){
  386.       // Given a hotelID, Staff SSN, roomNo, repairID , date create a repair request in the DB
  387.       // Your code goes here.
  388.       // ...
  389.       // ...
  390.    }//end repairRequest
  391.    
  392.    public static void numberOfAvailableRooms(DBProject esql){
  393.       // Given a hotelID, get the count of rooms available
  394.       // Your code goes here.
  395.       try{
  396.             System.out.print("\tEnter Hotel ID: ");
  397.             String hotelid = in.readLine();
  398.            
  399.             String query = String.format("SELECT COUNT(*) FROM Room R " +
  400.                         "WHERE R.hotelid = '%s' and R.roomNo not in (SELECT roomNo FROM Booking B WHERE B.hotelid = '%s');", hotelid, hotelid );
  401.  
  402.          esql.executeQuery(query);
  403.        
  404.       }catch(Exception e){
  405.          System.err.println (e.getMessage());
  406.       }
  407.    }//end numberOfAvailableRooms
  408.    
  409.    public static void numberOfBookedRooms(DBProject esql){
  410.       // Given a hotelID, get the count of rooms booked
  411.       // Your code goes here.
  412.       // ...
  413.       // ...
  414.    }//end numberOfBookedRooms
  415.    
  416.    public static void listHotelRoomBookingsForAWeek(DBProject esql){
  417.       // Given a hotelID, date - list all the rooms available for a week(including the input date)
  418.         try{
  419.            
  420.         System.out.print("\tEnter hotelID: ");
  421.         String hotelid = in.readLine();
  422.        
  423.         System.out.print("\tEnter date(mm/dd/yyyy): ");
  424.         String date = in.readLine();
  425.  
  426.         Date date1 = new SimpleDateFormat("MM/dd/yyyy").parse(date);  
  427.         Calendar c = Calendar.getInstance();
  428.         c.setTime(date1);
  429.         c.add(Calendar.DATE, 7);
  430.         DateFormat df = new SimpleDateFormat("MM/dd/yyyy");
  431.         String weeklater = df.format(c.getTime());
  432.  
  433.        
  434.         String sql = String.format("SELECT * from Booking WHERE hotelid = '%s' and bookingdate BETWEEN '%s' AND '%s'", hotelid, date, weeklater);
  435.         esql.executeQuery(sql);
  436.         }
  437.       catch(Exception e){
  438.         System.err.println (e.getMessage());
  439.       }
  440.    }//end listHotelRoomBookingsForAWeek
  441.    
  442.    public static void topKHighestRoomPriceForADateRange(DBProject esql){
  443.       // List Top K Rooms with the highest price for a given date range
  444.       // Your code goes here.
  445.       // ...
  446.       // ...
  447.    }//end topKHighestRoomPriceForADateRange
  448.    
  449.    public static void topKHighestPriceBookingsForACustomer(DBProject esql){
  450.       // Given a customer Name, List Top K highest booking price for a customer
  451.       // Your code goes here.
  452.        try{
  453.            
  454.          
  455.             System.out.print("\tEnter customer's first name: ");
  456.             String fName = in.readLine();
  457.             System.out.print("\tEnter customer's last name: ");
  458.             String lName = in.readLine();
  459.             System.out.print("\tEnter k: ");
  460.             String k = in.readLine();
  461.  
  462.             String sql = String.format("SELECT B.price FROM Booking B, Customer C " +
  463.                                         "WHERE C.customerid = B.customer AND C.fName = '%s' AND C.lName = '%s' " +
  464.                                         "ORDER BY B.price DESC LIMIT %s; ", fName, lName, k );
  465.        
  466.           esql.executeQuery(sql);
  467.        
  468.           }
  469.       catch(Exception e){
  470.         System.err.println (e.getMessage());
  471.       }
  472.    }//end topKHighestPriceBookingsForACustomer
  473.    
  474.    public static void totalCostForCustomer(DBProject esql){
  475.       // Given a hotelID, customer Name and date range get the total cost incurred by the customer
  476.       // Your code goes here.
  477.    
  478.      
  479.    }//end totalCostForCustomer
  480.    
  481.    public static void listRepairsMade(DBProject esql){
  482.       // Given a Maintenance company name list all the repairs along with repairType, hotelID and roomNo
  483.       // Your code goes here.
  484.        
  485.        try{
  486.            String mcomp = "";
  487.          
  488.             System.out.print("\tEnter Maintenance company name: ");
  489.             mcomp = in.readLine();
  490.  
  491.             String sql = String.format("SELECT r.rid, r.hotelid, r.roomNo, r.repairType FROM maintenancecompany mc, repair r " +
  492.                                        "WHERE mc.name = '%s' AND mc.cmpid = r.mcompany;", mcomp );
  493.        
  494.           esql.executeQuery(sql);
  495.        
  496.           }
  497.       catch(Exception e){
  498.         System.err.println (e.getMessage());
  499.       }
  500.      
  501.    }//end listRepairsMade
  502.    
  503.    public static void topKMaintenanceCompany(DBProject esql){
  504.       // List Top K Maintenance Company Names based on total repair count (descending order)
  505.       // Your code goes here.
  506.       // ...
  507.       // ...
  508.    }//end topKMaintenanceCompany
  509.    
  510.    public static void numberOfRepairsForEachRoomPerYear(DBProject esql){
  511.        
  512.       // Given a hotelID, roomNo, get the count of repairs per year
  513.       // Your code goes here.
  514.      
  515.        try{
  516.             System.out.print("\tEnter Hotel ID: ");
  517.             String hotelid = in.readLine();
  518.  
  519.             System.out.print("\tEnter room number: ");
  520.             String roomno = in.readLine();
  521.            
  522.             String sql = String.format("SELECT COUNT(DATE_PART('year', repairdate)) as number_of_repairs, DATE_PART('year', repairdate) as year " +
  523.             "FROM repair r WHERE r.hotelid = %s and r.roomno = %s GROUP BY DATE_PART('year', repairdate);", hotelid, roomno );
  524.        
  525.           esql.executeQuery(sql);
  526.        
  527.           }
  528.       catch(Exception e){
  529.         System.err.println (e.getMessage());
  530.       }
  531.      
  532.      
  533.    }//end listRepairsMade
  534.  
  535. }//end DBProject
RAW Paste Data
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
 
Top