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.

Alexey Soshin · Accepted Answer · 2018-09-02 10:39:15Z

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

edited Sep 2, 2018 at 10:39

answered Sep 2, 2018 at 10:33

Alexey Soshin

17.9k2 gold badges37 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alexey Soshin Over a year ago

Always happy to help.

user10138349 Over a year ago

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 :)

Collectives™ on Stack Overflow

Database query and Java

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related