I want to create a JPA query from JPA findBy method

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)

CodePudding user response：

There are multiple ways to solve it

• The Spring Data JPA way, using @Query and Named Parameters:

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

• The JPA way, using 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();
      }
  }