如何为AND和OR条件编写JPA FindBy查询

Question

我想从JPA findBy方法创建一个JPA查询

select * from somTable where Col1 =1
and (col2 > 0 or col2 = 0)
and (col3 > 0 or col3 = 0)
and (col4 >0 or col4 =0)
and (col5 >0 or col5 =0)

Answer 1

有多种解决方法

• Spring Data JPA方式，使用@Query和命名参数:

  public interface SomeTableRepository extends JpaRepository<SomeTable, Long> {   
  @Query("SELECT t FROM SomeTable t WHERE t.col1 = :col1 AND t.col2 >= :col2 AND t.col3 >= :col3")
  List<SomeTable> findAllByCol(@Param("col1") int col1, @Param("col2") int col2, @Param("col3") int col3);
  }
  

• JPA方式，使用JPA Criteria Queries:

  @Repository
  @Transactional
  public class SomeTableJpaRepository {
  @PersistenceContext
  EntityManager entityManager;
  public List<SomeTable> findAll(int col1, int col2, int col3, int col4, int col5) {
  CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
  CriteriaQuery<SomeTable> criteriaQuery = criteriaBuilder.createQuery(SomeTable.class);
  Root<SomeTable> itemRoot = criteriaQuery.from(SomeTable.class);
  List<Predicate> predicates = new ArrayList<>();
  Predicate predicateCol1
  = criteriaBuilder.equal(itemRoot.get("col1"), col1);
  predicates.add(predicateCol1);
  Predicate predicateCol2
  = criteriaBuilder.greaterThanOrEqualTo(itemRoot.get("col2"), col2);
  predicates.add(predicateCol2);
  Predicate predicateCol3
  = criteriaBuilder.greaterThanOrEqualTo(itemRoot.get("col3"), col3);
  predicates.add(predicateCol3);
  // ...
  criteriaQuery.select(itemRoot).where(predicates.toArray(new Predicate[]{}));
  return entityManager.createQuery(criteriaQuery).getResultList();
  }
  }