How to iterate Arrival and Departure date in Java, Mysql

Question

I have combobox for the type of room and 2 text boxes for the Arrival and Departure, What I want is when the Arrival = 2017-09-01 - Departure = 2017-09-05 is already exist, The customer will not able to choose 2017-09-02 to 2017-09-05 in the same type of room

String sql = "SELECT * FROM reservation WHERE room=? and arrival=? and 
departure=?";

try{
        pst=conn.prepareStatement(sql);
        pst.setString(1, ComboBox.getSelectedItem().toString());
        pst.setString(2, txtArrival.getText());
        pst.setString(3, txtDeparture.getText());
        rs=pst.executeQuery();
if(rs.next()){
// the customer needs to choose other date or room
}
else{
// continue to fill up the form below
}
}

enter image description here

Do you mean, you have one room, and this room can be booked, the database store start/end. Then if others what to book this room, and it input the start/end, then you want to search if the room is available? — forqzy, Sep 24 '17 at 15:04
If you're storing the dates within one database column in format `arrival-departure`, then it will be really difficult to do date comparisons or arithmetic. Instead, you should have two columns, `arrival` and `departure`, and have `DATE` as their data type. — Mick Mnemonic, Sep 24 '17 at 19:51
Yes @forqzy. My problem is the program checks the arrival and departure only. Not the range of date. What i want is when i check the availability of the room it will find a Available date for a specific room. A range o dates, not Arrival and Departure only — Francis, Sep 25 '17 at 00:07

Basil Bourque · Answer 1 · 2017-09-24T15:24:30.973

Store your date values in a column of a date data type, not text.

Use appropriate objects rather than mere strings.

LocalDate arrival = LocalDate.parse( "2017-09-01" ) ;

Pass that object to your PreparedStatement.

myPreparedStatement.setObject( 2 , arrival ) ;

And for ResultSet.

LocalDate arrival = myResultSet.getObject( 2 , LocalDate.class ) ;

Fix the logic of your SQL query. You should not be looking for exact date matches.

All of this has been covered many times already on Stack Overflow. Search thoroughly before posting.

How to iterate Arrival and Departure date in Java, Mysql

1 Answers1