CODEWITHSUNDEEP
javamysqlsql
Kim
Kim

Reputation: 1081

Building a dynamic SQL query based on user input in Java

If I have a search module which has the following: search box, dropdown 1, dropdown 2.

And I have a query like this:

SELECT * FROM MY_TABLE where q1 = 'searchBox' AND q2 = 'dropdown1' AND q3 = 'dropdown2'

How can I make that query dynamic depending on user filter, so if the user only fills the search box, the query will be:

SELECT * FROM MY_TABLE where q1 = 'searchBox'

If the user fills search box and dropdown1, the query will be:

SELECT * FROM MY_TABLE where q1 = 'searchBox' AND q2 = 'dropdown1'

and if the user doesn't fill anything, the query will be:

SELECT * FROM MY_TABLE

I am using Java.

Upvotes: 2

Views: 5498

Answers (2)

JwJamison
JwJamison

Reputation: 1

To improve upon the code provided by @blacktide

PreparedStatement preparedStatement = connection.prepareStatement(sb.toString());
for (int i = 1; i <= params.size(); i++) {
    preparedStatement.setString(i, params.get(i));
}

You would instead want the line within the for loop to be

preparedStatement.setString(i, params.get(i-1));

Since arrays start at 0, we'd want to grab that zeroth index to set the first value added to the list to the first value to be updated in the SQL string. Otherwise you would grab the second value within params which would not be the intended value.

Upvotes: 0

blacktide
blacktide

Reputation: 12176

There are frameworks that can help with this:

If you'd like to create a quick and simple solution, you can do something like the following:

List<String> params = new ArrayList<>();
StringBuilder sb = new StringBuilder();
sb.append("SELECT * FROM MY_TABLE WHERE 1 = 1");

if (searchBox != null) {
    sb.append(" AND q1 = ?");
    params.add(searchBox);
}

if (dropdown1 != null) {
    sb.append(" AND q2 = ?");
    params.add(dropdown1);
}

if (dropdown2 != null) {
    sb.append(" AND q3 = ?");
    params.add(dropdown2);
}

PreparedStatement preparedStatement = connection.prepareStatement(sb.toString());
for (int i = 1; i <= params.size(); i++) {
    preparedStatement.setString(i, params.get(i));
}

ResultSet resultSet = preparedStatement.executeQuery();

Upvotes: 4

Related Questions