数据库查询和Java

Question

我正在学习一个关于创建简单 Web 应用程序的项目。我正在创建一个关于酒店的网络应用程序，但我在预订房间时遇到了问题。我有3种房间，我想当有人预订一个房间时，另一个不能同时预订同一个房间。问题在于这种控制。我写了这段代码：

回答后更新代码

 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+"')");

        }

但它不能正常工作，因为它让我可以在相同的数据中预订相同的房间。在你看来我该怎么办？感谢您的关注。

Answer 1

首先，你完全忽略你的总数：while(rs3.next()){&nbsp; &nbsp;rs3.getInt("total");}应该：rs3.next(); // You'll ever have only one rowtotal = rs3.getInt("total");其次，永远不要在查询中使用串联：ResultSet rs3 = stmt.executeQuery( "SELECT COUNT(*) as total FROM reservation WHERE idRoom = '" + idRoom +&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "' AND ('" + arrivaldate + "' >= arrivaldate AND '" + arrivaldate + "' <= departuredate) OR ('" + departuredate + "' >= arrivaldate "&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; + "AND '" + departuredate + "' <= departuredate)");始终使用 PreparedStatements 代替：&nbsp;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