How to retrieve a data between dates

Question

I have 2 tables. A booking table and a room table. In the booking table I have the following columns: BookingID StartDate EndDate CustomerID RoomID In the Room table I have the following columns: RoomID RoomSize

I am creating a booking system. I want to be able to query the database where I am able to get a list of rooms that are booked between 2 dates which are also based on size (small, medium or large) types.

E.g. if user clicks on small room and enters dates between 2010-02-02 to 2010-02-25 then 4 should appear as my database contains 4 small rooms that are booked between those dates.

This is what I have so far:

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

This is the error I am getting:

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)

I am new to SQL and having trouble doing this. Also, is my logic right? startD and endD represents the dates that the user has entered and typeOfRoom represent the type of the room the user wants to book; e.g. eithier Small, Medium or Large

type is a string and can either be Small Medium or Large` based on what the user chose — john cena
This is an exact duplicate of stackoverflow.com/q/36215437/5830574 and stackoverflow.com/q/36214362/5830574. Perhaps you should follow the suggestions and quote your RoomSize like I already told you: Your stmt gives ... and RoomSize=Medium while it should be ... and RoomSize='Medium'. Please don't triple-post the very same question. — PerlDuck

Andreas Andreas · Accepted Answer · 2016-03-26T19:12:43

Do not use string concatenation to insert user-supplied values into SQL, especially for strings. It will leave you open to SQL Injection attacks, and SQL syntax issues. Use a PreparedStatement.

Also, replace is with =.

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
        }
    }
}

How to retrieve a data between dates

3 Answers