Saturday, June 22, 2013

How to programmatically append "WHERE" keyword

When querying database from program (Java, .Net, PHP, etc) with criteria (WHERE and/or AND clause), normally we will need to check the existence of WHERE or whether this is the first criteria and programmatically append the WHERE or AND keyword before search criteria..
eg.
String myQuery = "SELECT * FROM TABLE_A";
if(!myQuery.contains("WHERE")) {
    myQuery.append(" WHERE ");
} else {
    myQuery.append(" AND ");
}
// append criteria


OR

String myQuery = "SELECT * FROM TABLE_A";
for(int i = 0; i < criteriaList.size(); i++){
    if(i == 0) {
        myQuery.append(" WHERE ");
    } else {
        myQuery.append(" AND ");
    }
    myQuery.append(criteriaList.get(i));
}

although the above codes are not difficult, but it is quite troublesome to always coding the if...else... block.

alternative

always append the WHERE 1=1 in the base query, so we no need to bother whether this is the first criteria or whether the WHERE keyword already existed.
eg.
String myQuery = "SELECT * FROM TABLE_A WHERE 1=1 ";
// always append AND for the rest of criteria
for(int i = 0; i < criteriaList.size(); i++) {
    myQuery.append(" AND ").append(criteriaList.get(i));
}
just adding the short "WHERE 1=1" clause into our base query, the codes become shorter and cleaner.
p/s: the above example is applicable for JPQL as well.


Done!!

LinkWithin

Related Posts Plugin for WordPress, Blogger...