如何在Spring/Hibernate/JPA中正确调用PostgreSQL函数（存储过程）？

Question

我正在使用Spring MVC 4，Hibernate和PostgreSQL 9.3，并在PostgreSQL中定义了如下的函数（存储过程）：

CREATE OR REPLACE FUNCTION spa.create_tenant(t_name character varying)
  RETURNS void AS
  $BODY$
    BEGIN
      EXECUTE format('CREATE SCHEMA IF NOT EXISTS %I AUTHORIZATION postgres', t_name);
    END
  $BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION spa.create_tenant(character varying)
OWNER TO postgres;

如果我像这样在pgAdmin中运行该函数，它可以正常工作：

select spa.create_tenant('somename');

现在我正在尝试像这样从我的服务运行此函数：

@Override
@Transactional
public void createSchema(String name) {
    StoredProcedureQuery sp = em.createStoredProcedureQuery("spa.create_tenant");
    sp.registerStoredProcedureParameter("t_name", String.class, ParameterMode.IN);
    sp.setParameter("t_name", name);
    sp.execute();
}

如果我运行我的方法，会得到以下错误：

javax.persistence.PersistenceException: org.hibernate.MappingException: No Dialect mapping for JDBC type: 1111

我猜这是因为函数中定义了返回类型为 void，所以我将返回类型更改为以下内容：

RETURNS character varying AS

如果我再次运行我的方法，我会得到这个异常：

javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: Error calling CallableStatement.getMoreResults

有人知道这里发生了什么，如何正确调用具有void返回类型的PostgreSQL存储过程吗？

- Mirko Filipovic

---

你会如何调用JDBC CallableStatement？这就是JPA API所包装的全部内容。如果Hibernate没有反映出JDBC的特性，那么可以尝试使用不同的JPA提供程序来查看它是如何处理的。 - user3973283

Answer 1

如果您也在使用Spring Data，您可以在您的@Repository接口中定义一个过程，如下所示：

@Procedure(value = "spa.create_tenant")
public void createTenantOrSomething(@Param("t_name") String tNameOrSomething);

更多信息请参考文档。

- Georgios Syngouroglou

Answer 2

在您的实体类中，定义一个NamedNativeQuery，就像调用postgresql函数并选择一样。

import javax.persistence.NamedNativeQueries;
import javax.persistence.NamedNativeQuery;
import javax.persistence.Entity;
@NamedNativeQueries(
    value={
            // cast is used for Hibernate, to prevent No Dialect mapping for JDBC type: 1111
            @NamedNativeQuery(
                  name = "Tenant.createTenant",
                 query = "select cast(create_tenant(?) as text)"
            )
     }
)
@Entity
public class Tenant

Hibernate无法映射void类型，因此解决方法是将结果强制转换为文本。

public void createSchema(String name) {
    Query query = em.createNamedQuery("Tenant.createTenant")
            .setParameter(1, name);
    query.getSingleResult();
}

- srex

---

这个查询无法编译。 - Alex G

抱歉@AlexG，回复晚了，我已经编辑了实体代码建议，但希望你已经找到解决方案了。 - srex

Answer 3

我认为是RETURN VOID导致了问题。因此，将FUNCTION定义更改为：

CREATE OR REPLACE FUNCTION spa.create_tenant(t_name character varying)
  RETURNS bigint AS
  $BODY$
    BEGIN
      EXECUTE format('CREATE SCHEMA IF NOT EXISTS %I AUTHORIZATION postgres', t_name);
      RETURN 1;
    END
  $BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION spa.create_tenant(character varying)
OWNER TO postgres;

在您将函数更改为返回一些虚拟值后，将存储过程查询更改为以下内容：

StoredProcedureQuery query = entityManager
    .createStoredProcedureQuery("spa.create_tenant")
    .registerStoredProcedureParameter(1, 
        Long.class, ParameterMode.OUT)
    .registerStoredProcedureParameter(2, 
        String.class, ParameterMode.IN)
    .setParameter(2, name);
 
query.getResultList();

- Vlad Mihalcea

---

如何在控制器中调用函数并获取结果？请参见http://stackoverflow.com/q/41864288/287948 - Peter Krauss

Answer 4

由于您正在使用PostgreSQL，因此可以像您已经编写的那样，在SELECT中调用function类型的任何存储过程（否则，Oracle只允许您执行在选择中声明为只读的函数）。

您可以使用EntityManager.createNativeQuery(SQL)。

由于您正在使用Spring，因此还可以使用SimpleJdbcTemplate.query(SQL)执行任何SQL语句。

- Danubian Sailor

---

如何在控制器中调用函数并获取结果？请参见http://stackoverflow.com/q/41864288/287948 - Peter Krauss

Answer 5

对于一个过程，请尝试这样做：

@Procedure("spa.create_tenant")
String createTenant(String tenant);

- Deyvson Santos

Answer 6

如果你想保持简单，只需这样做：

    em.createSQLQuery("SELECT * FROM spa.create_tenant(:t_name) ")
                          .setParameter("t_name", name)").list();

注意我故意使用了list()..由于某种原因，.update()对我不起作用。

- Roberto Rodriguez

Answer 7

• PostgreSQL
• Hibernate
• Kotlin

---

CREATE OR REPLACE FUNCTION your_procedure() RETURNS text AS $$
BEGIN
    RETURN 'Some text';
END;
$$ LANGUAGE plpgsql;

---

val query = session.createNativeQuery("SELECT your_procedure()")
query.list().map {
    println("NativeQuery: $it")
}

- Braian Coronel

---

JPA怎么样？ - TheRealChx101