Build SQL where clause with optional from and to dates

Question

I'm looking for shortest if statement (or something else) to build an SQL query allowing for various cases:

1. Given 'from' date, no 'to' date,
2. Given 'to' date, no 'from' date,
3. Given both 'from' and 'to' dates,
4. No 'from' nor 'to' dates.

A missing 'from' or 'to' date means null value. Based on the output from the statement, a specific where clause would be dynamically added to typed query.

I could get it solved with one nested if but it would look ugly. Any suggestions?

Answer 1

When creating SQL statements you have not only the query string but also the prepared variables. You'll want to add to both of them at the same time. Something like:

StringBuilder sql = new StringBuilder();
List<Object> variables = new ArrayList<>();

sql.append("SELECT <columns> FROM <tables> WHERE 1");

if (fromDate != null) {
    sql.append(" AND date >= ?");
    variables.add(fromDate);
}

if (toDate != null) {
    sql.append(" AND date <= ?");
    variables.add(toDate);
}

PreparedStatement statement = connection.prepareStatement(sql.toString());

for (int i = 0; i < variables.size(); i++) {
    statement.setObject(i + 1, variables.get(i));
}

Doing this by hand is painful, especially if you start mixing AND and OR and using parentheses. Consider using an SQL library that will take care of the drudgery. I can vouch for jOOQ:

Dynamic construction is particularly useful in the case of the WHERE clause, for dynamic predicate building. For instance:

public Condition condition(HttpServletRequest request) {
    Condition result = trueCondition();
    
    if (request.getParameter("title") != null)
        result = result.and(BOOK.TITLE.like("%" + request.getParameter("title") + "%"));
        
    if (request.getParameter("author") != null)
        result = result.and(BOOK.AUTHOR_ID.in(
            selectOne().from(AUTHOR).where(
                    AUTHOR.FIRST_NAME.like("%" + request.getParameter("author") + "%")
                .or(AUTHOR.LAST_NAME .like("%" + request.getParameter("author") + "%"))
            )
        ));
    
    return result;
}

// And then:
create.select()
      .from(BOOK)
      .where(condition(httpRequest))
      .fetch();

Answer 2

tl;dr

Using LocalDateRange class from ThreeTen-Extra, along with ternary statements inside StringBuilder::append calls.

StringBuilder sql = new StringBuilder();
sql.append( "SELECT * \n" );
sql.append( "FROM event_ \n" );
sql.append( range.equals( LocalDateRange.ofUnbounded() ) ? "" : "WHERE \n" );  // If the range is unbounded, meaning no beginning AND no ending.
sql.append( range.isUnboundedStart() ? "" : "NOT start_ < ? \n" );  // "not before" is a short way of saying "is equal to or later".
sql.append( range.isUnboundedStart() || range.isUnboundedEnd() ? "" : "AND \n" );
sql.append( range.isUnboundedEnd() ? "" : "stop_  < ? \n" );  // Half-open approach where beginning is *inclusive* while the ending is *exclusive*.
sql.append( "; \n" );

Generates:

• SELECT * FROM event_ WHERE NOT start_ < ? AND stop_ < ? ;
• SELECT * FROM event_ WHERE NOT start_ < ? ;
• SELECT * FROM event_ WHERE stop_ < ? ;
• SELECT * FROM event_ ;

No if at all

How about using no if statements at all?

I experimented with nesting a ternary within a series of StringBuilder::append calls as a way of building up the SQL query string. If a condition is not met, we pass an empty string ("") to satisfy the append method's requirement for a CharSequence.

I am not sure if I like this solution or not, as it is too new to me. I post it as food for thought.

org.threeten.extra.LocalDateRange

I am using the LocalDateRange class from the ThreeTen-Extra library, to represent a pair of java.time.LocalDate objects marking a span-of-time. This library’s project is led by the same man who led JSR 310 defining the java.time classes, Stephen Colebourne. This library adds functionality to the java.time framework.

Set up your LocalDateRange object to be passed into your query-builder code. You can see all four possibilities listed in this example code: (a) both a beginning and an ending, (b) a beginning without an ending, (c) an ending without a beginning, (d) neither beginning nor ending. Comment-in/out which case you want to test.

The use of LocalDateRange from ThreeTen-Extra is actually optional here. But the pairing of the two dates together, plus the method names, makes this code much easier to read and understand.

We use the SQL placeholder ? for use with a PreparedStatement.

    LocalDate start = LocalDate.of( 2021 , Month.JANUARY , 23 );
    LocalDate end = LocalDate.of( 2021 , Month.FEBRUARY , 17 );
    LocalDateRange range = LocalDateRange.of( start , end );           // Both beginning and ending.
    // LocalDateRange range = LocalDateRange.ofUnboundedEnd( start );  // Beginning only.
    // LocalDateRange range = LocalDateRange.ofUnboundedStart( end );  // Ending only.
    // LocalDateRange range = LocalDateRange.ofUnbounded();            // Neither beginning nor ending. 
    Objects.requireNonNull( range );

Here is our SQL query-building code.

The part NOT start_ < ? is a short way of saying "is equal to or later". I like this syntax’ symmetry with the other part for ending: "where x is not before start AND where y is before end".

The logic of defining a span-of-time with beginning inclusive and ending exclusive is known as Half-Open. This approach is usually the wisest for date-time handling. Using this approach spans-of-time can neatly abut one another without gaps.

    StringBuilder sql = new StringBuilder();
    sql.append( "SELECT * \n" );
    sql.append( "FROM event_ \n" );
    sql.append( range.equals( LocalDateRange.ofUnbounded() ) ? "" : "WHERE \n" );  // If the range is unbounded, meaning no beginning AND no ending.
    sql.append( range.isUnboundedStart() ? "" : "NOT start_ < ? \n" );  // "not before" is a short way of saying "is equal to or later".
    sql.append( range.isUnboundedStart() || range.isUnboundedEnd() ? "" : "AND \n" );
    sql.append( range.isUnboundedEnd() ? "" : "stop_  < ? \n" );  // Half-open approach where beginning is *inclusive* while the ending is *exclusive*.
    sql.append( "; \n" );

Dump to console.

    System.out.println( "range = " + range );
    System.out.println( "sql = \n" + sql );

When run with both beginning and ending.

range = 2021-01-23/2021-02-17
sql = 
SELECT * 
FROM event_ 
WHERE 
NOT start_ < ? 
AND 
stop_  < ? 
; 

When run with beginning only.

range = 2021-01-23/+999999999-12-31
sql = 
SELECT * 
FROM event_ 
WHERE 
NOT start_ < ? 
; 

When run with ending only.

range = -999999999-01-01/2021-02-17
sql = 
SELECT * 
FROM event_ 
WHERE 
stop_  < ? 
; 

When run with neither beginning nor ending.

range = -999999999-01-01/+999999999-12-31
sql = 
SELECT * 
FROM event_ 
; 

Answer 3

What I normally do is build my where clause with a StringBuilder.

StringBuilder sql = new StringBuilder();
StringBuilder sqlWhere = new StringBuilder();
ArrayList<Object> params = new ArrayList<Object>();

if (fromDate != null) {
  sqlWhere.append(" fromDate >= ? ");    
  params.add( fromDate );
}
if (toDate != null) {
  sqlWhere.append(" toDate <= ? " + (parms.size() > 0 ? " and " : "") );    
  params.add( toDate );
}

if ( sqlWhere.size() > 0 ) {
  sql.append(" where ").append( sqlWhere.toString() );
}

Then later in your code:

for(int index = 1; index <= params.size() : index++) {   
  ps.setObject( index, obj );
}