javahibernatespring-data

Hibernate @Filter does not work with Spring JpaRepository.findById method

In order to create row-level authorization, I want to use @Filter and @FilterDef hibernate annotations in combination with JpaRepository<T, ID> interface of spring-data. Suppose that, we have a following entities:

@Entity
public class User {
   @Id
   private Long id;
   private String name;
    
   @ManyToOne
   private Pharmacy pharmacy;
}
    
@Entity
public class Pharmacy {
   @Id
   private Long id;
   private String name;
}

I want to create authorization based on whom send requests to server. For this purpose, I've added @Filter and @FilterDef annotations top of the Pharmacy entity. So, the pharmacy should be like below:

@Entity
@FilterDef(name = "pharmacyFilter", parameters = {@ParamDef(name = "userId", type = "long")})
@Filters({
   @Filter(name = "pharmacyFilter", condition = "id in (select user.pharmacy_id from user where user.id = :userId)")
})
public class Pharmacy {
   //...
}

The repository that I've created to access database is one that seen below:

@Repository
public interface PharmacyRepository extends JpaRepository<Pharmacy, Long> {
    
}

When I make pharmacyFilter enabled, everything works fine and the filter applied on all queries. You can see the query generated for repository.findAll() as follows:

select pharmacy0_.id as id1_0_, pharmacy0_.name as name2_0_ from pharmacy pharmacy0_ where pharmacy0_.id in (select user.pharmacy_id from user where user.id = ?)

But, the problem occurred when I want to try using repository.findById(ID id). When I use the mentioned method, the filter won't applied to the final query and we will see the following sql in the terminal:

select pharmacy0_.id as id1_0_0_, pharmacy0_.name as name2_0_0_ from pharmacy pharmacy0_ where pharmacy0_.id=?

I guessed the problem is due to using id multiple times. One in findById and another in filter condition. But when I tried to create query using session object, this problem didn't occurred and output is desirable:

select pharmacy0_.id as id1_0_, pharmacy0_.name as name2_0_ from pharmacy pharmacy0_ where pharmacy0_.id in (select user.pharmacy_id from user where user.id = ?) and pharmacy0_.id=2

The problem is resolved using the following approach, but what happens when we use the JpaRepository#findById default implementation?

@Query(value = "from Pharmacy where id = :id")
Optional<Pharmacy> findById(Long id);

Thanks in advance.

Solution

  • As it is stated in the hibernate documentation:

    Filters apply to entity queries, but not to direct fetching.

    But under the hood repository.findById(ID id) method calls EntityManager.find. So, this is expected behaviour.