如何在Java中从数据库中检索多行 [英] How to retrieve multiple row from database in java

问题描述

我需要在JTable的行中插入不同的结果.我使用purchaseID(即pID)搜索记录，它将为该特定purchaseID找到2个结果.问题是当我将其插入行时，它只是复制第一个的结果.例如:P0001有2个购买详细信息，当我将其插入行时，我有2个相同的行.我有什么办法可以一一插入吗?

PS.我从早上开始一直在编码，大脑运作不正常.

UI:

  else if(e.getSource()== jbtRetrieve){字符串pID = jTF3.getText();购买购买= purcControl.selectRecord(pID);if(purchase！= null){字符串pdCountStr = purchase.getPurchaseDetailsID();字符串pdCountStr2 = pdCountStr.substring(2,5);int pdCount = Integer.parseInt(pdCountStr2);for(int g = 0; g< pdCount; g ++){//g = 1tableModel.addRow(new Object [] {Purchase.getPurchaseDetailsID()，purchase.getStockID()，purchase.getPrice()，purchase.getQuantity()});;}} 

DA:

 公开购买getRecord(String pID){字符串queryStr ="SELECT PD.PURCHASEDETAILID，PD.PURCHASEID，PD.STOCKID，PD.ORDERQTY，S.STOCKPRICE来自" + tableName +"PD，在S位置PD.STOCKID = S.STOCKID和PurchaseID =?"；购买购买=空；System.out.println("asd");尝试{stmt = conn.prepareStatement(queryStr);stmt.setString(1，pID);ResultSet rs = stmt.executeQuery();if(rs.next()){Purchase = new Purchase(pID，rs.getString("purchaseID")，rs.getString("stockID")，rs.getDouble("stockprice")，rs.getInt("orderqty"));}}捕获(SQLException ex){JOptionPane.showMessageDialog(null，ex.getMessage()，"ERROR"，JOptionPane.ERROR_MESSAGE);}退货；} 

域:

 公共采购(字符串purchaseDetailsID，字符串purchaseID，字符串stockID，双价，整数){this.purchaseDetailsID = PurchaseDetailsID;this.purchaseID = PurchaseID;this.stockID = stockID;this.price =价格；数量=数量;}公共无效setPurchaseID(字符串u){this.purchaseID = u;}公共字符串getPurchaseID(){返回purchaseID；}公共字符串getPurchaseDetailsID(){返回PurchaseDetailsID;}公共双倍getPrice(){退货价格}公共字符串getStockID(){返回stockID；}public int getQuantity(){退货数量}公共无效setPurchaseDetailsID(String r){this.purchaseDetailsID = r;}公共无效setPrice(double p){this.price = p;}公共无效setStockID(String s){this.stockID = s;}公共无效setQuantity(int q){this.quantity = q;} 

控件:

 公共购买selectRecord(String pID){返回purcDA.getRecord(pID);} 

将Purchase设置为ArrayList<>接受列表，然后我收到此异常.

 线程"AWT-EventQueue-0"中的异常java.lang.IndexOutOfBoundsException:索引:1，大小:1在java.util.ArrayList.rangeCheck(ArrayList.java:635)在java.util.ArrayList.rangeCheck(ArrayList.java:635)在java.util.ArrayList.get(ArrayList.java:411)在ui.MainPurchasingFrame $ ListenerClass.actionPerformed(MainPurchasingFrame.java:183) 

代码:

  ArrayList<购买>购买= purcControl.selectRecord(pID);if(purchase！= null){字符串pdCountStr = purchase.get(0).getPurchaseDetailsID();字符串pdCountStr2 = pdCountStr.substring(2,5);int pdCount = Integer.parseInt(pdCountStr2);System.out.print(pdCount);for(int g = 0; g< pdCount; g ++){tableModel.addRow(new Object [] {purchase.get(g).getPurchaseDetailsID()，purchase.get(g).getStockID()，purchase.get(g).getPrice()，purchase.get(g).getQuantity()})；//这是发生异常的地方.} 

解决方案

如果要从查询返回多个对象，则返回类型应为数组或该类型的集合.

因此，无需定义

 公共购买getRecord(String pID){…} 

您应该将DA方法定义为:

 公共列表<购买>getRecords(String pID){…} 

在方法内部，定义一个列表对象，例如:

  List<购买>foundPurchases = new ArrayList<>(); 

然后在发出查询并获取结果集之后，使用while循环来填充它:

  while(rs.next()){Purchase = new Purchase(pID，rs.getString("purchaseID")，rs.getString("stockID")，rs.getDouble("stockprice")，rs.getInt("orderqty"));foundPurchases.add(购买);} 

然后，在完成并关闭语句后，您应该返回foundPurchases:

 返回找到的商品； 

当然，调用代码应该希望收到购买的 List 而不是购买的商品，然后进行迭代并显示出来.

---

现在有关您编辑的部分.您做错了几件事.

首先，您不必进入购买记录即可查看有多少记录.如果没有，列表将为空.如果有1，则大小将为1，如果有两个，则大小将为2.

第二，您实际上不需要知道有多少个元素，因为您可以在一个简单的for循环中使用列表的迭代器遍历列表:

  ArrayList<购买>PurchaseList = purcControl.selectRecord(pID);对于(购买购买:purchaseList){…} 

您实际上不必使用 get .您可以在变量 purchase 中购买当前的个人商品(请注意，我已将原始变量的名称更改为显示列表而不是购买的商品)，您可以直接使用它.

还请注意，您不需要检查null.按照我编写它的方式，DA方法始终返回对列表的引用，即使它是一个空列表也是如此.而且循环总是向列表中添加实际的 new Purchase(…)，因此列表中的项目也绝不会为空.

但是，有一件事:从错误的外观来看，当详细信息ID表示应该有两行时，您似乎只有一行.直接使用数据库检查您的查询，以确保实际上有两条记录.

---

如果要在未找到任何内容的情况下显示错误，则应使用 if 语句检查列表是否为空.这意味着根本没有返回任何记录.

  if(PurchaseList.isEmpty()){//显示错误对话框} 别的 {//执行for循环以填充行} 

I need to insert different result into the row of my JTable. I search my record by using purchaseID which is the pID, it will find 2 result for that particular purchaseID. The problem is when I insert it into row, it just duplicate the result of the first one. EG : P0001 have 2 purchase details and when I insert it into row, I have 2 same row. Is there any way for me to insert into row one by one ?

PS. I've been coding since morning, brain is not functioning well.

UI:

else if(e.getSource() == jbtRetrieve)
            {
                String pID = jTF3.getText();

            Purchase purchase = purcControl.selectRecord(pID);
            if (purchase != null) { 
                String pdCountStr = purchase.getPurchaseDetailsID();
                String pdCountStr2 = pdCountStr.substring(2,5);
                int pdCount = Integer.parseInt(pdCountStr2);
                for(int g = 0; g<pdCount;g++){      //g =1


                tableModel.addRow(new Object[]{ purchase.getPurchaseDetailsID(),purchase.getStockID(),purchase.getPrice(),purchase.getQuantity()});

            }}

DA :

  public Purchase getRecord(String pID){
    String queryStr = "SELECT PD.PURCHASEDETAILID,PD.PURCHASEID,PD.STOCKID,PD.ORDERQTY,S.STOCKPRICE FROM "+tableName+" PD, STOCKS S WHERE PD.STOCKID = S.STOCKID AND PurchaseID = ?";
    Purchase purchase = null;

    System.out.println("asd");
    try{
        stmt = conn.prepareStatement(queryStr);
        stmt.setString(1,pID);
        ResultSet rs = stmt.executeQuery();

     if(rs.next()){
        purchase = new Purchase(pID, rs.getString("purchaseID"),rs.getString("stockID"),rs.getDouble("stockprice"),rs.getInt("orderqty"));
         }
     }
       catch (SQLException ex){
        JOptionPane.showMessageDialog(null,ex.getMessage(),"ERROR",JOptionPane.ERROR_MESSAGE);
    }
    return purchase;
}

Domain:

public Purchase(String purchaseDetailsID,String purchaseID,String stockID,double price,int quantity)
{
    this.purchaseDetailsID = purchaseDetailsID;
    this.purchaseID = purchaseID;
    this.stockID = stockID;
    this.price = price;
    this.quantity = quantity;

}

public void setPurchaseID(String u){
    this.purchaseID = u;
} 
public String getPurchaseID(){
    return purchaseID;
}
public String getPurchaseDetailsID(){

    return purchaseDetailsID ;

}
public double getPrice(){
    return price;
}
public String getStockID(){
    return stockID;
}
public int getQuantity(){
    return quantity;
}
public void setPurchaseDetailsID(String r){
    this.purchaseDetailsID = r ;
}
public void setPrice(double p){
    this.price = p;
}
public void setStockID(String s){
    this.stockID = s;
}
public void setQuantity(int q){
    this.quantity = q;
}

Control :

public Purchase selectRecord(String pID){

    return purcDA.getRecord(pID);
}

Edit: Set Purchase to ArrayList<> to accept the List and I get this Exception.

Exception in thread "AWT-EventQueue-0" java.lang.IndexOutOfBoundsException: Index: 1, Size: 1
at java.util.ArrayList.rangeCheck(ArrayList.java:635)   at java.util.ArrayList.rangeCheck(ArrayList.java:635)
at java.util.ArrayList.get(ArrayList.java:411)
at ui.MainPurchasingFrame$ListenerClass.actionPerformed(MainPurchasingFrame.java:183)

code:

ArrayList<Purchase> purchase = purcControl.selectRecord(pID);
   if (purchase != null) { 
                String pdCountStr = purchase.get(0).getPurchaseDetailsID();
                String pdCountStr2 = pdCountStr.substring(2,5);
                int pdCount = Integer.parseInt(pdCountStr2);
                System.out.print(pdCount);
                for(int g = 0; g<pdCount;g++){
                tableModel.addRow(new Object[]{ purchase.get(g).getPurchaseDetailsID(),purchase.get(g).getStockID(),purchase.get(g).getPrice(),purchase.get(g).getQuantity()}); //This is where the exception occurs.
                }

解决方案

If you want to return multiple objects from a query, your return type should be either an array or a collection of that type.

So instead of defining

public Purchase getRecord(String pID){…}

You should define the DA method as:

public List<Purchase> getRecords(String pID) {…}

Inside the method, define a list object such as:

List<Purchase> foundPurchases = new ArrayList<>();

And then after issuing the query and getting the result set, you use a while loop to fill it:

while ( rs.next() ) {
    purchase = new Purchase(pID, rs.getString("purchaseID"),rs.getString("stockID"),rs.getDouble("stockprice"),rs.getInt("orderqty"));

    foundPurchases.add( purchase );
}

Then, after you finish and close your statement, you should return the foundPurchases:

return foundPurchases;

The calling code should, of course, expect to receive a List of purchases rather than a purchase, and then iterate and display it.

---

Now regarding your edited part. You are doing several things improperly.

First of all, you don't have to go inside the purchase records to see how many records there were. If there were none, the list will be empty. If there was 1, it's size is going to be 1, and if there are two, its size is going to be two.

Second, you actually don't need to know how many elements there are, because you can traverse a list by using its iterator, in a simple for loop:

ArrayList<Purchase> purchaseList = purcControl.selectRecord(pID); 
for ( Purchase purchase : purchaseList ) {
    …
}

You don't actually have to use get. You have the current individual purchase in the variable purchase (Note that I changed the name of your original variable to something that shows it's a list and not a purchase), and you can use it directly.

Note also that you don't need to check for nulls. The way I've written it, the DA method always returns a reference to a List, even if it's an empty list. And the loop always adds an actual new Purchase(…) to the list so the list's items are also never null.

One thing, though: from the way the error looks, it seems that you have only one row when the details ID says there should be two. Check your query directly with the database to be sure there are actually two records.

---

If you want to display an error when nothing was found, you should check with an if statement if the list is empty. This means that no records have been returned at all.

if ( purchasedList.isEmpty() ) {
    // Display error dialog
} else {
    // Do the for loop to fill the rows
}

这篇关于如何在Java中从数据库中检索多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！