如何在数据库列中存储多个项目? [英] How to store multiple items in a database column?

问题描述

在我的项目中，员工插入一个表格号，选择客户订购的所有项目并将其保存到数据库中.我有三个表:

In my project the employee inserts a table number, selects all the items the customer orders and saves that to a database. I have three tables:

Employee(empId, firstname, lastname)
Orders(orderId,tableNum,empIDFK,itemIDFK,totalPrice) 
Item(itemId,itemName, itemPrice)

我的问题是，如果员工在订单中放置多个项目，则仅将最后一个项目保存在itemIDFK列中.我该如何获取员工输入的所有项目的ID?

My problem is that if the employee puts more than one item in the order in only saves the last item in the itemIDFK column. How do I go about attaining the id of all the items that the employee entered?

这是一些示例代码，大多数按钮都具有与此类似的代码:

Here is some example code, mostly all the buttons have code similar to this:

 private void chickenbuttonActionPerformed(java.awt.event.ActionEvent evt) {                                              
    // TODO add your handling code here:
     try{
     st = connection.createStatement();   
    String query;
    query = "SELECT itemName, itemPrice FROM item WHERE itemID = '14446'";
    String itemName = " ",itemPrice =" ";  

      ResultSet rs = st.executeQuery(query);

       if(rs != null){
        while(rs.next())
        { 
         itemName = rs.getString(1);
         itemPrice = rs.getString(2);
        }
     model.addRow(new Object[]{itemName, itemPrice});
      total+= Double.parseDouble(itemPrice);
       String format = formatter.format(total);
       totalField.setText(format);
       }

       //inserts corresponding item id in itemIDFK 
      String query2 = "Update orders SET itemIDFK = '14446' Where tableNum =  " + tableNum;
      ps= connection.prepareStatement(query2);
      ps.executeUpdate();
         } catch (SQLException ex) {}

推荐答案

在这种情况下，您要添加另一个表，例如 OrderDetails ，该表将具有:

What you want to do in this case is add another table, say OrderDetails, and this table would have:

PK: Id
FK: OrderId
FK: ItemId

因此，您可以将多个项目添加到订单中.在 Order 和 OrderDetails 之间存在一个一对多，在之间存在一个 many-to-many > OrderDetails 和 Items

So then you can add multiple items to an order. There would be a one-to-many between Order and OrderDetails and many-to-many between OrderDetails and Items

这篇关于如何在数据库列中存储多个项目?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！