Postgres stored procedure call from java, table to return

Question

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

The second call isn't going to work because you're not using a ref_cursor in your function (and to be picky, you're not calling a stored procedure, you're calling a function. stored procs are new in pgsql 11 and don't return anything). — jkratz
– jkratz, Commented Oct 13, 2018 at 14:52

Renan Ribeiro · Accepted Answer · 2018-10-05 01:59:01Z

You can try using CallableStatement. Assuming that your Connection var is OK:

CallableStatement stmt = con.prepareCall("{call SCHEMA.PROCEDURE_NAME (?, ?)}");
stmt.setInt(1, custom_var);
stmt.registerOutParameter(2, OracleTypes.INTEGER);
stmt.execute();

To get result: stmt.getInt(3); stmt.getString(4)

If you can't succeed try using JdbcTemplate:

SimpleJdbcCall call = new SimpleJdbcCall(this.jdbcTemplate).withSchemaName(SCHEMA).withProcedureName(PROC);
MapSqlParameterSource params = new MapSqlParameterSource();
params.addValue("ipUserId", custom_var);
Map out = call.execute(params);

To get single result: Integer.parseInt("" + out.get("OUT_PARAM_NAME")); (String) out.get("OUT_PARAM_NAME2"));

Or you can save all the result in a list to work on it later:

SimpleJdbcCall call = new SimpleJdbcCall(this.jdbcTemplate);
List<Map<String, Object>> rows = call.getJdbcTemplate().queryForList(PROC_STRING, new Object[] { param1, param2 });

Bogdan · Accepted Answer · 2018-10-05 12:07:29Z

1

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();

answered Oct 5, 2018 at 12:07

Bogdan

3002 silver badges10 bronze badges

Comments

chvndb · Accepted Answer · 2018-11-14 11:00:41Z

0

Why not use getResultList on StoredProcedureQuery? This avoids having to do the string manipulation.

List<User> users = entityManager.createStoredProcedureQuery("GetUser")
  .registerStoredProcedureParameter("ipUserId", Integer.class, ParameterMode.IN)
  .setParameter("ipUserId", ipIdUser)
  .getResultList();

answered Nov 14, 2018 at 11:00

chvndb

7458 silver badges19 bronze badges

Comments

Lukas Eder · Accepted Answer · 2022-08-25 14:45:03Z

In your case, you have a table valued function that happens to map to an actual table, so the approach using the JPA native query is elegant too, but perhaps your projection is something different, or you return multiple nested data structures, arrays, etc, where working with JPA might be tricky.

jOOQ generates code for all of your procedures, including table valued functions. For example, you'll have a Routines class containing a getUser method, which you can query like this:

Result<GetUser> result = Routines.getUser(configuration, 1);

You can also use the table valued function as an actual table in the query, like this:

Result<GetUser> result = ctx.selectFrom(GETUSER(1)).fetch();

Disclaimer: I work for the company behind jOOQ.

Collectives™ on Stack Overflow

Postgres stored procedure call from java, table to return

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related