java - 通过 ID 更新多行

Question

我有一个 ID ArrayList，我想更新每行中与每个 ID 对应的特定列。我想做的是在该列中写入另一个现有的 ID。

我有一个或多或少可行的解决方案；我会先向您展示它，然后描述我的问题:

public static void partnerStmt(ArrayList<Integer> keys, int age, int sex, int variable){
    int amount = keys.size();
    int ageMin = age - variable;
    int ageMax = age + variable;

    if (ageMin < 18)
        ageMin = 18;

    String select = "SELECT ID FROM individuen WHERE Jahre BETWEEN " 
            + ageMin +" AND "+ ageMax + " AND family IS 2 AND married IS NULL AND Sex "
            + "IS " + sex + " ORDER BY RANDOM() LIMIT " +amount+";";

    try {
        Statement stmt = DBController.connection.createStatement();
        ResultSet rs = stmt.executeQuery(select);
        for (int i = 0; i < keys.size(); i++) {
            int key = keys.get(i);
            int idColumn = rs.findColumn("ID");
            int partnerID = rs.getInt(idColumn);

            PreparedStatement person = DBController.connection
                    .prepareStatement("UPDATE individuen set Partner = " + partnerID +  
                    " WHERE ID = " + key);
            person.executeUpdate();
            rs.next()
            }
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

我的问题是它很慢。当要更新的条目超过 100k 时，一次更新一行是行不通的。第二个问题是，出于某种原因，我写入表中的 ID (partnerID) 在运行程序后多次显示。

有人知道更有效、更好的方法来做这样的事情吗？

Answer 1

使用 JDBC 的批处理功能来减少往返次数:

PreparedStatement person = DBController.connection.prepareStatement(
    "UPDATE individuen set Partner = ? WHERE ID = ?"
);
int idColumn = rs.findColumn("ID");
for (Integer key : keys) {
    if (!rs.next()) {
        // Make sure that we do not read a missing parent ID.
        // This should never happen, as long as rs has enough rows.
        break;
    }
    person.setInt(1, rs.getInt(idColumn));
    person.setInt(2, key);
    person.addBatch();
}
person.executeBatch();

请注意，此代码使用带有 ? 参数和 setXYZ 方法的参数化语句来提供值。