How to call a stored procedure in Hibernate?

Question

I have a stored procedure in Oracle database.

GET_VENDOR_STATUS_COUNT(DOCUMENT_ID IN NUMBER, NOT_INVITED OUT NUMBER, INVITE_WITHDRAWN OUT NUMBER, ...

Other parameters are OUT parameters.

In my hbm file I have written the following:

<sql-query name="getVendorStatus" callable="true">
     <return-scalar column="NOT_INVITED" type="string"/>
     <return-scalar column="INVITE_WITHDRAWN" type="string"/>
     <return-scalar column="INVITED" type="string"/>
     <return-scalar column="DISQUALIFIED" type="string"/>
     <return-scalar column="RESPONSE_AWAITED" type="string"/>
     <return-scalar column="RESPONSE_IN_PROGRESS" type="string"/>
     <return-scalar column="RESPONSE_RECEIVED" type="string"/>
     { call GET_VENDOR_STATUS_COUNT(:DOCUMENT_ID , :NOT_INVITED ,:INVITE_WITHDRAWN ,:INVITED ,:DISQUALIFIED ,:RESPONSE_AWAITED ,:RESPONSE_IN_PROGRESS ,:RESPONSE_RECEIVED ) }
</sql-query>

And here is my Java code:

 session.getNamedQuery("getVendorStatus").setParameter("DOCUMENT_ID", "DOCUMENT_ID").setParameter("NOT_INVITED", "NOT_INVITED") 

... continue till all the parameters.

I am getting the following SQL exception:

18:29:33,056 WARN [JDBCExceptionReporter] SQL Error: 1006, SQLState: 72000
18:29:33,056 ERROR [JDBCExceptionReporter] ORA-01006: bind variable does not exist

Please let me know what is the exact process of calling a stored procedure in Hibernate? I do not want to use JDBC callable statement.

Solution 1

What you are trying (INOUT/OUT parameter handling) is not supported in 4.1. Through 4.1 Hibernate's callable statement support focuses on ResultSet returns. There is support for what you are trying already in place on upstream master and will be part of the next major release of Hibernate (which will be either 4.2 or 5.0); there, calling functions/procedures is now a first class operation.

For now, you have to either use JDBC directly or build Hibernate from master and use that new support. If you opt for the later, it would look like:

StoredProcedureCall call = session.createStoredProcedureCall( "GET_VENDOR_STATUS_COUNT" )
        .registerStoredProcedureParameter( "DOCUMENT_ID", Long.class, ParameterMode.IN )
        .registerStoredProcedureParameter( "NOT_INVITED", String.class, ParameterMode.OUT )
        ...;
call.getRegisteredParameter( "DOCUMENT_ID" ).bindValue( theDocumentId );
StoredProcedureOutputs outputs = call.getOutputs();
String notInvited = (String) outputs.getOutputParameterValue( "NOT_INVITED" );
...

That code is still young and will likely change. For example, as I write these examples more often I think that registerStoredProcedureParameter should be renamed as registerParameter or declareParameter and that it should return a typed representation of the declaration/registration; something like:

interface RegisteredParameter<T> {
    Class<T> getParameterType();
    // only valid for IN or INOUT params
    void bindValue(T value);
}

<T> RegisteredParameter<T> registerParameter(String name, Class<T> type, ParameterMode mode);

which would then allow:

StoredProcedureCall call = session.createStoredProcedureCall( "GET_VENDOR_STATUS_COUNT" )
call.registerParameter( "DOCUMENT_ID", Long.class, ParameterMode.IN ).bindValue( theDocumentId );
RegisteredParameter<String> notInvitedParam = call.registerParameter( "NOT_INVITED", String.class, ParameterMode.OUT );
...
String notInvited = outputs.getOutputParameterValue( notInvitedParam );

As an added bonus, people trying this out early get to help shape what this looks like prior to it being released (at which point it is much harder to change).

Solution 2

With Hibernate you have to apply a result class to transform the SP results to, and the names of the columns that return have to be aliased to the correct return fields in your result class. Hibernate wants SQLServer stored procs to have a single return result and it wants to know what kind of Object to create. Where I work we usually return a single row result of two columns: return_code and message.

For example...

• return_code = 404, message = "Page not found"

• return_code = 200, message = "OK"

The class is mapped like any other POJO, just be sure to make it Serializeable. For example:

@Entity
public class StoredProc implements Serializable {

  private Integer returnCode;
  private String message;

  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  @Column(name = "return_code", nullable = false, unique = true)
  public Integer getReturnCode() {
    return returnCode;
  }
  public void setReturnCode(Integer returnCode) {
    this.returnCode = returnCode;
  }

  @Column(name = "message")
  public String getMessage() {
    return message;
  }
  public void setMessage(String message) {
    this.message = message;
  }

}

I also seem to recall some general hokeyness with regard to the naming convention Hibernate uses. I think names_with_underscores are translated to camelCaseFieldNames for instance.

Comments

[Steve Ebersole]

I should clarify when I say about INOUT/OUT parameter handling not being supported currently. You can in fact have INOUT/OUT parameters, they just have to return ResultSets and you can have only one parameter returning results per call.

[Yasitha Waduge]

Thanks for the information provided in the answer, Seems above mentioned features available on latest hibernate version (4.3.6). It works perfectly when stored procedure returns one resultset, but multiple result sets there are issues, could you please point out URL for above features documentation. I can't find on google.