ArrayList output keeps repeating

Question

I need to extract data from a database and store it in a polymorphic arraylist. The database contains guests in a hotel, and I need to show output based on user choice, for example, they can choose choice 1: view all guests, choice 2: view STC guests choice 3: View SHN Guest I have gotten the correct output that I need but the problem is that it keeps repeating. I feel like I'm overlooking something but I cannot identify exactly what, any help would be appreciated! Thank you!

This is my current code:

public class HotelGuestAppEnhanced {
        ArrayList<Guest> guestList = new ArrayList<Guest>();
        private Connection conn;
        private Statement statement;
        private ResultSet rs;

        public static void main(String[] args) {
            HotelGuestAppEnhanced hga = new HotelGuestAppEnhanced();
            hga.start();
            // start method will handle program flow, methods will be non-static
        }
    
        public void start() {
            // Establish connection
            try {
                String connectionString = "jdbc:mysql://localhost:3306/c209ga_p3";
                String userid = "root";
                String password = "";
    
                conn = DriverManager.getConnection(connectionString, userid, password);
                statement = conn.createStatement();
    
                //create a method to add the list
                addtoList();
                int option = -1;
    
                while (option != 4) {
                    menu();
                    option = Helper.readInt("Enter choice > ");
    
                    if (option == 1) {
                        viewAllGuests();
                    } else if (option == 2) {
                        viewStaycationGuests();
                    } else if (option == 3) {
                        viewSHNGuests();
                    } else if (option == 4) {
    
                        rs.close();
                        statement.close();
                        conn.close();
    
                        System.out.println("Thank you for using the Hotel Guest App!");
                    }
                }
    
            } catch (SQLException se) {
                System.out.println("Error: " + se.getMessage());
    
            }
    
        }
    
        private void menu() {
            Helper.line(150, "=");
            System.out.println("HOTEL GUEST APP ENHANCED");
            Helper.line(150, "=");
            System.out.println("1. View All Guests");
            System.out.println("2. View Staycation Guests");
            System.out.println("3. View SHN Guests");
            System.out.println("4. Quit");
        }
    
        /**
         * 
         */
        private void addtoList() {
            //reading from arraylist and creating STC / SHN object 
            
            try {
                String sql = "SELECT * from hotel_guests, travel_docs";
                
                // prepares and executes sql statement
                rs = statement.executeQuery(sql);
                
                while (rs.next()) {
                    
                    // process results
                    int ID = rs.getInt("ID");
                    String Name = rs.getString("Name");
                    String RoomNo = rs.getString("RoomNo");
                    String GuestType = rs.getString("GuestType");
                    String DiscountCode = rs.getString("DiscountCode");
                    int GroupSize = rs.getInt("GroupSize");
                    int SHNdays = rs.getInt("SHNdays");
                    String Description = rs.getString("Description");
    
                    if (GuestType.equalsIgnoreCase("SHN")) {
                        // adding SHN objects
                        guestList.add(new SHNGuest(ID, Name, RoomNo, GuestType, SHNdays, Description));
    
                    } else if (GuestType.equalsIgnoreCase("STC")) {
                        // adding STC objects
                        guestList.add(new STCGuest(ID, Name, RoomNo, GuestType, DiscountCode, GroupSize));
                    }
    
                }
            } catch (SQLException se) {
                System.out.println("SQL Error: " + se.getMessage());
            }
        }
    
        private void viewAllGuests() {
    
            // Write code here to display all guests
            // guest registration ID, guest name, room number, and guest type
            // (staycation/SHN)
    
            System.out.println(String.format("%-10s %-20s %-20s %-20s\n", "Guest ID", "Guest Name", "Room Number","Guest Type"));
            
            
            for( Guest g : guestList) {
                System.out.println(String.format("%-10s %-20s %-20s %-20s\n", g.id,g.name,g.roomNo,g.GuestType));
            }
            
            }
            
    //      Helper.line(150, "-");
    //      System.out.println(output);
    
        
    
        private void viewStaycationGuests() {
    
            // Write code here to display only staycation guests
    
            
                System.out.println(String.format("%-10s %-15s %-15s %-15s %-15s %-10s\n", "Guest ID", "Guest Name", "Room Number",
                        "Guest Type", "Discount Code", "Group Size"));
            
            
            for( Guest g : guestList) {
                if ( g instanceof STCGuest) {
                //casting
                STCGuest s = (STCGuest) g;
                System.out.println(String.format("%-10s %-15s %-15s %-15s %-15s %-10s\n",s.id,s.name,s.roomNo,s.GuestType,s.getA(),s.getB()));
            }
            
            }
    //
        }
    
        private void viewSHNGuests() {
    
            System.out.println(String.format("%-10s %-15s %-15s %-15s %-25s %-10s\n", "Guest ID", "Guest Name",
                    "Room Number", "Guest Type", "Stay Home Notice Days", "Travel Documents Type"));
            for (Guest g : guestList) {
                if ( g instanceof SHNGuest) {
                    //casting
                    SHNGuest s = (SHNGuest) g;
                    System.out.println(String.format("%-10s %-15s %-15s %-15s %-25s %-10s\n", s.id,s.name,s.roomNo,s.GuestType,s.getB(),s.getA()));
                }
            }
    
        }
    
    }

This is the output I've gotten: Choice 1 output Choice 2 output

Follwoing is DB schema hotel_guests travel_docs

Answer 1

You haven't shared your database schema but it seems to be an issue with your SQL query

String sql = "SELECT * from hotel_guests, travel_docs";

This is effectively Cartesian product because it lacks a join predicate, meaning every row of hotel_guests will be joined with every row of travel_docs.

To resolve this you need to identify the join condition, may be you have guest_id in both the tables then you can say

SELECT * from hotel_guests JOIN travel_docs on hotel_guests.guest_id = travel_docs.guest_id"

Edit :

After looking at your database schema that you just shared, it seem to me that you want to get travel description, in that case you should use following query

SELECT * from hotel_guests JOIN travel_docs on hotel_guests.TravelDocId = travel_docs.ID

Answer 2

I suppose issue with while loop if can be used instead.