JPA repository OneToMany mapping with @Query condition on child column-CodePudding

JobName id is primary and is mapped with JobStatus jobid

public class JobName implements Serializable {

    private static final long serialVersionUID = 1;

    @Id
    @Column(name = "ID")
    private Integer id;

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

    @Column(name = "SCHEDULEDTIME")
    private String scheduledTime;

    @Column(name = "THRESHOLD")
    private Integer threshold;

    @Column(name = "TYPE")
    private String type;

    @OneToMany(mappedBy = "jobName", fetch = FetchType.EAGER)
    private List<JobStatus> jobStatus;

}

public class JobStatus implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @Column(name = "ID")
    private Integer statusId;

    @Column(name = "STARTDATE")
    private Timestamp startDate;

    @Column(name = "ENDDATE")
    private Timestamp endDate;

    @Column(name = "STATUS")
    private String status;

    @Column(name = "REMARKS")
    private String remarks;

    @ManyToOne
    @JoinColumn(name = "JOBID")
    @JsonIgnore
    private JobName jobName;

}

@Repository
public interface JobRepository extends JpaRepository<JobName, Integer> {

    @Query("select j from JobName j join j.jobStatus s where s.startDate> :fromDate")
    public List<JobName> findJobNameBylastTime(@Param("fromDate") Timestamp fromDate);

}

I want to fetch all records from jobstatus table where startDate is greater than the date sent.

I want response in below json format, I am getting jobName list size correct based on date passed but all jobStatus records present in DB are fetched.

    {
        "id":"",   
        "jobName": "",
        "scheduledTime": "",
        "threshold": "",
        "type": "",
        "jobStatus": [
            {
                "statusId":"", 
                "startDate": "",
                "endDate": "",
                "status": "",
                "remarks": ""
            },
            {
                "statusId":"",  
                "startDate": "",
                "endDate": "",
                "status": "",
                "remarks": ""
            }
        ]
    }
]

CodePudding user response：

First, it seems like there is an error in your query, it should be s.startDate > :fromDate instead of s.jobStatus

Then, if you want to query JobStatus entites then you should use a JobStatusRepository as this :

@Repository
public interface JobStatusRepository extends JpaRepository<JobStatus, Integer> {

    public List<JobName> findJobNameByCreationDateGreaterThan(Timestamp fromDate);

}

You shouldn't need a query as the method name is enough, you might want to add a ...Distinct... to avoid duplicates.

CodePudding user response：

Worked by adding FETCH in query

@Repository
public interface JobRepository extends JpaRepository<JobName, Integer> {

    @Query("select j from JobName j join fetch j.jobStatus s where s.startDate > :fromDate")
    public List<JobName> findJobNameBylastTime(@Param("fromDate") Timestamp fromDate);
}