Spring JPA repository method query name for between

Admin 1
Viewed 2728

I want to fetch the records for which either updated Date or created Date lie between a date range. The entity class is like below:

@Entity
public class Order {

  @Id
  @Setter(AccessLevel.NONE)
  private String orderId;

  @Temporal(TemporalType.TIMESTAMP)
  @Column(name = "created_at", nullable = false)
  private Date created;

  @Temporal(TemporalType.TIMESTAMP)
  @Column(name = "updated_at", nullable = false)
  private Date updated;

}

The repository on it is below:

public interface OrderRepository
    extends PagingAndSortingRepository<Order, String>, QuerydslPredicateExecutor<Order> {
  public Page<Order> findByCreatedBetweenOrUpdatedBetween(Date startDate, Date endDate,
      Pageable pageRequest);
}

Issue is when I use this, I get below error:

Caused by: java.lang.IllegalArgumentException: No parameter available for part updated BETWEEN (2): [IsBetween, Between] NEVER. at org.springframework.util.Assert.isTrue(Assert.java:116) at org.springframework.data.jpa.repository.query.ParameterMetadataProvider.next(ParameterMetadataProvider.java:123) at org.springframework.data.jpa.repository.query.JpaQueryCreator$PredicateBuilder.build(JpaQueryCreator.java:247) at org.springframework.data.jpa.repository.query.JpaQueryCreator.toPredicate(JpaQueryCreator.java:206) at org.springframework.data.jpa.repository.query.JpaQueryCreator.create(JpaQueryCreator.java:119) at org.springframework.data.jpa.repository.query.JpaQueryCreator.create(JpaQueryCreator.java:56)

spring-data spring-data-jpa jpql spring java
3 Answers

You need to provide 4 dates.

First two for the Create Between and second two for the Updated Between:

public interface OrderRepository
    extends PagingAndSortingRepository<Order, String>, QuerydslPredicateExecutor<Order> {
  public Page<Order> findByCreatedBetweenOrUpdatedBetween(
           Date startDateCreated, Date endDateCreated
         , Date startDateUpdated, Date endDateUpdated
         , Pageable pageRequest);
}
user_22024150

Write the findByCreatedBetweenOrUpdatedBetween method signature as below.

public interface OrderRepository
    extends PagingAndSortingRepository<Order, String>, QuerydslPredicateExecutor<Order> {
  public Page<Order> findByCreatedBetweenOrUpdatedBetween(Date startDate1, Date endDate1,
      Date startDate2, Date endDate2, Pageable pageRequest);
}
user_17682260

Auto generation is fine for simple queries: in this case is probably easier just to define the query manually then you only need 2 parameters.

public interface OrderRepository
    extends PagingAndSortingRepository<Order, String>, QuerydslPredicateExecutor<Order> {

  @Query("select o from Order o where (o.startDate between ?1 and ?2) " + 
            "or (o.updatedDate between ?1 and ?2)")
  public Page<Order> findByCreatedOrUpdatedBetween(Date startDate, Date endDate,
      Pageable pageRequest);
}
user_13564230
Related