Consider nullable parameters in an sql, can be built up either with if conditions or using a conditional where clause. Which one is better to use

Question

I have so many dynamic queries in my project and there may be 2 ways to build up a dynamic query. I have shown them below,

1. Conditional where

@Query("select c from Customer c where " +
            " (:name IS NULL OR c.name LIKE %:name%) "+
            "and (:email IS NULL OR c.email =:email) "+
            "and (:genderIds IS NULL OR c.genderId IN (:genderIds) ) ")
    List<Customer> findByParameters(@Param("name") String name
            , @Param("email") String email
            , @Param("genderIds") List<Integer> genderIds);

2. By using If conditions, e.g.

public List<Customer> getCustomersNativeQueryConditional(RequestCustomer request) {       
        StringBuilder sb = new StringBuilder();
        MapSqlParameterSource params = new MapSqlParameterSource ();

        sb.append("select * from Customer c where 1=1 ");

        Optional.ofNullable(request.getName())
                .ifPresent(s->{
                    sb.append(" AND c.name LIKE :name");
                    params.addValue("name","%"+s+"%");
                });

        Optional.ofNullable(request.getEmail())
                .ifPresent(s->{
                    sb.append(" AND c.email =:email ");
                    params.addValue("email",s);
                });
        Optional.ofNullable(request.getGenderIds())
                .ifPresent(ids->{
                    sb.append(" AND c.genderId IN (:genderIds) ");
                    params.addValue("genderIds",ids);
                });

       return namedPrmJdbcTemplate.query(sb.toString(), params, BeanPropertyRowMapper.newInstance(Customer.class));
}

Note: request object in the second case is a POJO,

public class RequestCustomer {
    private String name;
    private String email;
    private List<Integer> genderIds;
}

What I really wonder is which one of them is much more efficient/better with respect to database and/or coding standards. Which one is supposed to be used in an well-designed application by an experienced developer?

Answer 1

From a performance perspective, only including the parameters that are actually needed is generally much, much better.

In general, optimizers have a hard time with ors in where clauses. More specifically, optimizers can have problems with applying indexes when there are or conditions on different columns.