java - 在java中传递多个值来动态查询

Question

我试图在由两个 IN 子句组成的 SELECT QUERY 中传递多个值。我想执行以下查询:

String sql = "select ADDRESS from CDR where APARTY in(?,?,?) intersect select ADDRESS from CDR where BPARTY in (?,?) and USAGETYPE='SMSMT'";

如何使用 preparedStatement.setString() 传递值？提前致谢。

Answer 1

要构建查询，您可以使用 StringBuilder 例如:

假设您有两个这样的列表:

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");

现在我们将构建查询:

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);

到目前为止，这可以为您提供:

select ADDRESS from CDR where APARTY IN(?,?,?) intersect select ADDRESS from CDR where BPARTY IN (?,?) and USAGETYPE='SMSMT'

现在您必须在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 
}