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 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.
Aucun commentaire:
Enregistrer un commentaire