Hibernate call store procedure

Hibernate call store procedure

In Hibernate, there are three approaches to call a database store procedure.

1. Native SQL – createSQLQuery

You can use createSQLQuery() to call a store procedure directly.

Query query = session.createSQLQuery(
 "CALL GetStocks(:stockCode)")
 .addEntity(Stock.class)
 .setParameter("stockCode", "7277");
 
List result = query.list();
for(int i=0; i<result.size(); i++){
 Stock stock = (Stock)result.get(i);
 System.out.println(stock.getStockCode());
}

2. NamedNativeQuery in annotation

Declare your store procedure inside the @NamedNativeQueries annotation.

//Stock.java
...
@NamedNativeQueries({
 @NamedNativeQuery(
 name = "callStockStoreProcedure",
 query = "CALL GetStocks(:stockCode)",
 resultClass = Stock.class
 )
})
@Entity
@Table(name = "stock")
public class Stock implements java.io.Serializable {
...

Call it with getNamedQuery().

Query query = session.getNamedQuery("callStockStoreProcedure")
 .setParameter("stockCode", "7277");
List result = query.list();
for(int i=0; i<result.size(); i++){
 Stock stock = (Stock)result.get(i);
 System.out.println(stock.getStockCode());
}

3. sql-query in XML mapping file

Declare your store procedure inside the “sql-query” tag.

...
>
     name="com.mkyong.common.Stock" table="stock" ...>
         name="stockId" type="java.lang.Integer">
             name="STOCK_ID" />
            <generator class="identity" />
        >
         name="stockCode" type="string">
             name="STOCK_CODE" length="10" not-null="true" unique="true" />
        >
        ...
    >
 
     name="callStockStoreProcedure">
  alias="stock" class="com.mkyong.common.Stock"/>
 
    >
 
>

Call it with getNamedQuery().

Query query = session.getNamedQuery("callStockStoreProcedure")
 .setParameter("stockCode", "7277");
List result = query.list();
for(int i=0; i<result.size(); i++){
 Stock stock = (Stock)result.get(i);
 System.out.println(stock.getStockCode());
}

Conclusion

The above three approaches are doing the same thing, call a store procedure in database. There are not much big different between the three approaches, which method you choose is depend on your personal prefer.