CODEWITHSUNDEEP
javaandroidsqlsqlite
BullyWiiPlaza
BullyWiiPlaza

Reputation: 19185

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
}

Upvotes: 3

Views: 91

Answers (3)

Johnson Abraham
Johnson Abraham

Reputation: 781

Best approach would be to do the trick in SQL than checking for null in Java. This is how you can do it.

sqlQueryBuilder.append("(Number = '" + flight.getFlightNumber() + "' OR " + flight.getFlightNumber() + " IS NULL)");

This way you wont have to check null in java, if the flight.getFlightNumber() is null then this where clause will always return true which is what you would want.

The only drawback to this method is that the clause will be included in the query, but since you are intending to use these columns to query the table incase they are not null, i would assume the table would be indexed likewise.

Upvotes: 1

prsmax
prsmax

Reputation: 223

sqlQueryBuilder.append("SELECT * FROM Flights WHERE 1 = 1");

then you don't need emptyQuery flag and checks and many if else constractions.

List<Flight> foundFlights = new ArrayList<>();
    StringBuilder sqlQueryBuilder = new StringBuilder();
    sqlQueryBuilder.append("SELECT * FROM Flights WHERE 1 = 1");

    if(flight.getDeparture() != null) {
        sqlQueryBuilder.append("AND Departure = '" + flight.getDeparture() + "'");
    }

Upvotes: 0

javapointdotcom
javapointdotcom

Reputation: 35

You can create the common method for the below block and call the method by passing the arguments.

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

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

Upvotes: 2

Related Questions