Java. How to call a dB function and handle the cursor?

As a database I use postgresql
There is a function that returns a cursor with the list of goods.
CREATE OR REPLACE FUNCTION get_goods(market_id BIGINT, count BIGINT)
 RETURNS REFCURSOR AS
$BODY$
DECLARE
 post_markets REFCURSOR;
BEGIN
 OPEN post_markets FOR 
 SELECT * 
 FROM goods 
 WHERE goods.marketid = market_id; 
 RETURN post_markets;
END;
$BODY$
LANGUAGE plpgsql

There is a method-side application that pulls this function
private static void getGoods(Long marketId, Long count) {
 StoredProcedureQuery query = getEntityManager().createStoredProcedureQuery("get_goods")
 .registerStoredProcedureParameter(1, Class.class, ParameterMode.REF_CURSOR)
 .registerStoredProcedureParameter(2, Long.class, ParameterMode.IN)
 .registerStoredProcedureParameter(3, Long.class, ParameterMode.IN)
 .setParameter(2, marketId)
 .setParameter(3, count);
query.getResultList();
 }


Now I have a few questions which answers I have not yet found:
1. The method works successfully, but how sumapit output it in a list of objects(List)?
2. What to do if the database returns multiple cursors? How to properly catch?
3. What other ways, in a similar style, you can call the database method and sampit in any list?
March 23rd 20 at 18:46
2 answers
March 23rd 20 at 18:48
Solution
how sumapit output it in a list of objects(List)?

.createStoredProcedureQuery("get_goods", Goods.class)

List<Goods> goods = (List<Goods>) query.getResultList();

What to do if the database returns multiple cursors?

No idea how to call a stored procedure may cause return multiple cursors.

What other ways, in a similar style, you can call the database method and sampit in any list?

Query Query = session.createSQLQuery("CALL get_goods()").addEntity(Goods.class);
List<Goods> goods = query.list();
Triggered the exception. Model marked with the annotation of entity
Caused by: org.hibernate.MappingException: Unknown entity: Goods
 at org.hibernate.internal.SessionFactoryImpl.getEntityPersister(SessionFactoryImpl.java:1096)
 at org.hibernate.loader.custom.sql.SQLQueryReturnProcessor.getSQLLoadable(SQLQueryReturnProcessor.java:374)
 at org.hibernate.loader.custom.sql.SQLQueryReturnProcessor.processRootReturn(SQLQueryReturnProcessor.java:427)
 at org.hibernate.loader.custom.sql.SQLQueryReturnProcessor.processReturn(SQLQueryReturnProcessor.java:394)
 at org.hibernate.loader.custom.sql.SQLQueryReturnProcessor.process(SQLQueryReturnProcessor.java:196)
 at org.hibernate.result.internal.OutputsImpl.buildSpecializedCustomLoader(OutputsImpl.java:245)
 at org.hibernate.result.internal.OutputsImpl.<init>(OutputsImpl.java:66)
 at org.hibernate.procedure.internal.ProcedureOutputsImpl.<init>(ProcedureOutputsImpl.java:49)
 at org.hibernate.procedure.internal.ProcedureCallImpl.buildOutputs(ProcedureCallImpl.java:426)
 at org.hibernate.procedure.internal.ProcedureCallImpl.getOutputs(ProcedureCallImpl.java:378)
 at org.hibernate.jpa.internal.StoredProcedureQueryImpl.outputs(StoredProcedureQueryImpl.java:251)
 at org.hibernate.jpa.internal.StoredProcedureQueryImpl.getResultList(StoredProcedureQueryImpl.java:334)
 ... 2 more
- Kim commented on March 23rd 20 at 18:51
@Kim, well, you is Goods.class put the class that you have describes the return from the procedure value. - Penelope.Morar commented on March 23rd 20 at 18:54
@Penelope.Morar, He describes) - Kim commented on March 23rd 20 at 18:57
So the database can return multiple cursors
CREATE FUNCTION myfunc(refcursor, refcursor) RETURNS SETOF refcursor AS $$
BEGIN
 OPEN $1 FOR SELECT * FROM table_1;
 RETURN NEXT $1;
 OPEN $2 FOR SELECT * FROM table_2;
 RETURN NEXT $2;
END;
$$ LANGUAGE plpgsql;
- Kim commented on March 23rd 20 at 19:00
@Kim, apparently you have it is not a JPA entity. - Penelope.Morar commented on March 23rd 20 at 19:03
March 23rd 20 at 18:50
Solution
Protupil, removed the property of their file persistence.xml
<exclude-unlisted-classes>true</exclude-unlisted-classes>
I thought you were asking how sumapit output it in a list of objects(List). This configuration option is the answer to your question? - horace.Hoppe55 commented on March 23rd 20 at 18:53
@horace.Hoppe55, Yes, I've tried to do as he said @Penelope.Morar, but was in a state of exception because the parameter stood at the true value. Cleaned it and worked as it should) - Kim commented on March 23rd 20 at 18:56

Find more questions by tags HibernateJavaPostgreSQL