sql如何使用java jdbc在单个jdbc类中多次使用Resultset rs.next（）？

Question

我有一个数据库，有两个字段orderid和flow

Orderid字段将包含n个Orderid整数

flow field将有7个字符串，用于对属于该流的订单id进行分类

现在我想根据流类型对所有orderid字段进行分类

我在java jdbc类中编写了包含多个sql语句的代码

当我运行代码时，我得到的是第一个结果集sql查询的结果，而不是第二个结果集sql查询的结果

我使用两个独立的结果集，下面是我的代码片段

有谁能帮我做到这一点吗

谢谢

package com;


import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Scanner;
import com.mysql.jdbc.exceptions.MySQLSyntaxErrorException;
public class getOrderinfo
{
 static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
 static final String DB_URL = "jdbc:mysql://10.10.10.14/opsbank-ii";
 static final String USER = "root";
 static final String PASS = "p@ssw0rd";
 public static int row_count=0;
 public static int count_for_totalfiles=0;
 public static String filename_allocated=""; 
 public static String dateofcar_allocated="";
 public String row_data="";
 public static int orderid=0;
 public static int[] orderid_sto=new int[15000];
 public static int[] orderid_ABPEL=new int[15000];
 public static int[] orderid_WBPG=new int[15000];
 public static int[] orderid_sto2=new int[15000];
 public static String flow="";
 public static String[] flow_sto=new String[15000];
 public static String[] flow_ABPEL=new String[15000];
 public static String[] flow_WBPG=new String[15000]; 
 public static String[] flow_sto2=new String[15000];
 public static Date dateofprocessing;
 public static Date[] dateofprocessing_sto=new Date[15000];
 public static Date[] dateofprocessing_sto2=new Date[15000];
 public static Date[] dateofprocessing_WBPG=new Date[15000];
 public static String Filename_ret0="";
 public static String  Filename_ret_ABPEL="";
 public static String Filename_ret_WBPG="";
 public static String Filename0_all="";
 public static String Filename0_all_ABPEL="";
 public static String Filename0_all_WBPG="";

 public static String Filenameafterlastchar0="";
 public static String Filenameafterlastchar0_ABPEL="";
 public static String Filenameafterlastchar0_WBPG="";



 public static void main(String args[]) throws SQLTimeoutException
 {


  //public static void main(String[] args) 


  Connection conn = null;
 Statement stmt = null,stmt1=null;



  try{
 //STEP 2: Register JDBC driver
  Class.forName("com.mysql.jdbc.Driver");

  Scanner scanner = new Scanner(System.in);
  System.out.println("Enter the Date(Format : 2016-02-22) ");

  String date = scanner.next();

  SimpleDateFormat dateFormat = new SimpleDateFormat("dd-MMM-yyyy");
  Date date2=null;
  /* try {
    //Parsing the String
    date2 = (Date) dateFormat.parse(date);
  } 
  catch (ParseException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
  }*/


 System.out.println("Input Date:"+date);

 //STEP 3: Open a connection
 System.out.println("Connecting to database...");
 conn = DriverManager.getConnection(DB_URL,USER,PASS);

 //STEP 4: Execute a query
 System.out.println("Creating statement...");
 stmt = conn.createStatement();
 stmt1=conn.createStatement();
 String sql,sql_WBPG;

 sql = "select orderid,flow,dateofprocessing from orderinfo where     
 ordertype='CAR' and dateofprocessing like '%"+date+"%'and flow='ABPEL'";
 sql_WBPG = "select orderid,flow,dateofprocessing from 
  orderinfo where    
 ordertype='CAR' and dateofprocessing like '%"+date+"%'and flow='WBPG'";

         ResultSet rs = stmt.executeQuery(sql);


 //STEP 5: Extract data from result set
            System.out.println("For WBPG:\n");

  System.out.println("-----------------------
 ---------------------------------------\n");

  while(rs.next()){
    int i=0;

   orderid_sto[i]=rs.getInt("orderid");
   flow_sto[i]=rs.getString("flow");
   dateofprocessing_sto[i]=rs.getDate("dateofprocessing");
   // System.out.println("Order ID get : "+orderid);
   // orderid_sto[i]=orderid;
   // System.out.println("Order ID After storing : "+orderid_sto[i]);

   //  flow_sto[i++]=flow;

   // dateofprocessing_sto[i++]=dateofprocessing;

    System.out.println("orderid :"+orderid_sto[i]+" || Flow :    
   "+flow_sto[i]+" || date : "+dateofprocessing_sto[i]);    
     //  System.out.println(orderid_sto[i]);    
      i++;
      row_count++;
      count_for_totalfiles++;


      Filename_ret0="'"+rs.getInt("orderid")+"',";
     Filename0_all=Filename0_all+Filename_ret0;


   //Display values
   //System.out.print("BOOKISSID: " + BOOKISSID);
   //System.out.print(", ISSN: " + ISSN);
   //System.out.println("\n");


   }
    System.out.println("Total Number of CAR orders found for the date :       "+date2+" = "+row_count);
   //after removing the comma
   if (Filename0_all.length() > 0 &&     
   Filename0_all.charAt(Filename0_all.length()-1)==',') 
  {
    Filenameafterlastchar0 = Filename0_all.substring(0, 
  Filename0_all.length()-1);
  }
  else
  {
    System.out.println("comma not found as last character :1 ");
  }

 System.out.println("combined name after comma removal :1    
 :"+Filenameafterlastchar0);





  System.out.println("For WBPG:\n");
  System.out.println("-------------------------------
  -------------------------------\n");


  ResultSet rs_WBPG=stmt1.executeQuery(sql_WBPG);

   while(rs_WBPG.next())
  {
    int j=0;
    orderid_WBPG[j]=rs_rs_WBPG.getInt("orderid");
    flow_WBPG[j]=rs_rs_WBPG.getString("flow");


          Filename_ret_WBPG="'"+rs.getInt("orderid")+"',";
         Filename0_all_WBPG=Filename0_all_WBPG+Filename_ret_WBPG;

        System.out.println("orderid :"+orderid_WBPG[j]+" || Flow : 
  "+flow_WBPG[j]+" || date : "+dateofprocessing_WBPG[j]);   


   }

   //after removing the comma for ABPEL
   if (Filename0_all_WBPG.length() > 0 && 
   Filename0_all_WBPG.charAt(Filename0_all_WBPG.length()-1)==',') 
   {
    Filenameafterlastchar0_WBPG = Filename0_all_WBPG.substring(0, 
   Filename0_all_WBPG.length()-1);
    }
   else
    {
     System.out.println("comma not found as last character :1 ");
     }

    System.out.println("combined name after comma removal for ABPEL:1 
    :"+Filenameafterlastchar0_WBPG);




    //STEP 6: Clean-up environment

    rs.close();
     rs_WBPG.close();
     stmt.close();
    conn.close();
  }

  catch(SQLTimeoutException ste)
   {
   System.out.println("\nSQL Time out Error...");
    System.out.println("\nplease Restart the services...");
     ste.printStackTrace();
   }

   catch(MySQLSyntaxErrorException mysqlerr)
   {
    System.out.println("date issue");
   }
   catch(SQLException se){
   //Handle errors for JDBC
    se.printStackTrace();
    }catch(Exception e){
     //Handle errors for Class.forName
    e.printStackTrace();
   }

   finally{
   //finally block used to close resources
    try{
      if(stmt!=null)
          stmt.close();
    }catch(SQLException se2){
    }// nothing we can do
    try{
       if(conn!=null)
          conn.close();
    }catch(SQLException se){
     se.printStackTrace();
     }//end finally try
     }//end try



 }//end main
 }//end FirstExample

片段结束

下面是从while（！rs_WBPG.next（））到（rs_WBPG.next（））的更改后获取的错误

java.sql.SQLException: After end of result set
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1074)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:988)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:974)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:919)
at com.mysql.jdbc.ResultSetImpl.checkRowPos(ResultSetImpl.java:854)
at com.mysql.jdbc.ResultSetImpl.getInt(ResultSetImpl.java:2709)
at com.mysql.jdbc.ResultSetImpl.getInt(ResultSetImpl.java:2850)
at com.getOrderinfo.main(getOrderinfo.java:162)

提前谢谢

Answer 1

在循环内将i索引重置为0，因此只为每个数组的第一个元素赋值

改变：

while (rs.next()) {
    int i = 0;
    ...
    i++;
}

致：

int i = 0;
while (rs.next()) {
    ...
    i++;
}

编辑：

你的第二个循环应该是：

int j = 0;
while(rs_WBPG.next()) {
    ...
    orderid_WBPG[j]=rs_WBPG.getInt("orderid");
    ...
    j++;
}