Dynamic query Statement

Question

I need to write a query for a MySQL database using jdbc that depends on some business logic, which can be summarized as something like this

    if (someCondition) {
        myQuery = "stuff";
    } else {
        if (anotherCondition) { 
            myQuery = "some stuff";
        } else {
            myQuery = "even more stuff";
        }
    }

And I intend to build the myQuery string to serve as a template for a PreparedStatement object and then fill the ? with the actual data. however, the number of ? depends on the same logic as above, and therefore I'm duplicating the logic in the code

How can I avoid it?

Thanks

If you are adding different or additional where clauses based on criteria, use a StringBuffer for myQuery and keep appending those. Then use that to prepare the statement, — Bajal
– Bajal, Commented Jan 18, 2016 at 13:04
One way you can do is instead of having ? have #{name1} #{name2} etc . Use myquery.replaceAll("#{name1}",value1), .. if your query does'nt have name1 then it will not get replaced . Only the names which are present are replaced. This way u can avoid too many if conditions. — Mohammed Ahmed
– Mohammed Ahmed, Commented Jan 18, 2016 at 13:06

pkalinow · Accepted Answer · 2016-01-18 13:11:29Z

2

You can create a prepared statement and set its parameters inside the if blocks:

if (someCondition) {
    stmt = connection.prepareStatement("stuff");
    stmt.set... // set attributes
} else if (anotherCondition) {
    stmt = connection.prepareStatement("some stuff");
    // set attributes
} else {
    stmt = connection.prepareStatement("even more stuff");
    //set attributes
}

answered Jan 18, 2016 at 13:11

pkalinow

1,7471 gold badge17 silver badges46 bronze badges

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

Comments

Olivier Grégoire · Accepted Answer · 2016-01-18 13:05:14Z

1

You should use something like this:

String myQuery = ...;
List<Object> parameters = ... ;
if (someCondition) {
    myQuery = "stuff";
    parameters = ... ;
} else {
    if (anotherCondition) { 
        myQuery = "some stuff";
        parameters = ... ;
    } else {
        myQuery = "even more stuff";
        parameters = ... ;
    }
}

answered Jan 18, 2016 at 13:05

Olivier Grégoire

35.8k23 gold badges101 silver badges143 bronze badges

Comments

Sergey Kalinichenko · Accepted Answer · 2016-01-18 13:08:31Z

You can avoid duplication by preparing the objects that you are going to bind to the statement together with the query string:

List<Object> paramValues = new ArrayList<>();
String myQuery;
if (someCondition) {
    myQuery = "stuff ?";
    paramValues.add("single");
} else {
    if (anotherCondition) { 
        myQuery = "some stuff ? ?";
        paramValues.add("one");
        paramValues.add(2);
    } else {
        myQuery = "even more stuff ? ? ? ?";
        paramValues.add("one");
        paramValues.add(2);
        paramValues.add(3.0);
        paramValues.add("four");
    }
}
// Make prepared statement
PreparedStatement ps = ...
// Bind parameters
for (int i = 0 ; i != paramValues.size() ; i++) {
    ps.setObject(i+1, paramValues.get(i));
}

Collectives™ on Stack Overflow

Dynamic query Statement

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related