How can we call a stored procedure with Hibernate and JPA?
Solution 1
You can do the following
Session session = sessionFactory.openSession();
Transaction tx = session.beginTransaction();
PreparedStatement st = session.connection().prepareStatement("{call procedureName(?, ?)}");
st.setString(1, formatter.format(parameter1));
st.setString(2, formatter.format(parameter2));
st.execute();
tx.commit();
Please add the exception handling wherever required.
Solution 2
Considering the following stored procedure that simply returns a basic return value:
CREATE OR REPLACE PROCEDURE count_comments (
postId IN NUMBER,
commentCount OUT NUMBER )
AS
BEGIN
SELECT COUNT(*) INTO commentCount
FROM post_comment
WHERE post_id = postId;
END;
You can call this one with standard JPA:
StoredProcedureQuery query = entityManager
.createStoredProcedureQuery("count_comments")
.registerStoredProcedureParameter(1, Long.class,
ParameterMode.IN)
.registerStoredProcedureParameter(2, Long.class,
ParameterMode.OUT)
.setParameter(1, 1L);
query.execute();
Long commentCount = (Long) query.getOutputParameterValue(2);
If the stored procedure returns a SYS_REFCURSOR:
CREATE OR REPLACE PROCEDURE post_comments (
postId IN NUMBER,
postComments OUT SYS_REFCURSOR )
AS
BEGIN
OPEN postComments FOR
SELECT *
FROM post_comment
WHERE post_id = postId;
END;
You can call it like this:
StoredProcedureQuery query = entityManager
.createStoredProcedureQuery("post_comments")
.registerStoredProcedureParameter(1, Long.class,
ParameterMode.IN)
.registerStoredProcedureParameter(2, Class.class,
ParameterMode.REF_CURSOR)
.setParameter(1, 1L);
query.execute();
List<Object[]> postComments = query.getResultList();
If you want to call an Oracle database function:
CREATE OR REPLACE FUNCTION fn_count_comments (
postId IN NUMBER )
RETURN NUMBER
IS
commentCount NUMBER;
BEGIN
SELECT COUNT(*) INTO commentCount
FROM post_comment
WHERE post_id = postId;
RETURN( commentCount );
END;
You can't use the StoredProcedureQuery
since it does not work with Hibernate 5, so you can call it like this:
BigDecimal commentCount = (BigDecimal) entityManager
.createNativeQuery(
"SELECT fn_count_comments(:postId) FROM DUAL"
)
.setParameter("postId", 1L)
.getSingleResult();
or with plain JDBC:
Session session = entityManager.unwrap( Session.class );
Integer commentCount = session.doReturningWork( connection -> {
try (CallableStatement function = connection.prepareCall(
"{ ? = call fn_count_comments(?) }" )) {
function.registerOutParameter( 1, Types.INTEGER );
function.setInt( 2, 1 );
function.execute();
return function.getInt( 1 );
}
} );
For more details check out the following articles:
- How to call Oracle stored procedures and functions with JPA and Hibernate
- How to call SQL Server stored procedures and functions with JPA and Hibernate
- How to call PostgreSQL functions (stored procedures) with JPA and Hibernate
- How to call MySQL stored procedures and functions with JPA and Hibernate
Solution 3
To execute remote procedure use this construction:
Mapping
<sql-query name="RP">
{call some_rp(:param1, :param2)}
</sql-query>
Java code
session.getNamedQuery("RP").setInteger("param1", 1).setInteger("param2", 2).executeUpdate();
Solution 4
One way to call the stored procedure from hibernate
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());
}
This works
Comments
-
kandarp almost 2 years
How can we call a stored procedure using Hibernate or JPA?
-
Aravind Yarram over 13 yearsYou are retrieving the connection from hibernate session and then using plain JDBC. This code is not taking advantage of Hibernate support.
-
rogue lad over 8 yearsIs this method compatible for both RDBMs (Oracle and Sql Server) ?
-
mjalil over 4 yearsNotice that JPA 2.1 has
EntityManager.createStoredProcedureQuery
method and it will not resolve if JPA 2.0 was in your classpath stackoverflow.com/a/34174204/402488