java 从 Hibernate 调用数据库函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16902514/
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
Calling a DB function from Hibernate
提问by nbonbon
I am trying to build a job that reindexes our DB every Friday. We are using hibernate and everything I have tried so far has failed. Is there a way to execute SQL managament commands using hibernate? Such as:
我正在尝试构建一个每周五重新索引我们的数据库的工作。我们正在使用休眠，到目前为止我尝试过的一切都失败了。有没有办法使用休眠执行 SQL 管理命令？如：
Session session = helper.getSession();
session.createQuery("DBCCREINDEX(User)").executeUpdate();
Or is there a better way to reindex within Hibernate?
或者有没有更好的方法在 Hibernate 中重新索引？
回答by Marcel St?r
The examples referenced below are for Oracle PL/SQL but conceptually it's the same for other databases. By "build a job" and "execute SQL managament commands" I assume you'll have some sort of function stored in the database that you need to call.
下面引用的示例适用于 Oracle PL/SQL，但从概念上讲，它也适用于其他数据库。通过“构建作业”和“执行 SQL 管理命令”，我假设您将在需要调用的数据库中存储某种函数。
https://stackoverflow.com/a/2943733/131929has a lengthy explanation but it boils down to
https://stackoverflow.com/a/2943733/131929有一个冗长的解释，但归结为
CallableStatement statement = session.connection().prepareCall(
"{ ? = call name_of_your_function(?) }");
which allows you to work directly with a Connectionand a PreparedStatement/CallableStatement.
这允许您直接使用 aConnection和 a PreparedStatement/ CallableStatement。
https://stackoverflow.com/a/1703398/131929is essentially the same but it makes use of Session#doWork
https://stackoverflow.com/a/1703398/131929本质上是一样的，但它利用Session#doWork
session.doWork(new Work() {
public void execute(Connection connection) throws SQLException {
CallableStatement call = connection.prepareCall("{ ? = call name_of_your_function(?) }");
Note that in both examples it was required to process the function's return value in the Java code which may or may not be the case with you.
请注意，在两个示例中，都需要在 Java 代码中处理函数的返回值，您可能会也可能不会遇到这种情况。
回答by nbonbon
We ended up working our way after many errors up to the solution below. The prepared statement is a stored procedure to reindex created in MSSQL SSMS.
在多次错误之后，我们最终按照自己的方式工作，直到下面的解决方案。准备好的语句是在 MSSQL SSMS 中创建的用于重新索引的存储过程。
private void reindex() {
Session session = helper.getSession();
PreparedStatement ps;
session.doWork(new Work() {
public void execute(Connection connection) throws SQLException {
PreparedStatement ps = connection.prepareStatement("reindexTable");
ps.execute();
}
});
}
This seems to have given the desired result and executes...however we just have to solve a timeout issue now.
这似乎给出了所需的结果并执行了……但是我们现在只需要解决超时问题。
回答by JB Nizet
Session.createQuery()expects a HQL query. If you want to execute SQL, use Session.createSQLQuery().
Session.createQuery()需要 HQL 查询。如果要执行 SQL，请使用Session.createSQLQuery().
回答by sakthi
Try this code -
试试这个代码 -
private void reindex() {
Session session = helper.getSession();
PreparedStatement ps;
session.doWork(new Work() {
public void execute(Connection connection) throws SQLException {
PreparedStatement ps = connection.prepareStatement("reindexTable");
ps.execute();
}
});
}
