CODEWITHSUNDEEP
javaspringjpaspring-data-jpahql
Spartan
Spartan

Reputation: 1174

Order By a date field not working in Spring Data JPA

I am trying to get data sorted by date with DESC, but always I am getting it with ASC, I tried 2 ways but there is no good result:

I have this repository : 

@Repository
public interface CollectRepository extends JpaRepository<Collect, Long>

1st way, I used the sorting in @query:

@Query("SELECT c FROM Collect c LEFT JOIN c.payee p WHERE p.userId=:userId AND c.date BETWEEN :startDate AND :endDate ORDER BY c.date DESC")
List<Collect> getCollectionHistory(@Param("userId") String userId, 
                                   @Param("startDate") Date startDate, 
                                   @Param("endDate") Date endDate);

2nd way, I used the Sort 

@Query("SELECT c FROM Collect c LEFT JOIN c.payee p WHERE p.userId=:userId AND c.date BETWEEN :startDate AND :endDate")
List<Collect> getCollectionHistory(@Param("userId") String userId, 
                                   @Param("startDate") Date startDate, 
                                   @Param("endDate") Date endDate, Sort sort);

and calling the function using :

collectionList = collectRepository.getCollectionHistoryByCollector(userId, startDate, endDate, new Sort(Direction.DESC, "date"));

Collect Entity 

@Entity
@Table(name = "collect")
@SequenceGenerator(name = "SEQ", sequenceName = "collect_id_seq", allocationSize = 1)
@AttributeOverride(name = "id", column = @Column(name = "collect_id", nullable = false))
public class Collect extends GenericEntity {

    @Column(name = "collector_user_id")
    private String collectorUserId;

    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "payer")
    private Payer payer;

    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "payee")
    private Payee payee;

    @Column(name = "amount", precision = 5)
    private BigDecimal amount;

    @Column(name = "date", columnDefinition = "TIMESTAMP DEFAULT CURRENT_TIMESTAMP")
    @JsonSerialize(using = IsoDateSerializer.class)
    private Date date;

    @Column(name = "reason")
    private String reason;

    @Column(name = "reference")
    private String reference;

// getters and setters

Payee Entity : 

@Entity
@Table(name = "payee")
@SequenceGenerator(name = "SEQ", sequenceName = "payee_id_seq", allocationSize = 1)
@AttributeOverride(name = "id", column = @Column(name = "payee_id", nullable = false))
public class Payee extends GenericEntity {

    private String userId;

    @OneToMany(mappedBy = "payee", cascade = CascadeType.ALL)
    private List<Collect> collects;

    @OneToMany(mappedBy = "payee", cascade = CascadeType.ALL, fetch = FetchType.EAGER)
    private List<PayeePayer> payeePayers;

I am using Spring Data JPA version : 1.10.5.RELEASE

is it a bug or I have something wrong in my code ? How can I fix this issue ?

Upvotes: 3

Views: 8670

Answers (2)

ngc4151
ngc4151

Reputation: 442

Could you try this:

List<Collect> findByPayeeUserIdAndDateBetweenOrderByDateDesc(String payeeUserId, Date start, Date end);

or

List<Collect> findByPayeeUser_IdAndDateBetweenOrderByDateDesc(String payeeUserId, Date start, Date end);

For the 2nd one, i have a similar repository of one of my project and it is like this:

import com.buhane.property.domain.entity.Lease;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface LeaseRepository extends PagingAndSortingRepository<Lease, Long> {
    Page<Lease> findByApplicationId(Long applicationId, Pageable page);

    Page<Lease> findByApplicationProperty_Id(Long propertyId, Pageable page);

    List<Lease> findByApplicationIdAndActiveTrue(Long applicationId);
}

Please notice the line

Page<Lease> findByApplicationProperty_Id(Long propertyId, Pageable page);

and it is working fine :)

Upvotes: 0

xenteros
xenteros

Reputation: 15852

The following method will return a List of Collect with date between start and end and payee with id payeeId. Once you add Payee to the question I can adjust to the model.

List<Collect> findAllByPayeeUserIdAndDateBetweenOrderByDateDesc(String payeeUserId, Date start, Date end);

Upvotes: 3

Related Questions