Batch select and iterate mysql results in java to decrease waiting time

I have made an application that visualizes locations with diatom species found at those locations. My problem is, it’s extremely slow. When 128 locations are found, it takes roughly 130 seconds to visualise. With 6000+ locations in my database, this would be way too slow. The reason for this speed is the way I’m retrieving all diatoms found at a location for each location. I am opening a connection to the database for each iteration and I assume that’s where a lot of time is lost. Is there a way to speed this up? I have looked at addBatch and executeBatch. But how do I iterate the results if I would use batches. Or is there a different way I should be doing this?

For each Location object in listOfLocations, I use the Location object.getName to retrieve all diatom species at that location from the database and add those as a hashmap back to the same Location object that is in listOfLocations.

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;

To summarize: how can i open the connection with the database, execute all queries in one go and iterate the results to readd them to the Location objects.

Answer

You should phrase this as a WHERE IN (...) query

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...
}

To be explicit here, the above code generates a WHERE IN (...) clause with the same number of ? placeholders as there are locations in your listOfLocations list. So, assuming it had 5 placeholders, the WHERE clause would be this:

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

We then iterate the list of locations, and bind each value to a ? placeholder above. This approach avoids the multiple queries, because now we can cover every location with just a single query.