Working with SQL Null Elegantly Parameters
I am generating a statement SQL
by checking if each of the column fields sent to the request is empty (== null) or not. It looks like my approach is pretty naive, so I'm wondering what can be done with elegant handling of null parameters. When something is not specified, it should just match something.
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
}
source to share
You can create a generic method for the bottom block and call the method by passing arguments.
if(flight.getArrival() != null)
{
if(emptyQuery)
{
sqlQueryBuilder.append(" WHERE ");
emptyQuery = false;
}
else
{
sqlQueryBuilder.append(" AND ");
}
sqlQueryBuilder.append("Arrival = '" + flight.getArrival() + "'");
}
source to share
A better approach is to do the trick in SQL than check for null in Java. This is how you can do it.
sqlQueryBuilder.append("(Number = '" + flight.getFlightNumber() + "' OR " + flight.getFlightNumber() + " IS NULL)");
This way you don't need to check for null in java if flight.getFlightNumber () is null then this where clause will always return true which is what you want.
The only drawback to this method is that the clause will be included in the query, but since you are going to use these columns to query the table, if they are not null, I would assume the table will be indexed as well.
source to share
sqlQueryBuilder.append("SELECT * FROM Flights WHERE 1 = 1");
then you don't need the emptyQuery flag and validation as well as many if there are others.
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() + "'");
}
source to share