Checking for overlapping appointments in database not working?

I’m saving appointments to a database and have a method that checks if times are overlapping.

public static boolean checkForOverlappingAppointment(Timestamp start, Timestamp end, int 
customerID) {    
try {        
String sql = "SELECT * FROM appointments WHERE Customer_ID = ?";        
PreparedStatement ps = JDBC.getConnection().prepareStatement(sql);
ps.setString(1, String.valueOf(customerID));
ResultSet rs = ps.executeQuery();       
while (rs.next()) { 
    Timestamp startOtherAppts = rs.getTimestamp("Start");
    Timestamp endOtherAppts = rs.getTimestamp("End");   
     if ((start.before(startOtherAppts) && end.after(endOtherAppts)) ||                    
     (start.before(startOtherAppts) && end.after(startOtherAppts) && 
     end.before(endOtherAppts)) || (start.after(startOtherAppts) && 
     start.before(endOtherAppts) && end.after(endOtherAppts)) ||                    
     (start.after(startOtherAppts) && end.before(endOtherAppts))) 
     {    
     return true;
        }
    }
} 
catch (SQLException e) {
    e.printStackTrace();    }
   return false;
}

I’m calling the method using

if (DBAppointments.checkForOverlappingAppointment(Timestamp.valueOf(startDateTimeZDT.toLocalDateTime()), Timestamp.valueOf(endDateTimeZDT.toLocalDateTime()), customerIDint)) {
Alert alert3 = new Alert(Alert.AlertType.ERROR);
alert3.setHeaderText("APPOINTMENT OVERLAP");
alert3.setContentText("Appointment overlaps with an existing appointment.");    
alert3.showAndWait();}

I don’t receive any errors when saving appointments although they can be for the same exact times.
I’m converting a LocalTime to a ZonedDateTime to save in the DB by using Timestamp.valueOf(startDateTimeZDT.toLocalDateTime() Timestamp.valueOf(endDateTimeZDT.toLocalDateTime()

I can’t seem to find out where it is going wrong at. Any help would be great!

Answer

Your database may do that work for you.

public static boolean checkForOverlappingAppointment(
        LocalDateTime start, LocalDateTime end, int customerID) {
    try {
        String sql = "SELECT *"
                + " FROM appointments"
                + " WHERE Customer_ID = ? and Start < ? and end > ?";
        PreparedStatement ps = JDBC.getConnection().prepareStatement(sql);
        ps.setInt(1, customerID);
        ps.setObject(2, end);
        ps.setObject(3, start);
        ResultSet rs = ps.executeQuery();
        // If there is a result row, we know it’s an overlapping appointment
        return rs.next();
    }
    catch (SQLException e) {
        e.printStackTrace();
    }
    return false;
}

java.time: I recommend that you use java.time for your dates and times. Assuming that your start and end columns have type datetime or timestamp (without time zone), LocalDateTime is the correct corresponding type to use in Java. Since JDBC 4.2 you can directly pass LocalDateTime and other java.time classes to your prepared statement using its setObject method.

Checking for overlap: Your logic was overly complicated for this check. The argument may be a bit tricky, but as you can see, my code is simple. For each appointment already in the database there are three possibilities: either it’s earlier, it’s overlapping or it’s later. If its start is before the end of this appointment we know it’s not later. If its end is after the start of this appointment, it cannot be earlier. If both conditions are fulfilled, it must be overlapping.

Was there an error in your logic? First of all I found your if condition unreadable. My code formatter formatted it in this way, which helps quite a bit:

        if ((start.before(startOtherAppts) && end.after(endOtherAppts))
                || (start.before(startOtherAppts)
                        && end.after(startOtherAppts)
                        && end.before(endOtherAppts))
                || (start.after(startOtherAppts)
                        && start.before(endOtherAppts)
                        && end.after(endOtherAppts))
                || (start.after(startOtherAppts) && end.before(endOtherAppts))) {
            return true;
        }

I think you are not taking the cases into account where either both appointments have the exact same start time or they both have the same end time. You are always only checking whether one time is strictly before or strictly after the other.

Error handling: I don’t think you will want just to return false in case of an SQL error.

Tutorial link: Oracle Tutorial: Date Time