如何在一个数据库列中存储多个项目?

2022-01-20 00:00:00 sql foreign-key-relationship mysql java

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

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 之间会有 one-to-many 和 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

相关文章