java - 结果集循环少于行数

Question

我有一个返回 50 行的 ResultSet。我需要一个临时表来插入这 50 行，以便我可以对其执行查询。

对此没有其他选择，因此请不要建议使用子查询或其他内容。需要一个临时表。

因此，我使用以下方法插入行，显然，虽然我知道 ResultSet 由 50 行组成，但它在 while 循环中仅循环 13 次，因此当我从该表中提取一些字段时，我没有得到所需的结果。

public void insertValues(Connection con, ResultSet rs) {

    StringBuffer insert_into_temp = new StringBuffer();

    try {
        ResultSetMetaData rsmd = rs.getMetaData();
        int colCount = rsmd.getColumnCount();


        insert_into_temp.append("INSERT INTO SESSION.RETURNED_TICKETS (");

        for (int i = 1; i <= colCount; i++) {
            insert_into_temp.append(rsmd.getColumnLabel(i)); 
            insert_into_temp.append(",");
        }

        insert_into_temp.deleteCharAt(insert_into_temp.length()-1);
        insert_into_temp.append(")");

        insert_into_temp.append("\nVALUES(");

        // number of place-holders for values
        for (int i = 0; i < colCount; i++) {
             insert_into_temp.append("?,");  
        }

        insert_into_temp.deleteCharAt(insert_into_temp.length()-1);
        insert_into_temp.append(")");

        while(rs.next()){
            PreparedStatement pstmt = con.prepareStatement(insert_into_temp.toString());

            pstmt.setInt(1, rs.getInt(Ticket.FLD_ID));
            pstmt.setString(2, rs.getString(Ticket.FLD_DESCRIPTION));
            pstmt.setInt(3, rs.getInt(Ticket.FLD_TICKETTYPE));
            pstmt.setString(4, rs.getString("STATE"));
            pstmt.setString(5, rs.getString("PRIORITY"));
            pstmt.setString(6, rs.getString("OWNER"));
            pstmt.setString(7, rs.getString("SUBMITTER"));
            pstmt.setString(8, rs.getString("TYPE"));
            pstmt.setString(9, rs.getString(Ticket.FLD_TITLE));
            pstmt.setString(10, rs.getString("PROJECT"));
            pstmt.setInt(11, rs.getInt("PROJID"));
            pstmt.setDouble(12, rs.getDouble("RELEASE"));
            pstmt.setTimestamp(13, rs.getTimestamp(Ticket.FLD_SUBMITDATE));
            pstmt.setInt(14, rs.getInt(Ticket.FLD_CUSTOMER));  
            pstmt.setInt(15, rs.getInt("ROW_NEXT")); 

            int success = pstmt.executeUpdate();

            if (success != 1) // if not successful
                throw new SQLException("Failed to insert values into temporary table for linked/unlinked tickets");

        }

    } catch (SQLException e){
        LogFile.logError("[Report.execute()] "+e.getMessage());
        LogFile.logError(insert_into_temp.toString());
    }
}

可能是什么问题？我不明白为什么会发生这种情况。谢谢

Answer 1

如果我正确理解问题，而不是每次都像这样使用时执行查询，

con.setAutoCommit(false);
while (rs.next()) {
//您在准备好的语句代码上的设置值
pstmt.addBatch();
}
pstmt.executeBatch();
con.setAutoCommit(true);