java - 比较 SQL 中的元素(房间预订 WebApp)Java

Question

我创建了一个简单的 Web 应用程序。我需要检查，当有人预订房间时，arrival_date 是否不同。例如:人A:预订房间的数据:12/07/2018B 人:在数据中预订房间:12/07/2018(他不能，因为还有 A 人)。

我有这个数据库:

hotel_booking

cid, cname, email, arrival_date, departure_date, person, room, type_room,comment, status, uid.

这是我的代码

HotelBooking.java

package hotel;


import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;


/**
 *
 * @author OOPs
 */
public class Hotelbooking extends HttpServlet {

    /**
     * Processes requests for both HTTP <code>GET</code> and <code>POST</code>
     * methods.
     *
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();
        String s1 = request.getParameter("id");
        String s2 = request.getParameter("name");
           String s3 = request.getParameter("email");
           String s4 = request.getParameter("arrivaldate");
           String s5 = request.getParameter("departuredate");
           String s6 = request.getParameter("person");
           String s7 = request.getParameter("rooms");
           String s8 = request.getParameter("type_room");
           String s9 = request.getParameter("comment");
           String s10 = request.getParameter("status");
           
           // sesson..........
           HttpSession session=request.getSession(true);
          String s11 = (String)session.getAttribute("sessname");

       out.println(s11);
             
         
   
          
       try {
           
           
            Class.forName("com.mysql.jdbc.Driver");
      //  out.println("driver loaded");
     Connection  con = DriverManager.getConnection("jdbc:mysql://localhost:3306/HotelReservation","root" ,"123456789");
      //  out.println("Connect");
      Statement  st =  con.createStatement();
        //  out.println("conncetion successfull");
     
       
       int rs =  st.executeUpdate("insert into hotel_booking (cname,email,arrival_date,departure_date,person,room,type_room,comment,status,uid)values ('"+s2+"','"+s3+"','"+s4+"','"+s5+"','"+s6+"','"+s7+"','"+s8+"','"+s9+"','"+s10+"','"+s11+"')");
         // out.println(s1);
         // out.println(s2);
          out.println("<h1> Register sucsefulltttt </h1>");
           
          /* if(rs == 1)
           {
           out.println("<script type=\"text/javascript\">");
       out.println("alert('send successfully');");
       out.println("</script>");
       //response.sendRedirect("home.jsp");
           }else{
       
       
       }*/
           
          response.sendRedirect("thankyoureg.jsp"); 
           
           
           
        }catch(Exception e){
        out.println("nahiiiiiiiiiiiii" +e);
        }
        finally {
            out.close();
        }
    }

    // <editor-fold defaultstate="collapsed" desc="HttpServlet methods. Click on the + sign on the left to edit the code.">
    /**
     * Handles the HTTP <code>GET</code> method.
     *
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        processRequest(request, response);
    }

    /**
     * Handles the HTTP <code>POST</code> method.
     *
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        processRequest(request, response);
    }

    /**
     * Returns a short description of the servlet.
     *
     * @return a String containing servlet description
     */
    @Override
    public String getServletInfo() {
        return "Short description";
    }// </editor-fold>

}

你认为我该怎么办？感谢您的帮助。

Answer 1

检查如下查询是否返回 0。

SELECT count(*)
       FROM hotel_booking
       WHERE arrival_date <= $1
             AND departure_date > $1,
             AND room = $2;

如果返回0，则表明客人希望入住的当天房间未被占用。否则，则为被占用。

将 $1 替换为客人希望抵达的日期，将 $2 替换为房间(号码、姓名或您使用的任何内容)。

我假设 checkin / checkout 时间的处理方式如下:

• 在指定日期的晚上签到。
• 在指定日期的早上退房。接下来，一位客人可以在另一位客人退房的当天办理入住。

如果这个假设是错误的，您就必须相应地调整比较运算符。

还应考虑使用参数化查询，而不是与未经净化的用户输入值进行字符串连接。后者很危险，您的应用程序很容易受到 SQL 注入(inject)攻击。参数化查询还有助于找到给定数据类型的正确表示，从而防止因错误表示的值而产生的错误。