postgresql 存储过程作为查询：CallableStatement 与 PreparedStatement
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7457477/
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
stored procedures as queries: CallableStatement vs. PreparedStatement
提问by Paul Bellora
PostgreSQL documentationrecommends using a CallableStatementto call stored procedures.
PostgreSQL 文档推荐使用 aCallableStatement来调用存储过程。
In the case of a stored procedure that returns a rowset, what are the differences between using CallableStatement:
对于返回行集的存储过程，使用之间有什么区别CallableStatement：
String callString = "{ call rankFoos(?, ?) }";
CallableStatement callableStatement = con.prepareCall(callString);
callableStatement.setString(1, fooCategory);
callableStatement.setInt(2, minimumRank);
ResultSet results = statement.executeQuery();
And using a regular PreparedStatement:
并使用常规PreparedStatement：
String queryString = "SELECT FooUID, Rank FROM rankFoos(?, ?);";
PreparedStatement preparedStatement = connection.prepareStatement(queryString);
preparedStatement.setString(1, fooCategory);
preparedStatement.setInt(2, minimumRank);
ResultSet results = statement.executeQuery();
As I understand, CallableStatementoffers a language-agnostic way of calling stored procedures. This doesn't matter to me though, since I know I'm using PostgreSQL. As far as I can see, the obvious advantage of using the PreparedStatementis a more versatile query, treating the stored procedure as a table, on which I can use WHERE, JOIN, ORDER BY, etc.
据我了解，CallableStatement提供了一种与语言无关的调用存储过程的方式。不过这对我来说并不重要，因为我知道我正在使用 PostgreSQL。据我所看到的，使用的明显优势PreparedStatement是更通用的查询，处理存储过程作为一个表，关于这一点我可以使用WHERE，JOIN，ORDER BY，等。
Are there aspects or differences between the methods that I'm missing? In the case of a stored procedure used as a query, which is recommended?
我缺少的方法之间是否存在方面或差异？在将存储过程用作查询的情况下，推荐使用哪个？
采纳答案by dlawrence
I'm pretty sure the second approach does not work at all with some RDBMS's, but since you are only going to use PostgreSQL, that shouldn't matter too much. For your simple case, there really isn't much of a downside. There are two issues I can see popping up:
我很确定第二种方法对某些 RDBMS 根本不起作用，但是由于您只会使用 PostgreSQL，所以这不会太重要。对于您的简单案例，确实没有太大的缺点。我可以看到两个问题：
Depending on how the stored procedures are written, they may require you to register out parameters in order to execute the procedure. That's not going to be possible at all with prepared statements. If you control both the creation of the stored procedure and the calling code, you probably don't have to worry about this.
It limits the effectiveness of calling stored procedures in the first place. One of the main advantages of a stored procedure is to encapsulate the querying logic at the database level. This allows you to tune the query or in some cases add functionality without having to make changes to your code. If you're planning on adding where clauses joins to the results of the stored procedure call, why not just put the original query in your java layer?
根据存储过程的编写方式，它们可能需要您注册参数才能执行该过程。对于准备好的语句，这根本不可能。如果您控制存储过程的创建和调用代码，您可能不必担心这一点。
它首先限制了调用存储过程的有效性。存储过程的主要优点之一是在数据库级别封装查询逻辑。这允许您调整查询或在某些情况下添加功能而无需更改您的代码。如果您打算将 where 子句加入到存储过程调用的结果中，为什么不将原始查询放在您的 java 层中呢？
回答by John_sei4
The main difference is independence and encapsulated way of programming.
主要区别在于编程的独立性和封装方式。
Imagine that you are a Java Programmer and you don't know how to use Database, in this way you can not use the second method and u will meet problems in the second way.
假设你是一个Java程序员，你不知道如何使用数据库，这样你就不能使用第二种方法，你会遇到第二种方法的问题。
First method allows you to do your java code, and ask somebody to write the quires for you as Stored-Procedure, so u can use them easily.
第一种方法允许您编写 Java 代码，并请某人为您编写查询作为存储过程，因此您可以轻松使用它们。
I do agree with @dlawrence
我同意@dlawrence
