CODEWITHSUNDEEP
spring-bootspring-data-jpajpql
smeeb
smeeb

Reputation: 29537

Directionality in JPQL joins for Spring Boot JPA?

Spring Boot here. I have the following two JPA entities:

@Entity
@Table(name = "accounts")
public class Account {
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  @Column(name = "account_id")
  private Long id;

  // lots of stuff

  @OneToOne(fetch = FetchType.EAGER, cascade = [CascadeType.PERSIST, CascadeType.MERGE])
  @JoinColumn(name = "profile_id", referencedColumnName = "profile_id")
  private Profile profile;    // CAN be null

  // Getters, setters & ctors
}

@Entity
@Table(name = "profiles")
public class Profile {
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  @Column(name = "account_id")
  private Long id;

  // doesn't really matter lots of stuff in here

  // Getters, setters & ctors
}

It is possible for some Accounts to have Profiles, and some will not (their Profiles will be null). I would like to create a CrudRepository impl that essentially does this query:

SELECT *
FROM profiles p
INNER JOIN accounts a
WHERE a.profile_id = null

Essentially, get me all the Profiles that are "orphaned" and are associated with any accounts here.

I'm confused as to whether I need an CrudRepository<Long,Account> impl or a CrudRepository<Long,Profile> impl and on what that impl would look like. My best pseudo-attempt thus far looks like:

public interface ProfileRepository extends CrudRepository<Profile, Long> {
    @Query("FROM Account act WHERE act.profile = null")
    public Set<Profile> findOrphanedProfiles();
}

Can anyone help fill in the gaps for me?

Upvotes: 0

Views: 34

Answers (1)

Alex Savitsky
Alex Savitsky

Reputation: 2371

First, JPQL NULL check syntax is IS NULL, not == NULL (see JPQL docs - the link is for ObjectWeb, but applies to any JPA implementation)

Second, if you want to check for orphaned records, you definitely don't want to join in the table they're orphaned from.

Your last attempt at it, 

public interface ProfileRepository extends CrudRepository<Profile, Long> {
    @Query("FROM Account act WHERE act.profile = null")
    public Set<Profile> findOrphanedProfiles();
}

was actually pretty close, just replace == null with is null and you should be all set.

EDIT: if you're looking for profiles that don't have accounts associated with them, EXISTS query is what you're looking for:

public interface ProfileRepository extends CrudRepository<Profile, Long> {
    @Query("FROM Profile p WHERE NOT EXISTS (FROM Account a WHERE a.profile = p)")
    public Set<Profile> findDisassociatedProfiles();
}

EDIT: if your Profiles maintain a list of associated accounts as a property (it's not included in the code you posted, but maybe it was omitted), you can query for disassociated profiles even shorter:

FROM Profile p WHERE p.accounts IS EMPTY

Upvotes: 1

Related Questions