one-to-many with FK in composite-Id

tzman · **Joined:** Fri Jan 16, 2004 11:05 am **Posts:** 33

I have a situation with 2 tables where I want to retrieve a collection from a table that holds a FK as part of its PK.

The tables look something like the following.

SecAccs
unqe_key - PK

( other columns .. )

SecRcntPswd
accs_unqe_key - PK,FK
last_authtmst - PK
pswd - PK

( other columns .. )

Because SecRcntPswd has multiple PK columns it is broken out into a PK class.

From an application stand point I have no need for a bi-directional relationship between these 2 tables, I only need to retrieve a collection of recent passwords through the SecAccs object.

I have been trying a few different ways of mapping this relationship, all of which run, none of which return the expected results.

For the mappings I have the following.

In SecAccs:

Code:

<set
    name="rcntPswds"
    lazy="true"
    inverse="true"
    cascade="none"
    sort="unsorted"
>
      <key
          column="ACCS_UNQE_KEY"
      />
      <one-to-many
          class="com.sp.dao.sec.SecRcntPswdDao"
      />
</set>

In SecRcntPswd:

Code:

<hibernate-mapping>
    <class
        name="com.sp.dao.sec.SecRcntPswdDao"
        table="SPSECRCNTPSWD"
        dynamic-update="false"
        dynamic-insert="false"
    >
        <composite-id
            name="pk"
            class="com.sp.dao.sec.SecRcntPswdDaoPk"
        >
                     <key-property
                        name="accsUnqeKey"
                        type="com.sp.util.SpUnqeKeyType"
                        column="ACCS_UNQE_KEY"
                />

                     <key-property
                        name="lastAuthTmst"
                        type="timestamp"
                        column="LAST_AUTH_TMST"
                />

                     <key-property
                        name="pswd"
                        type="string"
                        column="PSWD"
                        length="16"
                />
        </composite-id>
    </class>
</hibernate-mapping>

I have a test client which tests this mapping in the following manner:

Code:

            crit = sesn.createCriteria( SecRcntPswdDao.class );
            crit.add( Expression.eq( "pk.accsUnqeKey", new SpUnqeKey( 342 ) ) );

            try
            {
                pswds = crit.list();
                System.out.println( "AccsUnqeKey:" + ( ( SecRcntPswdDao )    pswds.get( 0 ) ).getPk().getAccsUnqeKey() );
                System.out.println( "Pswd count:" + pswds.size() );
            }
            catch (HibernateException e1)
            {
                e1.printStackTrace();
            }

            accs = SecSvcFactory.findSecAccsByUserId( "tzman", sesn );
            System.out.println( "UnqeKey:" + accs.getUnqeKey() );
            System.out.println( "UserId:" + accs.getUserId() );
            System.out.println( "Password:" + accs.getPswd() );
            System.out.println( "UserRole:" + accs.getUserRoleDao().getCatg() );
            rcntPswds = accs.getRcntPswds();
            System.out.println( "RcntPswd count:" + rcntPswds.size() );

When run it outputs the following:

Doing a query directly from the Dao, returns the correct number of rows and the correct unique key.

Code:

Testing Sec Factory methods.
Hibernate: select this.ACCS_UNQE_KEY as ACCS_UNQ1_0_, this.LAST_AUTH_TMST as LAST_AUT2_0_, this.PSWD as PSWD0_ from Sp.SPSECRCNTPSWD this where this.ACCS_UNQE_KEY=?
AccsUnqeKey:[SpUnqeKey: keyGrp: com.sp.util.unqekey.SpUnqeKeyGrp@d67067 key: 342 valid: false]
Pswd count:2

The SecAccs lookup:

Code:

Jan 16, 2004 9:17:41 AM com.sp.svc.sec.SecSvcFactory findSecAccsByUserId
INFO: Looking up SecAccsDao by user_id: tzman

Hibernate: select secaccsdao0_.UNQE_KEY as UNQE_KEY, secaccsdao0_.ADD_ACCS as ADD_ACCS, secaccsdao0_.CNSC_INV_AUTH as CNSC_INV3_, secaccsdao0_.DEL_ACCS as DEL_ACCS, secaccsdao0_.INIT_MENU_NAME as INIT_MEN5_, secaccsdao0_.LAST_MDFD_TMST as LAST_MDF6_, secaccsdao0_.LAST_MDFD_USER as LAST_MDF7_, secaccsdao0_.LOGN_STAT as LOGN_STAT, secaccsdao0_.PSWD as PSWD, secaccsdao0_.PSWD_EXPN_TMST as PSWD_EX10_, secaccsdao0_.READ_ACCS as READ_ACCS, secaccsdao0_.SEC_LEVL as SEC_LEVL, secaccsdao0_.TEMP as TEMP, secaccsdao0_.UPDT_ACCS as UPDT_ACCS, secaccsdao0_.USER_ID as USER_ID, secaccsdao0_.USER_ROLE_UNQE_KEY as USER_RO16_ from Sp.SPSECACCS secaccsdao0_ where (secaccsdao0_.USER_ID=? )

Hibernate: select secuserrol0_.UNQE_KEY as UNQE_KEY1_, secuserrol0_.CATG as CATG1_, secuserrol0_.USER_UNQE_KEY as USER_UNQ3_1_, secuserdao1_.UNQE_KEY as UNQE_KEY0_, secuserdao1_.DOB as DOB0_, secuserdao1_.EMAIL_ADDR as EMAIL_ADDR0_, secuserdao1_.FIR_NAME as FIR_NAME0_, secuserdao1_.GNDR as GNDR0_, secuserdao1_.HEAR_IMPD as HEAR_IMPD0_, secuserdao1_.LAST_NAME as LAST_NAME0_, secuserdao1_.MIDL_INIT as MIDL_INIT0_, secuserdao1_.PREF_LANG as PREF_LANG0_, secuserdao1_.PSTL_CODE as PSTL_CODE0_, secuserdao1_.RACE as RACE0_, secuserdao1_.ST_ADDR_1 as ST_ADDR_10_, secuserdao1_.ST_ADDR_2 as ST_ADDR_20_, secuserdao1_.SSN as SSN0_, secuserdao1_.CITY as CITY0_, secuserdao1_.ST as ST0_ from Sp.SPSECUSERROLE secuserrol0_, Sp.SPSECUSER secuserdao1_ where secuserrol0_.UNQE_KEY=? and secuserrol0_.USER_UNQE_KEY=secuserdao1_.UNQE_KEY(+)

Hibernate: select secuserdao0_.UNQE_KEY as UNQE_KEY0_, secuserdao0_.DOB as DOB0_, secuserdao0_.EMAIL_ADDR as EMAIL_ADDR0_, secuserdao0_.FIR_NAME as FIR_NAME0_, secuserdao0_.GNDR as GNDR0_, secuserdao0_.HEAR_IMPD as HEAR_IMPD0_, secuserdao0_.LAST_NAME as LAST_NAME0_, secuserdao0_.MIDL_INIT as MIDL_INIT0_, secuserdao0_.PREF_LANG as PREF_LANG0_, secuserdao0_.PSTL_CODE as PSTL_CODE0_, secuserdao0_.RACE as RACE0_, secuserdao0_.ST_ADDR_1 as ST_ADDR_10_, secuserdao0_.ST_ADDR_2 as ST_ADDR_20_, secuserdao0_.SSN as SSN0_, secuserdao0_.CITY as CITY0_, secuserdao0_.ST as ST0_ from Sp.SPSECUSER secuserdao0_ where secuserdao0_.UNQE_KEY=?

As you can see the SecAccs unique matches that from the query directly to the SecRcntPswd Dao. So, it should return the same results from within the SecAccs Dao

Code:

UnqeKey:[SpUnqeKey: keyGrp: com.sp.util.unqekey.SpUnqeKeyGrp@d67067 key: 342 valid: false]
UserId:tzman
Password:password2*
UserRole:PART

Here is the select statements that are being generated by SecAccs trying to get the rcntPswds. It looks to me that the first select works and is correct. The subsequent selects are trying to query using the rest of SecRcntPswd's PK fields.

Code:

Hibernate: select rcntpswds0_.ACCS_UNQE_KEY as ACCS_UNQ1___, rcntpswds0_.LAST_AUTH_TMST as LAST_AUT2___, rcntpswds0_.PSWD as PSWD__, rcntpswds0_.ACCS_UNQE_KEY as ACCS_UNQ1_0_, rcntpswds0_.LAST_AUTH_TMST as LAST_AUT2_0_, rcntpswds0_.PSWD as PSWD0_ from Sp.SPSECRCNTPSWD rcntpswds0_ where rcntpswds0_.ACCS_UNQE_KEY=?

Hibernate: select secrcntpsw0_.ACCS_UNQE_KEY as ACCS_UNQ1_0_, secrcntpsw0_.LAST_AUTH_TMST as LAST_AUT2_0_, secrcntpsw0_.PSWD as PSWD0_ from Sp.SPSECRCNTPSWD secrcntpsw0_ where secrcntpsw0_.ACCS_UNQE_KEY=? and secrcntpsw0_.LAST_AUTH_TMST=? and secrcntpsw0_.PSWD=?

Hibernate: select secrcntpsw0_.ACCS_UNQE_KEY as ACCS_UNQ1_0_, secrcntpsw0_.LAST_AUTH_TMST as LAST_AUT2_0_, secrcntpsw0_.PSWD as PSWD0_ from Sp.SPSECRCNTPSWD secrcntpsw0_ where secrcntpsw0_.ACCS_UNQE_KEY=? and secrcntpsw0_.LAST_AUTH_TMST=? and secrcntpsw0_.PSWD=?

RcntPswd count:0

I have tried mappings generated by middlegen with the same result, so I am not even sure this is allowed in Hibernate 2.1.

How is this set up suggested to be done? We have this type of scenario in quite a few places.

Thanks in advance.

emmanuel · **Posted:** Fri Jan 16, 2004 1:44 pm

I read your post fast, but it sounds like inverse="true" should be inverse="false"
Check http://www.hibernate.org/Documentation/InsideExplanationOfInverseTrue

tzman · **Joined:** Fri Jan 16, 2004 11:05 am **Posts:** 33

Setting inverse="true" runs me into the problem commented on in the documentation.

Quote:

Very Important Note: If the <key> column of a <one-to-many> association is declared NOT NULL, Hibernate may cause constraint violations when it creates or updates the association. To prevent this problem, you must use a bidirectional association with the many valued end (the set or bag) marked as inverse="true".

[/quote]

tanman · **Posted:** Fri Jan 16, 2004 8:41 pm

Hello,

I''m very much a newbie have a similar issue (and similar user name) that I need help with:

REPORTS Table

Code:

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 RPT_ID                                    NOT NULL NUMBER(30)
 RPT_NAME                                           VARCHAR2(50)

REPORT_PARMS Table

Code:

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 RPT_ID                                             NUMBER(30)
 PARM_NAME                                          VARCHAR2(30)
 PARM_VALUE                                         VARCHAR2(30)

Report.hbm.xml:

Code:

<hibernate-mapping>
  <class name="Report" table="Reports">
    <id name="rptId" column="RPT_ID" length="30" unsaved-value="null">
      <generator class="increment"/>
    </id>
    <property name="name" column="RPT_NAME" not-null="true" length="50"/>
    <bag name="params" cascade="all-delete-orphan">
      <key column="RPT_ID"/>
      <one-to-many class="ReportParam"/>
    </bag>
  </class>
</hibernate-mapping>

ReportParam.hbm.xml:

Code:

<hibernate-mapping>
  <class name="ReportParam" table="Report_Parms">
    <composite-id>
      <key-property name="rptId" column="RPT_ID" length="30"/>
      <key-property name="name" column="PARM_NAME" length="30"/>
    </composite-id>
    <property name="value" column="PARM_VALUE" length="30"/>
    <many-to-one name="report" class="Report" column="RPT_ID" not-null="true" insert="false" update="false"/>
  </class>
</hibernate-mapping>

Sample Java to Add both objects:

Code:

                newReport = new Report();
                newReport.setName("Test Addition");
                Integer id = (Integer)s.save(newReport);

                ReportParam p1 = new ReportParam();
                p1.setRptId(id);
                p1.setName("Parm1");
                p1.setValue("Value1");
                newReport.addParam(p1);
                s.save(p1);

QUESTION: Is there a better way to do this so you don't have to explicitely save the parent first AND set the id from the parent? I know a fellow coworker here is doing both in the same step, but not with a composite-id.

I'd like to keep the table structure the same as this mapping on top of legacy tables.

Also, the delete on Report doesn't cascade to ReportParam correctly and I'm not sure why it does an update to null on the id first:

Code:

     [java] Hibernate: update SDLND.Report_Parms set RPT_ID=null where RPT_ID=?
     [java] 16:08:15,218 DEBUG BatcherImpl:227 - preparing statement
     [java] 16:08:15,250 DEBUG IntegerType:46 - binding '13' to parameter: 1
     [java] 16:08:15,296 DEBUG BasicCollectionPersister:507 - done deleting collection
     [java] 16:08:15,296 DEBUG BatcherImpl:199 - done closing: 0 open PreparedStatements, 0 open ResultSets
     [java] 16:08:15,296 DEBUG BatcherImpl:240 - closing statement
     [java] 16:08:15,328 DEBUG EntityPersister:553 - Deleting entity: [ReportParam#13:Parm1,Value1]
     [java] 16:08:15,328 DEBUG BatcherImpl:192 - about to open: 0 open PreparedStatements, 0 open ResultSets
     [java] 16:08:15,328 DEBUG SQL:223 - delete from SDLND.Report_Parms where RPT_ID=? and PARM_NAME=?
     [java] Hibernate: delete from SDLND.Report_Parms where RPT_ID=? and PARM_NAME=?
     [java] 16:08:15,328 DEBUG BatcherImpl:227 - preparing statement
     [java] 16:08:15,359 DEBUG IntegerType:46 - binding '13' to parameter: 1
     [java] 16:08:15,359 DEBUG StringType:46 - binding 'Parm1' to parameter: 2
     [java] 16:08:15,406 ERROR SessionImpl:2269 - Could not synchronize database state with session
     [java] net.sf.hibernate.HibernateException: SQL update or deletion failed (row not found)

Thank you in advance for any help. I think Hibernate is a really awesome tool and it's great that it's open source!

Tan

emmanuel · **Posted:** Sat Jan 17, 2004 9:22 am

tzman wrote:

Setting inverse="true" runs me into the problem commented on in the documentation.

So bi dir it

tzman · **Joined:** Fri Jan 16, 2004 11:05 am **Posts:** 33

Making this bi-directional still returns a collection of size 0. I am rather confused on how this situation is expected to be mapped.

If there is something else I could try, or further information I could give, I would be happy to do so.

Any and all help is greatly appreciated.

emmanuel · **Posted:** Sat Jan 17, 2004 2:01 pm

I suppose you bi dir it with <many-to-one insert="false" update="false">

Try to use key-many-to-one on ACCS_UNQE_KEY

tzman · **Joined:** Fri Jan 16, 2004 11:05 am **Posts:** 33

I have tried this as well, it seems each way I do it I can not get past the collection size being 0.

I am beginning to wonder if there is something else wrong. The accsUnqeKey is a custom type. Under the covers maybe one of my supplied functions is failing to return the correct value. Can you geuss ass to what I might look at? What happens under the covers to determine what to return in the collection?

Thanks again.

tzman · **Joined:** Fri Jan 16, 2004 11:05 am **Posts:** 33

If I change my accsUnqeKey type from my custom type to a BigDecimal it appears to work.

Any idea what would cause this?

Thanks again!

emmanuel · **Posted:** Mon Jan 19, 2004 2:23 am

Pack it to JIRA with all needed materials (working) and in a simplified version.

emmanuel · **Posted:** Wed Jan 21, 2004 11:13 am

Code:

<hibernate-mapping>
  <class name="ReportParam" table="Report_Parms">
    <composite-id>
      <key-many-to-one name="report" class="Report" column="RPT_ID"/>
      <key-property name="name" column="PARM_NAME" length="30"/>
    </composite-id>

and set inverse="true" in bag
That's the way tzman solved it.

Then reread the Parent/Child relationship materials (especially for the addParam method), it should work then.