Room reservation check

Question

in my project i have created a system to book a room. My problem concerns the booking of a room on the same date. This is the DB about reservation.

id_book,login,email,typeroom,numroom,arrivaldate,departuredate.

And this is the code to check if a room is available in a period:

try {

            Class.forName("com.mysql.cj.jdbc.Driver");
      //  out.println("driver loaded");
            Connection  con = DriverManager.getConnection("jdbc:mysql://localhost:3306/Hotel?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC","root" ,"123456789");
            out.println("Connect");
            Statement  st =  con.createStatement();
            Statement stmt = con.createStatement();
            out.println("connection successfull");

            String check = ("SELECT res1.id_prenotazione, res1.typeroom, res1.arrivaldate, res1.departuredate\n" + 
                    "FROM reservation res1, reservation res2\n" + 
                    "WHERE ( res1.typeroom = res2.typeroom ) \n" + 
                    "AND (res1.arrivaldate <= res2.departuredate)\n" + 
                    "AND (res2.arrivaldate <= res1.departuredate)");
            String check1 = ("SELECT count(*) FROM reservation WHERE arrivaldate");

            ResultSet rs2  = stmt.executeQuery(check);
            ResultSet rs3 = stmt.executeQuery(check1);
            if( rs2 != rs3) {
            int rs =  st.executeUpdate("insert into reservation (login,email,typeroom,numroom,arrivaldate,departuredate)values ('"+login+"','"+email+"','"+typeroom+"','"+numroom+"','"+arrivaldate+"','"+departuredate+"')");
            }
            String getResultSet = ("SELECT count(*) FROM reservation WHERE arrivaldate ='"+arrivaldate+"'");
            String rs1 = ("SELECT count(*) FROM reservation WHERE arrivaldate");


            if (getResultSet != rs1) {
                int i=st.executeUpdate("DELETE FROM reservation WHERE id_prenotazione ='"+id_prenotazione+"'");

            }

The problem is that in this way I keep recording the same rooms with the same date, how can i solve?

Unrelated to your problem, but you really need to start using [PreparedStatements](https://stackoverflow.com/questions/3271249/difference-between-statement-and-preparedstatement). — Kayaman, Aug 22 '18 at 11:45
Comparing a `ResultSet` with `!=` is obviously not going to work. Also `WHERE arrivaldate` doesn't look like a valid WHERE clause to me. — M. Deinum, Aug 22 '18 at 13:24

score 0 · Accepted Answer · answered Aug 22 '18 at 13:21

0

I did not get your question fully. The part that I understood is you obviously don't want to overbook rooms on a given date.
If it is the case, while showing available rooms, you can fire count(*) query for that date. There is no need to any update operation at that time.
It is at the time of actual booking, you need to handle the overbooking either by taking proper lock or writing query in a way that no. of rooms does not go below 0.

answered Aug 22 '18 at 13:21

NumeroUno

1,100
2
14
34

Yes, i want only don't allow to book a room in the same period. – Aug 22 '18 at 14:27
so, you can use locking at either app level or at database level during actual booking. You can also change query as select * from reservation where numroom > 0. It will return rowt ill rooms are available. – NumeroUno Aug 22 '18 at 14:36

Room reservation check

1 Answers1