如何从 Java 和 JPA 调用存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3572626/
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 call a stored procedure from Java and JPA
提问by user431514
I am writing a simple web application to call a stored procedure and retrieve some data. Its a very simple application, which interacts with client's database. We pass employee id and company id and the stored procedure will return employee details.
我正在编写一个简单的 Web 应用程序来调用存储过程并检索一些数据。它是一个非常简单的应用程序，它与客户端的数据库进行交互。我们传递员工 ID 和公司 ID，存储过程将返回员工详细信息。
Web application cannot update/delete data and is using SQL Server.
Web 应用程序无法更新/删除数据并且正在使用 SQL Server。
I am deploying my web application in Jboss AS. Should I use JPA to access the stored procedure or CallableStatement. Any advantage of using JPA in this case.
我正在 Jboss AS 中部署我的 Web 应用程序。我应该使用 JPA 来访问存储过程还是CallableStatement. 在这种情况下使用 JPA 的任何优势。
Also what will be the sql statement to call this stored procedure. I have never used stored procedures before and I am struggling with this one. Google was not much of a help.
还有调用这个存储过程的 sql 语句是什么。我以前从未使用过存储过程，我正在为这个而苦苦挣扎。谷歌帮不上什么忙。
Here is the stored procedure:
下面是存储过程：
CREATE procedure getEmployeeDetails (@employeeId int, @companyId int)
as
begin
select firstName,
lastName,
gender,
address
from employee et
where et.employeeId = @employeeId
and et.companyId = @companyId
end
Update:
更新：
For anyone else having problem calling stored procedure using JPA.
对于使用JPA调用存储过程有问题的其他人。
Query query = em.createNativeQuery("{call getEmployeeDetails(?,?)}",
EmployeeDetails.class)
.setParameter(1, employeeId)
.setParameter(2, companyId);
List<EmployeeDetails> result = query.getResultList();
Things I have noticed:
我注意到的事情：
- Parameter names didn't work for me, so try using parameter index.
- Correct sql statement
{call sp_name(?,?)}instead ofcall sp_name(?,?) - If stored procedure is returning a result set, even if you know with only one row,
getSingleResultwont work - Pass a
resultSetMappingname or result class details
- 参数名称对我不起作用，因此请尝试使用参数索引。
- 正确的 sql 语句
{call sp_name(?,?)}而不是call sp_name(?,?) - 如果存储过程返回结果集，即使你知道只有一行，也
getSingleResult不会工作 - 传递
resultSetMapping名称或结果类详细信息
回答by vinoth
To call stored procedure we can use Callable Statement in java.sql package.
要调用存储过程，我们可以使用 java.sql 包中的 Callable Statement。
回答by Sean Patrick Floyd
You need to pass the parameters to the stored procedure.
您需要将参数传递给存储过程。
It should work like this:
它应该像这样工作：
List result = em
.createNativeQuery("call getEmployeeDetails(:employeeId,:companyId)")
.setParameter("emplyoyeeId", 123L)
.setParameter("companyId", 456L)
.getResultList();
Update:
更新：
Or maybe it shouldn't.
或者也许它不应该。
In the Book EJB3 in Action, it says on page 383, that JPA does not support stored procedures(page is only a preview, you don't get the full text, the entire book is available as a download in several places including this one, I don't know if this is legal though).
在 Book EJB3 in Action 中，它在第 383 页说，JPA 不支持存储过程（页面只是预览，你没有得到全文，整本书可以在几个地方下载，包括这个，我不知道这是否合法）。
Anyway, the text is this:
不管怎样，正文是这样的：
JPA and database stored procedures
JPA 和数据库存储过程
If you're a big fan of SQL, you may be willing to exploit the power of database stored procedures. Unfortunately, JPA doesn't support stored procedures, and you have to depend on a proprietary feature of your persistence provider. However, you can use simple stored functions (without out parameters) with a native SQL query.
如果您是 SQL 的忠实粉丝，您可能愿意利用数据库存储过程的强大功能。不幸的是，JPA 不支持存储过程，您必须依赖持久性提供程序的专有功能。但是，您可以在本机 SQL 查询中使用简单的存储函数（不带输出参数）。
回答by Pascal Thivent
I am deploying my web application in Jboss AS. Should I use JPA to access the stored procedure or CallableStatement. Any advantage of using JPA in this case.
我正在 Jboss AS 中部署我的 Web 应用程序。我应该使用 JPA 来访问存储过程还是 CallableStatement。在这种情况下使用 JPA 的任何优势。
It is not really supported by JPA but it's doable. Still I wouldn't go this way:
JPA 并不真正支持它，但它是可行的。我仍然不会走这条路：
- using JPA just to map the result of a stored procedure call in some beans is really overkill,
- especially given that JPA is not really appropriate to call stored procedure (the syntax will be pretty verbose).
- 仅使用 JPA 来映射某些 bean 中存储过程调用的结果确实有点矫枉过正，
- 特别是考虑到 JPA 不太适合调用存储过程（语法将非常冗长）。
I would thus rather consider using Spring support for JDBC data access, or a data mapper like MyBatisor, given the simplicity of your application, raw JDBC and CallableStatement. Actually, JDBC would probably be my choice. Here is a basic kickoff example:
因此，我宁愿考虑使用Spring 对 JDBC 数据访问的支持，或者像MyBatis这样的数据映射器，或者考虑到您的应用程序的简单性，原始 JDBC 和CallableStatement. 实际上，JDBC 可能是我的选择。这是一个基本的开球示例：
CallableStatement cstmt = con.prepareCall("{call getEmployeeDetails(?, ?)}");
cstmt.setInt("employeeId", 123);
cstmt.setInt("companyId", 456);
ResultSet rs = cstmt.executeQuery();
Reference
参考
- JDBC documentation: Java SE 6
- JDBC 文档：Java SE 6
回答by Dmitry Chornyi
For me, only the following worked with Oracle 11g and Glassfish 2.1 (Toplink):
对我来说，只有以下内容适用于 Oracle 11g 和 Glassfish 2.1 (Toplink)：
Query query = entityManager.createNativeQuery("BEGIN PROCEDURE_NAME(); END;");
query.executeUpdate();
The variant with curly braces resulted in ORA-00900.
带有花括号的变体导致 ORA-00900。
回答by James
If using EclipseLink you can use the @NamedStoredProcedureQuery or StoreProcedureCall to execute any stored procedure, including ones with output parameters, or out cursors. Support for stored functions and PLSQL data-types is also available.
如果使用 EclipseLink，您可以使用 @NamedStoredProcedureQuery 或 StoreProcedureCall 来执行任何存储过程，包括带有输出参数或输出游标的存储过程。还提供对存储函数和 PLSQL 数据类型的支持。
See, http://en.wikibooks.org/wiki/Java_Persistence/Advanced_Topics#Stored_Procedures
见， http://en.wikibooks.org/wiki/Java_Persistence/Advanced_Topics#Stored_Procedures
回答by roberto quevedo
The following works for me:
以下对我有用：
Query query = em.createNativeQuery("BEGIN VALIDACIONES_QPAI.RECALC_COMP_ASSEMBLY('X','X','X',0); END;");
query.executeUpdate();
回答by tosha Shah
This answer might be helpful if you have entity manager
如果您有实体管理器，此答案可能会有所帮助
I had a stored procedure to create next number and on server side I have seam framework.
我有一个存储过程来创建下一个数字，在服务器端我有接缝框架。
Client side
客户端
Object on = entityManager.createNativeQuery("EXEC getNextNmber").executeUpdate();
log.info("New order id: " + on.toString());
Database Side (SQL server) I have stored procedure named getNextNmber
数据库端（SQL 服务器）我有一个名为的存储过程 getNextNmber
回答by Patricio Dominguez
Try this code:
试试这个代码：
return em.createNativeQuery("{call getEmployeeDetails(?,?)}",
EmployeeDetails.class)
.setParameter(1, employeeId)
.setParameter(2, companyId).getResultList();
回答by Réda
May be it's not the same for Sql Srver but for people using oracle and eclipslink it's working for me
对于 Sql Srver 可能不一样，但对于使用 oracle 和 eclipslink 的人来说，它对我有用
ex: a procedure that have one IN param (type CHAR) and two OUT params (NUMBER & VARCHAR)
例如：具有一个 IN 参数（类型 CHAR）和两个 OUT 参数（NUMBER & VARCHAR）的过程
in the persistence.xml declare the persistence-unit :
在persistence.xml 中声明persistence-unit ：
<persistence-unit name="presistanceNameOfProc" transaction-type="RESOURCE_LOCAL">
<provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
<jta-data-source>jdbc/DataSourceName</jta-data-source>
<mapping-file>META-INF/eclipselink-orm.xml</mapping-file>
<properties>
<property name="eclipselink.logging.level" value="FINEST"/>
<property name="eclipselink.logging.logger" value="DefaultLogger"/>
<property name="eclipselink.weaving" value="static"/>
<property name="eclipselink.ddl.table-creation-suffix" value="JPA_STORED_PROC" />
</properties>
</persistence-unit>
and declare the structure of the proc in the eclipselink-orm.xml
并在 eclipselink-orm.xml 中声明 proc 的结构
<?xml version="1.0" encoding="UTF-8"?><entity-mappings version="2.0"
xmlns="http://java.sun.com/xml/ns/persistence/orm" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/persistence/orm orm_2_0.xsd">
<named-stored-procedure-query name="PERSIST_PROC_NAME" procedure-name="name_of_proc" returns-result-set="false">
<parameter direction="IN" name="in_param_char" query-parameter="in_param_char" type="Character"/>
<parameter direction="OUT" name="out_param_int" query-parameter="out_param_int" type="Integer"/>
<parameter direction="OUT" name="out_param_varchar" query-parameter="out_param_varchar" type="String"/>
</named-stored-procedure-query>
in the code you just have to call your proc like this :
在代码中，您只需像这样调用 proc：
try {
final Query query = this.entityManager
.createNamedQuery("PERSIST_PROC_NAME");
query.setParameter("in_param_char", 'V');
resultQuery = (Object[]) query.getSingleResult();
} catch (final Exception ex) {
LOGGER.log(ex);
throw new TechnicalException(ex);
}
to get the two output params :
获取两个输出参数：
Integer myInt = (Integer) resultQuery[0];
String myStr = (String) resultQuery[1];
回答by Pau Kiat Wee
JPA 2.1 now support Stored Procedure, read the Java doc here.
JPA 2.1 现在支持存储过程，请在此处阅读 Java 文档。
Example:
例子：
StoredProcedureQuery storedProcedure = em.createStoredProcedureQuery("sales_tax");
// set parameters
storedProcedure.registerStoredProcedureParameter("subtotal", Double.class, ParameterMode.IN);
storedProcedure.registerStoredProcedureParameter("tax", Double.class, ParameterMode.OUT);
storedProcedure.setParameter("subtotal", 1f);
// execute SP
storedProcedure.execute();
// get result
Double tax = (Double)storedProcedure.getOutputParameterValue("tax");
See detailed example here.
请参阅此处的详细示例。
