jeudi 2 juillet 2015

Handling SQL Null Parameters Elegantly

I'm generating an SQL statement by checking if each of the column fields submitted to the query are empty (== null) or not. It seems that my approach is pretty naive so I'm wondering what can be done about handling null parameters elegantly. When something isn't specified it should simply match anything.

Here is the code:

public List<Flight> findMatchingFlights(Flight flight)
{
    List<Flight> foundFlights = new ArrayList<>();
    StringBuilder sqlQueryBuilder = new StringBuilder();
    sqlQueryBuilder.append("SELECT * FROM Flights");
    boolean emptyQuery = true;

    if(flight.getDeparture() != null)
    {
        if(emptyQuery)
        {
            sqlQueryBuilder.append(" WHERE ");
            emptyQuery = false;
        }

        sqlQueryBuilder.append("Departure = '" + flight.getDeparture() + "'");
    }

    if(flight.getArrival() != null)
    {
        if(emptyQuery)
        {
            sqlQueryBuilder.append(" WHERE ");
            emptyQuery = false;
        }
        else
        {
            sqlQueryBuilder.append(" AND ");
        }

        sqlQueryBuilder.append("Arrival = '" + flight.getArrival() + "'");
    }

    if(flight.getFlightNumber() != null)
    {
        if(emptyQuery)
        {
            sqlQueryBuilder.append(" WHERE ");
            emptyQuery = false;
        }
        else
        {
            sqlQueryBuilder.append(" AND ");
        }

        sqlQueryBuilder.append("Number = '" + flight.getFlightNumber() + "'");
    }

    if(flight.getFlightMinutes() != 0)
    {
        if(emptyQuery)
        {
            sqlQueryBuilder.append(" WHERE ");
            emptyQuery = false;
        }
        else
        {
            sqlQueryBuilder.append(" AND ");
        }

        sqlQueryBuilder.append("Duration = " + flight.getFlightMinutes());
    }

    /*
    ...
    A bunch more fields
    */

    if(flight.getAirplane() != null)
    {
        if(emptyQuery)
        {
            sqlQueryBuilder.append(" WHERE ");
        }
        else
        {
            sqlQueryBuilder.append(" AND ");
        }

        sqlQueryBuilder.append("Airplane = '" + flight.getAirplane() + "'");
    }

    sqlQueryBuilder.append(";");

    // Execute sql and fill list with rows that match
}

Aucun commentaire:

Enregistrer un commentaire