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

Question

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

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

我正在尝试调用我的过程。

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

我已经尝试了所有方法但没有成功。你能帮帮我吗？ 如果我尝试以上操作，它会显示：

PROCEDURE myProcedure的参数数量不正确；期望2个，得到1个

我尝试添加一个输出参数，如下所示：

myProcedure(:out_id:in_Id)

但是接下来它说：

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

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

.setParameter("out_id", ?);

任何帮助都是受欢迎的 :)

- waseemwk

---

这篇文章非常有帮助！https://stackoverflow.com/questions/20347931/hibernate-calling-stored-procedure-returns-cursor - Aamir

Answer 1

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

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

你可以使用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");

- Vlad Mihalcea

---

如果我想返回多种类型（如String、Int），会出现错误：org.hibernate.MappingException: No Dialect mapping for JDBC type: -1。 - Niraj

那可能有帮助，但我正在使用的是MySQL而不是Oracle。 - Niraj

Answer 2

最简单的方法是将输出参数作为返回参数的一部分返回（仅当您可以访问存储过程时才相关）。只需添加一个类似以下的存储过程即可。

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

完成这些步骤后，将其与Hibernet一起使用就非常简单了，具体操作如下：

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

该结果包含输出参数，如果您想返回多个参数，您可以通过执行 "select @parm1，@parm2，...，@parmn" 来添加它。

希望这有帮助。

- Asaf Manassen

Answer 3

例子：

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

- Quincy

---

最好能解释这些脚本是如何工作的，这将有助于未来用户更好地理解。 - Ashu Kumar

Answer 4

@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;

            }

这里我已经用@Autowired注入了EntityManager，并在config包中配置了Hibernate Persistence。

- Kathiravan Ramaswamy

Answer 5

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

- Pradip Wawge

Answer 6

@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;
}

请参考上面的例子

- chaitanya dalvi

Answer 7

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;
 }

- Dinesh Tope

---

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