如何在Spring / Hibernate / JPA中正确调用PostgreSQL函数（存储过程）？ [英] How to properly call PostgreSQL functions (stored procedures) within Spring/Hibernate/JPA?

问题描述

我使用Spring MVC 4，Hibernate和PostgreSQL 9.3，并且在Postgres里定义了这样的函数（存储过程）：

I'm using Spring MVC 4, Hibernate and PostgreSQL 9.3 and have defined function (stored procedure) inside Postgres like this:

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里面运行这个函数，它工作的很好：

If I run this function inside pgAdmin like this it's working fine:

select spa.create_tenant('somename');

现在我试图从我的服务中运行此功能，如下所示：

Now I'm trying to run this function from my service like this:

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

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

If I run my method I'm getting following error:

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

我猜这是因为在函数中定义的返回类型void，所以我将返回类型更改为如下所示：

I'm guessing this is because of return type void that is defined in function so I changed return type to look like this:

RETURNS character varying AS

如果我再次运行我的方法，我得到这个例外：

If I run my method again I'm getting this exception instead:

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

有谁知道这里发生了什么，以及如何正确在PostgreSQL中调用存储过程，甚至以void作为返回类型？

Does anyone know what is going on here and how to properly call stored procedures in PostgreSQL even with void as return type?

推荐答案

在实体类中，定义一个NamedNativeQuery，就像调用postgresql功能使用select。

In your entity class, define a NamedNativeQuery like you would call postgresql function with select.

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，因此解决方法是将结果作为文本进行转换

hibernate is not able to map void, so a workaround is to cast result as text

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

这篇关于如何在Spring / Hibernate / JPA中正确调用PostgreSQL函数（存储过程）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！