java数据库添加多行数据,如何在Java中从数据库中检索多行

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

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 = 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

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 getRecords(String pID) {…}

Inside the method, define a list object such as:

List 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 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

}