Java dynamically generate SQL query - ATHENA

Question

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.

Answer 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.