Spring boot JPA filter by join table

Question

I cant find something concrete in the docs (https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.query-methods.query-creation) and no satisfying answere in several blogs. so here my question.

I have table Entity like:

@Entity
@Table(name = "workstation")
public class Workstation
{
 @ManyToOne(fetch = FetchType.LAZY)
 @JoinColumn(name = "userid", nullable = false)
 public User user;
}

And the user table Entity:

public class user
{
 @Id
 @GeneratedValue(strategy = IDENTITY)
 @Column(name = "ID", unique = true, nullable = false)
 public Integer id;

 @Column(name = "age", nullable = false)
 public int age
}

Now I would like to have a query in my Repository like this:

public interface WorkstationRepo extends CrudRepository<Workstation, Long> {
 findByUserAgeLesserThan(int maxAge);
}

Means I want to find all user who are under a certain age through my Workstation Entity.

And is it possible without a @Query annotation? Or/And how should it look like?

Answer 1

Try this

List<Workstation> findByUserAgeLessThan(int maxAge);

Alternatively you can also write your query

@Query("Select w from Workstation w where w.user.age < :maxAge")
List<Workstation> findByUserAgeLesserThan(@Param("maxAge") int maxAge);

Answer 2

This works:

@Query("SELECT w from Workstation w INNER JOIN w.user u where u.age < ?1")
List<Workstation> findByUserAgeLessThan(int age);

Answer 3

You should have something like this:

@Query("SELECT w from Workstation w INNER JOIN w.user u where u.age < :age")
List<Workstation> findByUserAgeLessThan(@Param("age") int age);

Basically, you need to JOIN the tables using JPQL.