如何从具有IN和OUT参数的Hibernate调用存储过程

I want to call a Stored Procedure from Hibernate which returns an out value. Here is my Stored Procedure.

我想从Hibernate调用一个存储过程，它返回一个out值。这是我的存储过程。

create procedure myProcedure
(  
in in_Id int,  
out out_Id int  
)  
begin  
...  
END;

I am trying this to call my procedure

我正在尝试这个来调用我的程序

Query query = session.createSQLQuery(  
"CALL myProcedure(:in_Id)")  
.setParameter("in_id", 123);   
//Not sure how to register out parameters...??      
List result = query.list();

I tried everything but no luck. Can you help me please? If i try the above it says:

我尝试了一切，但没有运气。你能帮我吗？如果我尝试以上说法：

Incorrect number of arguments for PROCEDURE myProcedure; expected 2, got 1

PROCEDURE myProcedure的参数数量不正确;预计2，得到1

I tried to add an out parameter like

我试着添加一个out参数

myProcedure(:out_id:in_Id)

but then it says

但随后它说

Not all named parameters have been set:

并非所有已命名的参数都已设置：

I don't know how out parameter will be set? Is it like the following?

我不知道如何设置参数？它像下面这样吗？

.setParameter("out_id", ?);

Any help is appreciated :)

任何帮助表示赞赏:)

5 个解决方案

#1

The easiest way to do that is return the out parameter as part of the returning parameters (relevant only if you have access to the store procedures).
jest add a store procedure like the following one

最简单的方法是将out参数作为返回参数的一部分返回（仅当您有权访问存储过程时才相关）。 jest添加如下所示的商店程序

create procedure myProcedure_only_in_parms (
   in in_Id int)
begin
call myProcedure(in_id,@out_Id) ;
select @out_id
END;

after done that it quite simple to use it with Hibernet in the following way

完成后，以下列方式使用Hibernet非常简单

Query query = session.createSQLQuery(
"CALL myProcedure_only_in_parms (:in_Id)")
.setParameter("in_id", 123);
List result = query.list();

The result contains the out parameter, if you want return multiply parameters you can add it by doing select @parm1,@parm2,... ,@parmn

Hope it helped

结果包含out参数，如果你想要返回乘法参数，你可以通过选择@ parm1，@ parm2，...，@ parmn来添加它。希望它有所帮助

#2

I wrote a very detailed article about how you can call MySQL stored procedures and database functions from Hibernate, but I'll write a short summary here as well.

我写了一篇非常详细的文章，介绍如何从Hibernate调用MySQL存储过程和数据库函数，但我也会在这里写一篇简短的摘要。

Considering you have a simple stored procedure that outputs a basic type:

考虑到您有一个输出基本类型的简单存储过程：

CREATE PROCEDURE count_comments (
   IN postId INT, 
   OUT commentCount INT
) 
BEGIN
    SELECT COUNT(*) INTO commentCount 
    FROM post_comment  
    WHERE post_comment.post_id = postId; 
END

You can call this stored procedure using a JPA StoredProcedureQuery:

您可以使用JPA StoredProcedureQuery调用此存储过程：

StoredProcedureQuery query = entityManager
    .createStoredProcedureQuery("count_comments")
    .registerStoredProcedureParameter(
        "postId", Long.class, ParameterMode.IN)
    .registerStoredProcedureParameter(
        "commentCount", Long.class, ParameterMode.OUT)
    .setParameter("postId", 1L);

query.execute();

Long commentCount = (Long) query
    .getOutputParameterValue("commentCount");

#3

@Override
public String generateVoucherNumber(ExpenseHeaderDTO expenseHeaderDTO) {

    Session session = sessionFactory.getCurrentSession();
    ProcedureCall query =  session.createStoredProcedureCall("voucher_number");
            query.registerParameter(
                "module", String.class, ParameterMode.IN).bindValue("EMPLOYEE_EXPENSE");
            query.registerParameter(
                "voucherNumber", String.class, ParameterMode.OUT);

    ProcedureOutputs procedureResult=query.getOutputs();
    String voucherNumber= (String) procedureResult.getOutputParameterValue("voucherNumber");
    return voucherNumber;
}

Refer above example

参考上面的例子

#4

    just cal normal way but store response out param 
in Object[] and we can also get multiple response value in Onject[]

    Object[] respnseCode = (Object[])session.createSQLQuery(
    "CALL myProcedure_only_in_parms (:in_Id)")
    .setParameter("in_id", 123);
    List result = query.list();

    sysout("out_Id"+respnseCode[0]);

#5

ResultSet rs1=null;

ResultSet rs1 = null;

     ArrayList<Lookup_master> list=new ArrayList<Lookup_master>();

     CallableStatement cStmt;


    try {

        cStmt = hibernateConfiguration.dataSource().getConnection()
                    .prepareCall("{call SP_ADVSER_LOOKUPMASTER(?,?,?,?,?,?,?)}");

        cStmt.setString(1,lookup_master.getLookup_code());
        cStmt.setString(2,lookup_master.getLookup_type());
        cStmt.setString(3,lookup_master.getLookup_name());
        cStmt.setString(4,lookup_master.getMeaning());
        cStmt.setString(5,request.getParameter("start_date"));
        cStmt.setString(6,request.getParameter("end_date"));

        cStmt.registerOutParameter(7, OracleTypes.CURSOR);
        cStmt.executeQuery();


        rs1 = (ResultSet) cStmt.getObject(7);


        while (rs1.next()) {

            Lookup_master lookup_master1=new Lookup_master();

            lookup_master1.setLookup_id(rs1.getInt(1));
            lookup_master1.setLookup_code(rs1.getString(2));
            lookup_master1.setLookup_type(rs1.getString(3));
            lookup_master1.setLookup_name(rs1.getString(4));
            lookup_master1.setMeaning(rs1.getString(5));
            lookup_master1.setStart_date(rs1.getDate(6));
            lookup_master1.setEnd_date(rs1.getDate(7));

             list.add(lookup_master1);
            System.out.println(lookup_master1.getLookup_id());

        }



    } catch (SQLException e) {
        e.printStackTrace();
    }

     return  list;
 }