I am trying to pass multiple values in a SELECT QUERY consisting of two IN clauses. I would like to execute the following query:
String sql = "select ADDRESS from CDR where APARTY in(?,?,?) intersect select ADDRESS from CDR where BPARTY in (?,?) and USAGETYPE='SMSMT'";
How can I pass value using preparedStatement.setString() ?.Thanks in advance.
To build your query you can use for example StringBuilder for example :
Consider you have a two lists like this :
List<String> values1 = new ArrayList<>();
values1.add("val1_1");
values1.add("val1_2");
values1.add("val1_3");
List<String> values2 = new ArrayList<>();
values2.add("val2_1");
values2.add("val2_2");
Now we will build the query :
StringBuilder sql = new StringBuilder("select ADDRESS from CDR where APARTY IN(");
for (int i = 0; i < values1.size(); i++) {
sql.append(i == values1.size() - 1 ? "?" : "?,");
}
sql.append(") intersect select ADDRESS from CDR where BPARTY IN (");
for (int i = 0; i < values2.size(); i++) {
sql.append(i == values2.size() - 1 ? "?" : "?,");
}
sql.append(") and USAGETYPE='SMSMT'");
System.out.println(sql);
Until now this can gives you :
select ADDRESS from CDR where APARTY IN(?,?,?) intersect select ADDRESS from CDR where BPARTY IN (?,?) and USAGETYPE='SMSMT'
Now you have to use this query in the PreparedStatement :
try (PreparedStatement pstm = connection.prepareStatement(sql)) {
int i = 1;
for (String s : values1) {
pstm.setString(i++, s);
}
for (String s : values2) {
pstm.setString(i++, s);
}
//...execute the statement and retrieve the results
}
PreparedStatement.setString() for each String value added in the IN clause. As a side node, I think that the iterations with i and j you are using are a little bit complicated and could be simpler. Personally, I would have use a single int and an enhanced for to iterate on each list.
Here's how you do it :
PreparedStatement pstmt = yourconnexiontoDB.prepareStatement(sql);
pstmt.setString(1, "your first string");
pstmt.setString(2, "your second string");
etc ... for the rest of the values.
