7

Considering the following "model":

USER
    Long: PK
    String: firstName
    String: lastName

USER_EXT
    Long: PK
    String: moreInfo
    Date: lastModified

I'm trying to find/create the correct Hibernate mapping (using Annotations) such that, with an HQL query as simple as "from User", it would generate the following SQL: 

select firstName, moreInfo from USER, USER_EXT where user.pk = user_ext.pk

I've tried everything, from using @Secondarytable to @OneToOne association, but I can't make it work.

The best result I have now is with the @OneToOne association which generate multiple SQL queries, one to fetch rows in USER and for each rows in the resultset a select query from USER_EXT.

This is quite ineffective.

Any idea ?

David
  • 910
  • 1
  • 12
  • 22

1 Answers1

3

Choosing between OneToOne and Secondarytable somehow depends on the object model (i.e. if you want an entity for the user extension). I chose to use a OneToOne association and two entities.

For the User (note the use of the PrimaryKeyJoinColumn for the shared primary key):

@Entity
public class User {

    @Id private Long id;
    private String firstName;
    private String lastName;

    @OneToOne
    @PrimaryKeyJoinColumn
    private UserExt userExt;

    public UserExt getUserExt() {
        return userExt;
    }
    public void setUserExt(UserExt userExt) {
        this.userExt = userExt;
    }
    public Long getId() {
        return id;
    }
    public void setId(Long id) {
        this.id = id;
    }
    public String getFirstName() {
        return firstName;
    }
    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }
    public String getLastName() {
        return lastName;
    }
    public void setLastName(String lastName) {
        this.lastName = lastName;
    }
}

And the UserExt:

@Entity
public class UserExt {

    @Id private Long id;
    private String moreInfo;
    @Temporal(TemporalType.DATE)
    private Date lastModified;
    public Long getId() {
        return id;
    }
    public void setId(Long id) {
        this.id = id;
    }
    public String getMoreInfo() {
        return moreInfo;
    }
    public void setMoreInfo(String moreInfo) {
        this.moreInfo = moreInfo;
    }
    public Date getLastModified() {
        return lastModified;
    }
    public void setLastModified(Date lastModified) {
        this.lastModified = lastModified;
    }   
}

With the above entities, the following HQL query:

select u.firstName, u.userExt.moreInfo from User u

Generates the following SQL query:

select
  userx0_.firstName as col_0_0_,
  userextx1_.moreInfo as col_1_0_ 
 from
  User userx0_,
  UserExt userextx1_ 
 where
  userx0_.id=userextx1_.id

Which is the expected result.

PS: JPA 1.0 actually provides poor support of derived identifiers (things are much better in JPA 2.0) and you will have to set the Id manually on the UserExt, unless you use an Hibernate specific foreign generator. See the question below for details.

Related Question

Community
  • 1
  • 1
Pascal Thivent
  • 562,542
  • 136
  • 1,062
  • 1,124
  • Thanks for the answer. Actually, I'm currently testing the other way around with @SecondaryTable and only one entity. Everything seems to work so far except for the following: I've got a @ManyToMany association in my User entity which pertain to the 'UserExt' table. I cannot make the foreign key constraints to references the USER_EXT table. Using: @SecondaryTable(name = "USER_EXT") public class User { . ... @ManyToMany @JoinTable(name = "USEREXT_ACCOUNT", joinColumns = { @JoinColumn(table="USER_EXT", name = "USER_ID") }) ... } – David Aug 18 '10 at 16:00
  • I get as DDL : alter table USEREXT_ACCOUNT add constraint FK65656DF foreign key (USER_ID) references USER instead of 'references USER_EXT' – David Aug 18 '10 at 16:01
  • @David: I wonder if it makes sense to have a `ManyToMany` joining on the `SecondaryTable` since a `ManyToMany` should point on an Entity (and thus use the PK of the "Primary" table for the join). I don't think it does. Maybe providing your object model would help here. But this is IMHO an entirely different question. You should open a *new* question. – Pascal Thivent Aug 18 '10 at 16:54
  • Doing the way you suggested works as long as my HQL looks like your one (select u.firstName, u.userExt.moreInfo from User u) with specific properties requested. When requesting the full entity it generates too much SQL. I will modify my question. – David Aug 19 '10 at 11:48
  • @David: Thanks for changing entirely your question (title and body), making my answer almost irrelevant. Please rollback the changes and open a new question (as I already hinted), I won't answer here. – Pascal Thivent Aug 19 '10 at 17:14
  • @Pascal: Sorry, it wasn't done with the purpose of making your response irrelevant. I thought the question was not relevant anymore since I found a better way to express it. I'll rollback to the first one and create a new one then. – David Aug 21 '10 at 12:06
  • By the way, doesn't stackoverflow supports a feature to create a new question from another ? Linking them together in the process to show that they are related ? – David Aug 21 '10 at 12:12
  • @David I know it wasn't done on purpose. It's just that 1. StackOverflow is not just about getting a solution to your specific problem, it's about generating knowledge that can be reused by others too 2. Changing the current question wasn't really motivating me :) So no problem, don't worry. – Pascal Thivent Aug 21 '10 at 13:00
  • @David Regarding the mentioned feature, I don't think So offers something like that (but I don't open that many questions so I might just not be aware of it). It's somehow not a frequent need though. But you could submit the idea on http://meta.stackoverflow.com/ and see if the community likes the idea. – Pascal Thivent Aug 21 '10 at 13:02