I am trying to connect to Oracle database in Helidon MP 2.5 and execute a basic findAll() query for finding all entities. I am using Hibernate as the JPA provider. I have a generic abstract CRUD repository that contains the findAll() method:
1. public abstract class CrudRepository<T, ID> implements CrudInterface<T, ID> {
2.
3. protected final Class<T> persistentClass;
4. private final SessionFactory sessionFactory;
5. private static final Logger log = Logger.getLogger(CrudRepository.class.getName());
6.
7. protected CrudRepository(Class<T> persistenceClass, HelidonPersistenceUnitInfo hpuInfo) {
8. this.persistentClass = persistenceClass;
9. this.sessionFactory = new org.hibernate.cfg.Configuration()
10. .addProperties(hpuInfo.getProperties())
11. .addAnnotatedClass(persistenceClass)
12. .buildSessionFactory();
13. }
14.
15. public Page<T> findAll(Pageable pageableObj) {
16. List<T> list;
17. int page = pageableObj.getPageNum();
18. int size = pageableObj.getMaxRows();
19.
20. if(page < 0 || size < 0) {
21. throw new IllegalArgumentException("Invalid values for page and size");
22. }
23.
24. StringBuilder queryStr = new StringBuilder("SELECT e FROM " + persistentClass.getSimpleName() + " e");
25.
26. log.info("SQL QUERY IS: " + queryStr.toString());
27.
28. try (Session session = sessionFactory.openSession()) {
29. Query<T> query = session.createQuery(queryStr.toString(), persistentClass);
30. query.setFirstResult(page * size);
31. query.setMaxResults(size);
32. list = query.getResultList();
33. return new Page<T>(list, pageableObj);
34. } catch (Exception e) {
35. throw new RuntimeException("Failed to find entities", e);
36. }
37.
38. }
39.
40. }
I have a class EmployeeRepository which extends the above CRUD repository. When I execute a test on the findAll() method:
1. @Test
2. void getEmployees() throws Exception {
3. EmployeeRepository empRepo = new EmployeeRepository();
4. Page<Employee> p = empRepo.findAll(new Pageable(0, 20));
5. assertEquals(p.getEntities().size(), 20);
6. }
I get the error:
java.lang.RuntimeException: Failed to find entities
at oal.oracle.apps.ic.helidonmstemplate.CRUD.CrudRepository.findAll(CrudRepository.java:122)
at oal.oracle.apps.ic.helidonmstemplate.application.Employee.EmployeeControllerTest.getEmployees(EmployeeControllerTest.java:32)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
oal.oracle.apps.ic.helidonmstemplate.CRUD.CrudRepository.findAll(CrudRepository.java:119)
... 79 more
Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:63)
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:37)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:67)
at org.hibernate.loader.Loader.getResultSet(Loader.java:2322)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2075)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2037)
at org.hibernate.loader.Loader.doQuery(Loader.java:956)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:357)
at org.hibernate.loader.Loader.doList(Loader.java:2868)
at org.hibernate.loader.Loader.doList(Loader.java:2850)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2682)
at org.hibernate.loader.Loader.list(Loader.java:2677)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:338)
at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:2170)
at org.hibernate.internal.AbstractSharedSessionContract.list(AbstractSharedSessionContract.java:1190)
at org.hibernate.query.internal.NativeQueryImpl.doList(NativeQueryImpl.java:176)
at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1617)
... 81 more
Caused by: java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:509)
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:461)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1104)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:553)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:269)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:655)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:270)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:91)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:807)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:983)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1168)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3666)
at oracle.jdbc.driver.T4CPreparedStatement.executeInternal(T4CPreparedStatement.java:1426)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3713)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1167)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:57)
... 95 more
Caused by: Error : 933, Position : 22, Sql = SELECT e FROM EMPLOYEE e limit :1 , OriginalSql = SELECT e FROM EMPLOYEE e limit ?, Error Msg = ORA-00933: SQL command not properly ended
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:513)
... 110 more
The SQL statement I created in line 24 CrudRepository appears to be correct. I tried to diagnose the issue by adding a semicolon after the statement to end it, but that did not fix the issue. I tried using query.createSQLQuery() instead of createQuery() as well. Any idea what might be causing the issue?
