How to call ms sql store procedure using hibernate 4.0

Question

i am working with store procedure i.e

CREATE PROCEDURE test 
(
@INPUTPARAM INT,
@OUTPUTPARAM VARCHAR(20)
)
AS
SELECT @OUTPUTPARAM=S.NAME+','+D.NAME
FROM STUDENT S,DEPARTMENT D
WHERE S.DEPTID=D.DEPARTID AND D.DEPARTID=@INPUTPARAM
BEGIN
END

how to get out parameter from java class using hibernate please share code example

score 1 · Answer 1 · answered Feb 05 '14 at 19:52

1

CREATE PROCEDURE test 
(
@INPUTPARAM INT,
@OUTPUTPARAM VARCHAR(20) OUTPUT --<-- You need to use key word "OUTPUT" here
)
AS
BEGIN

  SELECT @OUTPUTPARAM = S.NAME + ',' + D.NAME
  FROM  STUDENT S INNER JOIN DEPARTMENT D
  ON    S.DEPTID = D.DEPARTID         --<-- Use New Syntax of join with On Clause
  WHERE D.DEPARTID = @INPUTPARAM

END

EXECUTE Procedure

DECLARE @Var VARCHAR(20);
EXECUTE dbo.test 
@INPUTPARAM = 1
@OUTPUTPARAM = @Var OUTPUT --<-- use OUTPUT key word here as well

SELECT  @Var

answered Feb 05 '14 at 19:52

M.Ali

67,945
13
101
127

sorry, it's my typing mistake,i want to call procedure from java code not from query browser – Yash Feb 05 '14 at 19:57
From where ever you call the procedure you need to make the parameter an OUTPUT parameter if you want to retrieve value. – M.Ali Feb 05 '14 at 19:59
out parameter make me confuse when i call store procedure. i have no idea how to register out put parameters using hibernate. – Yash Feb 05 '14 at 20:22

score -1 · Answer 2 · edited May 23 '17 at 12:21

The only way to do it is using em.createNativeQuery and talk directly with you DB Server in SQL.

Update:

Here is, how it could be done:

//get connection from em
Session session = (Session)em.getDelegate();
Connection conn = session.connection();

//Native SQL
final CallableStatement callStmt = conn.prepareCall("{call your.function(?)}");
callStmt.setLong(1, documentId);
callStmt.execute();

if (callStmt.getMoreResults()) {
   ResultSet resSet = cStmt.getResultSet();
   //Do something good with you result
   resSet.close();
}
callStmt.close();

//Don't know if calling conn.close(); is a good idea. Since the session owns it.

Hope that helps a little.

Notes:

If you are using JPA 2.0, you can get the session using

Connection conn = em.unwrap(Session.class).connection();

If you are using JPA 2.1, you can call stored procedures directly

 StoredProcedureQuery query = em.createNamedStoredProcedureQuery("ReadAddressById");
 query.setParameter("P_ADDRESS_ID", 12345);
 List<Address> result = query.getResultList();

maybe [this](http://stackoverflow.com/questions/3572626/calling-stored-procedure-from-java-jpa) can help you. — Hannes, Feb 05 '14 at 20:07

How to call ms sql store procedure using hibernate 4.0

2 Answers2