Building a String parameter (with SQL content) from resource file for SQL PreparedStatement

Question

I need to execute a SQL PreparedStatement in Java using jdbc. I'm facing problems with one of the parameters because it has SQL content and also Strings from a resource file. It looks something like this:

Required SQL:

SELECT * FROM Table T WHERE T.value = 10 AND T.display IN ('Sample1', 'Sample2')

In the above query, the Sample1 and Sample2 values must be passed through a parameter to a PreparedStatement.

PreparedStatement:

SELECT * FROM Table T WHERE T.value = 10 ?

In my application code I'm setting the parameters like:

statement.setString(1, "AND T.display IN ('Sample1', 'Sample2')");

However this is not returning the appropriate results.

Is there a better way to build this particular parameter considering it has SQL content and Strings too?

EDIT: Sample1, Sample2 etc. are strings that are retrieved from an external file at run-time and there can be different number of these strings each time. I.e. there can be only one string Sample1 or multiple strings Sample1, Sample2, Sample3, etc..

EDIT2: Database being used is Oracle.

Can you elaborate about "However this is not returning the appropriate results" ? — VirtualTroll, Jul 25 '13 at 17:29
The query isn't getting rejected, however the problem seems to be with double quotes being added to the parameter. — naren.katneni, Jul 25 '13 at 17:35
@Amine I don't think it is possible to inspect the generated SQL. http://stackoverflow.com/a/2382561/1505128 — naren.katneni, Jul 25 '13 at 17:43
http://stackoverflow.com/questions/3237916/how-can-i-see-queries-that-are-executed-against-oracle — VirtualTroll, Jul 25 '13 at 18:15

score 2 · Answer 1 · answered Jul 25 '13 at 17:30

The ? placeholder can only be used in a position where a value is expected in the query. Having a ? in any other position (as in your question: WHERE T.value = 10 ?) is simply a syntax error.

In other words: it is not possible to parametrize part of the query itself as you are trying to do; you can only parametrize values. If you need to add a dynamic number of parameters, you will need to construct the query dynamically by adding the required number of parameters and using setString(). For example:

StringBuilder sb = new StringBuilder(
     "SELECT * FROM Table T WHERE T.value = 10 AND T.display IN (?");
// Note: intentionally starting at 1, first parameter already above
// Assuming always at least 1 parameter
while (int i = 1; i < params.length; i++) {
    sb.append(", ?");
}
sb.append(')');

try (
    PreparedStatement pstmt = con.prepareStatement(sb.toString())
) {
    for (int i = 0; i < params.length; i++) {
        pstmt.setString(i + 1, params[i]);
    }
    try (
        ResultSet rs = pstmt.executeQuery();
    ) {
        // Use resultset
    }
}

jlordo · Answer 2 · 2013-07-25T17:18:38.997

1

Use this as PreparedStatement

"SELECT * FROM Table T WHERE T.value = 10 AND T.display IN (?, ?);"

and then call

statement.setString(1, "Sample1");
statement.setString(2, "Sample2");

before executing the statement.

Update:

String generateParamString(int params) {
    StringBuilder sb = new StringBuilder("(");
    for (int i = 1; i < params; i++) {
        sb.append("?, ");
    }
    sb.append("?)");
    return sb.toString();
}

List<String> samples = ... // your list with samples.
String stmtString = "SELECT * FROM Table T WHERE T.value = 10 AND T.display IN "
    + generateParamString(samples.size());
// generate statement with stmtString 
for (int i = 0; i < samples.size(); i++) {
    statement.setString(i + 1, samples.get(i));
}
// execute statement...

edited Jul 25 '13 at 17:18

answered Jul 25 '13 at 16:53

jlordo

37,490
6
58
83

Please see the edit in the question. There need not necessarily be only 2 of these strings. So hard coding `(?,?)` is not an option. – naren.katneni Jul 25 '13 at 17:02
@naren.katneni well, if hard coding is not an option - create `(?,?,? ... ?)` string dynamically – Your Common Sense Jul 25 '13 at 17:05
@naren.katneni where do your sample strings come from? An array, list, set or something else? – jlordo Jul 25 '13 at 17:09
@jlordo They are stored in a List which is generated from an XML file. – naren.katneni Jul 25 '13 at 17:12

Building a String parameter (with SQL content) from resource file for SQL PreparedStatement

2 Answers2