Database query and Java

Question

I'm studying at a project about the creation of simple web application. I'm creating a webapp about an Hotel and I have a problem about the reservation of a room. I have 3 kind of rooms and I want when someone book a room, another one can't book the same room in the same period. The problem is about this kind of control. I have write this code:

UPDATE CODE AFTER AN ANSWER

 Statement st = con.createStatement();
 Statement stmt = con.createStatement();
 out.println("connection successfull");
 int total = 0;
 ResultSet rs3 = stmt.executeQuery( "SELECT COUNT(*) as total FROM reservation WHERE idRoom = '" + idRoom + 
 "' AND ('" + arrivaldate + "' >= arrivaldate AND '" + arrivaldate + "' <= departuredate) OR ('" + departuredate + "' >= arrivaldate "
 + "AND '" + departuredate + "' <= departuredate)");
 rs3.next(); // You'll ever have only one row
 total = rs3.getInt("total");
 /* String query = "SELECT COUNT(*) FROM reservation WHERE idRoom = '" + idRoom + 
 "' AND ('" + arrivaldate + "' >= arrivaldate AND '" + arrivaldate + "' <= departuredate) OR ('" + departuredate + "' >= arrivaldate "
 + "AND '" + departuredate + "' <= departuredate)" ;
 */
 // ResultSet rs2 = stmt.executeQuery(check);
 out.println("<h1> Stringa check eseguito </h1>");
 if( total > 0) { // THIS DOESN't WORK OF COURSE 
 response.sendRedirect("home.jsp");
 }
 else {
 st.executeUpdate("insert into reservation (login,email,typeroom,idRoom,arrivaldate,departuredate)values ('"+login+"','"+email+"','"+typeroom+"','"+idRoom+"','"+arrivaldate+"','"+departuredate+"')");
 }

But it doesn't work properly because it lets me to book the same room in the same data. How can I do in your opinion? Thank you for your attention.

Answer 1

First, you totally ignore your total:

while(rs3.next()){
 rs3.getInt("total");
}

Should be:

rs3.next(); // You'll ever have only one row
total = rs3.getInt("total");

And second, never ever use concatenations in your queries:

ResultSet rs3 = stmt.executeQuery( "SELECT COUNT(*) as total FROM reservation WHERE idRoom = '" + idRoom + 
 "' AND ('" + arrivaldate + "' >= arrivaldate AND '" + arrivaldate + "' <= departuredate) OR ('" + departuredate + "' >= arrivaldate "
 + "AND '" + departuredate + "' <= departuredate)");

Always use PreparedStatements instead:

 PreparedStatement ps = stmt.prepareStatement( "SELECT COUNT(*) as total FROM reservation WHERE idRoom = ? AND (? >= arrivaldate AND ? <= departuredate) OR (? >= arrivaldate AND ? <= departuredate)");
int c = 0;
ps.setInt(++c, idRoom);
ps.setDate(++c, arrivaldate);
ps.setDate(++c, departuredate);
ps.setDate(++c, arrivaldate);
ps.setDate(++c, departuredate);
ResultSet rs = ps.executeQuery();
// And your usual code here

• Alexey Soshin: Always happy to help.
• user10138349: Whit the first advice you have answer my question, but i don't have understand well your second answer! I update my code in the first post :)