ResultSet循环的行数少于

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()) { // your setting values on prepared statement code pstmt.addBatch(); } pstmt.executeBatch(); con.setAutoCommit(true);

Answer 2

这并不能回答您的问题，但它确实展示了如何避免为每次迭代创建准备好的语句。相反，您可以准备一次，然后在每次迭代中绑定不同的值。

public void insertValues(Connection con, ResultSet rs) {
    try {
        StringBuffer insert_into_temp = new StringBuffer();
        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(")");
        PreparedStatement pstmt = con.prepareStatement(insert_into_temp.toString());
        while (rs.next()) {
            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());
    }
}