Use PreparedStatement to build a query

Question

I was wondering if using PreparedStatement.setString() was a good idea (possible, sensible?) to dynamically build a query.

For example :

sql code:

SELECT * FROM table1 WHERE table1.category = ? ?

java code:

ps.setString(1,"category1");
ps.setString(2,"AND table1.category = 'category2'");

Also, would it be possible to do something like:

ps.setString(1,"category1");
ps.setString(2," AND table1.category = ?");
ps.setString(3,"category2");

Best regards

Have you tried to run this code? Will it actually work? – Andremoniy Jan 21 '13 at 11:33 — Andremoniy, Jan 21 '13 at 11:33

score 9 · Accepted Answer · answered Jan 21 '13 at 11:17

9

Unfortunately, NO.

PreparedStatements are strictly for values only. Table Names and Column Names (as well as conditions in your example) are not allowed. So the best way to do is to concatenate it with the string.

String others = " AND table1.category = ?";
String query = "SELECT * FROM table1 WHERE table1.category = ? " + others;

java code:

ps.setString(1,"category1");
ps.setString(2,"category2");

answered Jan 21 '13 at 11:17

John Woo

258,903
69
498
492

Thanks, I will do it this way. – BenoitParis Jan 21 '13 at 11:22
@BenoitParis or directly `String query = "SELECT * FROM table1 WHERE table1.category = ? AND table1.category = ?";` – John Woo Jan 21 '13 at 11:23
Also as per OP's code `AND table1.category = ?` would be in single quotes so the generated query won't make sense – Bhavik Shah Jan 21 '13 at 11:23
@BhavikShah I know, it is just an example to show that you can concatenate ***other strings*** but not to add conditions as parameters. – John Woo Jan 21 '13 at 11:25

score 3 · Answer 2 · answered Jan 21 '13 at 11:17

3

Whatever you put inside setString will go within single quotes ' ' and will not be interpreted as a query.

answered Jan 21 '13 at 11:17

shazin

21,379
3
54
71

Use PreparedStatement to build a query

2 Answers2