java - 如何检索日期之间的数据

Question

我有两张 table 。预订表和房间表。在预订表中，我有以下列: BookingID StartDate EndDate CustomerID RoomID在 Room 表中，我有以下列: RoomID RoomSize

我正在创建一个预订系统。我希望能够查询数据库，从中获得在 2 个日期之间预订的房间列表，这些房间也基于尺寸(小型、中型或大型)类型。

例如如果用户点击小房间并输入 2010 年 2 月 2 日至 2010 年 2 月 25 日之间的日期，则应显示 4，因为我的数据库包含在这些日期之间预订的 4 个小房间。

这是我到目前为止所拥有的:

 String sqlStatement = "select RoomID from Booking where RoomID in (select Room.RoomID from Room where Room.RoomSize is " + type + ") AND ((Booking.StartDate between "+ startD +" AND " + endD + ") OR (Booking.EndDate between "+ startD + " AND " + endD + "))";

这是我收到的错误:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Medium) AND ((Booking.StartDate between 2016-02-09 AND 2016-02-09) OR (Booking.E' at line 1
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:408)

我是 SQL 新手，在执行此操作时遇到困难。另外，我的逻辑对吗？startD和endD表示用户输入的日期，typeOfRoom表示用户想要预订的房间类型；例如小、中或大

Answer 1

不要使用字符串连接将用户提供的值插入到 SQL 中，尤其是字符串。它会让你对SQL Injection保持开放。攻击和 SQL 语法问题。使用PreparedStatement。

此外，将 is 替换为 =。

String sql = "select RoomID" +
              " from Booking" +
             " where RoomID in (" +
                       "select Room.RoomID" +
                        " from Room" +
                       " where Room.RoomSize = ?" +
                    ")" +
               " and ((Booking.StartDate between ? AND ?)" +
                 " or (Booking.EndDate between ? AND ?))";
try (PreparedStatement stmt = conn.prepareStatement(sql)) {
    stmt.setString(1, type);
    stmt.setDate  (2, startD);
    stmt.setDate  (3, endD);
    stmt.setDate  (4, startD);
    stmt.setDate  (5, endD);
    try (ResultSet rs = stmt.executeQuery()) {
        while (rs.next()) {
            // code here
        }
    }
}