在java中批量选择和迭代mysql结果以减少等待时间

Question

try (Connection connection = DbConnection.getConnection()) {
    PreparedStatement preparedStatement;
    for (int x = 0; x <= listOfLocations.size() - 1; x++) {
        String query = "select diatom.species from entry" +
                " join location on location.name = entry.name" +
                " join diatom on diatom.taxonKey = entry.taxonKey" +
                " where location.name = ? AND diatom.species != \"\"";
        assert connection != null;
        preparedStatement = connection.prepareStatement(query);
        preparedStatement.setString(1, listOfLocations.get(x).getName());
        ResultSet rs = preparedStatement.executeQuery();
        HashMap<String, String> diatomHashmap = new HashMap<>();
        while (rs.next()) {
            //checks if diatom species was in the input
            //of the program to assign present/notpresent
            if (diatomArray.contains(rs.getString(1))) {
                diatomHashmap.put(rs.getString(1), "Present");
            } else if (!diatomArray.contains(rs.getString(1))) {
                diatomHashmap.put(rs.getString(1), "notPresent");
            }
            listOfLocations.get(x).setDiatoms(diatomHashmap);
        }
    }
} catch (SQLException ex) {
    System.out.println(ex);
}
return listOfLocations;

总结一下：如何打开与数据库的连接，一次执行所有查询，并迭代结果以将其读入位置对象。

Answer 1

您应该将其表述为where IN（...）查询

String query = "SELECT d.species " +
    "FROM entry e " +
    "INNER JOIN location l ON l.name = e.name " + 
    "INNER JOIN diatom d ON d.taxonKey = e.taxonKey " +
    "WHERE d.species != '' AND l.name IN ";

StringBuilder whereIn = new StringBuilder("(?");
for (int i=0; i < listOfLocations.size()-1; ++i) {
    whereIn.append(", ?");
}
whereIn.append(")");
query += whereIn.toString();

preparedStatement = connection.prepareStatement(query);
for (int i=1; i <= listOfLocations.size(); ++i) {
    preparedStatement.setString(i, listOfLocations.get(i-1).getName());
}

ResultSet rs = preparedStatement.executeQuery();
while (rs.next()) {
    // iterate and process result set here...
}

为了明确起见，上面的代码生成了一个WHERE IN(...)子句，该子句的？占位符的数量与ListofLocations列表中的位置相同。因此，假设它有5个占位符，WHERE子句应该是：

WHERE d.species != '' AND l.name IN (?, ?, ?, ?, ?)

然后我们迭代位置列表，并将每个值绑定到上面的？占位符。这种方法避免了多次查询，因为现在我们只需一个查询就可以覆盖每个位置。