NamedParameterJdbcTemplate set params with OR operator and check for null

Question

class Request{
    private string f1;
    private string f2;
    private string f3;
}

I have the following query

SELECT * FROM tbl WHERE f1  = 'val' OR f2  = 'val2' OR f3  = 'va3'

fields from Request can be null. I need to create NamedParameterJdbcTemplate query and check each field for null.

String query = "SELECT * FROM tbl"; 
if (request.getF1() !=null && !request.getF1().isEmpty()){
            query += ....
        }

This is a very bad solution....

Can I create something like

 Map<String, Object> namedParameters = new HashMap<>();

to check all fields for null and if not null add to map and set to query? And set operator OR

xyz · Answer 1 · 2017-06-10T11:46:18.393

in jdbc-template you can't do dynamically-query builder out of box (if value is null exclude it from where section). You should create your own builder.

if you need to ececute dynamic query in one- two places you can do that :

just to refactor request.getF1() !=null && !request.getF1().isEmpty() into something readable and supportable , something like :

String select = SqlDynamicWhereSectionWithOrHelper.getInstance().
                applyIfPresentValue(fieldName1 , value1).
                applyIfPresentValue("" , null).
                applyIfPresentValue(fieldName2 , value2)
                .buildSqlWithWhere(GIVEN_SELECT);



public final class SqlDynamicWhereSectionWithOrHelper {
    private StringBuffer whereSqlQuery = new StringBuffer();
    public static final String WHERE = " where ";
    public static final String OR = " or ";
    private boolean whereNotPresent = true;

    private SqlDynamicWhereSectionWithOrHelper() {
    }

    public static SqlDynamicWhereSectionWithOrHelper getInstance() {
        return new SqlDynamicWhereSectionWithOrHelper();
    }

    public SqlDynamicWhereSectionWithOrHelper applyIfPresentValue(String fieldName, String fieldValue) {
        if (isNotEmpty(fieldValue) && isNotEmpty(fieldName)) {
            applyWhereOrOr(fieldName + "=" + wrapValue(fieldValue));
        }
        return this;
    }

    public String buildSqlWithWhere(String mainQueryWithoutWhere) {
        return mainQueryWithoutWhere + whereSqlQuery.toString();
    }

    //private section
    private String wrapValue(String fieldValue) {
        return "'" + fieldValue + "'";
    }

    private void applyWhereOrOr(String predicate) {
        String prefix = OR;
        if (whereNotPresent) {
            whereNotPresent = false;
            prefix = WHERE;
        }
        whereSqlQuery.append(prefix);
        whereSqlQuery.append(predicate);
    }

    //if you have apache common(or some other implementation) use  method from lib StringUtils.isEmpty(String value)
    private boolean isNotEmpty(String value) {
        return value != null && value.trim().length() > 0;
    }

}

and test for it

 private static final String GIVEN_SELECT = "select * from table";

 @Test
    public void testForWithParamsWithNull(){
        //set-up data
        String fieldName1 = "field1";
        String value1 = "value for field1";

        String fieldName2 = "field2";
        String value2 = "value for field2";

        StringBuffer expectedSelectBuffer = new StringBuffer(GIVEN_SELECT);
        expectedSelectBuffer.append(SqlDynamicWhereSectionWithOrHelper.WHERE);
        expectedSelectBuffer.append(fieldName1+"='"+value1+"'");
        expectedSelectBuffer.append(SqlDynamicWhereSectionWithOrHelper.OR);
        expectedSelectBuffer.append(fieldName2+"='"+value2+"'");
        String expectedSelect = expectedSelectBuffer.toString();

        //execution

        String resultSelect = SqlDynamicWhereSectionWithOrHelper.getInstance().
                applyIfPresentValue(fieldName1 , value1).
                applyIfPresentValue("" , null).
                applyIfPresentValue(fieldName2 , value2)
                .buildSqlWithWhere(GIVEN_SELECT);

        //verification
        assertThat(resultSelect , is(notNullValue()));
        assertThat(resultSelect ,equalTo(expectedSelect));
    }

Also you can try to fix it in SQL with checking on empty, but if you have lots of data to go through, this technique will slow down your request.

SELECT * FROM tbl WHERE (f1  = 'val' or f1 = null) OR (f2  = 'val2' or f2 = null ) OR (f3  = 'va3' or f3=null)

NamedParameterJdbcTemplate set params with OR operator and check for null

1 Answers1