Hello,

I have a question about composite id. I used middlegen to generate the hbm and java file.

I have three tables with below relationship:

profile_skill: profile_skill_id_no, skill_skill_id_no (both primary keys)
skill: skill_id_no (prim key)
lob_skill: lob_lob_id_no, skill_skill_id_no (both prim keys)

profile_skill.skill_skill_id_no references to skill.skill_id_no
lob_skill.skill_skill_id_no references to skill.skill_id_no

profile_skill: has composite id as lob_profile_id_no and skill_skill_id_no
lob_skill : has composite id as lob_lob_id_no and skill_skill_id_no

I need to write a query to get row value back from profile_skill and here is my query:

from ProfileSkillEntity as profileSkill
where profileSkill.comp_id.lobProfileIdNo = ? and profileSkill.comp_id.skillSkillIdNo = ?

with values passed in as 10090 nd 1365.

I get below error:

org.springframework.orm.hibernate.HibernateSystemException: More than one row with the given identifier was found: 1365, for class: com.rhi.domain.common.data.hibernate.web.LobSkillEntity; nested exception is net.sf.hibernate.HibernateException: More than one row with the given identifier was found: 1365, for class: com.rhi.domain.common.data.hibernate.web.LobSkillEntity
net.sf.hibernate.HibernateException: More than one row with the given identifier was found: 1365, for class: com.rhi.domain.common.data.hibernate.web.LobSkillEntity

When I run a simple sql query, there is only 1 row returned.

I spent all day trying to resolve the problem and couldn't. Could anyone please kindly help? below is the hbm file of the profile_skill table:

<class name="com.hibernate.web.ProfileSkillEntity" table="PROFILE_SKILL" mutable="true" polymorphism="implicit" dynamic-update="false" dynamic-insert="false" batch-size="1" select-before-update="false" optimistic-lock="version">
<meta attribute="class-description" inherit="false">@hibernate.class table="PROFILE_SKILL"</meta>
- <composite-id name="comp_id" class="com.hibernate.web.ProfileSkillEntityPK" unsaved-value="none">
<meta attribute="field-description" inherit="false">@hibernate.id generator-class="assigned"</meta>
- <key-property name="lobProfileIdNo" column="LOB_PROFILE_ID_NO" type="java.lang.Integer" length="10">
<meta attribute="field-description" inherit="true">@hibernate.property column="LOB_PROFILE_ID_NO" length="10"</meta>
</key-property>
- <key-property name="skillSkillIdNo" column="SKILL_SKILL_ID_NO" type="java.lang.Short" length="5">
<meta attribute="field-description" inherit="true">@hibernate.property column="SKILL_SKILL_ID_NO" length="5"</meta>
</key-property>
</composite-id>
- <property name="createDt" type="java.sql.Timestamp" column="CREATE_DT" not-null="true" length="26" unique="false" update="true" insert="true">
<meta attribute="field-description" inherit="true">@hibernate.property column="CREATE_DT" length="26" not-null="true"</meta>
</property>
- <property name="skillLevel" type="java.lang.String" column="SKILL_LEVEL" length="20" not-null="false" unique="false" update="true" insert="true">
<meta attribute="field-description" inherit="true">@hibernate.property column="SKILL_LEVEL" length="20"</meta>
</property>
- <property name="updateDt" type="java.sql.Timestamp" column="UPDATE_DT" length="26" not-null="false" unique="false" update="true" insert="true">
<meta attribute="field-description" inherit="true">@hibernate.property column="UPDATE_DT" length="26"</meta>
</property>
- <!-- Associations
-->
- <!-- derived association(s) for compound key
-->
- <!-- bi-directional one-to-one association to SkillEntity
-->
- <many-to-one name="skill" class="com.rhi.domain.common.data.hibernate.web.SkillEntity" update="false" insert="false" not-null="false" unique="false" outer-join="auto">
<meta attribute="field-description" inherit="true">@hibernate.many-to-one update="false" insert="false"</meta>
<meta attribute="field-description" inherit="true">@hibernate.column name="SKILL_SKILL_ID_NO"</meta>
<meta attribute="field-description" inherit="true">@hibernate.column name="SKILL_SKILL_ID_NO"</meta>
<column name="SKILL_SKILL_ID_NO" />
<column name="SKILL_SKILL_ID_NO" />
</many-to-one>
- <!-- end of derived association(s)
-->
</class>

Thank you very much.

Best regards,
Sahra

Hard to say - need more information.

How are you executing the HQL querty? Show the code.
What SQL is being generated (and have you tested that SQL).

Here is the hql query generated:

Hibernate: select skillentit0_.SKILL_ID_NO as SKILL_ID1_2_, skillentit0_.SKILL_NAME as SKILL_NAME2_, skillentit0_.REF_CODE as REF_CODE2_, lobskillen1_.LOB_LOB_ID_NO as LOB_LOB_1_0_, lobskillen1_.SKILL_SKILL_ID_NO as SKILL_SK2_0_, lobskillen1_.SKILL_TYPE as SKILL_TYPE0_, profileski2_.LOB_PROFILE_ID_NO as LOB_PROF1_1_, profileski2_.SKILL_SKILL_ID_NO as SKILL_SK2_1_, profileski2_.CREATE_DT as CREATE_DT1_, profileski2_.SKILL_LEVEL as SKILL_LE4_1_, profileski2_.UPDATE_DT as UPDATE_DT1_ from WEB.SKILL skillentit0_ left outer join WEB.LOB_SKILL lobskillen1_ on skillentit0_.SKILL_ID_NO=lobskillen1_.SKILL_SKILL_ID_NO left outer join WEB.PROFILE_SKILL profileski2_ on skillentit0_.SKILL_ID_NO=profileski2_.SKILL_SKILL_ID_NO where skillentit0_.SKILL_ID_NO=?


I was search by comp_id, which takes in 2 param but the query only takes 1 param .. Is this a bug in hibernate? is there a work around for this?

Thanks in advance for your help.

Sahra

sorry. I didn't post the whole thing .. here it is:


Hibernate: select profileski0_.LOB_PROFILE_ID_NO as LOB_PROF1_, profileski0_.SKILL_SKILL_ID_NO as SKILL_SK2_, profileski0_.CREATE_DT as CREATE_DT, profileski0_.SKILL_LEVEL as SKILL_LE4_, profileski0_.UPDATE_DT as UPDATE_DT from WEB.PROFILE_SKILL profileski0_ where (profileski0_.LOB_PROFILE_ID_NO=? )and(profileski0_.SKILL_SKILL_ID_NO=? )

Hibernate: select skillentit0_.SKILL_ID_NO as SKILL_ID1_2_, skillentit0_.SKILL_NAME as SKILL_NAME2_, skillentit0_.REF_CODE as REF_CODE2_, lobskillen1_.LOB_LOB_ID_NO as LOB_LOB_1_0_, lobskillen1_.SKILL_SKILL_ID_NO as SKILL_SK2_0_, lobskillen1_.SKILL_TYPE as SKILL_TYPE0_, profileski2_.LOB_PROFILE_ID_NO as LOB_PROF1_1_, profileski2_.SKILL_SKILL_ID_NO as SKILL_SK2_1_, profileski2_.CREATE_DT as CREATE_DT1_, profileski2_.SKILL_LEVEL as SKILL_LE4_1_, profileski2_.UPDATE_DT as UPDATE_DT1_ from WEB.SKILL skillentit0_ left outer join WEB.LOB_SKILL lobskillen1_ on skillentit0_.SKILL_ID_NO=lobskillen1_.SKILL_SKILL_ID_NO left outer join WEB.PROFILE_SKILL profileski2_ on skillentit0_.SKILL_ID_NO=profileski2_.SKILL_SKILL_ID_NO where skillentit0_.SKILL_ID_NO=?

Hibernate: select lobskillen0_.LOB_LOB_ID_NO as LOB_LOB_1_2_, lobskillen0_.SKILL_SKILL_ID_NO as SKILL_SK2_2_, lobskillen0_.SKILL_TYPE as SKILL_TYPE2_, skillentit1_.SKILL_ID_NO as SKILL_ID1_0_, skillentit1_.SKILL_NAME as SKILL_NAME0_, skillentit1_.REF_CODE as REF_CODE0_, profileski2_.LOB_PROFILE_ID_NO as LOB_PROF1_1_, profileski2_.SKILL_SKILL_ID_NO as SKILL_SK2_1_, profileski2_.CREATE_DT as CREATE_DT1_, profileski2_.SKILL_LEVEL as SKILL_LE4_1_, profileski2_.UPDATE_DT as UPDATE_DT1_ from WEB.LOB_SKILL lobskillen0_ left outer join WEB.SKILL skillentit1_ on lobskillen0_.SKILL_SKILL_ID_NO=skillentit1_.SKILL_ID_NO left outer join WEB.PROFILE_SKILL profileski2_ on skillentit1_.SKILL_ID_NO=profileski2_.SKILL_SKILL_ID_NO where lobskillen0_.SKILL_SKILL_ID_NO=?

Can you show "the simple query that returned one row" and the HQL query you are using. Have you tried a simple HQL query ?