CODEWITHSUNDEEP
javastringjdbc
Azodious
Azodious

Reputation: 13872

replaceAll vs setString

I've a prepared statement with six '?' in it. and at all places it will be replaced by same string.

Which of following two methods should be preferred:

pstmt = con.prepareStatement(QUERY.replaceAll("\\?", id));

OR

pstmt = con.prepareStatement(QUERY));
pstmt.setString(1, id);
pstmt.setString(2, id);
pstmt.setString(3, id);
pstmt.setString(4, id);
pstmt.setString(5, id);
pstmt.setString(6, id);

Upvotes: 1

Views: 158

Answers (2)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726499

The second one.

In addition to hard-to-spot bugs, such as replacements of question marks inside string literals with IDs, the first query would need to be parsed and optimized each time that you send it over. This would pollute the cache of query plans with nearly identical plans that differ only in the exact value of the ID. There would be an additional cost of parsing the query each time, making the caching useless.

The second one would be prepared once, and the query plan for it would be reused each time you execute it.

Upvotes: 6

Elliott Frisch
Elliott Frisch

Reputation: 201439

The second one, the first is potentially vulnerable to SQL injection. Especially when (as it appears to be here) the id is a String.

pstmt = con.prepareStatement(QUERY));
pstmt.setString(1, id);
pstmt.setString(2, id);
pstmt.setString(3, id);
pstmt.setString(4, id);
pstmt.setString(5, id);
pstmt.setString(6, id);

Also, the above should perform better since the statement query cache can potentially reuse the statement for different id(s). That is not true of the first example.

Upvotes: 6

Related Questions