[jboss-user] [EJB 3.0] - Entity Inheritence Bug?
kotlusa
do-not-reply at jboss.com
Fri Jan 18 16:25:26 EST 2008
I'm on a project that has a base class entity, HistoricalEntity, that is extended when we want an entity that keeps track of its changes. This entity is extended by several other, sometimes unrelated, entities.
Although everything works, Hibernate generates some extremely long sql queries when it doesn't need to. As a result, performance is really poor. From what I can tell, when accessing a getter to another entity, the sql joins all talbes related to all of HistoricalEntity's subclasses rather than just the one we are working with.
Entities:
UserProfile: a user's profile, has lots of information about the user, we'll only discuss the important parts of it for now
OtherExperience: denotes a generic type of experience (like non-professional experience for a resume)
UserProfile has an @OneToMany relationship with OtherExperience
When OtherExperience does not extend HistoricalEntity, calling UserProfile.getOtherExeperiences() generates the following SQL (as expected):
select
| otherexper0_.USER_PROFILE_id as USER1_1_,
| otherexper0_.otherExperiences_id as otherExp2_1_,
| otherexper1_.id as id1185_0_,
| otherexper1_.jobTitle as jobTitle1185_0_,
| otherexper1_.startDate as startDate1185_0_,
| otherexper1_.endDate as endDate1185_0_,
| otherexper1_.description as descript5_1185_0_
| from
| USER_PROFILE_OTHER_EXPERIENCE otherexper0_
| left outer join OTHER_EXPERIENCE otherexper1_ on otherexper0_.otherExperiences_id=otherexper1_.id
| where
| otherexper0_.USER_PROFILE_id=?
|
If we make OtherExperience extend HistoricalEntity and we get the list of other experiences via EJBQL
EJBQL:
select oe from UserProfile up, IN(up.otherExperiences) oe where up.id=?1
We get the following sql (as expected):
select
| otherexper2_.id as id1059_,
| otherexper2_1_.created_id as created2_1059_,
| otherexper2_1_.lastUpdated_id as lastUpda3_1059_,
| otherexper2_.jobTitle as jobTitle1072_,
| otherexper2_.startDate as startDate1072_,
| otherexper2_.endDate as endDate1072_,
| otherexper2_.description as descript5_1072_
| from
| USER_PROFILE userprofil0_
| inner join USER_PROFILE_OTHER_EXPERIENCE otherexper1_ on userprofil0_.id=otherexper1_.USER_PROFILE_id
| inner join OTHER_EXPERIENCE otherexper2_ on otherexper1_.otherExperiences_id=otherexper2_.id
| inner join HistoricalEntity otherexper2_1_ on otherexper2_.id=otherexper2_1_.id
| where
| userprofil0_.id=?
|
Now, if OtherExperience still extends HistoricalEntity and we get the experience the normal with UserProfile.getOtherExeperiences(), the following sql is generated (definitely not expected):
select
| otherexper0_.USER_PROFILE_id as USER1_24_,
| otherexper0_.otherExperiences_id as otherExp2_24_,
| otherexper1_.id as id483_0_,
| otherexper1_1_.created_id as created2_483_0_,
| otherexper1_1_.lastUpdated_id as lastUpda3_483_0_,
| otherexper1_.jobTitle as jobTitle496_0_,
| otherexper1_.startDate as startDate496_0_,
| otherexper1_.endDate as endDate496_0_,
| otherexper1_.description as descript5_496_0_,
| modificati2_.id as id486_1_,
| modificati2_.time as time486_1_,
| modificati2_.user_id as user5_486_1_,
| modificati2_.entity_id as entity6_486_1_,
| modificati2_.description as descript3_486_1_,
| modificati2_.type as type486_1_,
| user3_.id as id504_2_,
| user3_.userId as userId504_2_,
| user3_.ENGLINK_ID as ENGLINK3_504_2_,
| user3_.CEFMS_ID as CEFMS4_504_2_,
| user3_.password as password504_2_,
| user3_.enabled as enabled504_2_,
| user3_.ID_SUFFIX as ID12_504_2_,
| user3_.ID_DESIGNATION as ID11_504_2_,
| user3_.firstName as firstName504_2_,
| user3_.middleName as middleName504_2_,
| user3_.lastName as lastName504_2_,
| user3_.primaryEmail as primary10_504_2_,
| historical4_.id as id483_3_,
| historical4_.created_id as created2_483_3_,
| historical4_.lastUpdated_id as lastUpda3_483_3_,
| historical4_1_.name as name484_3_,
| historical4_1_.description as descript3_484_3_,
| historical4_1_.active as active484_3_,
| historical4_1_.adminCategory_id as adminCat5_484_3_,
| historical4_2_.positionTitle as position2_485_3_,
| historical4_2_.organizationName as organiza3_485_3_,
| historical4_2_.skills_id as skills6_485_3_,
| historical4_2_.duration_id as duration5_485_3_,
| historical4_2_.notes as notes485_3_,
| historical4_3_.name as name487_3_,
| historical4_3_.street as street487_3_,
| historical4_3_.city as city487_3_,
| historical4_3_.state as state487_3_,
| historical4_3_.zip as zip487_3_,
| historical4_3_.longitude as longitude487_3_,
| historical4_3_.latitude as latitude487_3_,
| historical4_3_.parentOrg_id as parentOrg10_487_3_,
| historical4_3_.description as descript9_487_3_,
| historical4_4_.CERTIFICATION_DATE as CERTIFIC2_491_3_,
| historical4_4_.ID_LEVEL as ID4_491_3_,
| historical4_4_.ID_TEAM_TYPE as ID5_491_3_,
| historical4_4_.EXPIRATION_DATE as EXPIRATION3_491_3_,
| historical4_5_.taskerId as taskerId493_3_,
| historical4_5_.event as event493_3_,
| historical4_5_.mission as mission493_3_,
| historical4_5_.dutyDescription as dutyDesc5_493_3_,
| historical4_5_.startDate as startDate493_3_,
| historical4_5_.endDateas endDate493_3_,
| historical4_6_.IDENTIFICATION_NUM as IDENTIFI2_494_3_,
| historical4_6_.ID_DOCUMENT_TYPE as ID12_494_3_,
| historical4_6_.ID_SUB_TYPE as ID10_494_3_,
| historical4_6_.ID_PASSPORT_TYPE as ID11_494_3_,
| historical4_6_.licenseClass as licenseC3_494_3_,
| historical4_6_.licenseEndorsement as licenseE4_494_3_,
| historical4_6_.licenseSubType as licenseS5_494_3_,
| historical4_6_.ID_LICENSE_STATE as ID13_494_3_,
| historical4_6_.ID_IDENTIFICATION_COUNTRY as ID8_494_3_,
| historical4_6_.ID_IDENTIFICATION_STATE as ID9_494_3_,
| historical4_6_.expirationDate as expirati6_494_3_,
| historical4_6_.notes as notes494_3_,
| historical4_7_.LANGUAGE_NAME as LANGUAGE3_495_3_,
| historical4_7_.ID_LEVEL as ID4_495_3_,
| historical4_7_.notes as notes495_3_,
| historical4_8_.jobTitle as jobTitle496_3_,
| historical4_8_.startDate as startDate496_3_,
| historical4_8_.endDate as endDate496_3_,
| historical4_8_.description as descript5_496_3_,
| historical4_9_.positionTitle as position2_497_3_,
| historical4_9_.organization as organiza3_497_3_,
| historical4_9_.skills as skills497_3_,
| historical4_9_.ID_DURATION as ID6_497_3_,
| historical4_9_.notes as notes497_3_,
| historical4_10_.ID_TRAINING_TYPE as ID9_498_3_,
| historical4_10_.ID_DEGREE_TYPE as ID10_498_3_,
| historical4_10_.courseTitle as courseTi2_498_3_,
| historical4_10_.courseNumber as courseNu3_498_3_,
| historical4_10_.dateStarted as dateStar4_498_3_,
| historical4_10_.dateCompleted as dateComp5_498_3_,
| historical4_10_.expirationDate as expirati6_498_3_,
| historical4_10_.institution as institut7_498_3_,
| historical4_10_.location as location498_3_,
| historical4_11_.courseTitleas courseTi2_499_3_,
| historical4_11_.source as source499_3_,
| historical4_11_.status as status499_3_,
| historical4_11_.dateStarted as dateStar5_499_3_,
| historical4_11_.dateCompleted as dateComp6_499_3_,
| historical4_11_.expirationDate as expirati7_499_3_,
| historical4_11_.COURSE_HOURS as COURSE8_499_3_,
| historical4_12_.supervisor as supervisor501_3_,
| historical4_12_.emergencyManager as emergenc3_501_3_,
| historical4_12_.volunteerStatus as voluntee4_501_3_,
| historical4_12_.prtOpportunitiesStatus as prtOppor5_501_3_,
| historical4_12_.locations as locations501_3_,
| historical4_12_.eventTypes as eventTypes501_3_,
| historical4_12_.ID_DURATION as ID9_501_3_,
| historical4_12_.additionalInfo as addition8_501_3_,
| historical4_13_.name as name502_3_,
| historical4_13_.description as descript3_502_3_,
| case whenhistorical4_1_.id is not null then 1 when historical4_2_.id is not null then 2 when historical4_3_.id is not null then 3 when historical4_4_.id is not null then 4 when historical4_5_.id is not null then 5 when historical4_6_.id is not null then 6 when historical4_7_.id is not null then 7 when historical4_8_.id is not null then 8 when historical4_9_.id is not null then 9 when historical4_10_.id is not null then 10 when historical4_11_.id is not null then 11 when historical4_12_.id is not null then 12 when historical4_13_.id is not null then 13 when historical4_.id is not null then 0 end as clazz_3_,
| modificati5_.id as id486_4_,
| modificati5_.time as time486_4_,
| modificati5_.user_id as user5_486_4_,
| modificati5_.entity_id as entity6_486_4_,
| modificati5_.description as descript3_486_4_,
| modificati5_.type as type486_4_,
| admincateg6_.id as id481_5_,
| admincateg6_.name as name481_5_,
| admincateg6_.description as descript3_481_5_,
| admincateg6_.adminModule_id as adminMod4_481_5_,
| adminmodul7_.id as id482_6_,
| adminmodul7_.name as name482_6_,
| adminmodul7_.description as descript3_482_6_,
| adminvalue8_.id as id483_7_,
| adminvalue8_1_.created_id as created2_483_7_,
| adminvalue8_1_.lastUpdated_id as lastUpda3_483_7_,
| adminvalue8_.name as name484_7_,
| adminvalue8_.description as descript3_484_7_,
| adminvalue8_.active as active484_7_,
| adminvalue8_.adminCategory_id as adminCat5_484_7_,
| adminvalue9_.id as id483_8_,
| adminvalue9_1_.created_id as created2_483_8_,
| adminvalue9_1_.lastUpdated_id as lastUpda3_483_8_,
| adminvalue9_.name as name484_8_,
| adminvalue9_.description as descript3_484_8_,
| adminvalue9_.active as active484_8_,
| adminvalue9_.adminCategory_id as adminCat5_484_8_,
| organizati10_.id as id483_9_,
| organizati10_1_.created_id as created2_483_9_,
| organizati10_1_.lastUpdated_id as lastUpda3_483_9_,
| organizati10_.name as name487_9_,
| organizati10_.street as street487_9_,
| organizati10_.city as city487_9_,
| organizati10_.state as state487_9_,
| organizati10_.zip as zip487_9_,
| organizati10_.longitude as longitude487_9_,
| organizati10_.latitude as latitude487_9_,
| organizati10_.parentOrg_id as parentOrg10_487_9_,
| organizati10_.description as descript9_487_9_,
| adminvalue11_.id as id483_10_,
| adminvalue11_1_.created_id as created2_483_10_,
| adminvalue11_1_.lastUpdated_id as lastUpda3_483_10_,
| adminvalue11_.name as name484_10_,
| adminvalue11_.description as descript3_484_10_,
| adminvalue11_.activeas active484_10_,
| adminvalue11_.adminCategory_id as adminCat5_484_10_,
| adminvalue12_.id as id483_11_,
| adminvalue12_1_.created_id as created2_483_11_,
| adminvalue12_1_.lastUpdated_id as lastUpda3_483_11_,
| adminvalue12_.name as name484_11_,
| adminvalue12_.description as descript3_484_11_,
| adminvalue12_.active as active484_11_,
| adminvalue12_.adminCategory_id as adminCat5_484_11_,
| adminvalue13_.id as id483_12_,
| adminvalue13_1_.created_id as created2_483_12_,
| adminvalue13_1_.lastUpdated_id as lastUpda3_483_12_,
| adminvalue13_.name as name484_12_,
| adminvalue13_.description as descript3_484_12_,
| adminvalue13_.active as active484_12_,
| adminvalue13_.adminCategory_id as adminCat5_484_12_,
| adminvalue14_.id as id483_13_,
| adminvalue14_1_.created_id as created2_483_13_,
| adminvalue14_1_.lastUpdated_id as lastUpda3_483_13_,
| adminvalue14_.name as name484_13_,
| adminvalue14_.description as descript3_484_13_,
| adminvalue14_.active as active484_13_,
| adminvalue14_.adminCategory_id as adminCat5_484_13_,
| adminvalue15_.id as id483_14_,
| adminvalue15_1_.created_id as created2_483_14_,
| adminvalue15_1_.lastUpdated_id as lastUpda3_483_14_,
| adminvalue15_.name as name484_14_,
| adminvalue15_.description as descript3_484_14_,
| adminvalue15_.active as active484_14_,
| adminvalue15_.adminCategory_id as adminCat5_484_14_,
| adminvalue16_.id as id483_15_,
| adminvalue16_1_.created_id as created2_483_15_,
| adminvalue16_1_.lastUpdated_id as lastUpda3_483_15_,
| adminvalue16_.name as name484_15_,
| adminvalue16_.description as descript3_484_15_,
| adminvalue16_.active as active484_15_,
| adminvalue16_.adminCategory_id as adminCat5_484_15_,
| adminvalue17_.id as id483_16_,
| adminvalue17_1_.created_id as created2_483_16_,
| adminvalue17_1_.lastUpdated_id as lastUpda3_483_16_,
| adminvalue17_.name as name484_16_,
| adminvalue17_.description as descript3_484_16_,
| adminvalue17_.active as active484_16_,
| adminvalue17_.adminCategory_id as adminCat5_484_16_,
| adminvalue18_.id as id483_17_,
| adminvalue18_1_.created_id as created2_483_17_,
| adminvalue18_1_.lastUpdated_id as lastUpda3_483_17_,
| adminvalue18_.name as name484_17_,
| adminvalue18_.description as descript3_484_17_,
| adminvalue18_.active as active484_17_,
| adminvalue18_.adminCategory_id as adminCat5_484_17_,
| adminvalue19_.id as id483_18_,
| adminvalue19_1_.created_id as created2_483_18_,
| adminvalue19_1_.lastUpdated_id as lastUpda3_483_18_,
| adminvalue19_.name as name484_18_,
| adminvalue19_.description as descript3_484_18_,
| adminvalue19_.active as active484_18_,
| adminvalue19_.adminCategory_id as adminCat5_484_18_,
| adminvalue20_.id as id483_19_,
| adminvalue20_1_.created_id as created2_483_19_,
| adminvalue20_1_.lastUpdated_id as lastUpda3_483_19_,
| adminvalue20_.name as name484_19_,
| adminvalue20_.description as descript3_484_19_,
| adminvalue20_.active as active484_19_,
| adminvalue20_.adminCategory_id as adminCat5_484_19_,
| adminvalue21_.id as id483_20_,
| adminvalue21_1_.created_id as created2_483_20_,
| adminvalue21_1_.lastUpdated_id as lastUpda3_483_20_,
| adminvalue21_.name as name484_20_,
| adminvalue21_.description as descript3_484_20_,
| adminvalue21_.active as active484_20_,
| adminvalue21_.adminCategory_id as adminCat5_484_20_,
| adminvalue22_.id as id483_21_,
| adminvalue22_1_.created_id as created2_483_21_,
| adminvalue22_1_.lastUpdated_id as lastUpda3_483_21_,
| adminvalue22_.name as name484_21_,
| adminvalue22_.description as descript3_484_21_,
| adminvalue22_.active as active484_21_,
| adminvalue22_.adminCategory_id as adminCat5_484_21_,
| adminvalue23_.id as id483_22_,
| adminvalue23_1_.created_id as created2_483_22_,
| adminvalue23_1_.lastUpdated_id as lastUpda3_483_22_,
| adminvalue23_.name as name484_22_,
| adminvalue23_.description as descript3_484_22_,
| adminvalue23_.active as active484_22_,
| adminvalue23_.adminCategory_id as adminCat5_484_22_,
| adminvalue24_.id as id483_23_,
| adminvalue24_1_.created_id as created2_483_23_,
| adminvalue24_1_.lastUpdated_id as lastUpda3_483_23_,
| adminvalue24_.name as name484_23_,
| adminvalue24_.description as descript3_484_23_,
| adminvalue24_.active as active484_23_,
| adminvalue24_.adminCategory_id as adminCat5_484_23_
| from
| USER_PROFILE_OTHER_EXPERIENCE otherexper0_
| left outer join OTHER_EXPERIENCE otherexper1_ on otherexper0_.otherExperiences_id=otherexper1_.id
| left outer join HistoricalEntity otherexper1_1_ on otherexper1_.id=otherexper1_1_.id
| left outer join Modification modificati2_ on otherexper1_1_.created_id=modificati2_.id
| left outer join USERS user3_ on modificati2_.user_id=user3_.id
| left outer join HistoricalEntity historical4_ on modificati2_.entity_id=historical4_.id
| left outer join ADMIN_VALUE historical4_1_ on historical4_.id=historical4_1_.id
| left outer join SKILL historical4_2_ on historical4_.id=historical4_2_.id
| left outer join ORGANIZATIONS historical4_3_ on historical4_.id=historical4_3_.id
| left outer join CERTIFICATION historical4_4_ on historical4_.id=historical4_4_.id
| left outer join EXPERIENCE historical4_5_ on historical4_.id=historical4_5_.id
| left outer join IDENTIFICATION historical4_6_ on historical4_.id=historical4_6_.id
| left outer join LANGUAGES historical4_7_ on historical4_.id=historical4_7_.id
| left outer join OTHER_EXPERIENCE historical4_8_ on historical4_.id=historical4_8_.id
| left outer join OTHER_SKILL historical4_9_ on historical4_.id=historical4_9_.id
| left outer join OTHER_TRAINING historical4_10_ on historical4_.id=historical4_10_.id
| left outer join TRAINING historical4_11_ on historical4_.id=historical4_11_.id
| left outer join VOLUNTEER_DETAILS historical4_12_ on historical4_.id=historical4_12_.id
| left outer join PRIVGROUPS historical4_13_ on historical4_.id=historical4_13_.id
| left outer join Modification modificati5_ on historical4_.lastUpdated_id=modificati5_.id
| left outer join ADMIN_CATEGORY admincateg6_ on historical4_1_.adminCategory_id=admincateg6_.id
| left outer join ADMIN_MODULE adminmodul7_ on admincateg6_.adminModule_id=adminmodul7_.id
| left outer join ADMIN_VALUE adminvalue8_ on historical4_2_.skills_id=adminvalue8_.id
| left outer join HistoricalEntity adminvalue8_1_ on adminvalue8_.id=adminvalue8_1_.id
| left outer join ADMIN_VALUE adminvalue9_ on historical4_2_.duration_id=adminvalue9_.id
| left outer join HistoricalEntity adminvalue9_1_ on adminvalue9_.id=adminvalue9_1_.id
| left outer join ORGANIZATIONS organizati10_ on historical4_3_.parentOrg_id=organizati10_.id
| left outer join HistoricalEntity organizati10_1_ on organizati10_.id=organizati10_1_.id
| left outer join ADMIN_VALUE adminvalue11_ on historical4_4_.ID_LEVEL=adminvalue11_.id
| left outer join HistoricalEntity adminvalue11_1_ on adminvalue11_.id=adminvalue11_1_.id
| left outer join ADMIN_VALUE adminvalue12_ on historical4_4_.ID_TEAM_TYPE=adminvalue12_.id
| left outer join HistoricalEntity adminvalue12_1_ on adminvalue12_.id=adminvalue12_1_.id
| left outer join ADMIN_VALUE adminvalue13_ on historical4_6_.ID_DOCUMENT_TYPE=adminvalue13_.id
| left outer join HistoricalEntity adminvalue13_1_ on adminvalue13_.id=adminvalue13_1_.id
| left outer join ADMIN_VALUE adminvalue14_ on historical4_6_.ID_SUB_TYPE=adminvalue14_.id
| left outer join HistoricalEntity adminvalue14_1_ on adminvalue14_.id=adminvalue14_1_.id
| left outer join ADMIN_VALUE adminvalue15_ on historical4_6_.ID_PASSPORT_TYPE=adminvalue15_.id
| left outer join HistoricalEntity adminvalue15_1_ on adminvalue15_.id=adminvalue15_1_.id
| left outer join ADMIN_VALUE adminvalue16_ on historical4_6_.ID_LICENSE_STATE=adminvalue16_.id
| left outer join HistoricalEntity adminvalue16_1_ on adminvalue16_.id=adminvalue16_1_.id
| left outer join ADMIN_VALUE adminvalue17_ on historical4_6_.ID_IDENTIFICATION_COUNTRY=adminvalue17_.id
| left outer join HistoricalEntity adminvalue17_1_ on adminvalue17_.id=adminvalue17_1_.id
| left outer join ADMIN_VALUE adminvalue18_ on historical4_6_.ID_IDENTIFICATION_STATE=adminvalue18_.id
| left outer join HistoricalEntity adminvalue18_1_ on adminvalue18_.id=adminvalue18_1_.id
| left outer join ADMIN_VALUE adminvalue19_ on historical4_7_.LANGUAGE_NAME=adminvalue19_.id
| left outer join HistoricalEntity adminvalue19_1_ on adminvalue19_.id=adminvalue19_1_.id
| left outer join ADMIN_VALUE adminvalue20_ on historical4_7_.ID_LEVEL=adminvalue20_.id
| left outer join HistoricalEntity adminvalue20_1_ on adminvalue20_.id=adminvalue20_1_.id
| left outer join ADMIN_VALUE adminvalue21_ on historical4_9_.ID_DURATION=adminvalue21_.id
| left outer join HistoricalEntity adminvalue21_1_ on adminvalue21_.id=adminvalue21_1_.id
| left outer join ADMIN_VALUE adminvalue22_ on historical4_10_.ID_TRAINING_TYPE=adminvalue22_.id
| left outer join HistoricalEntity adminvalue22_1_ on adminvalue22_.id=adminvalue22_1_.id
| left outer join ADMIN_VALUE adminvalue23_ on historical4_10_.ID_DEGREE_TYPE=adminvalue23_.id
| left outer join HistoricalEntity adminvalue23_1_ on adminvalue23_.id=adminvalue23_1_.id
| left outer join ADMIN_VALUE adminvalue24_ on historical4_12_.ID_DURATION=adminvalue24_.id
| left outer join HistoricalEntity adminvalue24_1_ on adminvalue24_.id=adminvalue24_1_.id
| where
| otherexper0_.USER_PROFILE_id=?
|
Should I create a bug report in JIRA or am I missing something?
Your help is much appreciated.
Thank you,
Austin
View the original post : http://www.jboss.com/index.html?module=bb&op=viewtopic&p=4121435#4121435
Reply to the post : http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&p=4121435
More information about the jboss-user
mailing list