Stored procedures with hibernate

In an earlier post i wrote up an article explaining how to call stored procedures using Spring's StoredProcedureCall template. This i believe is a very clean solution to handle all stored procedure related details. But this article is for those who already use the hibernate template and want to get things done using that without going into much details of using the Spring's StoredProcedureCall.

So i will guide you step by step on how to achieve this.

• Following is a sample stored procedure. Note that this is not a complete proc, but just an extract.

create or replace PROCEDURE         MYSYS_P_MY_TEST_PROC
    ( 
        p_recordset      OUT SYS_REFCURSOR,
        p_airport_iata_id IN varchar2  ,
        p_flight_number   IN varchar2  ,
        p_flight_dep_date IN varchar2  
    )

      One very important thing to note here is that if your using named queries to call stored procedures there is one limitation as specified in the hibernate specification. That is, that the OUT parameter must be the first parameter in your procedure and it should be a SYS_REFCURSOR. Else it would not work.

• Next you need to define an entity representing the data output by your procedure.

@Entity
@NamedNativeQuery(name = "getFlightLoadData", query = "call MYSYS_P_MY_TEST_PROC
(?, :p_airport_iata_id,:p_flight_number,:p_flight_dep_date)", callable = true, resultClass = FlightLoadData.class)
public class FlightLoadData implements Serializable {

 private static final long serialVersionUID = -6259506689335159484L;

 @Id
    private int rownum;

    @Column(name="DEP_IATA_CODE")
    private String depIATACode;

  /**
     * @return the rownum
     */
    public int getRownum() {
        return rownum;
    }

    /**
     * @param rownum the rownum to set
     */
    public void setRownum(int rownum) {
        this.rownum = rownum;
    }

    /**
     * @return the depIATACode
     */
    public String getDepIATACode() {
        return depIATACode;
    }

    /**
     * @param depIATACode the depIATACode to set
     */
    public void setDepIATACode(String depIATACode) {
        this.depIATACode = depIATACode;
    }

}

Although this is not an entity per se, you need to define it as an entity in order to use it with the hibernate template. Afterwards you define a named native query, give it a name, the question mark as the first parameter is the OUT parameter of the procedure.

One very important thing to note here is the user of @Id annotation. We have given it the name rownum. For us to get the results of the ref cursor and map it to the domain object there has to be a unique identifier within the result set. ROWNUM as defined here says

rownum is a pseudo column. It numbers the records in a result set. The first record that meets the where criteria in a select statement is given rownum=1, and every subsequent record meeting that same criteria increases rownum.

So when you write into your ref cursor make sure you select the rownum as your first column in the result set.

The other two important attributes within the @NamedNativeQuery are;

• callable = true  - Specifies to execute the store procedure.

• resultClass = FlightLoadData.class  - Specifies the result to be mapped to the following DTO.

Finally i will present to you the way you will call this procedure using the hibernate template;

List<FlightLoadData> result = (List<FlightLoadData>) getHibernateTemplate().execute(new HibernateCallback() {

            @Override
            public Object doInHibernate(final Session session) throws HibernateException, SQLException {
                return session.getNamedQuery("getFlightLoadData").setString("p_airport_iata_id", airportIATAID)
                        .setString("p_flight_number", flightNo).setString("p_flight_dep_date", date).list();
            }
        });

Thats it guys. Im sure the last coding snippet is self explanatory and hence i would not delve into the details.

If you do have any queries pls do leave a comment and i will be more than happy to help you.

Cheers!!!!