Improve JdbcJobInstanceDao to Use LIMIT and OFFSET for Efficient Pagination

[zakyvit]

The current implementation of JdbcJobInstanceDao in Spring Batch is too generic and not optimized for scenarios where pagination is needed. Specifically, methods like:

public List<JobInstance> findJobInstancesByName(String jobName, final int start, final int count)
public List<JobInstance> getJobInstances(String jobName, final int start, final int count)

load all matching data from the database and then manually filter it based on the start and count parameters. This approach is inefficient, particularly when dealing with large datasets.

Proposal:
To improve performance, it would be beneficial to provide a more optimized implementation for databases that support LIMIT and OFFSET. These clauses allow for efficient pagination directly at the database level, avoiding the need to load and filter unnecessary data in memory.

Suggested Changes:
Add support for LIMIT and OFFSET in the SQL queries for methods like findJobInstancesByName and getJobInstances when using databases such as:

• MySQL
• PostgreSQL
• H2
• Other databases that natively support LIMIT and OFFSET.

Provide database-specific implementations for JdbcJobInstanceDao or extend the base implementation to use optimized SQL for supported databases.

Retain the existing implementation as a fallback for databases that do not support LIMIT and OFFSET.

Benefits:
Improved Performance: Reduces the amount of data fetched and processed in memory, especially for large datasets.
Better Resource Utilization: Minimizes memory usage and reduces database load by fetching only the required rows.
More Scalable: Makes the framework more efficient for applications with large job histories or high concurrency.
Example:
Instead of:

SELECT JOB_INSTANCE_ID, JOB_NAME 
FROM %PREFIX%JOB_INSTANCE 
WHERE JOB_NAME = ? 
ORDER BY JOB_INSTANCE_ID DESC;

and then manually iterating over the results, the query should be:

SELECT JOB_INSTANCE_ID, JOB_NAME 
FROM %PREFIX%JOB_INSTANCE 
WHERE JOB_NAME = ? 
ORDER BY JOB_INSTANCE_ID DESC 
LIMIT ? OFFSET ?;

Impact:
This change would primarily benefit users of supported databases, improving the performance of pagination-related methods in JdbcJobInstanceDao.

If you find this suggestion useful, I'd be happy to contribute a pull request with an implementation. Let me know your thoughts!