Sometimes, you need to build dynamically the WHERE statement in a SQL query.
So you will concatenate some key/value seperated with a "AND" operator
If you run the following:
import java.util.HashMap;
public class Test {
public static void main(String[] args){
StringBuffer query = new StringBuffer("SELECT * FROM users WHERE ");
HashMap mp = new HashMap();
mp.put("firstname", "John");
mp.put("lastname", "Doe");
mp.put("login", "john.doe");
for (String k: mp.keySet()) {
query.append(" AND " + k + " = \"" + mp.get(k) + "\"");
}
System.out.println(query.toString());
}
}
The above will return:
SELECT * FROM users WHERE AND lastname = "Doe" AND login = "john.doe" AND firstname = "John"
The WHERE statement has been generated successfully except that now, we would need to remove the first occurence of the AND operator.
The trick is to add the following statement at the beginning of the query:
1 = 1
With this additionnal above statement, we wont need to look for the first occurence of the AND operator and our query will work since 1=1 will always be evaluated to true
import java.util.HashMap;
public class Test {
public static void main(String[] args){
StringBuffer query = new StringBuffer("SELECT * FROM users WHERE ");
HashMap mp = new HashMap();
mp.put("firstname", "John");
mp.put("lastname", "Doe");
mp.put("login", "john.doe");
query.append("1=1"); // This can also be moved when declaring the query in the StringBuffer
for (String k: mp.keySet()) {
query.append(" AND " + k + " = \"" + mp.get(k) + "\"");
}
System.out.println(query.toString());
}
}
The result will return:
SELECT * FROM users WHERE 1=1 AND lastname = "Doe" AND login = "john.doe" AND firstname = "John"
