How to create a dynamic WHERE clause for all queries in Spring Data JPA?

Question

I have a requirement where I need to pass a global WHERE condition dynamically to all the queries?

We have data in the tables for multiple organizations. So, I need to pass ORG_ID = ? while retrieving data.

I don't want to write finder methods like findBy..AndOrgId(... Integer orgId). Is there a better way to do this? To apply a Global WHERE clause or a Predicate?

I receive this orgId as part of the request attribute in RestController. Since it is dynamic, I can't use the hibernate annotation @Where on the entity classes.

Answer 1

You can try to use hibernate @Filter annotation. As it stated in the documentation:

The @Filter annotation is another way to filter out entities or collections using custom SQL criteria. Unlike the @Where annotation, @Filter allows you to parameterize the filter clause at runtime.

Example 1:

import org.hibernate.annotations.Filter;
import org.hibernate.annotations.FilterDef;
import org.hibernate.annotations.ParamDef;

@Entity
@Table(name = "MY_ORDER")
@FilterDef(
   name="byId",
   parameters = @ParamDef(
      name="ordId",
      type="long"
   )
)
@Filter(
   name="byId",
   condition="ord_id = :ordId"
)
public class MyOrder
{
   @Id
   @Column(name = "ord_id")
   private Long id;

   // ...
}

and usage:

EntityManager em = emFactory.createEntityManager();
// ...
em.unwrap(Session.class).enableFilter("byId").setParameter("ordId", 2L);

List<MyOrder> orders = em.createQuery("select o from MyOrder o", MyOrder.class)
   .getResultList();

the following sql will be generated:

/* select o from MyOrder o */
select
  myorder0_.ord_id as ord_id1_3_,
  myorder0_.ord_time as ord_time2_3_ 
from TEST_SCHEMA.MY_ORDER myorder0_ 
where myorder0_.ord_id = ?

EDIT

The described above approach also works with spring data jpa @Query and the queries derived from the method names.

Example 2:

@Entity
@Table(name = "post")
@FilterDef(
   name="postById",
      parameters = @ParamDef(
         name="postId",
         type="long"
      )
   )
@Filter(
   name="postById",
   condition="id = :postId"
)
public class Post
{
   @Id
   private Long id;

   @Enumerated(EnumType.ORDINAL)
   private PostStatus status;

   // getters, setters
}

public enum PostStatus { OPEN, CLOSE }

public interface PostRepository extends JpaRepository<Post, Long>
{
   @Query(value = "select p from Post p where p.status = :sts")
   List<Post> test(@Param("sts") PostStatus status);

   List<Post> findByStatus(PostStatus status);
}

@RestController
public class TestRestController
{
   @Autowired
   EntityManager entityManager;

   @Autowired
   private PostRepository postRepository;

   @RequestMapping("/")
   @Transactional
   public String home()
   {
      entityManager
          .unwrap(Session.class)
          .enableFilter("postById")
          .setParameter("postId", 10L);
       // ....
       List<Post> posts1 = postRepository.test(PostStatus.OPEN);
       List<Post> posts2 = postRepository.findByStatus(PostStatus.OPEN);
   }      
}