Java JDBC一次显示前500条记录，然后提交，然后显示接下来的500条记录等 [英] Java JDBC display first 500 records at a time, commit, than display the next 500 records and etc

问题描述

因此，我希望能够一次显示500条记录，并提交并打印已显示的记录1至500条记录已提交.然后，接下来的500条记录会再次提交，直到达到最大记录(超过2万条记录)为止.我设法获得了前500条记录，但是我被困在如何提交它们以及如何提交它们，并继续获得接下来的500条记录中，以此类推.

So I want to be able to display 500 records at a time, commit and print that it has been displayed records 1 to 500 records have been committed. And than do the next 500 records and commit again until reached the maximum records which is over 20k records. I managed to get the first 500 records but I am stuck how can I commit them and in commit them and continue to get the next 500 records and so on.

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");
         }

      }


}

推荐答案

更新此部分

  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的其余批处理.有关批次的更多详细信息

This check if the rowCount can be divided by 500, execute the batch. Don't forget to execute the batch after all statements finish to execute the remaining batches which couldn't divided by 500 . for more details regarding batches

这篇关于Java JDBC一次显示前500条记录，然后提交，然后显示接下来的500条记录等的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！