Map refcursor of PostgreSQL to java entity using Hibernate

Question

I am developing a Java application and recently a colleague advised me that my solution will get neater if I use stored procedures for some of my needs. I started reading about them and they really seem promising, but now I am having hard times at making Hibernate map the result returned from such stored procedure to java bean.

Here is the procedure I am trying to get to:

CREATE OR REPLACE FUNCTION wrong_user_answers(testId INTEGER, userId INTEGER) RETURNS refcursor AS $wrong_user_answers$
   DECLARE
      ref refcursor;
   BEGIN
      OPEN ref FOR SELECT ua.*
      FROM t_tests as t
      JOIN t_user_answers as ua on ua.fk_test_id = t.pk_test_id
      WHERE t.pk_test_id = testId and ua.fk_user_id = userId and is_correct(ua) = false;
      RETURN ref;
   END;
   $wrong_user_answers$ LANGUAGE plpgsql;

Here is_correct(ua) is another stored procedure I have defined. I have tried this procedure in the PGAdmin and it returns exactly what i expect - refcursor containing user answers.

Here is the java part of the code that I thought should get it all rolling:

The additional annotation (@NamedNativeQuery) in the UserAnswerBean:

@NamedNativeQueries({ 
       @NamedNativeQuery(name = "getWrongUserAnswers", 
                         query = "select wrong_user_answers(:testId, :userId)", 
                         resultClass = UserAnswerBean.class) 
})
@Entity
@Table(name = "t_user_answers")
public class UserAnswerBean {

And the code that uses the procedure:

Query query = getEntityManager().createNamedQuery("getWrongUserAnswers");
query.setParameter("testId", testId);
query.setParameter("userId", userId);
List<UserAnswerBean> answers = query.getResultList();

When the code execution attempts on the last line above the following erorr is received:

org.postgresql.util.PSQLException: The column name pk_user_answer_id was not found in this ResultSet. org.postgresql.jdbc2.AbstractJdbc2ResultSet.findColumn(AbstractJdbc2ResultSet.java:2728) org.postgresql.jdbc2.AbstractJdbc2ResultSet.getInt(AbstractJdbc2ResultSet.java:2589) org.hibernate.type.descriptor.sql.IntegerTypeDescriptor$2.doExtract(IntegerTypeDescriptor.java:74) org.hibernate.type.descriptor.sql.BasicExtractor.extract(BasicExtractor.java:64) org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:267) org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:263) org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:253) org.hibernate.type.AbstractStandardBasicType.hydrate(AbstractStandardBasicType.java:338) org.hibernate.loader.Loader.extractKeysFromResultSet(Loader.java:784) org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:720) org.hibernate.loader.Loader.processResultSet(Loader.java:952)

I did not know how to proceed here, I could not find any useful resource in internet. So I assumed that the problem was that I was returning refcursor, not immediately the result of a select, so I changed the stored procedure to:

CREATE OR REPLACE FUNCTION wrong_user_answers(testId INTEGER, userId INTEGER) RETURNS setof record AS $wrong_user_answers$
   SELECT ua.*
      FROM t_tests as t
      JOIN t_user_answers as ua on ua.fk_test_id = t.pk_test_id
      WHERE t.pk_test_id = testId and ua.fk_user_id = userId and is_correct(ua) = false;
   $wrong_user_answers$ LANGUAGE sql;

Regretfully this did not change the error.

And finally I got to know about the @NamedStoredProcedureQuery annotation:

@NamedStoredProcedureQuery(name = "getWrongUserAnswers", 
                           procedureName = "wrong_user_answers", parameters = {
                           @StoredProcedureParameter(name = "testId", type = Integer.class, mode = ParameterMode.IN),
                           @StoredProcedureParameter(name = "userId", type = Integer.class, mode = ParameterMode.IN) },
                           resultClasses = UserAnswerBean.class)
@Entity
@Table(name = "t_user_answers")
public class UserAnswerBean {

With the changed java code:

    Query query = getEntityManager().createNamedStoredProcedureQuery("getWrongUserAnswers");
    query.setParameter("testId", testId);
    query.setParameter("userId", userId);
    List<UserAnswerBean> answers = query.getResultList();

This did not chnage the error either! Can somebody help me figure out what i am doing wrong?

I'd be quite surprised if Hibernate played well with refcursors, except perhaps if you do native query FETCH statements rather than expecting it to understand them as result sets. — Craig Ringer
– Craig Ringer, Commented Dec 27, 2014 at 16:27
See my second attempt - no refcursor used there. BTW i think i saw somewhere that hibernate digests refcursors without a trouble, probably playing smart and fetching on them — Boris Strandjev
– Boris Strandjev, Commented Dec 27, 2014 at 16:50
It's actually PgJDBC that special-cases refcursor in some contexts, treating it as multiple result sets via the normal jdbc APIs. — Craig Ringer
– Craig Ringer, Commented Dec 27, 2014 at 18:06

Boris Strandjev · Accepted Answer · 2014-12-30 16:25:49Z

Fainally I was able to resolve my problem.

I think I might have reported wrong error on the second approach. I basically did not have any luck in the fight with refcursors, but using LANGUAGE sql gave me what I wanted. That is the stored procedure:

CREATE OR REPLACE FUNCTION wrong_user_answers(testId INTEGER, userId INTEGER) 
   RETURNS setof t_user_answers 
   AS $wrong_user_answers$
   SELECT ua.*
      FROM t_tests as t
      JOIN t_user_answers as ua on ua.fk_test_id = t.pk_test_id
      WHERE t.pk_test_id = testId and ua.fk_user_id = userId and is_correct(ua) = false;
   $wrong_user_answers$ LANGUAGE sql;

The only difference from before being that I have explicitly specified the return type to be setof t_user_answers. The annotations and the java code is exactly as pasted in the question and now everything works for me.

PS: For those wondering I had even harder times to get everything going when I had the result to be no-DB entity, but finally I found @SqlResultSetMapping annotation with @ConstructorResult classes subannotation to solve the issue (very hard to find solution though).

Collectives™ on Stack Overflow

Map refcursor of PostgreSQL to java entity using Hibernate

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related