2

I am trying to generate sql query based on user input. There are 4 search fields on the UI: FIRST_NAME, LAST_NAME, SUBJECT, MARKS

Based on user input I am planning to generate SQL query. Input can be of any combination.

eg: select * from TABLE where FIRST_NAME="some_value";

This query needs to be generated when FIRST_NAME is given and other fields are null

select * from TABLE where FIRST_NAME="some_value" and LAST_NAME="some_value";

This query needs to be generated when FIRST_NAME and LAST_NAME are given and other fields are null

Since there are 4 input fields, number of possible queries that can be generated are 24 (factorial of 4).

One idea is to write if condition for all 24 cases.

Java pseudo code:

String QUERY = "select * from TABLE where ";

if (FIRST_NAME!=null) {

    QUERY = QUERY + "FIRST_NAME='use_input_value';"

}
if (LAST_NAME!=null) {

    QUERY = QUERY + "LAST_NAME='use_input_value';"

}
if (SUBJECT!=null) {

    QUERY = QUERY + "SUBJECT='use_input_value';"

}
if (MARKS!=null) {

    QUERY = QUERY + "MARKS='use_input_value';"

}

I am not able to figure out how to generate SQL queries with AND coditions for multiple Input values. I have been through concepts on dynamically generate sql query but couldn't process further.

Can someone help me on this.

FYI: I have been through How to dynamically generate SQL query based on user's selections?, still not able to generate query string based on user input.

Improve this question

1 Answer 1

Reset to default
1

Let's think about what would happen if you just ran the code you wrote and both FIRST_NAME and LAST_NAME are provided. You'll wind up with this:

select * from TABLE where FIRST_NAME='use_input_value';LAST_NAME='use_input_value';

There are two problems here:

  1. The query is syntactically incorrect.
  2. It contains the literals 'use_input_value' instead of the values you want.

To fix the first problem, let's first add and to the start of each expression, and remove the semicolons, something like this:

String QUERY = "select * from TABLE where";
if (FIRST_NAME!=null) {
    QUERY = QUERY + " and FIRST_NAME='use_input_value'";
}

Notice the space before the and. We can also remove the space after where.

Now the query with both FIRST_NAME and LAST_NAME will look like this:

select * from TABLE where and FIRST_NAME='use_input_value' and LAST_NAME='use_input_value'

Better but now there's an extra and. We can fix that by adding a dummy always-true condition at the start of the query:

String QUERY = "select * from TABLE where 1=1";

Then we append a semicolon after all the conditions have been evaluated, and we have a valid query:

select * from TABLE where 1=1 and FIRST_NAME='use_input_value' and LAST_NAME='use_input_value';

(It may not be necessary to append the semicolon. Most databases don't require semicolons at the end of a single query like this.)

On to the string literals. You should add a placeholder instead, and simultaneously add the value you want to use to a List.

String QUERY = "select * from TABLE where";
List<String> args = new ArrayList<>();
if (FIRST_NAME!=null) {
    QUERY = QUERY + " and FIRST_NAME=?";
    args.add(FIRST_NAME);
}

After you've handled all the conditions you'll have a string with N '?' placeholders and a List with N values. At that point just prepare a query from the SQL string and add the placeholders.

PreparedStatement statement = conn.prepareStatement(QUERY);
for (int i = 0; i < args.size(); i++) {
    statement.setString(i + 1, args[i]);
}

For some reason columns and parameters are indexed starting at 1 in the JDBC API, so we have to add 1 to i to produce the parameter index.

Then execute the PreparedStatement.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Thanks for detailed explanation. Is there is a way to build string instead of using PreparedStatement?
Also in String literal part string QUERY = "select * from TABLE where"; is used. Can you please explain why AND is not used here. Since I am ATHENA(AWS service, PRESTO based) to query I cannot use Prepared statements. I think I should go for option 1(1=1 condition).
Please suggest me any better approaches in case there are any. Once again thanks for detailed explanation
In this scenerio(where prepareStatement is not supported) I think I should got for string format to generate sql queries
If FIRST_NAME etc. are user input then you don't want to append them directly to the string due to xkcd.com/327. That's why you'd normally use a PreparedStatement with placeholders, to allow JDBC to properly escape or sanitize the input for you.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.