CODEWITHSUNDEEP
javasqljdbc
user3930976
user3930976

Reputation: 171

Moving to PreparedStatement from concatenating SQL string in Java

Suppose, I have a form and based on the fields filled in in the form by the user, I create in Java code the SQL statement by using Java string and concatentaion of clauses like "field1 = 'A' and field2 = 'B' and field3 = 'C'", etc. If the field is not filled in I just skip this part and not add it to my Java SQL string thus accepting any values in that field in database table. I'm justly told that concatenating SQL strings is a bad practice and I should rather use PreparedStatement rather than direct executeQuery. How would I do it in such case without creating separate PreparedStatement for each combination of present fields in the form?

Upvotes: 3

Views: 613

Answers (2)

Stefaan Neyts
Stefaan Neyts

Reputation: 2092

Consider using a framework like QueryDSL or JOOQ.

Upvotes: 1

Dawood ibn Kareem
Dawood ibn Kareem

Reputation: 79838

Firstly, well done for moving away from concatenating strings with the field values. You just saved yourself from a nasty SQL injection attack.

You want to use the same logic as before - the same set of if statements, and only add a clause to the SQL where the field is present. You'll probably want a StringBuilder to build up the SQL and an ArrayList<String> to store the parameter values until you're ready to use them. You might end up with a whole lot of blocks that look something like this.

if (! field1Text.equals("")) {
    sql.append(" and field1 = ?");
    params.add(field1Text);
}

Then, at the end, after you've made your PreparedStatement, you'll iterate through the list of parameters, calling setString for each.

Upvotes: 3

Related Questions