How to call an Oracle function from Hibernate with a return parameter?

Question

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?

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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