How to reference entity property in JPA JQL?

Question

I have SQL table posts with properties

id
user_id
is_active

I have Java class PostsRepository:

public interface PostsRepository extends CrudRepository<PostEntity, Long> {
    Collection<PostEntity> findByUser(UserEntity user);
}

Now I want to modify findByUser method to return only posts where column is_active equal to true.

As I understand there is no "generic" template for methods which will generate my query just by method name findActiveByUser, so I need to use JPA @Query annotation;

public interface PostsRepository extends CrudRepository<PostEntity, Long> {
    
    @Query("SELECT p FROM PostEntity p WHERE p.is_active = true AND p.user.id = ?How to reference User ID?")
    Collection<PostEntity> findActiveByUser(UserEntity user);
}

The problem is I don't know how to reference user ID in this query. I can find examples of using method parameters of primitive types in JQL query, but I can't find examples of using entities properties in JQL query.

Answer 1

You should use UserEntity as a param:

public interface PostsRepository extends CrudRepository<PostEntity, Long> {

 @Query("SELECT p FROM PostEntity p WHERE p.isActive = true AND p.user = :user")
 Collection<PostEntity> findActiveByUser(@Param("user") UserEntity user);

}

Or if you want to use userId you can change the method into :

@Query("SELECT p FROM PostEntity p WHERE p.isActive = true AND p.user.id = :id")
 Collection<PostEntity> findActiveByUserId(@Param("id") Long id);