Java JDBC 批量查询的高性能
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9853197/
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
JDBC batch query for high performance
提问by superche
I want to do batch query DB for high performance, example sql to query based on different customer_id:
我想做高性能的批处理查询数据库，示例sql根据不同的customer_id进行查询：
select order_id,
cost
from customer c
join order o using(id)
where c.id = ...
order by
I'm not sure how to do it using JDBC statement. I know I can use stored procedure for this purpose, but it's much better if I can just write sql in Java app instead of SP.
I'm using DBCP for my Java client and MySQL DB.
我不确定如何使用 JDBC 语句来做到这一点。我知道我可以为此目的使用存储过程，但是如果我可以在 Java 应用程序而不是 SP 中编写 sql 会更好。
我将 DBCP 用于我的 Java 客户端和 MySQL DB。
采纳答案by Edwin Dalorzo
The JDBC Specification 4.0describes a mechanism for batch updates. As such, the batch features in JDBC can be used for insert or update purposes. This is described in chapter 14 of the specification.
在JDBC 4.0规范描述了批量更新的机制。因此，JDBC 中的批处理功能可用于插入或更新目的。这在规范的第 14 章中进行了描述。
AFAIK there is not a mechanism for select batches, probably because there is no apparent need for that since, as others have recommended, you can simply retrieve all the rows that you want at once by properly constructing your query.
AFAIK 没有选择批次的机制，可能是因为没有明显的需要，因为正如其他人所建议的那样，您可以通过正确构建查询来简单地一次检索您想要的所有行。
int[] ids = { 1, 2, 3, 4 };
StringBuilder sql = new StringBuilder();
sql.append("select jedi_name from jedi where id in(");
for (int i = 0; i < ids.length; i++) {
sql.append("?");
if(i+1 < ids.length){
sql.append(",");
}
}
sql.append(")");
System.out.println(sql.toString());
try (Connection con = DriverManager.getConnection(...)) {
PreparedStatement stm = con.prepareStatement(sql.toString());
for(int i=0; i < ids.length; i++){
stm.setInt(i+1, ids[i]);
}
ResultSet rs = stm.executeQuery();
while (rs.next()) {
System.out.println(rs.getString("jedi_name"));
}
} catch (SQLException e) {
e.printStackTrace();
}
Output
输出
select jedi_name from jedi where id in(?,?,?,?)
Luke, Obiwan, Yoda, Mace Windu
Is there any reason why you would consider that you need a thing like a batch-select statement?
您是否有任何理由认为您需要像批处理选择语句这样的东西？
回答by aviad
It is really does not matter what is your SQL statement (you can use as many nested joins as your DB can handle). Below is basic Java example (not DBCP). For DBCP example which is pretty similar you can check out their example.
您的 SQL 语句是什么实际上并不重要（您可以使用数据库可以处理的尽可能多的嵌套连接）。下面是基本的 Java 示例（不是 DBCP）。对于非常相似的 DBCP 示例，您可以查看他们的示例。
Connection connect = DriverManager.getConnection(YOUR_CONNECTION_STRING);
// Statements allow to issue SQL queries to the database
Statement statement = connect.createStatement();
ResultSet resultSet = statement.executeQuery("select order_id, cost
from customer c
join order o using(id)
where c.id = ...
order by");
