I am facing an issue after migrating from Spring Boot 2.5 to 3.2.0 where I am not able to call Postgresql Function using entityManager.createStoredProcedureQuery() .
My Postgre Function returns a table:
CREATE OR REPLACE FUNCTION abc.my_func(
v_response bigint)
RETURNS TABLE(variableA bigint, variableB text, variableC bigint, phone character varying)
LANGUAGE 'plpgsql' ..........
The function calling code:
StoredProcedureQuery query = entityManager
.createStoredProcedureQuery("abc.my_func", ResultTable.class)
.registerStoredProcedureParameter(1, Long.class, ParameterMode.IN)
.setParameter(1, Long.valueOf(myvariable));
try{
query.getResultList().forEach(i->results.add((ResultTable)i));
}
This above code is working in SpringBoot 2.5 using Hibernate 5 but in Hibernate 6 I am getting below error,
ERROR: abc.my_func(bigint) is not a procedure
Hint: To call a function, use SELECT.
Position: 6
After some scrolling through the internet, I came across the below post where user is facing the same issue but in Spring boot 3.1.2, and as a workaround he is setting hibernate dialect to org.hibernate.dialect.PostgreSQL10Dialect which resolves the issue. https://stackoverflow.com/questions/76927479/postgresql-hibernate-6-store-function-cannot-call But in my case, in SB 3.2 org.hibernate.dialect.PostgreSQL10Dialect has been depricated and I am getting ClassNotFoundException if I set it. I even tried using entityManager.createNativeQuery and @Query and tried to directly run the query and store the result in ResultTable. But I am getting exceptions with Mapping the result to my Entity class.
Excepting using @Query
java.lang.ClassCastException: class org.postgresql.util.PGobject cannot be cast to class com.sample.dto.ResultTable (org.postgresql.util.PGobject and com.sample.dto.ResultTable are in unnamed module of loader 'app')
Exception using createNativeQuery (variableA is the Id for the entity class ResultTable )
org.hibernate.exception.SQLGrammarException: Unable to find column position by name: variableA [The column name variableA was not found in this ResultSet.] [n/a]
I even tried @ SqlResultSetMapping but still error
org.hibernate.query.results.MissingSqlSelectionException: ResultSet mapping specified selected-alias `variableA` which was not part of the ResultSet
Can someone please help who has already implemented this, I am totally stuck on this unable to find any reference. I am using postgresql 12 database provided by Google Cloud SQL and need to find a soln for version 12 only, cant upgrade SQL version.
Thanks to Adrian I was able to resolve this issue by calling the function using,
Query query=em.createNativeQuery("select * from my_func(:resp)", ResultTable.class).setParameter("resp", Long.valueOf(myvariable));
List<ResultTable> res =query.getResultList();