How to eliminate for loop results from database using java?

Ask Warvin 0
Viewed 65

I am writing a script to generate seat number per schedule and date with the total capacity per accommodation.

| Accommodation   |  Capacity  |
| VIP             |       25   |
| Premium         |      100   |
| Economy         |      150   |

Here's the data that I have now in my table:

| Booking #      | Fullname    |  Accommodation  |   Seat #  | Trxn Date  |
| 0000001        | Joe Doe     |  VIP            |    001    | 2022-09-13 |

I am planning to eliminate the seat number which is currently stored in my table from my for loop.

Here's the code I tried:

String val = request.getParameter("accommodation");int capacity=0;String date = request.getParameter("date");
String sql = "Select Capacity from tblcapacity WHERE Seat_Type=?";
pst = conn.prepareStatement(sql);
pst.setString(1, val);
rs = pst.executeQuery();
if(rs.next())
{
    capacity = rs.getInt("Capacity");
}

for(int x = 1;x<=capacity;x++)
{
    String vals = String.format("%03d", x);
    pst = conn.prepareStatement("Select SeatNumber from tblcustomer WHERE TrxnDate='"+date+"' AND SeatNumber!= '"+vals+"'");
    rs = pst.executeQuery();
    if(rs.next())
    {
        write.print("<option>"+vals+"</option>");
    }
}

It will generate seat numbers according to the capacity per accommodation. How to eliminate the existing seat number which is already stored in tblcustomer?

jdbc java
1 Answers

Here's the code I tried to solve 

int capacity = rs.getInt("Capacity");
List<String> list = new ArrayList<>();
for(int x = 1;x<=capacity;x++)
{
    list.add(String.format("%03d", x));
} 
pst = conn.prepareStatement("Select SeatNumber from tblcustomer WHERE TrxnDate=? AND Seat_Type=? AND ID=?");
pst.setString(1, date);
pst.setString(2, seat);
pst.setInt(3, id);
rs = pst.executeQuery();
while(rs.next())
{
    String seats = rs.getString("SeatNumber");
    list.removeIf(seats::equals);
}
Object[] objects = list.toArray();
for (Object obj : objects)
write.print("<option>"+obj+"</option>");

It could easily identify the unavailable and available seats. Thanks @Thomas

Ask Warvin0
Related