CODEWITHSUNDEEP
javasqlarraysdatabaseselect
Java Apprentice
Java Apprentice

Reputation: 92

Efficient way to select data with a single condition

Is there an efficient way to obtain a list (preferably an array, a ResultSet will do) to SELECT a lot of rows.

For example:

Connection con = DriverManager.getConnection(host,username,password);
String sql = "SELECT * FROM table_name WHERE food = ? AND expiration > ?";
PreparedStatement stmt = con.prepareStatement(sql);

Using the above code, I want to get all the food from a given array that isn't expired.

String[] foodList  = {"banana","apple","orange",...}

where the expiration date is a constant date (lets say 3 days ago). However, the way I have it is that the String and PreparedStatement are in a for loop that loop the number of foods in the array to individually check the expiration date. This creates a lot of ResultSets after I execute each individually.

Upvotes: 1

Views: 93

Answers (2)

eckes
eckes

Reputation: 10433

Most SQL Databases support a IN (list) expression. This is roughly equivalent to providing a or expression:

SELECT id FROM table WHERE food IN ('Apple', 'Banana')  AND exp < ?

is similar to

SELECT id FROM table WHERE (food = 'Apple' or food = 'Banana') AND exp < ?

In both cases some RDBMS can optimize it.

However first of all there is a limitation in the number of list items you can specify in the IN or number of characters you can use in the statement. So if your list can be variable long you need to be prepared to run multiple statements.

Secondly you cannot* set a array as an argument to a PreparedStatement and expect it to work with IN.

Unfortunately in plain JDBC all you can do is to concatenate a String. This is frowned upon, but there is no good alternative (unless you want to do something like giving the list of foods as a single list and use a "instring" expression).

Make sure to add as many ? (but not too many) as you expect parameters and then set them in the IN:

String[] foods = ...;
int remain = foods.length;
int start = 0;
while(remain > 0)
{  if (remain >= 100)
     executeBatch(foods, start, 100); start+=100; remain-=100;
   else if (remain >= 30)
     executeBatch(foods, start, 30); start+=30; remain-=30;
   else {
     executeBatch(foods, start, 1); start+=1; remain-=1;
   }
}


void executeBatch(String[] f, int off, int len)
{
    StringBuilder sqlBuf = StringBuilder("... IN(");
    for(int i=0;i<len;i++) {
        sqlBuf.append((i!=0)?",?":"?");
    }
    String sql = sqlBuf.append(") AND exp < ?").toString();
    PreparedStatement ps = c.prepareStatement(sql);
    for(int i=0;i<foods.length;i++)
        ps.setString(i+1, foods[i+off]);
    ps.setTimestamp(foods.length+1, now);
    ....
}

This avoids to generate a lot of different SQL statement to compile. (Only 100,30 or 1 ?)). You can use the same logic for the OR case.

* not to be confused with ARRAY database type.

Upvotes: 2

Sean Bright
Sean Bright

Reputation: 120714

Probably not the most elegant solution, and you won't get any performance benefit from the prepared statement (but you will get parameter binding):

StringBuilder sql = new StringBuilder("SELECT * FROM table_name WHERE expiration > ? AND food IN (");

for (int i = 0; i < foodList.length; i++) {
    if (i > 0) {
        sql.append(',');
    }
    sql.append('?');
}

sql.append(")");

Connection con = DriverManager.getConnection(host, username, password);
PreparedStatement stmt = con.prepareStatement(sql.toString());

stmt.setDate(1, expirationDate);
for (int i = 0; i < foodList.length; i++) {
    stmt.setString(i + 2, foodList[i]);
}

ResultSet rs = stmt.executeQuery();

/* ... Do Stuff ... */

Upvotes: 2

Related Questions