How to join two entities using another common entity in JPA Specification?

Question

I have a simple data model with 3 element, Principal (user) and then his settings and profile data. UserSetting and UserProfile have references to the Principal, but Principal has reference to neither (unidirectional).

UserProfile ----> Principal <---- UserSettings

Here are the entities (only fields related to the question are shown):

@Entity
public class Principal {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO) 
    private Long id;

    private boolean enabled; 
}

@Entity
public class UserProfile {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO) 
    private Long id;

    @OneToOne(fetch = FetchType.LAZY) 
    private Principal principal;
}

@Entity
public class UserSettings{
    @Id 
    @GeneratedValue(strategy = GenerationType.AUTO) 
    private Long id;

    @OneToOne(fetch = FetchType.LAZY) 
    private Principal principal;

    private boolean isHidden;
}

Here is a working JPA query that joins the tables and pulls all the correct data:

@Query( "SELECT DISTINCT(userProfile) " +
    "FROM UserProfile userProfile " +
    "LEFT JOIN FETCH userProfile.principal AS principal " +
    "WHERE principal.enabled = :enabled " +
    "AND principal.id IN ( " +
        "SELECT userSettings.principal.id " +
        "FROM UserSettings userSettings " +
        "WHERE userSettings.isHidden = :hidden)")

Is there a way to create JPA Specifications that would do the following (keep in mind Principal does not have references to UserSettings or UserProfile):

a) Filter out user profiles whose principals that are not enabled

WHERE principal.enabled = :enabled

b) Filter out user profiles for those who chose to be hidden via user setting

AND principal.id IN (
    SELECT userSettings.principal.id
    FROM UserSettings userSettings
    WHERE userSettings.isHidden = :hidden
)

score 1 · Accepted Answer · answered Dec 12 '13 at 22:07

1

If I were you I'll create a bidirectional @OneToOne relation between UserProfile and UserSettings

answered Dec 12 '13 at 22:07

Flying Dumpling

1,294
1
11
13

Dumping - Funny you say that, I am actually in the process of moving away from bidirectional relationships because fetch = FetchType.LAZY does not work on bidirectional OneToOne's. Spent an entire day trying to figure out how to force it to not fetch OneToOne's, tried JoinColum, JoinTable, mappedBy, you name it, nothing worked short of having unidirectional relationships. Currently hacking away at a solution for my question, it is definitely doable, I am just not very familiar with CriteriaBuilder/Specification way of queries, so it is proving to be a little challenging. – SergeyB Dec 12 '13 at 22:16
Hm, you must be running outside a J2EE application server. If so, you have to enable weaving in your JPA provider. In J2EE AS weaving is enabled by default. – Flying Dumpling Dec 12 '13 at 22:39
take a look at this post, it describes the same issue I am running into: http://stackoverflow.com/questions/7510131/jpa-2-0-hibernate-why-does-lazy-fetching-with-onetoone-work-out-of-the-box – SergeyB Jan 02 '14 at 23:12
Those two answers are very good. Just ensure that you have runtime weaving enabled and you are good to go. – Flying Dumpling Jan 03 '14 at 09:02

How to join two entities using another common entity in JPA Specification?

1 Answers1