一对多双向关联，级联删除，sql语句优化问题

Question

父类
public class Item {
private long itemId;
private String name;
private Set subItem = new HashSet();
...................
}

        <class name="Item" >
    <id name="itemId">
        <generator class="native"/>
    </id>
    <property name="name"></property>

    <set name="subItem" inverse="true" cascade="save-update,delete" >
        <key column="item_id"></key>
        <one-to-many class="SubItem"/>
    </set>  
        </class>

子类

public class SubItem {
private long subItemId;
private String subItemName;
private Item item;
...................
}

<class name="SubItem" >
    <id name="subItemId">
        <generator class="native"/>
    </id>
    <property name="subItemName"></property>    
    <many-to-one name="item" column="item_id"></many-to-one>        
</class>

级联删除
session.beginTransaction();
Item item = (Item) session.load(Item.class, new Long(2));
session.delete(item);
session.getTransaction().commit();

生成的sql语句

Hibernate: select item0_.itemId as itemId0_0_, item0_.name as name0_0_ from Item item0_ where item0_.itemId=?
Hibernate: select subitem0_.item_id as item3_1_, subitem0_.subItemId as subItemId1_, subitem0_.subItemId as subItemId1_0_, subitem0_.subItemName as subItemN2_1_0_, subitem0_.item_id as item3_1_0_ from SubItem subitem0_ where subitem0_.item_id=?
[size=large][b]Hibernate: delete from SubItem where subItemId=?
Hibernate: delete from SubItem where subItemId=?
Hibernate: delete from SubItem where subItemId=?
Hibernate: delete from SubItem where subItemId=?[/b][/size]
Hibernate: delete from Item where itemId=?

其中加粗部分 显然是低效率的 如果字表有1000条语句的匹配的话就要1000条delete语句
其实一条就可以了 delete from SubItem where item_id=?
在hibernate中如何做到

Answer 1

不知道具体您的数据量有多少?

系统的优化需要考虑很多方面,虽然采用类似in (...)来处理，就着一次执行来说，性能有一定的提高。但如果DB是Oracle(其他DB也应该类似)，delete from SubItem where subItemId=?会对这样的语句做缓存，而in(...)很难有效缓存，并且甚至，如果in(...)的内容过多sql过大，会导致DB性能严重下降，直至崩溃。
所以，如果你的items数量不是很大(平均 < 1000)，没有必要优化。

如果确实数量很大，建议直接使用sql来删除，例如你这里可以考虑使用
delete from SubItem where Item_Id=? 删除。或者通过存储过程来优化。