CODEWITHSUNDEEP
javamysqlspringjpaspring-data
Plog
Plog

Reputation: 9612

Spring Data: findByEntityNot() in a many to many relationship

I have the following many to many relationship:

@Entity
public class Foo implements Serializable {

  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Long id;

  @JsonIgnore
  @ManyToMany
  @JoinTable(name = "foo_bar",
      joinColumns = {@JoinColumn(name = "foo_id", referencedColumnName = "id")},
      inverseJoinColumns = {@JoinColumn(name = "bar_name", referencedColumnName = "name")})
  private Set<Bar> bars = new HashSet<>();

}

@Entity
public class Bar implements Serializable {

  @Id
  private String name;

}

Now I want to query the FooRepository for all Foo's which DO NOT contain a Bar with the name "example". I tried to use the following Spring data method in FooRepository:

findByBars_NameNot(String barName);

But this returns one of every entry of the pivot table foo_bar which doesn't have "example" in its bar_name column. This means it can return duplicate Foo objects as well as Foo object which do actually contain a Bar with name "example" i.e. it's equivalent to the following SQL:

SELECT * FROM myschema.foo_bar WHERE bar_name != "example";

Is there any nice way in Spring data to write a repository method to do what I am trying?

I have found the following native query which does what I need but I am hesitant to use a native query as I feel there is a cleaner way of doing this:

SELECT * FROM myschema.foo WHERE id NOT IN (SELECT foo_id FROM myschema.foo_bar WHERE bar_name = "example")

Upvotes: 0

Views: 38

Answers (1)

Alex Savitsky
Alex Savitsky

Reputation: 2371

From Spring Data JPA docs

public interface FooRepository extends JpaRepository<Foo, Long> {

  @Query("SELECT f FROM Foo f WHERE NOT EXISTS (SELECT b FROM f.bars b WHERE b.name = ?1)")
  Foo findByNotHavingBarName(String name);
}

Unfortunately, there's no support for EXISTS queries in the query creation from method names

Upvotes: 1

Related Questions