用Java控制数据库中的数据

Question

try {

    Class.forName("com.mysql.cj.jdbc.Driver");
    //  out.println("driver loaded");
    Connection  con = DriverManager.getConnection("jdbc:mysql://localhost:3306/Hotel?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC", "root", "123456789");
    out.println("Connect");
    Statement  st = con.createStatement();
    Statement stmt = con.createStatement();
    out.println("connection successfull");

    String check = ("SELECT res1.id_prenotazione, res1.typeroom, res1.arrivaldate, res1.departuredate\n" +
        "FROM reservation res1, reservation res2\n" +
        "WHERE ( res1.typeroom = res2.typeroom ) \n" +
        "AND res1.id_prenotazione != res2.id_prenotazione\n" +
        "AND (res1.arrivaldate <= res2.departuredate)\n" +
        "AND (res2.arrivaldate <= res1.departuredate)");


    // ResultSet rs2  = stmt.executeQuery(check);
    out.println("<h1> Stringa chek eseguita </h1>");

    if (check) { // THIS DOESN't WORK OF COURSE
        int rs = st.executeUpdate("insert into reservation (login,email,typeroom,numroom,arrivaldate,departuredate)values ('" + login + "','" + email + "','" + typeroom + "','" + numroom + "','" + arrivaldate + "','" + departuredate + "')");
    }
    String getResultSet = ("SELECT count(*) FROM reservation WHERE arrivaldate ='" + arrivaldate + "'");
    String rs1 = ("SELECT count(*) FROM reservation WHERE arrivaldate");


    if (getResultSet != rs1) {
        int i = st.executeUpdate("DELETE FROM reservation WHERE id_prenotazione ='" + id_prenotazione + "'");

    } 

Answer 1

您在check SELECT中使用了同一个表的联接，但还没有插入任何内容，因此查询毫无意义。您需要对照要存储的保留的值进行检查。

SELECT COUNT(*) 
FROM reservation 
WHERE numroom = $roomnumber -- I am guessing numroom is room number
  AND ($startdate >= arrivaldate AND $startdate <= departuredate)
   OR ($enddate >= arrivaldate AND $enddate <= departuredate)

用您的值替换变量($...)。您可能希望了解如何相互验证日期，在比较中可能应该是<、>而不是<=、>=。

在Java中，这将是

String query = "SELECT COUNT(*) FROM reservation WHERE numroom = '" + numroom + "' AND ('" + arrivaldate + "' >= arrivaldate AND '" + arrivaldate + "' <= departuredate) OR ('" + departuredate + "' >= arrivaldate AND '" + departuredate + "' <= departuredate)