SQLite查询TEMP临时表时ResultSet.next()仅返回1行问题排查

故障根因 #

你共用了同一个PreparedStatement对象执行查询和更新操作，JDBC规范明确规定：同一个Statement对象执行新的SQL语句时，会自动关闭该对象之前打开的所有未处理完的ResultSet实例。
你在内层循环第一次调用rs.next()之后，立刻用同一个prepareStatement执行了UPDATE语句，这个操作直接把查询临时表得到的rs结果集关闭，所以下一次循环调用rs.next()直接返回false，自然只能更新第一条数据。
你之前查永久表的代码能正常运行，是因为遍历ResultSet的过程中没有复用同一个statement对象执行其他SQL，所以结果集不会被提前关闭，和操作的是临时表还是永久表没有关联。

修复方案 #

你可以任选以下两种方案之一解决问题：

• 方案一：遍历ResultSet前先把所有符合条件的主键ID全部查出来存到本地集合，遍历集合执行更新操作

while( total_tfgs > 0 ) {
    int tfgs_round = 0;
    min = prepareStatement.executeQuery("SELECT MIN("+ PROFESORES_COL_TFGS +") FROM TEMP_TABLA_PROFESORES WHERE " + PROFESORES_COL_OBLIGA + " = 'SÍ'").getInt(1);
    ResultSet rs = prepareStatement.executeQuery("SELECT * FROM TEMP_TABLA_PROFESORES WHERE " + PROFESORES_COL_TFGS + " = '" + min + "' AND " + PROFESORES_COL_OBLIGA + " = 'SÍ'");
    // 先把所有符合条件的ID存到集合
    List<Integer> idList = new ArrayList<>();
    while(rs.next()) {
        idList.add(rs.getInt(1));
    }
    rs.close();
    // 遍历集合执行更新
    for(Integer id : idList) {
        prepareStatement.executeUpdate("UPDATE TEMP_TABLA_PROFESORES SET PROFESORES_COL_TFGS = PROFESORES_COL_TFGS + 1 WHERE PROFESORES_COL_ID = '" + id + "'");
        tfgs_round = tfgs_round + 1;
    }
    total_tfgs = total_tfgs - tfgs_round;
}

• 方案二：单独创建一个新的Statement/PreparedStatement对象专门用来执行更新操作，不要和查询用的Statement共用

while( total_tfgs > 0 ) {
    int tfgs_round = 0;
    min = prepareStatement.executeQuery("SELECT MIN("+ PROFESORES_COL_TFGS +") FROM TEMP_TABLA_PROFESORES WHERE " + PROFESORES_COL_OBLIGA + " = 'SÍ'").getInt(1);
    ResultSet rs = prepareStatement.executeQuery("SELECT * FROM TEMP_TABLA_PROFESORES WHERE " + PROFESORES_COL_TFGS + " = '" + min + "' AND " + PROFESORES_COL_OBLIGA + " = 'SÍ'");
    // 单独创建更新用的Statement
    Statement updateStmt = con.createStatement();
    while(rs.next()) {
        updateStmt.executeUpdate("UPDATE TEMP_TABLA_PROFESORES SET PROFESORES_COL_TFGS = PROFESORES_COL_TFGS + 1 WHERE PROFESORES_COL_ID = '" + rs.getInt(1) + "'");
        tfgs_round = tfgs_round + 1;
    }
    rs.close();
    updateStmt.close();
    total_tfgs = total_tfgs - tfgs_round;
}

优化建议 #

你当前的逻辑完全可以用一条UPDATE语句实现，不需要查出来再逐行更新，执行效率更高：

UPDATE TEMP_TABLA_PROFESORES 
SET PROFESORES_COL_TFGS = PROFESORES_COL_TFGS + 1 
WHERE PROFESORES_COL_OBLIGA = 'SÍ' 
AND PROFESORES_COL_TFGS = (
    SELECT MIN(PROFESORES_COL_TFGS) 
    FROM TEMP_TABLA_PROFESORES 
    WHERE PROFESORES_COL_OBLIGA = 'SÍ'
)

直接执行这条UPDATE即可一次性更新所有符合条件的行，不需要双层循环遍历，性能提升非常明显。

内容的提问来源于stack exchange，提问作者Piti