如何调用从 JPA 返回值的自定义 Oracle 函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6989263/
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 custom Oracle function returning a value from JPA
提问by James Shin
I have a custom function in oracle returning a number after deleting records. I could get a value in sql_plus such as
我在 oracle 中有一个自定义函数，在删除记录后返回一个数字。我可以在 sql_plus 中获得一个值，例如
call my_function(4) into :out_number;
where out_number is a variable defined as number.
其中 out_number 是定义为数字的变量。
I could verify out_number has a value when I do "PRINT OUT_NUMBER."
当我执行“PRINT OUT_NUMBER”时，我可以验证 out_number 是否有值。
My question is how to call this function from JPA.
我的问题是如何从 JPA 调用这个函数。
I've tried like
我试过
Query query = em.createNativeQuery("{call my_function(?)}");
query.serParameter(1, 4);
return query.executeUpdate();
and got an error basically my_function is not defined. How can I get a return value as in CALL...INTO on SQL_PLUS?
并得到一个错误，基本上 my_function 未定义。如何获得 SQL_PLUS 上的 CALL...INTO 中的返回值？
If this method is not desirable, can someone please recommend any suggestion? JPA is the only option for me at this moment. I could create a stored procedure but I'm not sure if I can get a return value from it since OUT parameters are not supported.
如果这种方法不可取，有人可以推荐任何建议吗？JPA 是我目前唯一的选择。我可以创建一个存储过程，但我不确定是否可以从中获取返回值，因为不支持 OUT 参数。
Thanks for your help in advance!
提前感谢您的帮助！
回答by Denis Tulskiy
I used this to execute native functions in oracle:
我用它在 oracle 中执行本机函数：
Query query = em.createNativeQuery("select my_function(:param) from dual");
query.setParameter("param", 4);
return query.getSingleResult();
回答by Vadzim
From https://vladmihalcea.com/how-to-call-oracle-stored-procedures-and-functions-from-hibernate/:
从https://vladmihalcea.com/how-to-call-oracle-stored-procedures-and-functions-from-hibernate/：
First, we can simply call the Oracle function just like any other SQL query:
首先，我们可以像任何其他 SQL 查询一样简单地调用 Oracle 函数：
BigDecimal commentCount = (BigDecimal) entityManager
.createNativeQuery(
"SELECT fn_count_comments(:postId) FROM DUAL"
)
.setParameter("postId", 1L)
.getSingleResult();
Another approach is to call the database function using plain JDBC API:
另一种方法是使用普通的 JDBC API 调用数据库函数：
Session session = entityManager.unwrap( Session.class );
final AtomicReference<Integer> commentCount =
new AtomicReference<>();
session.doWork( connection -> {
try (CallableStatement function = connection
.prepareCall(
"{ ? = call fn_count_comments(?) }"
)
) {
function.registerOutParameter( 1, Types.INTEGER );
function.setInt( 2, 1 );
function.execute();
commentCount.set( function.getInt( 1 ) );
}
} );
回答by Micha? Stochmal
When using Spring Data JPA / Spring JDBC you can use SimpleJdbcCallfor calling functions and procedures.
使用 Spring Data JPA / Spring JDBC 时，您可以SimpleJdbcCall用于调用函数和过程。
For such function header:
对于这样的函数头：
FUNCTION get_text (p_param IN VARCHAR2) RETURN VARCHAR2
Use this call:
使用此调用：
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;
import org.springframework.stereotype.Repository;
@Repository
public class MyRepository {
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
public String callGetTextFunction(String param) {
SimpleJdbcCall jdbcCall = new SimpleJdbcCall(jdbcTemplate)
.withCatalogName("PKG_PACKAGE") //package name
.withFunctionName("GET_TEXT"); //function name
SqlParameterSource paramMap = new MapSqlParameterSource()
.addValue("p_param", param));
//First parameter is function output parameter type.
return jdbcCall.executeFunction(String.class, paramMap));
}
}
回答by James
If you are using EclipseLink you can use a StoredFunctionCall object to call a stored function such as this.
如果您使用 EclipseLink，您可以使用 StoredFunctionCall 对象来调用这样的存储函数。
You could also define it as a named query using the @NamedStoredFunctionQuery.
您还可以使用@NamedStoredFunctionQuery 将其定义为命名查询。
回答by Artur P
It's a quite old question but I have not found here the proper solution... Here is a code that works in my project:
这是一个很老的问题，但我还没有在这里找到合适的解决方案......这是一个适用于我的项目的代码：
EntityManager em;
String functionName = "NameOfStoredFunction";
Map<String, Object> arguments = new LinkedHashMap();
arguments.put("nameOfArgument", "value");
StoredFunctionCall functionCall = new StoredFunctionCall();
functionCall.setProcedureName(functionName);
functionCall.setResult("RESULT", String.class);
for(String key : arguments.keySet()){
functionCall.addNamedArgumentValue(key, arguments.get(key));
}
ValueReadQuery valQuery = new ValueReadQuery();
valQuery.setCall(functionCall);
Query query = ((JpaEntityManager)em.getDelegate()).createQuery(valQuery);
String call_result = (String)query.getSingleResult();
