CODEWITHSUNDEEP
javamysqlarraylistprepared-statement
SrividhyaShama
SrividhyaShama

Reputation: 549

How to use each element of Arraylist as a column value in SQL statement (iterate the Arraylist for each element)?

I collected the distinct Item value from the table in a ArrayList. Now I want to iterate each value of ArrayList as a column value.

PreparedStatement pstmt =conn.preparedStatement("select distinct(A.ID) from Products A,Products B where A.ID=B.ID and A.Item in (?)");

The above must comes under the loop and each element in ArrayList is used as the column value for each iteration.

Upvotes: 0

Views: 1086

Answers (1)

Andreas
Andreas

Reputation: 159135

You need to add a ? parameter marker for each value, so your code would be something like this.

Connection conn = /*connection provided elsewhere*/;
List<String> items = /*item values provided elsewhere*/;

StringBuilder sql = new StringBuilder();
sql.append("select distinct A.ID" +
            " from Products A" +
            " join Products B on B.ID = A.ID" +
           " where A.Item in (");
for (int i = 0; i < items.size(); i++) {
    if (i != 0) sql.append(',');
    sql.append('?');
}
sql.append(')');
try (PreparedStatement stmt = conn.prepareStatement(sql.toString())) {
    for (int i = 1; i <= items.size(); i++)
        stmt.setString(i, items.get(i));
    try (ResultSet rs = stmt.executeQuery()) {
        while (rs.next()) {
            int id = rs.getInt("ID");
            // use id here
        }
    }
}

In Java 8, using a StringJoiner can simplify the code a bit.

Upvotes: 1

Related Questions