JpaRepository - Oracle Pagination - Limit and Offset

Question

I'm trying to use H2 with Oracle mode since I'm using Oracle 11G on my application but I'm getting an error because when I use pagination the query puts limit and offset on sql.

application.properties

....
spring.datasource.driver-class-name = org.h2.Driver
spring.sql.init.platform = h2
spring.datasource.name = CSM_SECURITY
spring.datasource.url = jdbc:h2:mem:CSM_SECURITY;MODE=Oracle;DB_CLOSE_DELAY=-1;INIT=CREATE SCHEMA IF NOT EXISTS CSM_SECURITY
spring.sql.init.mode = embedded
...

JpaRepository method:

@Query(value = """
select distinct new com.closeupinternational.csmsecurity.models.entities.Person
            (p.id, p.email, p.personName, p.sysAdmin, p.defaultLanguage,
             p.wso2Id, p.sysAdminCountry, p.sysAdminCountryId)
        from Person p
            left join CompanyPerson cp on cp.person.id = p.id
            left join CompanyCountry cc on cc.id = cp.companyCountry.id
        where cc.company.id = :companyId and cc.country.id like :countryId and p.personName like :name""",
        countQuery = """
             select count(p) from Person p 
             left join CompanyPerson cp on cp.person.id = p.id left join CompanyCountry cc on cc.id = cp.companyCountry.id 
             where cc.company.id = :companyId and cc.country.id like :countryId and p.personName like :name""")
Optional<Page<Person>> findByNameAndCompanyAndCountry(@Param("name")String name, @Param("countryId") String countryId,
                                                      @Param("companyId") Long companyId, PageRequest page);

I put countQuery but it still uses limit and offset.

Generated query:

select distinct person0_.id as col_0_0_, person0_.email as col_1_0_, person0_.person_name as col_2_0_, person0_.sys_admin as col_3_0_, 
person0_.default_language as col_4_0_, person0_.wso2_id as col_5_0_, person0_.sys_admin_country as col_6_0_, person0_.sys_adm_country_id as col_7_0_ 
from CSM_SECURITY.csm_person person0_ left outer 
join CSM_SECURITY.csm_company_person companyper1_ on (companyper1_.csm_person_id_fk=person0_.id) left outer join CSM_SECURITY.csm_company_country companycou2_ on (companycou2_.id=companyper1_.csm_company_country_id_fk) 
where companycou2_.csm_company_id_fk=? and (companycou2_.csm_country_id_fk like ?) and (person0_.person_name like ?) 
limit ? offset ?

Error:

org.springframework.dao.InvalidDataAccessResourceUsageException: could not prepare statement; SQL [select ... r1_.csm_person_id_fk=person0_.id) 
left outer join CSM_SECURITY.csm_company_country companycou2_ on (companycou2_.id=companyper1_.csm_company_country_id_fk) 
where companycou2_.csm_company_id_fk=? and (companycou2_.csm_country_id_fk like ?) and (person0_.person_name like ?) 
limit ? offset ?]; nested exception is org.hibernate.exception.SQLGrammarException: could not prepare statement

How can I solve to JpaRespository use rownum and offset on Oracle pagination?

Answer 1

As @Evegnij said the problem is that the LIMIT on H2 and Oracle mode is not compatible with Hibernate ORM 5. I've tried to upgrade to hibernate-core 6.1.1.Final but I ran into another problem since spring-boot 2.7.0 is not compatible with this version. So the last solution was to set H2 limit to true by programatically.

@Configuration
@EnableJpaRepositories
@EnableTransactionManagement
public class H2TestJPAConfig {

    @Bean
    public DataSource dataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName("org.h2.Driver");
        dataSource.setUrl("jdbc:h2:mem:CSM_SECURITY;MODE=Oracle;DB_CLOSE_DELAY=-1;INIT=CREATE SCHEMA IF NOT EXISTS CSM_SECURITY");
        dataSource.setUsername("sa");
        dataSource.setPassword("sa");

        // Ref https://groups.google.com/g/h2-database/c/yxnv64Ak-u8/m/n-kqYV_yBQAJ
        org.h2.engine.Mode mode = org.h2.engine.Mode.getInstance("ORACLE");
        mode.limit = true;
        return dataSource;
    }

}

PS: Please note that this workaround uses internals of H2, they may be modified in every version of H2 without a notice.