I have 3 tables {Group, FeatureAccess, Feature}. The Group table and the Feature table have a many-to-many relationship, and per Database Design, I created an associative table that makes the relationships one-to-manies.
Okay without going into too much detail, here is the concept. An User can be part of MANY Groups and a Group has Many Features (this relationship is
Group (1) -----> (1..*) FeatureAccess (1..*) <----- (1) Feature
FeatureAccess has attributes Group, Feature, and access (int)
I want to be able to do group.getFeatureAccess() and feature.getFeatureAccess()
Here are my mapping files:
Code:
Group.hbm.xml
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class name="com.saic.ide.group.Group" table="idegroup">
<id name="id" column="groupID" type="long">
<generator class="native"/>
</id>
<property name="groupName" column="groupName" type="string"/>
<property name="description" column="groupDescription" type="string"/>
<set name="features" table="featureaccess" lazy="false">
<key column="groupID"/>
<composite-element class="com.saic.ide.menu.feature.FeatureAccess">
<property name="access" type="int" not-null="true"/>
<many-to-one name="feature" class="com.saic.ide.menu.feature.Feature" lazy="false" column="featureID"/>
</composite-element>
</set>
</class>
</hibernate-mapping>
Code:
Feature.hbm.xml
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class name="com.saic.ide.menu.feature.Feature" table="feature">
<id name="id" column="featureID" type="long">
<generator class="native"/>
</id>
<property name="visible" column="visible" type="boolean"/>
<property name="iconPath" column="iconPath" type="string"/>
<property name="feature" column="name" type="string"/>
<property name="forward" column="forward" type="string"/>
<many-to-one name="menu" column="menuID" class="com.saic.ide.menu.Menu" not-null="false" lazy="false"/>
<!--<set name="groups" table="featureaccess" lazy="false">
<key column="featureID"/>
<many-to-many class="com.saic.ide.group.Group" column="groupID"/>
</set>
-->
<set name="accessGroups" table="featureaccess" lazy="false">
<key column="featureID"/>
<composite-element class="com.saic.ide.menu.feature.FeatureAccess">
<property name="access" type="int" not-null="true"/>
<many-to-one name="group" class="com.saic.ide.group.Group" column="groupID" lazy="false"/>
</composite-element>
</set>
</class>
Code:
User.hbm.xml
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class name="com.saic.ide.user.User" table="user">
<id name="id" column="userID" type="long">
<generator class="increment"/>
</id>
<property name="firstName" column="firstname" type="string"/>
<property name="lastName" column="lastname" type="string" />
<property name="middleName" column="middle" type="string" />
<property name="email" column="email" type="string" />
<property name="title" column="title" type="string" />
<set name="groups" lazy="false" table="usergroup">
<key column="userID"/>
<many-to-many class="com.saic.ide.group.Group" column="groupID"/>
</set>
</class>
</hibernate-mapping>
When hibernate retrieves a User there are several random inserts and deletes they look like:
Code:
Hibernate: select this_.groupID as groupID5_0_, this_.groupName as groupName5_0_, this_.groupDescription as groupDes3_5_0_ from idegroup this_
Hibernate: select features0_.groupID as groupID0_, features0_.access as access0_, features0_.featureID as featureID0_ from featureaccess features0_ where features0_.groupID=?
Hibernate: select feature0_.featureID as featureID3_0_, feature0_.visible as visible3_0_, feature0_.iconPath as iconPath3_0_, feature0_.name as name3_0_, feature0_.forward as forward3_0_, feature0_.menuID as menuID3_0_ from feature feature0_ where feature0_.featureID=?
Hibernate: select menu0_.menuID as menuID2_0_, menu0_.menuName as menuName2_0_, menu0_.visible as visible2_0_ from menu menu0_ where menu0_.menuID=?
Hibernate: select features0_.menuID as menuID1_, features0_.featureID as featureID1_, features0_.featureID as featureID3_0_, features0_.visible as visible3_0_, features0_.iconPath as iconPath3_0_, features0_.name as name3_0_, features0_.forward as forward3_0_, features0_.menuID as menuID3_0_ from feature features0_ where features0_.menuID=?
Hibernate: select accessgrou0_.featureID as featureID0_, accessgrou0_.access as access0_, accessgrou0_.groupID as groupID0_ from featureaccess accessgrou0_ where accessgrou0_.featureID=?
Hibernate: select accessgrou0_.featureID as featureID0_, accessgrou0_.access as access0_, accessgrou0_.groupID as groupID0_ from featureaccess accessgrou0_ where accessgrou0_.featureID=?
Hibernate: select accessgrou0_.featureID as featureID0_, accessgrou0_.access as access0_, accessgrou0_.groupID as groupID0_ from featureaccess accessgrou0_ where accessgrou0_.featureID=?
Hibernate: select feature0_.featureID as featureID3_0_, feature0_.visible as visible3_0_, feature0_.iconPath as iconPath3_0_, feature0_.name as name3_0_, feature0_.forward as forward3_0_, feature0_.menuID as menuID3_0_ from feature feature0_ where feature0_.featureID=?
Hibernate: select menu0_.menuID as menuID2_0_, menu0_.menuName as menuName2_0_, menu0_.visible as visible2_0_ from menu menu0_ where menu0_.menuID=?
Hibernate: select features0_.menuID as menuID1_, features0_.featureID as featureID1_, features0_.featureID as featureID3_0_, features0_.visible as visible3_0_, features0_.iconPath as iconPath3_0_, features0_.name as name3_0_, features0_.forward as forward3_0_, features0_.menuID as menuID3_0_ from feature features0_ where features0_.menuID=?
Hibernate: select accessgrou0_.featureID as featureID0_, accessgrou0_.access as access0_, accessgrou0_.groupID as groupID0_ from featureaccess accessgrou0_ where accessgrou0_.featureID=?
Hibernate: select accessgrou0_.featureID as featureID0_, accessgrou0_.access as access0_, accessgrou0_.groupID as groupID0_ from featureaccess accessgrou0_ where accessgrou0_.featureID=?
Hibernate: select accessgrou0_.featureID as featureID0_, accessgrou0_.access as access0_, accessgrou0_.groupID as groupID0_ from featureaccess accessgrou0_ where accessgrou0_.featureID=?
Hibernate: select feature0_.featureID as featureID3_0_, feature0_.visible as visible3_0_, feature0_.iconPath as iconPath3_0_, feature0_.name as name3_0_, feature0_.forward as forward3_0_, feature0_.menuID as menuID3_0_ from feature feature0_ where feature0_.featureID=?
Hibernate: select menu0_.menuID as menuID2_0_, menu0_.menuName as menuName2_0_, menu0_.visible as visible2_0_ from menu menu0_ where menu0_.menuID=?
Hibernate: select features0_.menuID as menuID1_, features0_.featureID as featureID1_, features0_.featureID as featureID3_0_, features0_.visible as visible3_0_, features0_.iconPath as iconPath3_0_, features0_.name as name3_0_, features0_.forward as forward3_0_, features0_.menuID as menuID3_0_ from feature features0_ where features0_.menuID=?
Hibernate: select accessgrou0_.featureID as featureID0_, accessgrou0_.access as access0_, accessgrou0_.groupID as groupID0_ from featureaccess accessgrou0_ where accessgrou0_.featureID=?
Hibernate: select accessgrou0_.featureID as featureID0_, accessgrou0_.access as access0_, accessgrou0_.groupID as groupID0_ from featureaccess accessgrou0_ where accessgrou0_.featureID=?
Hibernate: select accessgrou0_.featureID as featureID0_, accessgrou0_.access as access0_, accessgrou0_.groupID as groupID0_ from featureaccess accessgrou0_ where accessgrou0_.featureID=?
Hibernate: select feature0_.featureID as featureID3_0_, feature0_.visible as visible3_0_, feature0_.iconPath as iconPath3_0_, feature0_.name as name3_0_, feature0_.forward as forward3_0_, feature0_.menuID as menuID3_0_ from feature feature0_ where feature0_.featureID=?
Hibernate: select menu0_.menuID as menuID2_0_, menu0_.menuName as menuName2_0_, menu0_.visible as visible2_0_ from menu menu0_ where menu0_.menuID=?
Hibernate: select features0_.menuID as menuID1_, features0_.featureID as featureID1_, features0_.featureID as featureID3_0_, features0_.visible as visible3_0_, features0_.iconPath as iconPath3_0_, features0_.name as name3_0_, features0_.forward as forward3_0_, features0_.menuID as menuID3_0_ from feature features0_ where features0_.menuID=?
Hibernate: select accessgrou0_.featureID as featureID0_, accessgrou0_.access as access0_, accessgrou0_.groupID as groupID0_ from featureaccess accessgrou0_ where accessgrou0_.featureID=?
Hibernate: select accessgrou0_.featureID as featureID0_, accessgrou0_.access as access0_, accessgrou0_.groupID as groupID0_ from featureaccess accessgrou0_ where accessgrou0_.featureID=?
Hibernate: select accessgrou0_.featureID as featureID0_, accessgrou0_.access as access0_, accessgrou0_.groupID as groupID0_ from featureaccess accessgrou0_ where accessgrou0_.featureID=?
Hibernate: select feature0_.featureID as featureID3_0_, feature0_.visible as visible3_0_, feature0_.iconPath as iconPath3_0_, feature0_.name as name3_0_, feature0_.forward as forward3_0_, feature0_.menuID as menuID3_0_ from feature feature0_ where feature0_.featureID=?
Hibernate: select menu0_.menuID as menuID2_0_, menu0_.menuName as menuName2_0_, menu0_.visible as visible2_0_ from menu menu0_ where menu0_.menuID=?
Hibernate: select features0_.menuID as menuID1_, features0_.featureID as featureID1_, features0_.featureID as featureID3_0_, features0_.visible as visible3_0_, features0_.iconPath as iconPath3_0_, features0_.name as name3_0_, features0_.forward as forward3_0_, features0_.menuID as menuID3_0_ from feature features0_ where features0_.menuID=?
Hibernate: select accessgrou0_.featureID as featureID0_, accessgrou0_.access as access0_, accessgrou0_.groupID as groupID0_ from featureaccess accessgrou0_ where accessgrou0_.featureID=?
Hibernate: delete from featureaccess where groupID=? and access=?
Hibernate: insert into featureaccess (groupID, access, featureID) values (?, ?, ?)
Hibernate: insert into featureaccess (groupID, access, featureID) values (?, ?, ?)
Hibernate: insert into featureaccess (groupID, access, featureID) values (?, ?, ?)
Hibernate: insert into featureaccess (groupID, access, featureID) values (?, ?, ?)
Hibernate: insert into featureaccess (groupID, access, featureID) values (?, ?, ?)
Hibernate: insert into featureaccess (groupID, access, featureID) values (?, ?, ?)
Hibernate: insert into featureaccess (groupID, access, featureID) values (?, ?, ?)
Hibernate: insert into featureaccess (groupID, access, featureID) values (?, ?, ?)
Hibernate: insert into featureaccess (groupID, access, featureID) values (?, ?, ?)
Hibernate: insert into featureaccess (groupID, access, featureID) values (?, ?, ?)
Hibernate: insert into featureaccess (groupID, access, featureID) values (?, ?, ?)
Hibernate: insert into featureaccess (groupID, access, featureID) values (?, ?, ?)
Hibernate: insert into featureaccess (groupID, access, featureID) values (?, ?, ?)
Hibernate: delete from featureaccess where featureID=? and access=?
Hibernate: insert into featureaccess (featureID, access, groupID) values (?, ?, ?)
Hibernate: delete from featureaccess where featureID=? and access=?
Hibernate: insert into featureaccess (featureID, access, groupID) values (?, ?, ?)
Hibernate: delete from featureaccess where featureID=? and access=?
Hibernate: insert into featureaccess (featureID, access, groupID) values (?, ?, ?)
Hibernate: delete from featureaccess where featureID=? and access=?
Hibernate: insert into featureaccess (featureID, access, groupID) values (?, ?, ?)
Hibernate: delete from featureaccess where featureID=? and access=?
Hibernate: insert into featureaccess (featureID, access, groupID) values (?, ?, ?)
Hibernate: delete from featureaccess where featureID=? and access=?
Hibernate: insert into featureaccess (featureID, access, groupID) values (?, ?, ?)
Hibernate: delete from featureaccess where featureID=? and access=?
Hibernate: insert into featureaccess (featureID, access, groupID) values (?, ?, ?)
Hibernate: delete from featureaccess where featureID=? and access=?
Hibernate: insert into featureaccess (featureID, access, groupID) values (?, ?, ?)
Hibernate: delete from featureaccess where featureID=? and access=?
Hibernate: insert into featureaccess (featureID, access, groupID) values (?, ?, ?)
Hibernate: delete from featureaccess where featureID=? and access=?
Hibernate: insert into featureaccess (featureID, access, groupID) values (?, ?, ?)
Hibernate: delete from featureaccess where featureID=? and access=?
Hibernate: insert into featureaccess (featureID, access, groupID) values (?, ?, ?)
Hibernate: delete from featureaccess where featureID=? and access=?
Hibernate: insert into featureaccess (featureID, access, groupID) values (?, ?, ?)
Hibernate: delete from featureaccess where featureID=? and access=?
Hibernate: insert into featureaccess (featureID, access, groupID) values (?, ?, ?)
I did some research and it says Hibernate sometimes does brute force type retrieves but the blog was on bags and idbags. All these random inserts and deletes are killing performance. And as more elements are added to the FeatureAccess table these random inserts/deletes increase proportionally.
Is there something wrong with my mapping files? Should I use a different collection type?
Do you have any advice. I will be more than happy to give you more detail if you need it.