如何在 Spring/Hibernate/JPA 中正确调用 PostgreSQL 函数（存储过程）？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26122796/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to properly call PostgreSQL functions (stored procedures) within Spring/Hibernate/JPA?
提问by Mirko Filipovic
I'm using Spring MVC 4, Hibernate and PostgreSQL 9.3 and have defined function (stored procedure) inside Postgres like this:
我正在使用 Spring MVC 4、Hibernate 和 PostgreSQL 9.3，并在 Postgres 中定义了函数（存储过程），如下所示：
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;
If I run this function inside pgAdmin like this it's working fine:
如果我像这样在 pgAdmin 中运行这个函数，它工作正常：
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
I'm guessing this is because of return type void that is defined in function so I changed return type to look like this:
我猜这是因为函数中定义的返回类型 void 所以我将返回类型更改为如下所示：
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
Does anyone know what is going on here and how to properly call stored procedures in PostgreSQL even with void as return type?
有谁知道这里发生了什么以及如何在 PostgreSQL 中正确调用存储过程，即使返回类型为 void？
回答by srex
In your entity class, define a NamedNativeQuery like you would call postgresql function with select.
在您的实体类中，定义一个 NamedNativeQuery，就像您使用 select 调用 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 is not able to map void, so a workaround is to cast result as text
Hibernate 无法映射 void，因此解决方法是将结果转换为文本
public void createSchema(String name) {
Query query = em.createNamedQuery("Tenant.createTenant")
.setParameter(1, name);
query.getSingleResult();
}
回答by George Siggouroglou
In case you are using also spring data, you could just define a procedure inside your @Repositoryinterface like this,
如果您还使用 spring 数据，您可以@Repository像这样在界面中定义一个过程，
@Procedure(value = "spa.create_tenant")
public void createTenantOrSomething(@Param("t_name") String tNameOrSomething);
More in the docs.
文档中的更多内容。
回答by Vlad Mihalcea
I think it's the RETURN VOIDthat's causing the issue. There is an article on this topicthat might help you as well:
我认为这是RETURN VOID导致问题的原因。有一篇关于此主题的文章也可能对您有所帮助：
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;
After you changed your function to return some dummy value, change the stored procedure query to this:
更改函数以返回一些虚拟值后，将存储过程查询更改为：
StoredProcedureQuery query = entityManager
.createStoredProcedureQuery("spa.create_tenant")
.registerStoredProcedureParameter(1,
Long.class, ParameterMode.OUT)
.registerStoredProcedureParameter(2,
String.class, ParameterMode.IN)
.setParameter(2, name);
query.getResultList();
回答by Danubian Sailor
Since you're using PostgreSQL, you can, as you've already written, call anystored procedure of type functionin SELECT(Oracle, otherwise, would let you only execute functions declared to be read only in selects).
由于您使用的是PostgreSQL，您可以像您已经编写的那样，调用任何类型为functionin 的存储过程SELECT（否则，Oracle 将让您只执行在选择中声明为只读的函数）。
You can use EntityManager.createNativeQuery(SQL).
您可以使用EntityManager.createNativeQuery(SQL).
Since you're using Spring, you can use SimpleJdbcTemplate.query(SQL)to execute any SQL statement, as well.
由于您使用的是 Spring，因此您也可以使用它SimpleJdbcTemplate.query(SQL)来执行任何 SQL 语句。
回答by Roberto Rodriguez
If you want to keep it simple, just do this:
如果您想保持简单，请执行以下操作：
em.createSQLQuery("SELECT * FROM spa.create_tenant(:t_name) ")
.setParameter("t_name", name)").list();
Notice I used list() intentionally.. for some reason .update() didn't work for me.
请注意，我故意使用了 list() ......出于某种原因 .update() 对我不起作用。
