I'm working on a hotel reservation management project. Except for reserved rooms during a specific period, only available rooms are displayed If you create a query statement and run it, selectP22?checkin1=2020-11-18&checkout1=2020-11-18&roomtype=SweetRoom:1 Failed to load resource: the server responded with a status of 500 (Internal Server Error) There's an error. This error does not appear when it is a simple sql statement, but if you try it with the sql statement below, the error will occur. How should I fix this? I'm asking you a question because I don't know.
request.setCharacterEncoding("utf-8");
response.setContentType("text/html; charset=utf-8");
String checkin1 = request.getParameter("checkin1");
System.out.println(checkin1);
String checkout1 = request.getParameter("checkout1");
System.out.println(checkout1);
String roomtype = request.getParameter("roomtype");
System.out.println(roomtype);
url="jdbc:oracle:thin:@localhost:1521:orcl";
userid=";//covering
passwd="; //Cover
Connection conn=null;
PreparedStatement stmt=null;
ResultSet rs=null;
PrintWriter out=response.getWriter();
try {
String sql="select * from roommanage where room_id not in "
+ + "(select room_id from bookmanage where (check_indate>TO_DATE(?) and check_indate<TO_DATE(?) or "
+ + "(check_outdate >TO_DATE(?) and check_outdate<TO_DATE(?)))) and room_type like ?;";
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection(url,userid,passwd);
stmt=conn.prepareStatement(sql);
System.out.println(sql);
stmt.setString(1, checkin1);
stmt.setString(2, checkout1);
stmt.setString(3, checkin1);
stmt.setString(4, checkout1);
stmt.setString(5, roomtype);
rs=stmt.executeQuery();
JSONArray ja = new JSONArray();
while(rs.next()) {
JSONObject jo =new JSONObject();
jo.put("roomid",rs.getString("ROOM_ID"));
jo.put("roomname",rs.getString("ROOM_NAME"));
jo.put("roomtype",rs.getString("ROOM_TYPE"));
jo.put("howmuch",rs.getInt("HOWMUCH"));
jo.put("howmany",rs.getInt("HOWMANY"));
ja.add(jo);
}
out.println(ja.toString());
out.close();
} } catch(Exception e) {
out.println("error");
} } finally {
try {
conn.close();
stmt.close();
rs.close();
} } catch (SQLException e) {
// // TODO Auto-generated catch block
e.printStackTrace();
}
}
I think it's about the number of brackets that open and close. Can you check if the query below is okay?
SELECT *
FROM roommanage r
WHERE r.room_id NOT IN (
SELECT b.room_id
FROM bookmanage b
WHERE (
(b.check_indate > TO_DATE(?) AND b.check_indate < TO_DATE(?))
OR (b.check_outdate > TO_DATE(?) AND b.check_outdate < TO_DATE(?))
)
)
AND r.room_type LIKE ?
+ roommanage and bookmanage I think it would be simpler to query JOIN but it was hard to look into it more, so I just organized the query.
© 2024 OneMinuteCode. All rights reserved.