19

My question is very much like Getting the return value of a PL/SQL function via Hibernate

I have a function which does some modifications internally and it returns a value.

The original idea was to do something like this:

protected Integer checkXXX(Long id, Long transId)
        throws Exception {
    final String sql = "SELECT MYSCHEMA.MYFUNC(" + id + ", "
            + transId + ") FROM DUAL";
    final BigDecimal nr = (BigDecimal) this.getHibernateTemplate()
            .getSessionFactory().getCurrentSession().createSQLQuery(sql)
            .uniqueResult();
    return nr.intValue();
}

Unfortunately this doesn't work with Oracle. What is the recommended way to do something like this?

Is there a way to extract declared variables from within my statement?

Improve this question

5 Answers 5

Reset to default
37

Hibernate Session provides a doWork() method that gives you direct access to java.sql.Connection. You can then create and use java.sql.CallableStatement to execute your function:

session.doWork(new Work() {
  public void execute(Connection connection) throws SQLException {
    CallableStatement call = connection.prepareCall("{ ? = call MYSCHEMA.MYFUNC(?,?) }");
    call.registerOutParameter( 1, Types.INTEGER ); // or whatever it is
    call.setLong(2, id);
    call.setLong(3, transId);
    call.execute();
    int result = call.getInt(1); // propagate this back to enclosing class
  }
});
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

would you mind to provide an example? I'm a bit lost how to get the result from the function. Do I need to use a out parameter?
I've provided an example above. Depending on your function / stored procedure you may need to use the other invocation form within prepareCall() instead - CallableStatement documentation describes both.
@ChssPly76: I never knew of doWork(..) much better than Hibernate's @NamedNativeQuery which requires the sproc/function to return a refcursor as the first parameter (the out parameter).
@ChssPly76, as I noted below, I didn't copy your answer; I just wrote it at the same time. My post doesn't deserve a down vote.
13

You have the following options:

  1. With a @NamedNativeQuery:

     @org.hibernate.annotations.NamedNativeQuery(
     name = "fn_my_func",
     query = "{ ? = call MYSCHEMA.MYFUNC(?, ?) }",
     callable = true,
     resultClass = Integer.class
 )

 Integer result = (Integer) entityManager.createNamedQuery("fn_my_func")
     .setParameter(1, 1)
     .setParameter(2, 1)
     .getSingleResult();

  2. With JDBC API:

     Session session = entityManager.unwrap( Session.class );

 final AtomicReference<Integer> result = 
     new AtomicReference<>();

 session.doWork( connection -> {
     try (CallableStatement function = connection
             .prepareCall(
                 "{ ? = call MYSCHEMA.MYFUNC(?, ?) }"
             )
         ) {
         function.registerOutParameter( 1, Types.INTEGER );
         function.setInt( 2, 1 );
         function.setInt( 3, 1 );
         function.execute();
         result.set( function.getInt( 1 ) );
     }
 } );

  3. With a native Oracle query:

     Integer result = (Integer) entityManager.createNativeQuery(
     "SELECT MYSCHEMA.MYFUNC(:postId, :transId) FROM DUAL")
     .setParameter("postId", 1)
     .setParameter("transId", 1)
     .getSingleResult();
Improve this answer

4 Comments

When I try the first option, it results with the following exception. Just FYI: org.hibernate.MappingException: Unknown entity: java.lang.Integer
You can fork my book, High-Performance Java Persistence, GitHub Repository and run the test yourself. I've just run it with Hibernate 5.2.11 and it works like a charm.
@VladMihalcea there is no "resultClass=Integer.class" in your test on GitHub
@Alex This answer is for the OP question while my GitHub repository is for different examples and use cases.
6

Yes, you do need to use an out parameter. If you use the doWork() method, you'd do something like this:

session.doWork(new Work() {
   public void execute(Connection conn) {
      CallableStatement stmt = conn.prepareCall("? = call <some function name>(?)");
      stmt.registerOutParameter(1, OracleTypes.INTEGER);
      stmt.setInt(2, <some value>);
      stmt.execute();
      Integer outputValue = stmt.getInt(1);
      // And then you'd do something with this outputValue
   }
});
Improve this answer

3 Comments

@ChssPly76, I wrote it at the same time as you, and after I posted it, yours was already there. Nor is it an exact copy, although of course it is quite similar, since the problem is pretty simple to begin with. It would be nice if you removed your down vote.
The commonly accepted etiquette on SO is to delete your answer if you see that there's another one just like it already unless they both were literally written at the same time. In this case there's a 16 minute difference between mine and yours. If you delete it now, rep lost due to down-vote will be restored to your account during the next rep recalc (they're usually done every 6 to 8 weeks). Down-vote can not be rescinded (it's too old) unless your answer is edited.
Oh. Is it also commonly accepted etiquette to down-vote someone in those cases?
6

Alternative code :)

if you want to direct result you can use below code 

 int result = session.doReturningWork(new ReturningWork<Integer>() {
  @Override
   public Integer  execute(Connection connection) throws SQLException {
    CallableStatement call = connection.prepareCall("{ ? = call MYSCHEMA.MYFUNC(?,?) }");
    call.registerOutParameter( 1, Types.INTEGER ); // or whatever it is
    call.setLong(2, id);
    call.setLong(3, transId);
    call.execute();
    return call.getInt(1); // propagate this back to enclosing class
  }
});

http://keyurj.blogspot.com.tr/2012/12/dowork-in-hibernate.html

Improve this answer

Comments

1 
public static void getThroHibConnTest() throws Exception {
    logger.debug("UsersActiion.getThroHibConnTest() | BEG ");
    Transaction tx = null;
    Connection conn = null;
    CallableStatement cs = null;
    Session session = HibernateUtil.getInstance().getCurrentSession();
    try {
        tx = session.beginTransaction();
        conn = session.connection();

        System.out.println("Connection = "+conn);
        if (cs == null)
        {
            cs = 
                conn.prepareCall("{ ?=call P_TEST.FN_GETSUM(?,?) }");
        }
        cs.clearParameters();
        cs.registerOutParameter(1,OracleTypes.INTEGER);
        cs.setInt(2,1);
        cs.setInt(3,2);
        cs.execute();
        int retInt=cs.getInt(1);
        tx.commit();            
    }catch (Exception ex) {  
        logger.error("UsersActiion.getThroHibConnTest() | ERROR | " , ex);  
        if (tx != null && tx.isActive()) {
            try {
                // Second try catch as the rollback could fail as well
                tx.rollback();
            } catch (HibernateException e1) {
                logger.debug("Error rolling back transaction");
            }
            // throw again the first exception
            throw ex;
        }
    }finally{
        try {
            if (cs != null) {
                cs.close();
                cs = null;
            }
            if(conn!=null)conn.close();

        } catch (Exception ex){;}
    }
    logger.debug("UsersActiion.getThroHibConnTest() | END ");
}
Improve this answer

2 Comments

the connection() method is deprecated in Hibernate 3.3.2GA+
Even connection() is deprecated. In those versions this is the way you can access.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.