javamysqlselectjdbcprepared-statement

Running the same select query multiple times with different parameters

I have a Java program that needs to iterate through a HashMap to get parameters that are then used to query the MySQL database.

The code is as follows:

Iterator<Entry<String, Double>>it = ws.entrySet().iterator();
Connection con = null;

while(it.hasNext())  
{
    Entry<String, Double>pairs = it.next();
    PreparedStatement ps = con.prepareStatement("select doc_freq from lookup where word=?");
    ps.setString(1, pairs.getKey());
    ResultSet rs = ps.executeQuery();
}

The process of repeatedly accessing the database for every iteration of the loop (which is about 500 times) is slowing down my application.

Is there any way I can send all these parameters at once, so that I can access the database only once?

Solution

  • Considering ws is a map, you can do a single query that way:

    Connection con = getConnection();
Set<String> ks = ws.keySet();

if (ks.size() > 0) {
    StringBuilder inStatement = new StringBuilder("?");
    for (int i = 1; i < ks.size(); i++) {
        inStatement.append(", ?");
    }

    PreparedStatement ps = con.prepareStatement("select doc_freq from lookup where word in (" + inStatement.toString() + ")");

    int k = 1;
    for (String key : keySet) {
        ps.setString(k++, key);
    }
    ResultSet rs = ps.executeQuery();
}