CODEWITHSUNDEEP
javahibernatejpaspring-dataspring-data-jpa
SergeyB
SergeyB

Reputation: 9848

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

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
)

Upvotes: 3

Views: 2556

Answers (1)

Flying Dumpling
Flying Dumpling

Reputation: 1324

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

Upvotes: 1

Related Questions