Java JDBC PreparedStatement with a list of values in an IN clause

---

< Previous Next >

JDBC Tutorial

---

In this article, we will discuss how to add a list of values dynamically to IN clause using JDBC PreparedStatement.

Consider we have a users table in a database and we have few records in database users table. Let's create a list of user ids and dynamically create a SQL statement using StringBuilder.

Below code creates a dynamic SQL query with a number of placeholders ? in IN clause:

private static String createQuery(int length) {
    String query = "select id, name from users where id in (";
    StringBuilder queryBuilder = new StringBuilder(query);
    for (int i = 0; i < length; i++) {
        queryBuilder.append(" ?");
        if (i != length - 1)
            queryBuilder.append(",");
    }
    queryBuilder.append(")");
    return queryBuilder.toString();
}

JDBC PreparedStatement with a list of parameters in an IN clause Example

package com.javaguides.jdbc.preparestatement.examples;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

/**
 * JDBC PreparedStatement with list of parameters in a IN clause Example
 * @author Ramesh Fadatare
 *
 */
public class JDBCPreparedStatementDynamic {

    public static void main(String[] args) {
        List < Integer > ids = new ArrayList < > ();
        ids.add(1);
        ids.add(2);
        ids.add(3);
        ids.add(4);
        processDynamicQuery(ids);

    }
    public static void processDynamicQuery(List < Integer > ids) {

        String query = createQuery(ids.size());

        System.out.println("Query=" + query);
        try (Connection connection = DriverManager
            .getConnection("jdbc:mysql://localhost:3306/mysql_database?useSSL=false", "root", "root");

            // Step 2:Create a statement using connection object
            PreparedStatement preparedStatement = connection.prepareStatement(query)) {
            ResultSet rs = null;

            int parameterIndex = 1;
            for (Iterator < Integer > iterator = ids.iterator(); iterator.hasNext();) {
                Integer id = (Integer) iterator.next();
                preparedStatement.setInt(parameterIndex++, id);
            }
            System.out.println(preparedStatement);
            rs = preparedStatement.executeQuery();
            while (rs.next()) {
                System.out.println("User ID=" + rs.getInt("id") + ", Name=" + rs.getString("name"));
            }

            // close the resultset here
            try {
                rs.close();
            } catch (SQLException e) {}

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static String createQuery(int length) {
        String query = "select id, name from users where id in (";
        StringBuilder queryBuilder = new StringBuilder(query);
        for (int i = 0; i < length; i++) {
            queryBuilder.append(" ?");
            if (i != length - 1)
                queryBuilder.append(",");
        }
        queryBuilder.append(")");
        return queryBuilder.toString();
    }
}

Output:

Query=select id, name from users where id in ( ?, ?, ?, ?)
com.mysql.jdbc.JDBC42PreparedStatement@579bb367: select id, name from users where id in ( 1, 2, 3, 4)
User ID=1, Name=A
User ID=2, Name=Pramod
User ID=3, Name=A
User ID=4, Name=B

---

< Previous Next >

JDBC Tutorial

---