Is there a way to use SecondaryTable to jump multiple tables?

Question

Let's say I have a primary user table called UserProfile, and on it there's a display name.

I have various modules that you can be a member of, with a UserProfile and a ModuleId on it to signify your ModuleMembership. You then can have a profile for each different module to store data related to that module, for example if you're signed up for the PokerModule you'll get a PokerProfile.

I'd like to put the display name from UserProfile on the PokerProfile, but I'd like to do it in a normalized manner. I could do it via Hibernate or via SQL, either way works. The exact relationship would be PokerProfile.membership.userProfile.displayName - how can I get this into an @Column on the PokerProfile class?

Answer 1

You can use a derived property to fetch the displayName in your PokerProfile class, as follows:

@Formula(
    "(SELECT up.displayName"
        + " FROM ModuleMembership mm"
        + " JOIN UserProfile up"
        + " ON (mm.userProfileId = up.userProfileId)"
        + " WHERE mm.moduleMembershipId = membershipId)")
String displayName;

Note that the derived property uses SQL only, not HQL. membershipId is defined as the @JoinColumn name of the membership property. Other column names are similarly defined.

However, although it's not what you asked for exactly, I usually implement my own shortcut properties in Java for things like this, as follows:

@Transient
public String getDisplayName() {
    if (membership == null) {
        return null;
    }
    UserProfile userProfile = membership.getUserProfile();
    return userProfile == null ? null : userProfile.getDisplayName();
}

For me, shortcut properties are easier to both read and write than the corresponding SQL.

_{Code samples were tested to work using Hibernate 5.0.6.Final against an H2 database version 1.4.190.}

Answer 2

If we think of the given domain in terms of object-oriented/relational way, we can conclude the following relationship:

PokerProfile inherits UserProfile

You can achieve the same relationship with JPA and DBMS with the help of "One Table Per Subclass Inheritance" and Foreign Key relationships respectively.

So, you can define something like below:

UserProfile.java

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Inheritance;
import javax.persistence.InheritanceType;
import javax.persistence.Table;


@Entity
@Table(name = "TBL_USER_PROFILE")
@Inheritance(strategy=InheritanceType.JOINED)
public class UserProfile {

    @Id
    @GeneratedValue
    @Column(name = "ID")
    private Long id;

    @Column(name = "DISPLAY_NAME")
    private String displayName;

    public Person() {

    }

    public Person(String displayName) {
        this.displayName = displayName;
    }

    // Getter and Setter methods
}

PokerProfile.java

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.PrimaryKeyJoinColumn;
import javax.persistence.Table;

@Entity
@Table(name="TBL_POKER_PROFILE")
@PrimaryKeyJoinColumn(name="ID")
public class PokerProfile extends UserProfile {

    // Properties & Getters & Setters here

}

The advantage of this model is that it clearly expresses the desired functionality and of course clearly synced with the parent profile. There is no workaround or tweak required. And under the hood, it uses foreign key relationship between PokerProfile & UserProfile and so no redundancy of data.

Answer 3

Hibernate Formulas are elegant solution for this, however they may be difficult to get right if they are complex (Hibernate has limited ability to parse native SQL). Also, even if you get them to work properly, the performance may suffer because formulas may involve subqueries which are always embedded into the final SQL.

The approach I use often is to create a database view which does the necessary joins for me and then I map my entity to the view. The database is able to optimize the view much better then nested subqueries, and Java code is a bit cleaner because there is no native SQL in it.

So, you could create a db view Membership_Profile and a reusable entity mapped to it, say MembershipProfile.

@Entity
@Table(name = Membership_Profile)
class MembershipProfile {
   Long moduleMembershipId;
   String displayName;
   // Other properties if needed
}

Then, you can associate this entity with specific profiles, for example:

@Entity
class PokerProfile {
   @OneToOne(fetch=FetchType.LAZY)
   @JoinColumn(name = "membershipId")
   MembershipProfile membershipProfile;
   ...
}

The benefit of this approach is that you load the displayName (and any other MembershipProfile properties) lazily when you need them (thus avoiding the cost of executing the view or subqueries).

An alternative to creating database views are Hibernate Subselects which are a kind of Hibernate (read-only) views, but I expect real db views to perform much better.