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

How to call a Stored Procedure from Hibernate having both IN and OUT parameters

提问by Waseem Khan

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

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

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

我试图添加一个输出参数，如

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 :)

任何帮助表示赞赏:)

回答by Asaf Manassen

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 参数作为返回参数的一部分返回（仅当您有权访问存储过程时才相关）。
开玩笑添加一个像下面这样的存储过程

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

结果包含输出参数，如果你想返回乘法参数，你可以通过执行 select @parm1,@parm2,... ,@parmn

希望它有所帮助

回答by Dinesh Tope

ResultSet rs1=null;

结果集 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;
 }

回答by Vlad Mihalcea

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");

回答by chaitanya dalvi

@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

参考上面的例子

回答by Pradip Wawge

    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]);

回答by Quincy

example:

例子：

        Session session = em.unwrap(Session.class);
        session.setHibernateFlushMode(FlushMode.MANUAL);
        ProcedureCall query = session.createStoredProcedureCall("dbo.sp_getorderlistbyparam",BasicResult.class);
        query.registerParameter("search_text",String.class, ParameterMode.IN).bindValue(searchText);
        query.registerParameter("membership_nbr",String.class, 
        query.registerParameter("is_debug",Integer.class, ParameterMode.IN).bindValue(0);
        query.registerParameter("result_count",Integer.class, ParameterMode.OUT);
        basicResults = (List<BasicResult>) query.getResultList();
        ProcedureOutputs procedureOutputs = query.getOutputs();
        parameterMap.put("totalOrderCnt" ,(Integer) procedureOutputs.getOutputParameterValue("result_count"));

回答by Kathiravan Ramaswamy

@PersistenceContext 
private EntityManager entitymanager;
     @Override
            public String function(int id, int emp_id, String letype, String days, Date fromdate, Date todate, String reason,
                    String backup, int user_id) {

                //Session session = entitymanager.unwrap(Session.class);

                StoredProcedureQuery  applyLeave = entitymanager.createStoredProcedureQuery("sp_apply_leave");

                applyLeave.registerStoredProcedureParameter("p_id", Integer.class, ParameterMode.IN)
                        .registerStoredProcedureParameter("p_emp_id", Integer.class, ParameterMode.IN)
                        .registerStoredProcedureParameter("p_letype", String.class, ParameterMode.IN)
                        .registerStoredProcedureParameter("p_days", String.class, ParameterMode.IN)
                        .registerStoredProcedureParameter("p_fmdate", Date.class, ParameterMode.IN)
                        .registerStoredProcedureParameter("p_todate", Date.class, ParameterMode.IN)
                        .registerStoredProcedureParameter("p_reason", String.class, ParameterMode.IN)
                        .registerStoredProcedureParameter("p_backper", String.class, ParameterMode.IN)
                        .registerStoredProcedureParameter("p_user_id", Integer.class, ParameterMode.IN)
                        .registerStoredProcedureParameter("error_msg", String.class, ParameterMode.INOUT);


                applyLeave.setParameter("p_id", id);
                applyLeave.setParameter("p_emp_id" , emp_id);
                applyLeave.setParameter("p_letype" , letype);
                applyLeave.setParameter("p_days" , days);
                applyLeave.setParameter("p_fmdate" , fromdate);
                applyLeave.setParameter("p_todate" , todate);
                applyLeave.setParameter("p_reason" , reason);
                applyLeave.setParameter("p_backper" , backup);
                applyLeave.setParameter("p_user_id" , user_id);
                applyLeave.setParameter("error_msg", new String("error_msg"));
                //applyLeave.executeUpdate();

                String error_msg = (String) applyLeave.getOutputParameterValue("error_msg");
                return error_msg;

            }

here i have autowired entitymanager and configured hibernate Persistence in config package

在这里，我在配置包中自动连接了 entitymanager 并配置了 hibernate Persistence