如何使用 JDBC PreparedStatement将值列表动态添加到 IN 子句

川长思鸟来 2023-09-28 02:37 85阅读 0赞

在本文中，我们将讨论如何使用 JDBC [PreparedStatement][]将值列表动态添加到 IN 子句。

考虑我们在数据库中有一个用户表，而数据库用户表中的记录很少。让我们创建一个用户*ID列表并使用**StringBuilder*动态创建一个 SQL 语句。

下面的代码创建一个带有多个占位符的动态 SQL 查询？在 IN 子句中：

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

### 带有 IN 子句中参数列表的 JDBC PreparedStatement 示例 ###

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

输出：

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

[PreparedStatement]: http://www.javaguides.net/2018/10/jdbc-preparedstatement-interface.html