我们如何使用Hibernate和JPA调用存储过程？

Question

如何使用Hibernate或JPA调用存储过程？

- kandarp

---

可能是Can I call a stored procedure with hibernate criteria?的重复问题。 - Bostone

Answer 1

考虑下面的存储过程，它只返回基本的返回值：

CREATE OR REPLACE PROCEDURE count_comments (  
   postId IN NUMBER,  
   commentCount OUT NUMBER )  
AS 
BEGIN 
    SELECT COUNT(*) INTO commentCount  
    FROM post_comment  
    WHERE post_id = postId; 
END;

您可以使用标准的JPA来调用此方法：

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

query.execute();

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

如果存储过程返回 SYS_REFCURSOR：

CREATE OR REPLACE PROCEDURE post_comments ( 
   postId IN NUMBER, 
   postComments OUT SYS_REFCURSOR ) 
AS 
BEGIN
    OPEN postComments FOR
    SELECT *
    FROM post_comment 
    WHERE post_id = postId; 
END;

您可以这样调用它：

StoredProcedureQuery query = entityManager
    .createStoredProcedureQuery("post_comments")
    .registerStoredProcedureParameter(1, Long.class, 
         ParameterMode.IN)
    .registerStoredProcedureParameter(2, Class.class, 
         ParameterMode.REF_CURSOR)
    .setParameter(1, 1L);

query.execute();

List<Object[]> postComments = query.getResultList();

如果你想调用 Oracle 数据库函数：

CREATE OR REPLACE FUNCTION fn_count_comments ( 
    postId IN NUMBER ) 
    RETURN NUMBER 
IS
    commentCount NUMBER; 
BEGIN
    SELECT COUNT(*) INTO commentCount 
    FROM post_comment 
    WHERE post_id = postId; 
    RETURN( commentCount ); 
END;

使用StoredProcedureQuery是无法与Hibernate 5一起使用的，所以你可以像这样调用它：

BigDecimal commentCount = (BigDecimal) entityManager
    .createNativeQuery(
        "SELECT fn_count_comments(:postId) FROM DUAL"
    )
    .setParameter("postId", 1L)
    .getSingleResult();

或者使用纯JDBC：

Session session = entityManager.unwrap( Session.class ); 

Integer commentCount = session.doReturningWork( connection -> {
    try (CallableStatement function = connection.prepareCall(
            "{ ? = call fn_count_comments(?) }" )) {
        function.registerOutParameter( 1, Types.INTEGER );
        function.setInt( 2, 1 );
        function.execute();
        return function.getInt( 1 );
    }
} );

查看以下文章以获取更多详细信息：

• 如何使用JPA和Hibernate调用Oracle存储过程和函数
• 如何使用JPA和Hibernate调用SQL Server存储过程和函数
• 如何使用JPA和Hibernate调用PostgreSQL函数（存储过程）
• 如何使用JPA和Hibernate调用MySQL存储过程和函数

- Vlad Mihalcea

---

请注意，JPA 2.1具有EntityManager.createStoredProcedureQuery方法，如果您的类路径中存在JPA 2.0，则不会解析它。https://stackoverflow.com/a/34174204/402488 - mjalil

Answer 2

执行远程过程需要使用以下结构：

映射

<sql-query name="RP">   
    {call some_rp(:param1, :param2)}
</sql-query>

Java 代码

session.getNamedQuery("RP").setInteger("param1", 1).setInteger("param2", 2).executeUpdate();

- Mikhail.Mamaev

---

这个方法对于关系型数据库管理系统（Oracle和SQL Server）都兼容吗？ - rogue lad

Answer 3

从Hibernate调用存储过程的一种方法

在@NamedNativeQueries注释中声明您的存储过程

//Stock.java

@NamedNativeQueries({
    @NamedNativeQuery(
    name = "callStockStoreProcedure",
    query = "CALL GetStocks(:stockCode)",
    resultClass = Stock.class
    )
})
@Entity
@Table(name = "stock")
public class Stock implements java.io.Serializable {

// Call it with getNamedQuery().

Query query = session.getNamedQuery("callStockStoreProcedure")
    .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());
}

这个有效

- Sandeep Jain

Answer 4

你可以做以下事情

 Session session = sessionFactory.openSession();
Transaction tx = session.beginTransaction();
PreparedStatement st = session.connection().prepareStatement("{call procedureName(?, ?)}");
                st.setString(1, formatter.format(parameter1));
                st.setString(2, formatter.format(parameter2));
                st.execute();
tx.commit();

请在必要的地方添加异常处理。

- dsr

---

您正在从 Hibernate 会话中检索连接，然后使用纯JDBC。此代码未利用Hibernate的支持。 - Aravind Yarram

Answer 5

一种方法是使用getNamedQuery()。

Query query = session.getNamedQuery("callStockStoreProcedure")
    .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());
}

你必须进行映射或使用注释

还有其他的：source

- demian

Answer 6

这是一种简单而智能的调用过程的方式：

 @Query(value = "call SCHEMA.PROCEDURE_NAME()" , nativeQuery = true)

如果你的过程包含参数，你可以像下面这样调用它：

@Transactional
@Modifying
@Query(value = "call SCHEMA.PROCEDURE_NAME(:param1 , :param2)" , nativeQuery = true)
void validateUpdatePaymentStatus(@Param("param1") String param1, @Param("param2") int param2);

如果你的程序包含更新、插入或删除操作，请使用

@Transactional
@Modifying

- Abd Abughazaleh

Answer 7

这里是使用仅 IN 参数调用存储过程的完整解决方案 ---

1）创建可作用于表或一组表的存储过程：

CREATE OR REPLACE procedure insertHouseHello (
house_date in timestamp,
house_name in varchar2,
house_number in number,
house_value in float) 
is
begin
 insert into House("HOUSE_DATE","HOUSE_NAME","HOUSE_NUMBER","HOUSE_VALUE")
 values ( house_date, house_name,house_number,house_value);
 commit;
 end;

2) 从SQL提示符中执行存储过程以检查输入。当您从Java/Hibernate调用该过程时，您也应该看到类似的结果：

exec insertHouseHello(sysdate,'one',123,104); 

3) 在Java代码中：

log.info("Now trying to call the Stored Procedure*****************");
Query exQuery = session.createSQLQuery("CALL " +
        "insertHouseHello(:timestmp,:hname,:hno,:hvalue)");
exQuery.setParameter("timestmp", 
        new java.sql.Timestamp(Calendar.getInstance().getTime().getTime()));
exQuery.setParameter("hname", 34);
exQuery.setParameter("hno", 212);
exQuery.setParameter("hvalue", 12);
int exRows = exQuery.executeUpdate();
log.info("Executed Rows from Stored Procedure****************"+exRows);

4) 现在检查表格中的结果，应该相应地得到更新：

- kanaparthikiran

Answer 8

Hibernate提供了通过存储过程和函数进行查询的支持。例如，如果我们有以下存储过程：

CREATE OR REPLACE FUNCTION selectAllEmployments
RETURN SYS_REFCURSOR
AS
    st_cursor SYS_REFCURSOR;
BEGIN
    OPEN st_cursor FOR
 SELECT EMPLOYEE, EMPLOYER,
 STARTDATE, ENDDATE,
 REGIONCODE, EID, VALUE, CURRENCY
 FROM EMPLOYMENT;
      RETURN  st_cursor;
 END;

返回所有员工列表。存储过程/函数必须返回一个结果集作为第一个输出参数，以便能够与Hibernate一起使用。

要在Hibernate中使用上述查询，您需要通过命名查询进行映射。

<sql-query name="selectAllEmployees_SP" callable="true">
    <return alias="emp" class="Employment">
        <return-property name="employee" column="EMPLOYEE"/>
        <return-property name="employer" column="EMPLOYER"/>
        <return-property name="startDate" column="STARTDATE"/>
        <return-property name="endDate" column="ENDDATE"/>
        <return-property name="regionCode" column="REGIONCODE"/>
        <return-property name="id" column="EID"/>
        <return-property name="salary">
            <return-column name="VALUE"/>
            <return-column name="CURRENCY"/>
        </return-property>
    </return>
    { ? = call selectAllEmployments() }
</sql-query>

使用存储过程的规则/限制：

• 无法使用setFirstResult()/setMaxResults()对存储过程查询进行分页。
• 建议使用标准SQL92调用形式：{ ? = call functionName(<parameters>) }或{ ? = call procedureName(<parameters>}。不支持本地调用语法。

对于Oracle，应遵循以下规则：

• 函数必须返回结果集。
• 过程的第一个参数必须是OUT类型，返回结果集。在Oracle 9或10中，可以使用SYS_REFCURSOR类型来实现。在Oracle中，需要定义REF CURSOR类型。有关详细信息，请参阅Oracle文献。

对于Sybase或MS SQL Server，应遵循以下规则：

• 过程必须返回结果集。请注意，由于这些服务器可以返回多个结果集和更新计数，因此Hibernate将迭代结果并将第一个结果作为其返回值的结果集。其他所有内容都将被丢弃。
• 如果可以在过程中启用SET NOCOUNT ON，则可能更有效，但这不是必需的。

来源参考：来自官方的Hibernate文档。

- Lucky