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

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);
   }      
}

Spring Data (I assume you are using Spring Data based on your post tags) doesn't support query augmentation (at time of writing). There is a long standing request for that feature here that also describes some alternatives that others have tried.

Good luck!

I needed to load all the entities, mapping helped

@Entity
@Where(clause = "is_active = true")
data class Account(
    @Column(unique = true, nullable = false)
    var phone: String,
    @Embedded
    val name: Name,
    @Column(nullable = false)
    var password: String,
    @ManyToOne(fetch = FetchType.EAGER)
    var role: Role,
    var isActive: Boolean
){
    @Id
    var id: Long = 0

    @CreationTimestamp
    var dateCreated: Timestamp? = null

    @UpdateTimestamp
    var dateUpdated: Timestamp? = null
}

Create the same entity and map it

@Entity
@Immutable
@Subselect("select * from account")
data class AccountWithRemove(
    @Column(unique = true, nullable = false)
    var phone: String,
    @Embedded
    val name: Name,
    @Column(nullable = false)
    var password: String,
    @ManyToOne(fetch = FetchType.EAGER)
    var role: Role,
    var isActive: Boolean,
    var code: Long?
) {
    @Id
    var id: Long = 0

    @CreationTimestamp
    var dateCreated: Timestamp? = null

    @UpdateTimestamp
    var dateUpdated: Timestamp? = null
}

This is how the request with the specification looks like. Search for regular and deleted accounts

from
        ( select
            * 
        from
            account ) accountwit0_ 
    where
        accountwit0_.is_active=true 
        or accountwit0_.is_active=false 

And this is a normal findAll()

from
        ( select
            * 
        from
            account ) accountwit0_

Hibernate doesn't create a new table, we just create a new entity and map the one where we don't need the @Wnere annotation. I removed some of the fields, but I think the meaning is clear