CODEWITHSUNDEEP
javamysqlsql
john cena
john cena

Reputation: 21

How to retrieve a data between dates

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

Upvotes: 0

Views: 78

Answers (3)

Andreas
Andreas

Reputation: 159096

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

Upvotes: 1

Pravin Umamaheswaran
Pravin Umamaheswaran

Reputation: 704

Can you try if this statement works? I have replaced the 'is' keyword with '=' operator and put all the variables between "".

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

Upvotes: 0

gustf
gustf

Reputation: 2017

The date handling look ok I think, but you need to quote the type string in the statement. And you should not use is, just use normal =

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

Upvotes: 0

Related Questions