Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Secondarytables or OnetoOne associations?

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 ?

like image 741
David Avatar asked Aug 18 '10 13:08

David


1 Answers

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

  • JPA Hibernate One-to-One relationship
like image 148
Pascal Thivent Avatar answered Sep 29 '22 01:09

Pascal Thivent