ArrayList输出不断重复

Question

我需要从数据库中提取数据，并将其存储在多态数组列表中。数据库包含酒店中的客人，我需要根据用户的选择显示输出，例如，他们可以选择选择1:查看所有客人，选择2:查看STC客人选择3:查看SHN客人我得到了我需要的正确输出，但问题是它一直在重复。我觉得我忽略了什么，但我无法确定具体是什么，任何帮助都将不胜感激！非常感谢。

这是我当前的代码：

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 %-20sn", "Guest ID", "Guest Name", "Room Number","Guest Type"));


for( Guest g : guestList) {
System.out.println(String.format("%-10s %-20s %-20s %-20sn", 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 %-10sn", "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 %-10sn",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 %-10sn", "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 %-10sn", s.id,s.name,s.roomNo,s.GuestType,s.getB(),s.getA()));
}
}

}

}

这是我得到的输出：选择1输出选择2输出

遵循DB模式酒店_客人travel_docs

Answer 1

您尚未共享数据库架构，但SQL查询似乎有问题

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

这实际上是笛卡尔乘积，因为它缺少联接谓词，这意味着每一行hotel_filters都将与每一行travel_docs联接。

为了解决这个问题，你需要识别连接条件，可能是你在两个表中都有guest_id，然后你可以说

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

编辑：

在查看了您刚才共享的数据库模式后，在我看来，您想要获得旅行描述，在这种情况下，您应该使用以下查询

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

Answer 2

我认为while循环有问题则可以使用CCD_ 2。