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
Free Spring Boot Tutorial | Full In-depth Course | Learn Spring Boot in 10 Hours
Watch this course on YouTube at Spring Boot Tutorial | Fee 10 Hours Full Course