How to get OUTPUT value from stored procedure in hibernate (SQL SERVER)
14,163
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());
}
Comments
-
Constantine Gladky almost 2 years
I have stored procedure in
MS SQL Server
CREATE PROC dbo.insertData @param1 nvarchar(11), @param2 nvarchar(30), @ID bigint OUTPUT AS BEGIN INSERT INTO dbo.Adaptor (Col1, Col2) VALUES (@param1, @param2) SET @ID = SCOPE_IDENTITY(); END
And I want to get this generated ID as output parameter in java.
I have created a mapping file
<sql-query name="insertData" callable="true"> <return alias="adaptor" class="MyClass"> <return-property name="id" column="ID" /> </return> { ? = call InsertData(:param1, :param2) } </sql-query>
and trying to execute it from java
Query query = entityManager.createNamedQuery("insertData"); query.setParameter("param1", object); query.setParameter("param2", getName()); // TODO try to use getSingleResult in tests List result = query.getResultList();
But I have exception java.sql.SQLException: Parameter #3 has not been set
If I remove ? = I got
java.sql.SQLException: Procedure or function 'insertData' expects parameter '@ID', which was not supplied.
Has anyone any idea how code/mapping file should look like? Thanks in advance
-
Constantine Gladky almost 11 years1) I don't have session object. entityManager only. 2) In my case insert statement is executed. Also I have one more parameter which in sql server is marked input/output. And I don't know how to handle it
-
NimChimpsky almost 11 yearscreateSQlQuery just allows you to run a native sql ... the returned value wil lbe handled the same way