Java JDBC一次显示前500条记录，提交，然后显示接下来的500条记录等

Question

所以我希望能够一次显示500条记录，提交并打印已经显示的记录1到500条记录已经提交。然后执行接下来的 500 条记录并再次提交，直到达到超过 20k 记录的最大记录。我设法获取了前 500 条记录，但我陷入困境，如何提交它们并提交它们并继续获取接下来的 500 条记录，依此类推。

public static void selectRecordsIcore() throws SQLException {

    Connection dbConnection = null;
    PreparedStatement preparedStatement = null;
    Statement statement = null;

    String selectTableSQL = "SELECT profile_id, ingress_flag, egress_flag, ce_ingress_flag, ce_egress_flag from COS_PROFILE"
    + " WHERE profile_id >= ? AND profile_id <= ?;";

    try {
        dbConnection = getInformixConnection();    //connects to ICORE database
        System.out.println("I am in the try");

        //Gets the max profile_id record
        statement = dbConnection.createStatement();
        ResultSet r = statement.executeQuery("SELECT max(profile_id) AS rowcount FROM COS_PROFILE");       
        r.next();
        int maxCount = r.getInt("rowcount");
        System.out.println("COS_PROFILE table before update has " + maxCount + " row(s).");

        preparedStatement = dbConnection.prepareStatement(selectTableSQL);
        preparedStatement.setInt(1, 1);
        preparedStatement.setInt(2, maxCount);

        // execute select SQL statement
        rs = preparedStatement.executeQuery();

          updateRecordIntoBids();

    } catch (SQLException e) {

        System.out.println(e.getMessage());

    } finally {
         if (rs != null) {
             rs.close();
         }
         if (statement != null) {
             statement.close();
         }
         if (preparedStatement != null) {
             preparedStatement.close();
         }

         if (dbConnection != null) {
             dbConnection.close();
             System.out.println("Database ICORE Connection is closed");
         }

      }



}



private static void updateRecordIntoBids() throws SQLException {

    System.out.println("I am inside update method");

      Connection dbConnection = null;
      PreparedStatement preparedStatement = null;
      dbConnection = getOracleConnection(); //connects to BIDS database

         String updateTableSQL = 
                    "UPDATE traffic_profile_temp SET pe_ingress_flag  = ?, "
                 + " pe_egress_flag = ?,"
                 + " ce_ingress_flag = ?,"
                 + " ce_egress_flag = ? "
                 + " WHERE traffic_profile_id = ?  ";

      preparedStatement = dbConnection.prepareStatement(updateTableSQL);

         try {
             int rowCount = 0;   
           while (rs.next() && rowCount < 500) {
            //  System.out.println("inside the while loop");


                 String ingressflag = rs.getString("ingress_flag");     //BIDS column is pe_ingress_flag
                 String egressflag = rs.getString("egress_flag");       //BIDS column is pe_egress_flag
                 String ceingressflag = rs.getString("ce_ingress_flag"); //BIDS column is ce_ingress_flag
                 String ceegressflag = rs.getString("ce_egress_flag");  //BIDS column is ce_egress_flag
                 int profileid = rs.getInt("profile_id");               //BIDS column is traffic_profile_id

                preparedStatement.setString(1, ingressflag);
                preparedStatement.setString(2, egressflag);
                preparedStatement.setString(3, ceingressflag);
                preparedStatement.setString(4, ceegressflag);
                preparedStatement.setInt(5, profileid);

                  //  System.out.println(updateTableSQL);

                System.out.println("Record " +profileid +" is updated to traffic_profile_temp table!");

                // execute update SQL stetement
                preparedStatement.addBatch();
                rowCount++;
                System.out.println(rowCount);   


           }

          preparedStatement.executeBatch();

            } catch (SQLException e) {

                System.out.println(e.getMessage());

    } finally {


         if (preparedStatement != null) {
            preparedStatement.close();
         }

         if (dbConnection != null) {
             dbConnection.close();
             System.out.println("Database BIDS Connection is closed");
         }

      }


}

Answer 1

更新这部分

  while (rs.next() && rowCount < 500) {

与

   while (rs.next()) {

和

// execute update SQL stetement
                preparedStatement.addBatch();
                rowCount++;
                System.out.println(rowCount); 

与

  // execute update SQL stetement
  preparedStatement.addBatch();
  rowCount++;
  if(rowCount % 500 == 0){
      preparedStatement.executeBatch(); 
  }
  System.out.println(rowCount);   

检查 rowCount 是否可以除以 500，然后执行批处理。 不要忘记在所有语句完成后执行该批处理，以执行无法除以 500 的剩余批处理 。有关 batches 的更多详细信息