更新/插入/删除数据库中的子记录

Question

考虑一个保存在数据库中的两个表中的经典销售订单:

SALE_ORDER                   SALE_ORDER_ITEM
----------                   ---------------
id       integer             ...
...                           order_id        integer references sale_order (id)
                              item_id         integer references item (id)
                              quantity        integer
                              ...

对于这个问题，忽略item_id对另一个表的引用——这是无关的。

我有一个包含顺序的类，类似于:

public class SaleOrder {
    private int orderId;
    ...
    private List<BoughtItem> items;
    ....
}

订单可以更改(我的需求)，更改可以包括列表中的项目:增加的项目，删除的项目或数量更改。当我在数据库中更新这个顺序时，显然最简单的方法是执行delete from sale_order_item where order_id=<my_order_id>，然后在列表中插入行。然而，这对我来说似乎相当低效，特别是考虑到列表可能包含(潜在的)数百个不同的项目。(背景:这些是超市给批发产品供应商的订单。)

另一种方法是查询数据库，然后将列表与游标进行比较，在必要时添加、删除或更新——这看起来至少同样低效。

1. 有没有另一种(第三种?)更有效的方法?
2. 如果不是，两者中哪一个更好?我总是使用第一个(删除所有，然后重新插入，但现在开始怀疑自己。

更新:数据库是sqlite3(在android上如果这很重要)

Answer 1

如果您还没有sale_order_item，则需要它的PK。

保留一个脏缓冲区，以便在更新数据库时可以重放。因此，如果用户更改了一个项目，则添加一个条目"update sale_order_item set item_id = "1234"，其中sale_order_item。Id = "2222"'.

当涉及到与数据库通信时，回放这些更改。如何记录更改并不重要(记录SQL并不是最有效的)。关键是能够识别每个项的变化，或添加/删除项，然后能够基于此创建sql。

我希望它能给你一个想法。

Answer 2

这实际上取决于您是要更新项还是只是重新插入项。如果你不需要做任何更新，一个delete + insert将很好，如果你添加现有的项目到子句中(这样你就不会删除在新列表中的东西)

如果新列表总是金色的，那么使用游标就没有意义了，因为你已经有了你的数据，所以只要坚持使用删除/重新插入-它不像游标那样低效。

。t - sql

insert into sale_order_item(columns..)
select field1, 2, etc. from your_new_list a 
where not exists (select 1 from sale_order_item where item_id = a.item_id)

Answer 3

如果您没有原始的项目id列表，您必须至少删除列表中不再存在的所有项目:

DELETE FROM sale_order_item
WHERE order_id = ?
  AND item_id NOT IN (id1, id2, ...)

如果您不知道记录是否更改，则无法避免更新记录。

但是，可以避免插入已经存在的记录:如果在(order_id, item_id)列上有合适的主键，则可以使用INSERT OR REPLACE命令。

然而，所有这些并不比简单的DELETE和INSERT s更有效。

Answer 4

1. 增加字段recordState: enum {rfNone, rfInserted, rfupupdated, rdDeleted}。

2. 为所有setter方法添加适当的代码，例如

   public void setData(String value) {
       if (!this.data.equals(value)) {
           this.data = value;
           if (getRecordState() == rfNone)
               setRecordState(rfUpdated);
       }    
   }
   

   如果记录类的任何值发生了变化，这段代码将确保您将记录对象标记为已更新，当然，如果该对象不是新构建的

3. 从数据库中读取的所有记录必须初始化:recordState = rfNone

4. 新添加的记录必须标记为:recordState = rfInserted at creation
5. 已删除的记录必须标记:recordState = rfDeleted(不要立即删除)
6. 在结束时遍历记录对象列表并根据recordState值
做出适当的操作