How to get the start_time of the last completed job in Spring Batch?

Question

Suppose these are my job runs:

job_id | job_status
-------------------
1        COMPLETED
2        COMPLETED
3        FAILED
4        COMPLETED
5        FAILED

Now during the runtime of job 6, I want to find out the start_time of the last successfully completed job i.e. job 4

How can I get it?

I tried using the JobExplorer API but it gives output based on input count

This will not work for me if there is a COMPLETED job followed by 10 FAILED jobs. I will not get the COMPLETED job then.

List<JobInstance> jobInstances = jobExplorer.getJobInstances("myJob", 0, 10);

Optional<JobInstance> lastCompletedJobInstance =
jobInstances.stream()
            .filter(jobInstance -> {
                long instanceId = jobInstance.getInstanceId();
                return BatchStatus.COMPLETED == jobExplorer.getJobExecution(instanceId).getStatus();
             })
            .findFirst();

The reason why I need is because in my ItemReader, I want to fetch delta records from my database using a query similar to this:

SELECT * FROM items i WHERE i.created_at >= :lastSuccessfulJobRun

I don't want to manually query Batch tables like batch_job_execution, batch_job_execution_context etc

Answer 1

I had to eventually write a SQL query myself:

SELECT job_execution_id, start_time, end_time, status
FROM
batch_job_execution
WHERE
job_instance_id IN (SELECT job_instance_id FROM batch_job_instance WHERE job_name = :jobName)
AND 
status = :status
ORDER BY job_execution_id DESC LIMIT 1;