Java 如何使用返回参数从 Hibernate 调用 Oracle 函数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1703351/
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 an Oracle function from Hibernate with a return parameter?
提问by Mauli
My question is very much like Getting the return value of a PL/SQL function via Hibernate
我的问题很像通过 Hibernate 获取 PL/SQL 函数的返回值
I have a function which does some modifications internally and it returns a value.
我有一个函数在内部进行一些修改并返回一个值。
The original idea was to do something like this:
最初的想法是做这样的事情：
protected Integer checkXXX(Long id, Long transId)
throws Exception {
final String sql = "SELECT MYSCHEMA.MYFUNC(" + id + ", "
+ transId + ") FROM DUAL";
final BigDecimal nr = (BigDecimal) this.getHibernateTemplate()
.getSessionFactory().getCurrentSession().createSQLQuery(sql)
.uniqueResult();
return nr.intValue();
}
Unfortunately this doesn't work with Oracle. What is the recommended way to do something like this?
不幸的是，这不适用于 Oracle。做这样的事情的推荐方法是什么？
Is there a way to extract declared variables from within my statement?
有没有办法从我的语句中提取声明的变量？
采纳答案by ChssPly76
Hibernate Session provides a doWork()method that gives you direct access to java.sql.Connection. You can then create and use java.sql.CallableStatementto execute your function:
Hibernate Session 提供了一种doWork()方法，可让您直接访问java.sql.Connection. 然后，您可以创建并使用它java.sql.CallableStatement来执行您的函数：
session.doWork(new Work() {
public void execute(Connection connection) throws SQLException {
CallableStatement call = connection.prepareCall("{ ? = call MYSCHEMA.MYFUNC(?,?) }");
call.registerOutParameter( 1, Types.INTEGER ); // or whatever it is
call.setLong(2, id);
call.setLong(3, transId);
call.execute();
int result = call.getInt(1); // propagate this back to enclosing class
}
});
回答by Ladlestein
Yes, you do need to use an out parameter. If you use the doWork() method, you'd do something like this:
是的，您确实需要使用 out 参数。如果你使用 doWork() 方法，你会做这样的事情：
session.doWork(new Work() {
public void execute(Connection conn) {
CallableStatement stmt = conn.prepareCall("? = call <some function name>(?)");
stmt.registerOutParameter(1, OracleTypes.INTEGER);
stmt.setInt(2, <some value>);
stmt.execute();
Integer outputValue = stmt.getInt(1);
// And then you'd do something with this outputValue
}
});
回答by Kanipori_tamil
public static void getThroHibConnTest() throws Exception {
logger.debug("UsersActiion.getThroHibConnTest() | BEG ");
Transaction tx = null;
Connection conn = null;
CallableStatement cs = null;
Session session = HibernateUtil.getInstance().getCurrentSession();
try {
tx = session.beginTransaction();
conn = session.connection();
System.out.println("Connection = "+conn);
if (cs == null)
{
cs =
conn.prepareCall("{ ?=call P_TEST.FN_GETSUM(?,?) }");
}
cs.clearParameters();
cs.registerOutParameter(1,OracleTypes.INTEGER);
cs.setInt(2,1);
cs.setInt(3,2);
cs.execute();
int retInt=cs.getInt(1);
tx.commit();
}catch (Exception ex) {
logger.error("UsersActiion.getThroHibConnTest() | ERROR | " , ex);
if (tx != null && tx.isActive()) {
try {
// Second try catch as the rollback could fail as well
tx.rollback();
} catch (HibernateException e1) {
logger.debug("Error rolling back transaction");
}
// throw again the first exception
throw ex;
}
}finally{
try {
if (cs != null) {
cs.close();
cs = null;
}
if(conn!=null)conn.close();
} catch (Exception ex){;}
}
logger.debug("UsersActiion.getThroHibConnTest() | END ");
}
回答by worldrain
Alternative code :)
替代代码:)
if you want to direct result you can use below code
如果你想直接结果，你可以使用下面的代码
int result = session.doReturningWork(new ReturningWork<Integer>() {
@Override
public Integer execute(Connection connection) throws SQLException {
CallableStatement call = connection.prepareCall("{ ? = call MYSCHEMA.MYFUNC(?,?) }");
call.registerOutParameter( 1, Types.INTEGER ); // or whatever it is
call.setLong(2, id);
call.setLong(3, transId);
call.execute();
return call.getInt(1); // propagate this back to enclosing class
}
});
http://keyurj.blogspot.com.tr/2012/12/dowork-in-hibernate.html
http://keyurj.blogspot.com.tr/2012/12/dowork-in-hibernate.html
回答by Vlad Mihalcea
I wrote an article about various ways of calling Oracle stored procedures and functions from Hibernateso, to summarize it, you have the following options:
我写了一篇关于从 Hibernate 调用 Oracle 存储过程和函数的各种方法的文章，总结一下，您有以下选择：
With a
@NamedNativeQuery:@org.hibernate.annotations.NamedNativeQuery( name = "fn_my_func", query = "{ ? = call MYSCHEMA.MYFUNC(?, ?) }", callable = true, resultClass = Integer.class ) Integer result = (Integer) entityManager.createNamedQuery("fn_my_func") .setParameter(1, 1) .setParameter(2, 1) .getSingleResult();With JDBC API:
Session session = entityManager.unwrap( Session.class ); final AtomicReference<Integer> result = new AtomicReference<>(); session.doWork( connection -> { try (CallableStatement function = connection .prepareCall( "{ ? = call MYSCHEMA.MYFUNC(?, ?) }" ) ) { function.registerOutParameter( 1, Types.INTEGER ); function.setInt( 2, 1 ); function.setInt( 3, 1 ); function.execute(); result.set( function.getInt( 1 ) ); } } );With a native Oracle query:
Integer result = (Integer) entityManager.createNativeQuery( "SELECT MYSCHEMA.MYFUNC(:postId, :transId) FROM DUAL") .setParameter("postId", 1) .setParameter("transId", 1) .getSingleResult();
随着
@NamedNativeQuery：@org.hibernate.annotations.NamedNativeQuery( name = "fn_my_func", query = "{ ? = call MYSCHEMA.MYFUNC(?, ?) }", callable = true, resultClass = Integer.class ) Integer result = (Integer) entityManager.createNamedQuery("fn_my_func") .setParameter(1, 1) .setParameter(2, 1) .getSingleResult();使用 JDBC API：
Session session = entityManager.unwrap( Session.class ); final AtomicReference<Integer> result = new AtomicReference<>(); session.doWork( connection -> { try (CallableStatement function = connection .prepareCall( "{ ? = call MYSCHEMA.MYFUNC(?, ?) }" ) ) { function.registerOutParameter( 1, Types.INTEGER ); function.setInt( 2, 1 ); function.setInt( 3, 1 ); function.execute(); result.set( function.getInt( 1 ) ); } } );使用本机 Oracle 查询：
Integer result = (Integer) entityManager.createNativeQuery( "SELECT MYSCHEMA.MYFUNC(:postId, :transId) FROM DUAL") .setParameter("postId", 1) .setParameter("transId", 1) .getSingleResult();
