src/com/vehiclerental/dataLayer/sqliteImplementation/VehicleDaoSqliteImpl.java
/**
     * This methods returns the main SQL query used to search available vehicles or detect if a specific vehicle is available for given dates
     * The template is built using the String.format system of Java, which is why I use integer placements (%x$d). They will be replaced later
     * during the population of the query with the given numbers
     * I am using a raw SQL query here because the ORM doesn't support all the used SQL features for this case
     *
     * This query involves a custom part depending of we are looking for a specific vehicle or all the vehicles available
     *
     * @param isVehicleSpecificSearch - true if it concerns the search of a specific vehicle
     * @return The SQL query template
     */
This methods returns the main SQL query used to search available vehicles or detect if a specific vehicle is available for given dates
The template is built using the String.format system of Java, which is why I use integer placements (%x$d). They will be replaced later
during the population of the query with the given numbers
I am using a raw SQL query here because the ORM doesn't support all the used SQL features for this case

This query involves a custom part depending of we are looking for a specific vehicle or all the vehicles available

@param isVehicleSpecificSearch - true if it concerns the search of a specific vehicle
@return The SQL query template
This methods returns the main SQL query used to search available vehicles or detect if a specific vehicle is available for given dates The template is built using the String . format system of Java which is why I use integer placements ( %x$d ) . They will be replaced later during the population of the query with the given numbers I am using a raw SQL query here because the ORM doesn ' t support all the used SQL features for this case This query involves a custom part depending of we are looking for a specific vehicle or all the vehicles available @param isVehicleSpecificSearch - true if it concerns the search of a specific vehicle @return The SQL query template
private String buildQueryTemplate(boolean isVehicleSpecificSearch) {
        String customPart;
        String queryTemplate;
        if (isVehicleSpecificSearch) {
            customPart = "`ve`.`" + Vehicle.ID_FIELD_NAME + "` = %4$d ";
        } else {
            customPart = "`ve`.`" + Vehicle.TYPE_FIELD_NAME + "` = %4$d ";
        }
        queryTemplate =
            "SELECT * FROM `" + OrmLiteHelper.VEHICLE_TABLE_NAME + "` ve WHERE " + 
            "( " +
                "`ve`.`" + Vehicle.BRANCH_FIELD_NAME + "` = %1$d " + 
                "AND " +
                "`ve`.`" + Vehicle.STATUS_FIELD_NAME + "` != " + Vehicle.Status.MAINTENANCE.getCode() + " " + 
                "AND " +
                customPart + 
                "AND " +
                "( " +
                    "NOT EXISTS " + 
                    "( " +
                        "SELECT * FROM `" + OrmLiteHelper.BOOKING_TABLE_NAME + "` bo " + 
                            "LEFT JOIN `" + OrmLiteHelper.VEHICLE_MOVE_TABLE_NAME + "` vm " + 
                            "ON `vm`.`" + VehicleMove.ID_FIELD_NAME + "` = `bo`.`" + Booking.VEHICLE_MOVE_FIELD_NAME + "` " + 
                        "WHERE " +
                        "( " +
                            "`bo`.`" + Booking.VEHICLE_FIELD_NAME + "` = `ve`.`" + Vehicle.ID_FIELD_NAME+ "` " + 
                            "AND " +
                            "`bo`.`" + Booking.BOOKING_VALIDATED_FIELD_NAME + "` = 1 " + 
                            "AND " +
                            "( " +
                                "(" + getIfNullStart() + " < %2$d AND " + getIfNullEnd() + " > %3$d) " +
                                "OR " +
                                "(" + getIfNullStart() + " < %2$d AND " + getIfNullEnd() + " <= %3$d AND " + getIfNullEnd() + " >= %2$d) " +
                                "OR " +
                                "(" + getIfNullStart() + " >= %2$d AND " + getIfNullEnd() + " > %3$d AND " + getIfNullStart() + " <= %3$d) " +
                                "OR " +
                                "(" + getIfNullStart() + " >= %2$d AND " + getIfNullEnd() + " <= %3$d) " +
                            ") " +
                        ") " +
                    ") " +
                ") " +
            ") ";
        return queryTemplate;
    }
private String buildQueryTemplate ( boolean isVehicleSpecificSearch ) { String customPart ; String queryTemplate ; if ( isVehicleSpecificSearch ) { customPart = "`ve`.`" + Vehicle . ID_FIELD_NAME + "` = %4$d " ; } else { customPart = "`ve`.`" + Vehicle . TYPE_FIELD_NAME + "` = %4$d " ; } queryTemplate = "SELECT * FROM `" + OrmLiteHelper . VEHICLE_TABLE_NAME + "` ve WHERE " + "( " + "`ve`.`" + Vehicle . BRANCH_FIELD_NAME + "` = %1$d " + "AND " + "`ve`.`" + Vehicle . STATUS_FIELD_NAME + "` != " + Vehicle . Status . MAINTENANCE . getCode ( ) + " " + "AND " + customPart + "AND " + "( " + "NOT EXISTS " + "( " + "SELECT * FROM `" + OrmLiteHelper . BOOKING_TABLE_NAME + "` bo " + "LEFT JOIN `" + OrmLiteHelper . VEHICLE_MOVE_TABLE_NAME + "` vm " + "ON `vm`.`" + VehicleMove . ID_FIELD_NAME + "` = `bo`.`" + Booking . VEHICLE_MOVE_FIELD_NAME + "` " + "WHERE " + "( " + "`bo`.`" + Booking . VEHICLE_FIELD_NAME + "` = `ve`.`" + Vehicle . ID_FIELD_NAME + "` " + "AND " + "`bo`.`" + Booking . BOOKING_VALIDATED_FIELD_NAME + "` = 1 " + "AND " + "( " + "(" + getIfNullStart ( ) + " < %2$d AND " + getIfNullEnd ( ) + " > %3$d) " + "OR " + "(" + getIfNullStart ( ) + " < %2$d AND " + getIfNullEnd ( ) + " <= %3$d AND " + getIfNullEnd ( ) + " >= %2$d) " + "OR " + "(" + getIfNullStart ( ) + " >= %2$d AND " + getIfNullEnd ( ) + " > %3$d AND " + getIfNullStart ( ) + " <= %3$d) " + "OR " + "(" + getIfNullStart ( ) + " >= %2$d AND " + getIfNullEnd ( ) + " <= %3$d) " + ") " + ") " + ") " + ") " + ") " ; return queryTemplate ; }
This methods returns the main SQL query used to search available vehicles or detect if a specific vehicle is available for given dates
 The template is built using the String.format system of Java, which is why I use integer placements (%x$d).
This methods returns the main SQL query used to search available vehicles or detect if a specific vehicle is available for given dates The template is built using the String . format system of Java which is why I use integer placements ( %x$d ) .
//If we want to search a single vehicle, then the request will target a vehicle ID //If not, we will target a vehicle type ID // Search vehicles // Search in a specific branch //The vehicle should not be available if under maintenance //Insert custom part defined at the beginning of the method // We want to find every vehicle that doesn't have any booking matching the next rules //Select all from bookings //With vehicles moves (nullified if no move required) // Simple foreign key binding //This is a sub-query, and we want to get all the bookings corresponding to the ID of the parent request for the vehicle //We also want the booking to be validated //Is there any booking starting before and ending after the specified dates (Booking would be in the middle of the existing booking) //Is there any booking starting before the beginning and ending before the ending specified dates (with limits to stay inside the period) (Booking would be too early for the previous booking to end) //Is there any booking starting after the beginning and ending after the ending specified dates (with limits to stay inside the period) //Is there any booking starting after the beginning and ending before the ending specified dates