Hibernate Community • View topic - Querying on composite key's foreign key props

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

Querying on composite key's foreign key props

Page 1 of 1

[ 2 posts ]

Previous topic | Next topic

Author

Message

naesc

Post subject: Querying on composite key's foreign key props

Posted: Thu May 08, 2008 6:42 pm

Newbie

Joined: Mon Apr 14, 2008 1:13 pm
Posts: 11

This is probably a basic question and I fear I'm going to make it sound much more complicated than it is but here goes:

Using JPA w/ all the latest stable Hibernate versions (i.e. 3.2), I have 3 entities as follows:

Code:

@Entity
public class Team {
    @Id
    private Long id;
    
    @ManyToOne
    @JoinColumn(name="division_id")
    private Division division;
    ...
}

@Entity
public class Division {
    @Id
    private Long id;
    ...
}

@Entity
public class TeamSeasonRecord {
    @Id
    private Key id;
    ...
    
    @Embeddable
    public static final class Key {
        @ManyToOne
        @JoinColumn(name="team_id")
   private Team team;

        private Integer season;
        ...
    }
}

If I want to get all the TeamSeasonRecords for 2008 - the following criteria does the trick:

Code:

Criteria criteria = session.createCriteria(TeamSeasonRecord.class);
criteria.add(Restrictions.eq("id.season", 2008));

But I also want to get the TeamSeasonRecords for Teams in a certain division so I try this:

Code:

criteria.add(Restrictions.eq("id.season", 2008));
criteria.add(Restrictions.eq("id.team.division.id", new Long(1)));

this generates an error:

Code:

"could not resolve property: id.team.division.id"

I can easily write the SQL for this using an inner join as follows:

Code:

select * from TeamSeasonRecord r inner join Team t on r.team_id = t.team.id where r.season = 2008 and t.division_id = 1

But I'm stumped trying to get Hibernate to do the same :(

Can someone straighten me out here?

Top

naesc

Post subject:

Posted: Fri May 09, 2008 5:02 pm

Newbie

Joined: Mon Apr 14, 2008 1:13 pm
Posts: 11

To answer my own question, I had to do a couple of things:

1) Add the annotation @IdClass(TeamSeasonRecord.Key.class) to TeamSeasonRecord and then add fields for each value in that composite key value to TeamSeasonRecord itself... so TeamSeasonRecord looked like this:

Code:

@Entity
@IdClass(TeamSeasonRecord.Key.class)
public class TeamSeasonRecord {
    @Id
    @ManyToOne
    @JoinColumn(name="team_id")
    private Team team;
    
    @Id
    private Integer season;
    ...
   
    @Embeddable
    public static final class Key {
        @ManyToOne
        @JoinColumn(name="team_id")
        private Team team;

        private Integer season;
        ...
    }
}

2) Recode the criteria to include a subquery as follows:

Code:

DetachedCriteria division = DetachedCriteria.forClass(Team.class);
division.createCriteria("division").add(Restrictions.eq(
    "id", new Long(divisionId)));
division.setProjection(Projections.id());
      
Criteria criteria = ((Session) entityManager.getDelegate()).createCriteria(
    TeamSeasonRecord.class);
      
criteria.add(Restrictions.eq("season", 2008));   
criteria.add(Subqueries.propertyIn("team", division));

Seems simple but damn if it didn't take me a day and half to figure it out :)

Top

Page 1 of 1

[ 2 posts ]

Board index » Hibernate & Java Persistence » Hibernate Users

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