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!!!!
No comments:
Post a Comment