Shweta Gulati Shweta Gulati - 2 months ago 12
Java Question

Optimize if -else statement

I am writing a query for searching records from db. I pass 3 values to the method . I want to write my query in such a way that the condition for that column is appended in the query whose passed corresponding value is non-null. I have ended up with messy if else statements. Is there a way to optimize it?

if (StringUtils.isNotEmpty(projectId)) {
sql.append(" UPPER(CIRC.PROJECT_ID) like ?");
}

if (StringUtils.isNotEmpty(circuitId)) {
if (StringUtils.isNotEmpty(projectId)) {
sql.append(" AND");
}
sql.append(" UPPER(CIRC.CIRCUIT_ID) like ?");
}

if (StringUtils.isNotEmpty(orderRef)) {
if (StringUtils.isNotEmpty(projectId) || StringUtils.isNotEmpty(circuitId)) {
sql.append(" AND");
}
sql.append(" UPPER(CIRC.ORDERID) like ?");
}

JSONArray jsonArray = new JSONArray();
ResultSet rs = null;
SimpleDateFormat sdf = new SimpleDateFormat("dd-MM-yyyy");

PreparedStatement ps = null;
try {
if (connection != null) {
ps = connection.prepareStatement(sql.toString());
if (StringUtils.isNotEmpty(projectId)) {
ps.setString(1, "%" + projectId.toUpperCase() + "%");
}

if (StringUtils.isNotEmpty(circuitId)) {
if (StringUtils.isEmpty(projectId)) {
ps.setString(1, "%" + circuitId.toUpperCase() + "%");
} else {
ps.setString(2, "%" + circuitId.toUpperCase() + "%");
}
}

if (StringUtils.isNotEmpty(orderRef)) {
if (StringUtils.isEmpty(projectId) && StringUtils.isEmpty(circuitId)) {
ps.setString(1, "%" + orderRef.toUpperCase() + "%");
} else if (StringUtils.isEmpty(projectId) || StringUtils.isEmpty(circuitId)) {
ps.setString(2, "%" + orderRef.toUpperCase() + "%");
} else {
ps.setString(3, "%" + orderRef.toUpperCase() + "%");
}
}


Note: This question is not a subject of orm/jdbc. Also please ignore hard coding.

Answer

You could collect the clauses in list and join with " AND ", and then collect the args in another list, e.g.:

    // Uncomment out on Java 6
    // import com.google.common.base.Joiner;

    if (StringUtils.isNotEmpty(projectId)) {
        clauses.add("UPPER(CIRC.PROJECT_ID) like ?");
        args.add(projectId);
    }

    if (StringUtils.isNotEmpty(circuitId)) {
        clauses.add("UPPER(CIRC.CIRCUIT_ID) like ?");
        args.add(circuitId);
    }

    if (StringUtils.isNotEmpty(orderRef)) {
        clauses.add("UPPER(CIRC.ORDERID) like ?");
        args.add(orderRef);
    }

    // Java 8 (comment out for Java 6)
    sql.append(clauses.stream().collect(Collectors.joining(" AND ")));

    // Uncomment for Java 6
    // sql.append(Joiner.on(" AND ").join(clauses));

    JSONArray jsonArray = new JSONArray();
    ResultSet rs = null;
    SimpleDateFormat sdf = new SimpleDateFormat("dd-MM-yyyy");

    PreparedStatement ps = null;
    try {
        if (connection != null) {
            ps = connection.prepareStatement(sql.toString());
            for (int i = 0; i < args.size(); ++i) {
                ps.setString(i+1, "%" + args.get(i).toUpperCase() + "%");
            }
        }
    }

On Java 6 comment out the Java 8 line and uncomment out the Java 6 lines. Also add Guava to your project.