从数据库中检索特定数据并显示。

时间:2021-01-06 19:30:01

How to retrieve specific data from database and display?

如何从数据库中检索特定数据并显示?

    String sql = "Select Product_ID,Order_Quantity,Sub_Total from Order_Menu Where Order_ID='"+order_id_1+"'";
    pst = conn.prepareStatement(sql);
    rs = pst.executeQuery();

    while(rs.next()){

        list.add(rs.getString("Product_ID"));
        list.add(rs.getString("Order_Quantity"));
        list.add(rs.getString("Sub_Total"));

        for(int i = 0;i<list.size();i++){
            String item = list.get(i);
            System.out.println("Item" + i + ":" + item);
        }

I define the array

我定义数组

ArrayList <String> list = new ArrayList<String>();

my output

我的输出

Item0:P0001  
Item1:1  
Item2:37.0
Item0:P0001 
Item1:1 
Item2:37.0
Item3:P0002 
Item4:2 
Item5:666.0

My database only contains 2 items which is P0001 and P0002, how come the result will display 1 more item Sorry, I'm slightly confuse about array

我的数据库只包含两个项目,P0001和P0002,为什么结果会显示另外一个项目对不起,我对数组有点迷惑

5 个解决方案

#1


2  

You're printing the entire list contents every time you add a record from the result set. In the first iteration, list.size() == 3, so it prints:

每次从结果集中添加一条记录时,都要打印整个列表内容。

Item0:P0001 Item1:1 Item2:37.0

Item0:P0001 Item1:1 Item2:37.0

In the second iteration, list.size() == 6, so it prints:

在第二次迭代中,list.size() = 6,因此它输出:

Item0:P0001 Item1:1 Item2:37.0 Item3:P0002 Item4:2 Item5:666.0

项目1:项目1:项目2:项目3:项目4:项目2:项目5:666.0

Thereby making your eventual output:

从而使您的最终输出:

Item0:P0001 Item1:1 Item2:37.0 Item0:P0001 Item1:1 Item2:37.0 Item3:P0002 Item4:2 Item5:666.0

项目0:P0001项目1:项目1:项目2:项目1:项目1:项目2:项目3:项目4:项目2:项目5:666.0

Moving your for loop outside the while statement should fix it.

在while语句之外移动您的for循环应该可以修复它。

while(rs.next()){
  list.add(rs.getString("Product_ID"));
  list.add(rs.getString("Order_Quantity"));
  list.add(rs.getString("Sub_Total"));
}

for(int i = 0;i<list.size();i++){
  String item = list.get(i);
  System.out.println("Item" + i + ":" + item);
}

#2


1  

First of all this is not the correct way of using PreparedStatement . You should use it in following way:

首先,这不是使用PreparedStatement的正确方法。你应以下列方式使用:

String sql = "Select Product_ID,Order_Quantity,Sub_Total from Order_Menu Where Order_ID=?";
pst = conn.prepareStatement(sql);
pst.setString(order_id_1);
rs = pst.executeQuery();

Now, the reason for your output.

现在,你输出的原因。

You are adding records in list assuming that at each index of list only one product is placing. But it is not the case. For example , if your program gets product P0001 then you are adding p001 at index 0 , Order_Quantity 1 at index 1 and subtotal 37 at index 2. So , you are getting such output. I would suggest you to create a separate class for Product as follows:

在列表中添加记录,假设在列表的每个索引中只有一个产品正在放置。但事实并非如此。例如,如果你的程序得到product P0001,那么你在索引0处添加p001,在索引1处添加Order_Quantity 1,在索引2处添加subtotal 37。你会得到这样的输出。我建议您为产品创建一个单独的类如下:

class Product
{
  String product_id;
  String order_quantity;
  String sub_total;
  public Product(String product_id, String order_quantity, String sub_total)
  {
    this.product_id = product_id;
    this.order_quantity = order_quantity;
    this.sub_total = sub_total;
  }
  public void setProduct_id(String product_id)
  {
    this.product_id = product_id;
  }
  public void setOrder_quantity(String order_quantity)
  {
    this.order_quantity = order_quantity;
  }
  public void setSub_total(String sub_total)
  {
    this.sub_total = sub_total;
  }
  public String getProduct_id()
  {
    return product_id;
  }
  public String getOrder_quantiy()
  {
    return order_quantity;
  }
  public String getSub_total() 
  {
    return sub_total;
  }
  @Override
  public boolean equals(Object obj)
  {
    if ( obj instanceof Product)
    {
        temp = (Product)obj;
        if (product_id.equals(temp.product_id))
        {
          return true;
        }
    }
    return false;
  }
  @Override
  public int hashCode()//override it in such a way so that you get different hashCode for each product ID.
  {
    return product_id.hashCode();
  }
  @Override
  public String toString()
  {
    return "product_id:"+product_id+", order_quantity:"+order_quantity+", sub_total:"+sub_total;
  }
}

And finally, You can use the object of Product class as follows:

最后,您可以使用Product类的对象如下:

List<Product> list = new ArrayList<Product>();
while(rs.next()){
 Product product = new Product(rs.getString("Product_ID"),rs.getString("Order_Quantity"),rs.getString("Sub_Total"));
  list.add(product);
 }
 for(int i = 0;i<list.size();i++){
 Product item = list.get(i);
 System.out.println("Item" + i + ":" + item);
}

#3


0  

You are not clearing your list in between iterations. This means that when you process the first row, your list will contain three items, when you display the second row, your list will contain the items from the first row and the second row...

您没有在迭代之间清理列表。这意味着当您处理第一行时,您的列表将包含三个项,当您显示第二行时,您的列表将包含来自第一行和第二行的项…

Why don't you forget the list and just

你为什么不把清单忘了呢

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

    System.out.println("Item " + i);
    System.out.println("Product ID    : " + rs.getString("Product_ID"));
    System.out.println("Order Quantity: " + rs.getString("Order_Quantity"));
    System.out.println("Sub_Total     : " + rs.getString("Sub_Total"));

}

and of course don't forget to wrap the whole thing in try finally and:

当然,别忘了最后把整件事都写下来:

rs.close();
pst.close();

#4


0  

Make sure you are iterating list out side the aray list

确保在aray列表之外迭代列表

while(){
    //implementation
   }
   for(){
    //Iterate your list
    }

#5


0  

Say your database table is as follows:

假设您的数据库表如下所示:

Product_ID  Order_Quantity  Sub_Total 
P0001       1               37.0
P0002       2               666.0

Your while loop adds elements to your list as:

while循环将元素添加到列表中:

First iteration:

第一次迭代:

list=["P0001","1","37.0"]

Second iteration:

第二个迭代:

list=["P0001","1","37.0","P0002","2","666.0"]

So when the while loop executes the first time, You are getting the output as:

因此,当while循环第一次执行时,您将得到输出为:

Item0:P0001
Item1:1
Item2:37.0

In the second iteration, since list is ["P0001","1","37.0","P0002","2","666.0"],hence you get further output.

在第二个迭代中,由于list是["P0001"、"1"、"37.0"、"P0002"、"2"、"666.0"],因此可以得到进一步的输出。

Item0:P0001
Item1:1
Item2:37.0
Item3:P0002
Item4:2
Item5:666.0

combining the above two outputs,you are getting:

结合以上两个输出,您将得到:

Item0:P0001
Item1:1
Item2:37.0
Item0:P0001
Item1:1
Item2:37.0
Item3:P0002
Item4:2
Item5:666.0

So you need two dimensional data structure.I would suggest add a one dimensional array of strings to the list as:

所以你需要二维的数据结构。我建议将一维字符串数组添加到列表中,如下所示:

String sql = "Select Product_ID,Order_Quantity,Sub_Total from Order_Menu Where    Order_ID='"+order_id_1+"'";
    pst = conn.prepareStatement(sql);
    rs = pst.executeQuery();
    String[] row=new String[3]
    while(rs.next()){

        row[0]=rs.getString("Product_ID");
        row[1]=rs.getString("Order_Quantity");
        row[2]=rs.getString("Sub_Total");

        list.add(row);
}

#1


2  

You're printing the entire list contents every time you add a record from the result set. In the first iteration, list.size() == 3, so it prints:

每次从结果集中添加一条记录时,都要打印整个列表内容。

Item0:P0001 Item1:1 Item2:37.0

Item0:P0001 Item1:1 Item2:37.0

In the second iteration, list.size() == 6, so it prints:

在第二次迭代中,list.size() = 6,因此它输出:

Item0:P0001 Item1:1 Item2:37.0 Item3:P0002 Item4:2 Item5:666.0

项目1:项目1:项目2:项目3:项目4:项目2:项目5:666.0

Thereby making your eventual output:

从而使您的最终输出:

Item0:P0001 Item1:1 Item2:37.0 Item0:P0001 Item1:1 Item2:37.0 Item3:P0002 Item4:2 Item5:666.0

项目0:P0001项目1:项目1:项目2:项目1:项目1:项目2:项目3:项目4:项目2:项目5:666.0

Moving your for loop outside the while statement should fix it.

在while语句之外移动您的for循环应该可以修复它。

while(rs.next()){
  list.add(rs.getString("Product_ID"));
  list.add(rs.getString("Order_Quantity"));
  list.add(rs.getString("Sub_Total"));
}

for(int i = 0;i<list.size();i++){
  String item = list.get(i);
  System.out.println("Item" + i + ":" + item);
}

#2


1  

First of all this is not the correct way of using PreparedStatement . You should use it in following way:

首先,这不是使用PreparedStatement的正确方法。你应以下列方式使用:

String sql = "Select Product_ID,Order_Quantity,Sub_Total from Order_Menu Where Order_ID=?";
pst = conn.prepareStatement(sql);
pst.setString(order_id_1);
rs = pst.executeQuery();

Now, the reason for your output.

现在,你输出的原因。

You are adding records in list assuming that at each index of list only one product is placing. But it is not the case. For example , if your program gets product P0001 then you are adding p001 at index 0 , Order_Quantity 1 at index 1 and subtotal 37 at index 2. So , you are getting such output. I would suggest you to create a separate class for Product as follows:

在列表中添加记录,假设在列表的每个索引中只有一个产品正在放置。但事实并非如此。例如,如果你的程序得到product P0001,那么你在索引0处添加p001,在索引1处添加Order_Quantity 1,在索引2处添加subtotal 37。你会得到这样的输出。我建议您为产品创建一个单独的类如下:

class Product
{
  String product_id;
  String order_quantity;
  String sub_total;
  public Product(String product_id, String order_quantity, String sub_total)
  {
    this.product_id = product_id;
    this.order_quantity = order_quantity;
    this.sub_total = sub_total;
  }
  public void setProduct_id(String product_id)
  {
    this.product_id = product_id;
  }
  public void setOrder_quantity(String order_quantity)
  {
    this.order_quantity = order_quantity;
  }
  public void setSub_total(String sub_total)
  {
    this.sub_total = sub_total;
  }
  public String getProduct_id()
  {
    return product_id;
  }
  public String getOrder_quantiy()
  {
    return order_quantity;
  }
  public String getSub_total() 
  {
    return sub_total;
  }
  @Override
  public boolean equals(Object obj)
  {
    if ( obj instanceof Product)
    {
        temp = (Product)obj;
        if (product_id.equals(temp.product_id))
        {
          return true;
        }
    }
    return false;
  }
  @Override
  public int hashCode()//override it in such a way so that you get different hashCode for each product ID.
  {
    return product_id.hashCode();
  }
  @Override
  public String toString()
  {
    return "product_id:"+product_id+", order_quantity:"+order_quantity+", sub_total:"+sub_total;
  }
}

And finally, You can use the object of Product class as follows:

最后,您可以使用Product类的对象如下:

List<Product> list = new ArrayList<Product>();
while(rs.next()){
 Product product = new Product(rs.getString("Product_ID"),rs.getString("Order_Quantity"),rs.getString("Sub_Total"));
  list.add(product);
 }
 for(int i = 0;i<list.size();i++){
 Product item = list.get(i);
 System.out.println("Item" + i + ":" + item);
}

#3


0  

You are not clearing your list in between iterations. This means that when you process the first row, your list will contain three items, when you display the second row, your list will contain the items from the first row and the second row...

您没有在迭代之间清理列表。这意味着当您处理第一行时,您的列表将包含三个项,当您显示第二行时,您的列表将包含来自第一行和第二行的项…

Why don't you forget the list and just

你为什么不把清单忘了呢

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

    System.out.println("Item " + i);
    System.out.println("Product ID    : " + rs.getString("Product_ID"));
    System.out.println("Order Quantity: " + rs.getString("Order_Quantity"));
    System.out.println("Sub_Total     : " + rs.getString("Sub_Total"));

}

and of course don't forget to wrap the whole thing in try finally and:

当然,别忘了最后把整件事都写下来:

rs.close();
pst.close();

#4


0  

Make sure you are iterating list out side the aray list

确保在aray列表之外迭代列表

while(){
    //implementation
   }
   for(){
    //Iterate your list
    }

#5


0  

Say your database table is as follows:

假设您的数据库表如下所示:

Product_ID  Order_Quantity  Sub_Total 
P0001       1               37.0
P0002       2               666.0

Your while loop adds elements to your list as:

while循环将元素添加到列表中:

First iteration:

第一次迭代:

list=["P0001","1","37.0"]

Second iteration:

第二个迭代:

list=["P0001","1","37.0","P0002","2","666.0"]

So when the while loop executes the first time, You are getting the output as:

因此,当while循环第一次执行时,您将得到输出为:

Item0:P0001
Item1:1
Item2:37.0

In the second iteration, since list is ["P0001","1","37.0","P0002","2","666.0"],hence you get further output.

在第二个迭代中,由于list是["P0001"、"1"、"37.0"、"P0002"、"2"、"666.0"],因此可以得到进一步的输出。

Item0:P0001
Item1:1
Item2:37.0
Item3:P0002
Item4:2
Item5:666.0

combining the above two outputs,you are getting:

结合以上两个输出,您将得到:

Item0:P0001
Item1:1
Item2:37.0
Item0:P0001
Item1:1
Item2:37.0
Item3:P0002
Item4:2
Item5:666.0

So you need two dimensional data structure.I would suggest add a one dimensional array of strings to the list as:

所以你需要二维的数据结构。我建议将一维字符串数组添加到列表中,如下所示:

String sql = "Select Product_ID,Order_Quantity,Sub_Total from Order_Menu Where    Order_ID='"+order_id_1+"'";
    pst = conn.prepareStatement(sql);
    rs = pst.executeQuery();
    String[] row=new String[3]
    while(rs.next()){

        row[0]=rs.getString("Product_ID");
        row[1]=rs.getString("Order_Quantity");
        row[2]=rs.getString("Sub_Total");

        list.add(row);
}