How to add global where clause for all find methods of Spring data JPA with Hibernate?

Question

We are working on web application using Spring data JPA with hibernate.

In the application there is a field of compid in each entity. Which means in every DB call (Spring Data methods) will have to be checked with the compid.

I need a way that, this "where compid = ?" check to be injected automatically for every find method. So that we won't have to specifically bother about compid checks.

Is this possible to achieve from Spring Data JPA framework?

Answer 1

Maybe Hibernate‘s annotation @Where will help you. It adds the passed condition to any JPA queries related to the entity. For example

@Entity
@Where(clause = "isDeleted='false'")
public class Customer {
    //...
    @Column
    private Boolean isDeleted;
}

More info: 1, 2

Answer 2

Agree with Abhijit Sarkar.

You can achieve your goal hibernate listeners and aspects. I can suggest the following : create an annotation @Compable (or whatever you call it) to mark service methods create CompAspect which should be a bean and @Aspect. It should have something like this

@Around("@annotation(compable)")`
    public Object enableClientFilter(ProceedingJoinPoint pjp, Compable compable) throws Throwable {
        Session session = (Session) em.getDelegate();
        try {
            if (session.isOpen()) {
                session.enableFilter("compid_filter_name")
                        .setParameter("comp_id", your_comp_id);
            }
            return pjp.proceed();
        } finally {
            if (session.isOpen()) {
                session.disableFilter("filter_name");
            }
        }
    }

em  - EntityManager

3)Also you need to provide hibernate filters. If you use annotation this can look like this:

@FilterDef(name="compid_filter_name", parameters=@ParamDef(name="comp_id", type="java.util.Long"))
@Filters(@Filter(name="compid_filter_name", condition="comp_id=:comp_id"))

So your condition where compid = ? will be @Service method below

   @Compable
    someServicweMethod(){
     List<YourEntity> l = someRepository.findAllWithNamesLike("test");
    } 

That's basically it for Selects, For updates/deletes this scheme requires an EntityListener.

Answer 3

Like other people have said there is no set method for this

One option is to look at Query by example - from the spring data documentation -

Person person = new Person();
person.setFirstname("Dave");
Example<Person> example = Example.of(person); 

So you could default compid in the object, or parent JPA object

Another option is a custom repository

Answer 4

I can contribute a 50% solution. 50% because it seems to be not easy to wrap Query Methods. Also custom JPA queries are an issue for this global approach. If the standard finders are sufficient it is possible to extend an own SimpleJpaRepository:

public class CustomJpaRepositoryIml<T, ID extends Serializable> extends
    SimpleJpaRepository<T, ID> {

private JpaEntityInformation<T, ?> entityInformation;

@Autowired
public CustomJpaRepositoryIml(JpaEntityInformation<T, ?> entityInformation,
                              EntityManager entityManager) {
    super(entityInformation, entityManager);
    this.entityInformation = entityInformation;
}



private Sort applyDefaultOrder(Sort sort) {
    if (sort == null) {
        return null;
    }
    if (sort.isUnsorted()) {
        return Sort.by("insert whatever is a default").ascending();
    }
    return sort;
}

private Pageable applyDefaultOrder(Pageable pageable) {
    if (pageable.getSort().isUnsorted()) {
        Sort defaultSort = Sort.by("insert whatever is a default").ascending();
        pageable = PageRequest.of(pageable.getPageNumber(), pageable.getPageSize(), defaultSort);
    }
    return pageable;
}


@Override
public Optional<T> findById(ID id) {
    Specification<T> filterSpec = filterOperatorUserAccess();
    if (filterSpec == null) {
        return super.findById(id);
    }
    return findOne(filterSpec.and((Specification<T>) (root, query, criteriaBuilder) -> {
        Path<?> path = root.get(entityInformation.getIdAttribute());
        return criteriaBuilder.equal(path, id);
    }));
}

@Override
protected <S extends T> TypedQuery<S> getQuery(Specification<S> spec, Class<S> domainClass, Sort sort) {
    sort = applyDefaultOrder(sort);
    Specification<T> filterSpec = filterOperatorUserAccess();
    if (filterSpec != null) {
        spec = (Specification<S>) filterSpec.and((Specification<T>) spec);
    }
    return super.getQuery(spec, domainClass, sort);
}

}

This implementation is picked up e.g. by adding it to the Spring Boot:

@SpringBootApplication
@EnableJpaRepositories(repositoryBaseClass = CustomJpaRepositoryIml.class)
public class ServerStart {
...

If you need this kind of filtering also for Querydsl it is also possible to implement and register a QuerydslPredicateExecutor.