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?
CodePudding user response:
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.