Use OrderBy with JPARepository

Question

I have a code like this:

public interface BatchExecuteHistoryRepository extends JpaRepository<BatchExecuteHistory, Long> {
Page<BatchExecuteHistory> findByBatchExecuteHistoryIdBatchIdOrderByTimeEndAsc(String batchId, Pageable pageable);

}

Here is my database:

Here is what I got on my website:

Anyone know why the query does not work with Order By time_end ASC??? I tried findByBatchExecuteHistoryIdBatchId(String batchId, Pageable pageable) and got the same result

Notice that BatchExecuteHistoryId is a composite id, and batchId is a element of it

Update, here is my BatchExecuteHistory class:

public class BatchExecuteHistory implements Serializable {

private static final long serialVersionUID = 1L;

@EmbeddedId
private BatchExecuteHistoryId batchExecuteHistoryId;

@NotNull
@Size(max = 100)
@Column(name = "batch_name", length = 100, nullable = false)
private String batchName;

@NotNull
@Column(name = "status", nullable = false)
private Boolean status;

@NotNull
@Column(name = "time_end", nullable = false)
private Instant timeEnd;

@Size(max = 100)
@Column(name = "error_step", length = 100)
private String errorStep;

@Size(max = 100)
@Column(name = "error_content", length = 100)
private String errorContent;

@Column(name = "row_input")
private Long rowInput;

public BatchExecuteHistoryId getBatchExecuteHistoryId() {
    return batchExecuteHistoryId;
}

public void setBatchExecuteHistoryId(BatchExecuteHistoryId batchExecuteHistoryId) {
    this.batchExecuteHistoryId = batchExecuteHistoryId;
}

public Boolean getStatus() {
    return status;
}

public String getBatchName() {
    return batchName;
}

public BatchExecuteHistory batchName(String batchName) {
    this.batchName = batchName;
    return this;
}

public void setBatchName(String batchName) {
    this.batchName = batchName;
}

public Boolean isStatus() {
    return status;
}

public BatchExecuteHistory status(Boolean status) {
    this.status = status;
    return this;
}

public void setStatus(Boolean status) {
    this.status = status;
}

public Instant getTimeEnd() {
    return timeEnd;
}

public BatchExecuteHistory timeEnd(Instant timeEnd) {
    this.timeEnd = timeEnd;
    return this;
}

public void setTimeEnd(Instant timeEnd) {
    this.timeEnd = timeEnd;
}

public String getErrorStep() {
    return errorStep;
}

public BatchExecuteHistory errorStep(String errorStep) {
    this.errorStep = errorStep;
    return this;
}

public void setErrorStep(String errorStep) {
    this.errorStep = errorStep;
}

public String getErrorContent() {
    return errorContent;
}

public BatchExecuteHistory errorContent(String errorContent) {
    this.errorContent = errorContent;
    return this;
}

public void setErrorContent(String errorContent) {
    this.errorContent = errorContent;
}

public Long getRowInput() {
    return rowInput;
}

public BatchExecuteHistory rowInput(Long rowInput) {
    this.rowInput = rowInput;
    return this;
}

public void setRowInput(Long rowInput) {
    this.rowInput = rowInput;
}

@Override
public int hashCode() {
    final int prime = 31;
    int result = 1;
    result = prime * result + ((batchExecuteHistoryId == null) ? 0 : batchExecuteHistoryId.hashCode());
    result = prime * result + ((batchName == null) ? 0 : batchName.hashCode());
    result = prime * result + ((errorContent == null) ? 0 : errorContent.hashCode());
    result = prime * result + ((errorStep == null) ? 0 : errorStep.hashCode());
    result = prime * result + ((rowInput == null) ? 0 : rowInput.hashCode());
    result = prime * result + ((status == null) ? 0 : status.hashCode());
    result = prime * result + ((timeEnd == null) ? 0 : timeEnd.hashCode());
    return result;
}

@Override
public boolean equals(Object obj) {
    if (this == obj)
        return true;
    if (obj == null)
        return false;
    if (getClass() != obj.getClass())
        return false;
    BatchExecuteHistory other = (BatchExecuteHistory) obj;
    if (batchExecuteHistoryId == null) {
        if (other.batchExecuteHistoryId != null)
            return false;
    } else if (!batchExecuteHistoryId.equals(other.batchExecuteHistoryId))
        return false;
    if (batchName == null) {
        if (other.batchName != null)
            return false;
    } else if (!batchName.equals(other.batchName))
        return false;
    if (errorContent == null) {
        if (other.errorContent != null)
            return false;
    } else if (!errorContent.equals(other.errorContent))
        return false;
    if (errorStep == null) {
        if (other.errorStep != null)
            return false;
    } else if (!errorStep.equals(other.errorStep))
        return false;
    if (rowInput == null) {
        if (other.rowInput != null)
            return false;
    } else if (!rowInput.equals(other.rowInput))
        return false;
    if (status == null) {
        if (other.status != null)
            return false;
    } else if (!status.equals(other.status))
        return false;
    if (timeEnd == null) {
        if (other.timeEnd != null)
            return false;
    } else if (!timeEnd.equals(other.timeEnd))
        return false;
    return true;
}

@Override
public String toString() {
    return "BatchExecuteHistory [" + batchExecuteHistoryId.toString() + ", batchName=" + batchName + ", status="
            + status + ", timeEnd=" + timeEnd + ", errorStep=" + errorStep + ", errorContent=" + errorContent
            + ", rowInput=" + rowInput + "]";
}

}

score 2 · Answer 1 · answered Apr 11 '18 at 04:33

2

You should add a By before OrderBy, so your method should be: findByBatchExecuteHistoryIdBatchIdByOrderByTimeEndAsc

answered Apr 11 '18 at 04:33

Luay Abdulraheem

751
3
11

Also have a look at: https://stackoverflow.com/questions/25486583/how-to-use-orderby-with-findall-in-spring-data – Luay Abdulraheem Apr 11 '18 at 10:27
2

Invalid derived query! No property by found for type String! Traversed path: BatchExecuteHistory.batchExecuteHistoryId.batchId. – Nguyen Hoang Vu Apr 12 '18 at 04:27

score 1 · Answer 2 · answered Nov 23 '21 at 06:55

I think you don't miss naming.See Spring document

OrderBy

findByAgeOrderByLastnameDesc

… where x.age = ?1 order by x.lastname desc

It works well in my environment. I am using 2.6.0 version.
I recommand you check jpa version.and I can't see your BatchExecuteHistoryId class.

Anyway Try it for checking it works well in your environment.

Database

CREATE TABLE MEMBER 
(
 id character varying(10),
 batch_id character varying(10),
 create_datetime timestamp without time zone NOT NULL,
 CONSTRAINT MEMBER_PK
 PRIMARY KEY (id,batch_id)
);
INSERT into MEMBER (id,create_datetime,batch_id) VALUES ('USER1','2021/11/20 14:00:00','1');
INSERT into MEMBER (id,create_datetime,batch_id) VALUES ('USER2','2021/11/15 14:00:00','1');
INSERT into MEMBER (id,create_datetime,batch_id) VALUES ('USER3','2021/11/10 14:00:00','1');

Entity

@Entity(name = "Member")
@Table(name = "member")
@ToString
@Data
public class MemberEntity {

    @EmbeddedId
    private MemberPk batchExecuteHistoryId;
    
    @Column(name = "create_datetime")
    private LocalDateTime createDateTime;
}

Pk of Entity

@Embeddable
@ToString
public class MemberPk implements Serializable {
    
    private static final long serialVersionUID = 1L;
    
    private String id; 
    
    @Column(name = "batch_id")
    private String batchId;
}

Repository

@Repository
public interface MemberRepository extends JpaRepository<MemberEntity, String> {
    
    public List<MemberEntity> findByBatchExecuteHistoryIdBatchIdOrderByCreateDateTimeAsc(String batchId, Pageable pageable);

}

Service

@Service
public class MemberService {

    private final MemberRepository memberRepository;

    @Autowired
    public MemberService(MemberRepository memberRepository) {
        this.memberRepository = memberRepository;
    }
    
    @PostConstruct
    public void findAllMember() {
        List<MemberEntity> memberEntitys = memberRepository.findAll();
        
        //MemberEntity(batchExecuteHistoryId=MemberPk(id=USER1, batchId=1), createDateTime=2021-11-20T14:00)
        //MemberEntity(batchExecuteHistoryId=MemberPk(id=USER2, batchId=1), createDateTime=2021-11-15T14:00)
        //MemberEntity(batchExecuteHistoryId=MemberPk(id=USER3, batchId=1), createDateTime=2021-11-10T14:00)
        memberEntitys.forEach(System.out::println); 
        
        System.out.println("----------------------------");
        
        //MemberEntity(batchExecuteHistoryId=MemberPk(id=USER3, batchId=1), createDateTime=2021-11-10T14:00)
        //MemberEntity(batchExecuteHistoryId=MemberPk(id=USER2, batchId=1), createDateTime=2021-11-15T14:00)
        List<MemberEntity> memberEntitysWithOrderBy = memberRepository.findByBatchExecuteHistoryIdBatchIdOrderByCreateDateTimeAsc("1",PageRequest.of(0, 2));
        
        memberEntitysWithOrderBy.forEach(System.out::println);
    }
}

Use OrderBy with JPARepository

2 Answers2