Hibernate Community • View topic - Problem searching with composite keys mapped to two tables

View unanswered posts | View active topics

Board index » Projects » Tools

All times are UTC - 5 hours [ DST ]

Problem searching with composite keys mapped to two tables

Page 1 of 1

[ 4 posts ]

Previous topic | Next topic

Author

Message

ldupeval

Post subject: Problem searching with composite keys mapped to two tables

Posted: Wed Nov 17, 2004 5:43 pm

Newbie

Joined: Fri Oct 29, 2004 1:05 pm
Posts: 10

Hi,

I have a schema that look like this

Code:

create table terminal (
   terminalid   integer not null,
   constraint PK_terminal primary key (terminalid)
);

create table plantype (
   terminalid integer not null,
   plan_lid integer not null,
   plan_sid integer not null,
    constraint PK_plantype primary key (terminalid, plan_lid, plan_sid),
    constraint FK_plantype_terminal foreign key (terminalid) references terminal(terminalid)
);

create table profile(
   terminalid integer not null,
   profile_lid integer not null,
   profile_sid integer not null,
    plan_lid integer,
    plan_sid integer,
    constraint PK_profile primary key (terminalid, profile_lid, profile_sid),
    constraint FK_profile_terminal foreign key (terminalid) references terminal(terminalid),
    constraint FK_profile_plantype foreign key (terminalid, plan_lid, plan_sid) references plantype(terminalid, plan_lid, plan_sid) 
);

middlegen generates the following:

Code:

<class 
    name="data.model.Profile" 
    table="profile"
>

    <composite-id name="comp_id" class="data.model.ProfilePK">
        <key-property 
            name="terminalid" 
            column="TERMINALID" 
            type="java.lang.Integer"
            length="10"
        />
        <key-property 
            name="profileLid" 
            column="PROFILE_LID" 
            type="java.lang.Integer"
            length="10"
        />
        <key-property 
            name="profileSid" 
            column="PROFILE_SID" 
            type="java.lang.Short"
            length="5"
        />
    </composite-id>    

    <!-- Associations -->
    <!-- derived association(s) for compound key -->
    <!-- uni-directional many-to-one association to Terminal -->
    <many-to-one
        name="terminal"
       class="data.model.Terminal"
       update="false"
       insert="false"
   >
       <column name="TERMINALID" />
   </many-to-one>
   
    <!-- end of derived association(s) -->
  
    <!-- uni-directional many-to-one association to DotPlanType -->
    <many-to-one
        name="planType"
        class="data.model.planType"
    >
        <column name="TERMINALID" />
        <column name="PLAN_TYPE_SID" />
        <column name="PLAN_TYPE_LID" />
    </many-to-one>

</class>

I'm having problems getting searches to work properly. I tried:

Code:

Profile getProfile(Profile profile) {
    Example example = Example.create(profile);
    return session.createCriteria(Profile.class).add(example).list();
}

This will cause an exception if the PLAN_TYPE_LID and the PLAN_TYPE_SID columns are empty. I get an exception that looks like this:

Code:

No row with the given identifier exists: net.sf.hibernate.UnresolvableObjectException: No row with the given identifier exists: data.model.PlanTypePK@18f7386[terminalid=9999,planTypeLid=<null>,planTypeSid=<null>], of class: data.model.DotPlanType
.
.
.

Is it possible to do searches (with out building a complete HQL query manually) by specifying an object and have Hibernate ignore everything that is null, even foreign composite id's?

Thanks,

L

Top

ldupeval

Post subject:

Posted: Thu Nov 18, 2004 3:59 pm

Newbie

Joined: Fri Oct 29, 2004 1:05 pm
Posts: 10

I tried not using the Example but it still doesn't work as expected:

Code:

query = "from Profile t where t.comp_id.terminalid = ? "
  + " and t.comp_id.profileLid = ? " + " and t.comp_id.profileSid = ? ";

return session.find(query, 
  new Object[] { profile.getComp_id().getTerminalid(),   
  profile.getComp_id().getProfileLid(),
  profile.getComp_id().getProfileSid() },
  new Type[] {Hibernate.INTEGER, Hibernate.INTEGER, Hibernate.SHORT});

But I still get the error about the foreign key (terminalid, null, null) being invalid.

Either Hibernate can't deal with my model (which I can't change for now) or there is a concept I am not grasping.

While going through the code in debug mode, I noticed that HIbernate totally skips over the parameters of the foreign key when building the query but still uses it when executing the search:

Code:

Hibernate: select profile0_.TERMINALID as TERMINALID, profile0_.PRFL_LID as PRFL2_, profile0_.PRFL_SID as PRFL3_, profile0_.PRFL_AID as PRFL4_, profile0_.PRFL_NAME as PRFL5_,  profile0_.TERMINALID as TERMINALID, profile0_.PRFL_PLAN_TYPE_LID as PRF12_, profile0_.PRFL_PLAN_TYPE_SID as PRF13_ from profl profile0_ where (profile0_.TERMINALID=? )and(profile0_.PRFL_LID=? )and(profile0_.PRFL_SID=? )
Hibernate: select plantyp0_.TERMINALID as TERMINALID0_, plantyp0_.PLAN_TYPE_LID as PLAN2_0_, plantyp0_.PLAN_TYPE_SID as PLAN3_0_, plantyp0_.PLAN_TYPE_AID as PLAN4_0_, plantyp0_.PLAN_TYPE_NAME as PLAN5_0_ from PLTYP plantyp0_ where plantyp0_.TERMINALID=? and plantyp0_.PLAN_TYPE_LID=? and plantyp0_.PLAN_TYPE_SID=?

(a lot of fields (>30) have been removed for brevity and legibility).

This is using Hibernate 2.1.6, SQL Server 2000 and the jtds 0.9. I noticed I didn't post the mappingfor PlanType:

Code:

<class 
    name="data.model.PlanType" 
    table="PLTYP"
>

    <composite-id name="comp_id" class="data.model.PlanTypePK">
        <key-property 
            name="terminalid" 
            column="TERMINALID" 
            type="java.lang.Integer"
            length="10"
        />
        <key-property 
            name="planTypeLid" 
            column="PLAN_TYPE_LID" 
            type="java.lang.Integer"
            length="10"
        />
        <key-property 
            name="planTypeSid" 
            column="PLAN_TYPE_SID" 
            type="java.lang.Short"
            length="5"
        />
    </composite-id>    

    <timestamp
        name="planTypeAid"
        column="PLAN_TYPE_AID"
        unsaved-value="null"
    />

    <property
        name="planTypeName"
        type="java.lang.String"
        column="PLAN_TYPE_NAME"
        length="30"
    >
        <meta attribute="use-in-tostring">true</meta>
    </property>

.
.
.

    <!-- Associations -->
    <!-- derived association(s) for compound key -->
    <!-- end of derived association(s) -->
  

</class>

Top

ldupeval

Post subject:

Posted: Fri Nov 19, 2004 12:17 pm

Newbie

Joined: Fri Oct 29, 2004 1:05 pm
Posts: 10

Aha! Breakthrough, somewhat. Sleep has its benefits!

I now see that there are two, probably related, problems:

1) My model does not store the foreign key. If I create a new Profile, set the PlanType to a known entry in the database, when I look at the added entry, it does not store the LID/SID values. Both are null. So right there, I see I have a problem, but I'm unsure where.

2) Even though my database schema allows me to store the foreign keyx as null, doing so prevents retrieval of data from the database because Hibernate(?) enforces the use of the foreign key in the search, even if the key is null. I think it should be ignored.

3) Middlegen/hbm2java generate duplicate information for Terminals. There will be a terminal id (Integer) in my primary key, another one in my foreign key (I should mention that I have other tables that contain three and possibly four of these foreign, composite keys -- it's an awful model, I know), and yet another Terminal as a field

Code:

class Terminal {
  Integer terminalId;
}

class PlanType {
  PlanTypePK id;
  Terminal terminal;
}

class PlanTypePK {
  Integer terminalId;
  Integer lid;
  Short sid;
}

class Profile {
  ProfilePK id;
  PlanType planType;
  Terminal terminal;
}

class ProfilePK {
  Integer terminalId;
  Integer lid;
  Short sid;
}

If for some reason the Terminal changes, doesn't that create issues?

4) Finally, with all the struggling I'm doing just trying to get a few tables working, am I using the proper technology? When googling to find information on using composite keys with Hibernate, I often fall upon comments saying that Hibernate doesn't like badly designed database schemas and therefore, doesn't work too well with composite keys. If this is the case, should I be looking at other technologies instead? If so, which ones?

Thanks,

L

Top

david

Post subject:

Posted: Fri Nov 19, 2004 8:13 pm

Hibernate Team

Joined: Tue Aug 26, 2003 7:19 pm
Posts: 2364
Location: Brisbane, Australia

It works fine with composite keys - you just have to work harder. The issue is usually very poorly designed schema's that make it difficult to map. Usually poor schema designs have employed composite keys. This is not to say composite keys are bad just they are easier to abuse and they are used in many places where it is not ideal to do so.

Top

Page 1 of 1

[ 4 posts ]

Board index » Projects » Tools

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum