Can Spring Data Pageables be used with raw JDBC queries?

Question

Spring Boot here. I've seen Pageable used with repository methods like so:

@Repository
public interface SomeThingRepository extends JpaRepository<SomeThing, Long> {
    List<SomeThing> findByClientName(String clientName, Pageable pageable);
}

However I have a situation where I don't have the ability to use a repository class/method and instead am working with the raw DataSource under the hood.

Is there any way to make Pageable work with JDBC DataSource?

String ctQuery = "SELECT blah blah blah";

Connection conn = null;
PreparedStatement statement = null;
ResultSet rs = null;
try {

    DataSource ds = getDataSource();
    conn = ds.getConnection();

    // anyway to infuse/inject a Pageable here?
    statement = conn.prepareStatement(ctQuery);
    statement.setString(1, clientName);
    rs = statement.executeQuery();

    rs.next();

    // process rs here

} catch (SQLException sqlException) {
    log.error(ExceptionUtils.getStackTrace(sqlException));
    // etc...omitted for brevity
} finally {
    // etc...omitted for brevity
}

Thanks in advance!

Answer 1

You need to put the limits in your SQL. Sadly each DB has its own keywords to do this - see here

For Oracle, do this:

SELECT blah blah blah

WHERE ROWNUM >= :pageSize * (:pageNumber - 1)
AND ROWNUM < :pageSize * :pageNumber