Dynamic query Statement

Question

I need to write a query for a MySQL database using jdbc that depends on some business logic, which can be summarized as something like this

 if (someCondition) {
 myQuery = "stuff";
 } else {
 if (anotherCondition) { 
 myQuery = "some stuff";
 } else {
 myQuery = "even more stuff";
 }
 }

And I intend to build the myQuery string to serve as a template for a PreparedStatement object and then fill the ? with the actual data. however, the number of ? depends on the same logic as above, and therefore I'm duplicating the logic in the code

How can I avoid it?

Thanks

• Arnaud: What should your different queries look like ?
• Bajal: If you are adding different or additional where clauses based on criteria, use a StringBuffer for myQuery and keep appending those. Then use that to prepare the statement,
• Mohammed Ahmed: One way you can do is instead of having ? have #{name1} #{name2} etc . Use myquery.replaceAll("#{name1}",value1), .. if your query does'nt have name1 then it will not get replaced . Only the names which are present are replaced. This way u can avoid too many if conditions.

Answer 1

You can create a prepared statement and set its parameters inside the if blocks:

if (someCondition) {
 stmt = connection.prepareStatement("stuff");
 stmt.set... // set attributes
} else if (anotherCondition) {
 stmt = connection.prepareStatement("some stuff");
 // set attributes
} else {
 stmt = connection.prepareStatement("even more stuff");
 //set attributes
}

Answer 2

You should use something like this:

String myQuery = ...;
List<Object> parameters = ... ;
if (someCondition) {
 myQuery = "stuff";
 parameters = ... ;
} else {
 if (anotherCondition) { 
 myQuery = "some stuff";
 parameters = ... ;
 } else {
 myQuery = "even more stuff";
 parameters = ... ;
 }
}

Answer 3

You can avoid duplication by preparing the objects that you are going to bind to the statement together with the query string:

List<Object> paramValues = new ArrayList<>();
String myQuery;
if (someCondition) {
 myQuery = "stuff ?";
 paramValues.add("single");
} else {
 if (anotherCondition) { 
 myQuery = "some stuff ? ?";
 paramValues.add("one");
 paramValues.add(2);
 } else {
 myQuery = "even more stuff ? ? ? ?";
 paramValues.add("one");
 paramValues.add(2);
 paramValues.add(3.0);
 paramValues.add("four");
 }
}
// Make prepared statement
PreparedStatement ps = ...
// Bind parameters
for (int i = 0 ; i != paramValues.size() ; i++) {
 ps.setObject(i+1, paramValues.get(i));
}