many-to-many性能

在hibernate的many-to-many关系中,如user<1...*---1...*>role,当删除inverse="true"(user)一端的一个实例时,需要循环的从inverse="false"(role)一端查找与user的关系并删除之,最后删除inverse="true"的一端,hibernate会产生以下的sql语句:

1)一个select from user.
2)一个select from user_role.
3)2*n个(user的roles个数)select from user_role.
4)一个select from user.
5)n个delete from user_role.
6)一个delete form user.

 

下面是删除一个user所产生的sql语句.user具有3个role.

select authuser0_.ID as ID0_, authuser0_.password as password0_, authuser0_.username   as username0_ from auth_user authuser0_ where authuser0_.ID=?

select authroles0_.roleID as roleID__, authroles0_.userID as userID__ from auth_user_role authroles0_ where authroles0_.userID=?

  select authrole0_.ID as ID0_, authrole0_.name as name0_, authrole0_.description as descript3_0_ from auth_role authrole0_ where authrole0_.ID=?

 

select authrole0_.ID as ID0_, authrole0_.name as name0_, authrole0_.description as descript3_0_ from auth_role authrole0_ where authrole0_.ID=?

 

select authrole0_.ID as ID0_, authrole0_.name as name0_, authrole0_.description as descript3_0_ from auth_role authrole0_ where authrole0_.ID=?

 

select authusers0_.userID as userID__, authusers0_.roleID as roleID__ from auth_user_role  authusers0_ where authusers0_.roleID=?

 

select authusers0_.userID as userID__, authusers0_.roleID as roleID__ from auth_user_role authusers0_ where authusers0_.roleID=?

 

select authusers0_.userID as userID__, authusers0_.roleID as roleID__ from auth_user_role authusers0_ where authusers0_.roleID=?

 

select authuser0_.ID as ID0_, authuser0_.password as password0_, authuser0_.username as username0_ from auth_user authuser0_ where authuser0_.ID=?

 

select authuser0_.ID as ID0_, authuser0_.password as password0_, authuser0_.username as username0_ from auth_user authuser0_ where authuser0_.ID=?

 

delete from auth_user_role where roleID=?

 

delete from auth_user_role where roleID=? and userID=?

 

delete from auth_user_role where roleID=?

 

delete from auth_user where ID=?

此时set各属性如下:

user:

<set

name="authRoles"

table="auth_user_role"

lazy="true"

inverse="true"

cascade="save-update"

sort="unsorted"

>

<key

column="userID"

/>

<many-to-many

class="cn.bluewind.security.model.AuthRole"

column="roleID"

outer-join="auto"

/>

</set>

 

 

role:

 

<set

name="authUsers"

table="auth_user_role"

lazy="true"

inverse="false"

cascade="save-update"

sort="unsorted"

>

<key

column="roleID"

/>

<many-to-many

class="cn.bluewind.security.model.AuthUser"

column="userID"

outer-join="auto"

/>

</set>

此时数据库中中间表user_role中的外间约束是delete->restrict.

但是如果手动的在ddl中将外键约束改为delete->cascade时,在删除user时,就不需要由程序先删除user_role之间的关系,而是由数据库代劳了.此时产生的sql如下:

 

select authuser0_.ID as ID0_, authuser0_.password as password0_, authuser0_.username as username0_ from auth_user authuser0_ where authuser0_.ID=?

 

delete from auth_user where ID=?

两个sql而已.比起上面来少了很多.这样对性能的提升是否有好处,这种方式是否正确.