Java – Handles SQL null parameters gracefully

Handles SQL null parameters gracefully… here is a solution to the problem.

Handles SQL null parameters gracefully

I generate an SQL statement by checking if each column field submitted to the query is null (== null). It seems that my approach is naïve, so I was wondering how to handle empty arguments gracefully. When something is not specified, it should simply match anything.

The code is as follows:

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
}

Solution

You can create a generic method for the following block and call the method by passing parameters.

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

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

Related Problems and Solutions