Initial addition of cursored pagination for SQL

[andriy-dmytruk]

This is an initial PR to check if this approach could be considered.

• Create the CursoredPageable type.
• Modify the DefaultSqlPreparedQuery to support cursored pageable for SQL. Since cursored pagination requires a new query part with parameters, the type was modified with additional query bindings.
• Modify DefaultFindPageInterceptor to return the correct pageable for further pagination in the cursored case.

Other interceptors will further need to be changed for this to work in all cases, like the FindPageSpecificationInterceptor. Additionally, the cursor could be encoded to base 64 instead of a JSON array. It seems like Pageabe.hasNext() method would also be beneficial. Should that be added to both offset and cursor pagination?

[graemerocher]

Could you look at the Jakarta Data spec https://github.com/jakartaee/data/blob/main/api/src/main/java/jakarta/data/page/CursoredPage.java

And make sure we mirror that implementation as closely as possible

[dstepanov]

Please target 4.8.x with adjusted @since

[andriy-dmytruk]

Do you know how I can solve this error?

io.micronaut.context.exceptions.BeanInstantiationException: Bean definition [io.micronaut.data.jdbc.config.SchemaGenerator] could not be loaded: Error instantiating bean of type  [io.micronaut.data.jdbc.config.SchemaGenerator]

Message: Unable to create database schema: FATAL: sorry, too many clients already
Path Taken: new SchemaGenerator(List configurations,JdbcSchemaHandler schemaHandler)

[dstepanov]

Looks like DB is out of connections. How do you get it?

[andriy-dmytruk]

Looks like DB is out of connections. How do you get it?

The AbstractCursoredPageSpec has a few parametrized tests that perhaps create many connections. But this error appears only when testing with PostgreSQL.

[andriy-dmytruk]

And make sure we mirror that implementation as closely as possible

I added similar Page.hasNext() and Page.hasPrevious() methods.

[dstepanov]

Can you please make an API closer to Jakarta Data? We should have similar API as https://github.com/jakartaee/data/blob/main/api/src/main/java/jakarta/data/page/PageRequest.java New class Pageable.Cursor methods afterCursor, beforeCursor, Optional<Cursor> cursor();, Mode.

It would be nice to add requestTotal, withoutTotal, withoutTotal with a logic it has - retrieving a total from the DB or not.

[dstepanov]

Can you also add tests for R2dbc and Hibernate? Not sure how it should behave for other implementations like Mongo, maybe it should throw an error

[dstepanov]

@andriy-dmytruk Let's try to finish this PR, the only thing that is missing is the documentation and maybe the changes you mentioned regarding the DefaultCursoredPage

[sonarqubecloud[bot]]

Quality Gate failed

Failed conditions
3 New Bugs (required ≤ 0)
2 New Blocker Issues (required ≤ 0)
7 New Critical Issues (required ≤ 0)

See analysis details on SonarCloud

Catch issues before they fail your Quality Gate with our IDE extension SonarLint

[andriy-dmytruk]

@dstepanov and @sdelamo could you take another look?

[dstepanov]

@sdelamo It would be nice to show how to propagate the cursor to the FE