I made a Postgres stored procedure:
CREATE OR REPLACE FUNCTION GetUser(ipUserId integer)
RETURNS setof users AS $$
BEGIN
IF ipUserId is null THEN
return query select * from users A order by modifieddate desc;
END IF;
return query select * from users where iduser = ipUserId;
END;
$$ LANGUAGE plpgsql;
I tried to use it in java like this:
StoredProcedureQuery query = entityManager.createStoredProcedureQuery("GetUser").
registerStoredProcedureParameter("ipUserId",
Integer.class, ParameterMode.IN)
.registerStoredProcedureParameter("users",
List.class, ParameterMode.OUT)
.setParameter("postId", 1);
or
StoredProcedureQuery query = entityManager.createStoredProcedureQuery("GetUser")
.registerStoredProcedureParameter(1,void.class, ParameterMode.REF_CURSOR)
.registerStoredProcedureParameter(2,Integer.class, ParameterMode.IN)
.setParameter(2, ipIdUser);
I want to store the result in a List.
What and how should i do, because i'm getting all kind of errors?
Update :
Those are the errors :
javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: Error calling CallableStatement.getMoreResults
Caused by: org.hibernate.exception.SQLGrammarException: Error calling CallableStatement.getMoreResults
at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:106)
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111)
at org.hibernate.result.internal.OutputsImpl.convert(OutputsImpl.java:79)
at org.hibernate.result.internal.OutputsImpl.<init>(OutputsImpl.java:56)
at org.hibernate.procedure.internal.ProcedureOutputsImpl.<init>(ProcedureOutputsImpl.java:34)
at org.hibernate.procedure.internal.ProcedureCallImpl.buildOutputs(ProcedureCallImpl.java:453)
at org.hibernate.procedure.internal.ProcedureCallImpl.getOutputs(ProcedureCallImpl.java:404)
at org.hibernate.procedure.internal.ProcedureCallImpl.outputs(ProcedureCallImpl.java:663)
at org.hibernate.procedure.internal.ProcedureCallImpl.getResultList(ProcedureCallImpl.java:751)
... 21 more
Caused by: org.postgresql.util.PSQLException: A CallableStatement was executed with an invalid number of parameters
I found a much simple solution, just make a SQL Query to call the procedure with hibernate.
String SqlString = "select * from GetUser({0})";
if (ipIdUser == null )
SqlString = MessageFormat.format(SqlString, "NULL");
else
SqlString = MessageFormat.format(SqlString, ipIdUser);
LOGGER.info("SqlSting =" + SqlString);
return entityManager.createNativeQuery(SqlString, User.class)
.getResultList();