数据库查询和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()){

   rs3.getInt("total");

}

应该：

rs3.next(); // You'll ever have only one row

total = rs3.getInt("total");

其次，永远不要在查询中使用串联：

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

始终使用 PreparedStatements 代替：

 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