Querying mysql with java with multiple possible "where" statements

Question

I'm trying to find a nice solution to a movie filtering system built in java and with a mysql database. The user is supposed to be able to filter which movies they wish to see based on a number of attributes, such as: director, actor, length, genre, year,...,. In total there are 11 fields which can be used to filter the query.

The problem is some of these fields can (and probably will) be left blank. For instance, maybe the user only wants to filter data based on a certain genre, director and length. Or maybe they only want to filter it based on the prodution studio, and dont care about the other filter options.

I have made a connection to the server, and the problem is in creating the "SQL_String" that I will use in statement.executeQuery(SQL_String).

Lets say I only wanted to filter for one field. Then I know I could write

String field = //user input (for example: actor)
String filter = //user input (for example: 'tom cruise')
String SQL_String = "SELECT * FROM Movies WHERE "+field + "=" +filter

But if i want to allow the user to filter based on several (or zero) fields, then I dont know how to write the code.

Some example queries could be:

"SELECT * FROM Movies WHERE (director = 'steven spielberg' AND genre = 'action' AND length >100)"

"SELECT * FROM Movies WHERE (type = 'tv-series' AND actor = 'bob odenkirk')"

So the user can specify which fields they want to filter (if any) and i need to come up with a java code that can take those into account and construct a query string.

For an optional parameter sql paradigm I would suggest using a `CriteriaBuilder` — Scary Wombat, Oct 16 '18 at 00:18
BTW, even if you decide to remain using `SQL` as above, you should not be simply appended columns and values as this has a risk of SQL Injection attacks — Scary Wombat, Oct 16 '18 at 00:19
Do not use `*` when it comes to use in application, it can cause the trouble.. — dwir182, Oct 16 '18 at 00:29
Would it work to use CONCAT(attribute1,attribute2,...,11) LIKE(userinput1%userinput2%...%11)? Where attribute1 can be actor, and userinput1 either empty if the user doesnt want to filter based on that, or 'tom cruise' for example if they do want to filter it. — noobprogrr, Oct 16 '18 at 01:04

score 0 · Answer 1 · answered Oct 16 '18 at 01:05

Since you don't know how many fields the user will filter on but you do know that the data you're dealing with has two parts (the field and the filter), the first two things that come to my mind are maps and tuples. Since, unlike Python, Java does not have a built in tuple data type (to my knowledge), here is a small example solution that I thought of for your problem solved using Java's HashMap and Map classes.

In this example, I create a HashMap with the key being a string for the "field" and the value being a string for the "filter". You can set these values based on the user input wherever you have that in your code (in this example, simply hard-coded in the main method). Then you can loop through the key-value pairs in your HashMap (see this helpful post), appending the key and value as well as the additional characters necessary for the query. This is a simple example but shows a possible solution route.

If you want to make sure that this solution works for the cases where you filter value is an integer, then just add in another if-statement in the loop to try parsing for an integer and if one exists to not add the extra \' escape characters.

import java.util.HashMap;
import java.util.Map;

public class MovieQueryTest {
  public static void main(String[] args) {

    String SQL_Query = "SELECT * FROM Movies WHERE ";

    HashMap<String, String> queryFilters = new HashMap<>();

    queryFilters.put("director", "Steven Spielberg");
    queryFilters.put("type", "tv-series");
    queryFilters.put("actor", "Bob Odenkirk");

    boolean firstQueryFilter = true;

    for (Map.Entry<String, String> entry : queryFilters.entrySet()) {
      if (firstQueryFilter) {
        SQL_Query += entry.getKey() + "=\'" + entry.getValue() + "\'";
        firstQueryFilter = false;
      } else {
        SQL_Query += " AND " + entry.getKey() + "=\'" + entry.getValue() + "\'";
      }

    }

    System.out.println(SQL_Query);

  }
}

Querying mysql with java with multiple possible "where" statements

1 Answers1