preparing query based on input values in java

Question

I dont know whether it is possible or not,But here is my question:

I am getting 13 input fields,based on input

 Ex:String firstname=request.getParameter("firstname"); ......

I have to prepare sql where clause like if (firstname!=null){ where firstname='test' and ..}

Any advises for this kind of scenario.

Regards,

Raj

JB Nizet · Accepted Answer · 2011-03-03 20:27:56Z

2

If I understand correctly, you would like to generate queries dynamically, depending on the value of input fields. There are frameworks helping to do that, like MyBatis. But you could roll your own solution with prepared statements :

String query = "select * from foo f";
List<String> clauses = new ArrayList<String>();
List<Object> parameters = new ArrayList<Object>();

if (firstName != null) {
    clauses.add("f.name = ?");
    parameters.add(firstName);
}
// ...
if (!clauses.isEmpty()) {
    query += " where " + StringUtils.join(clauses, " and ");
}

PreparedStatement ps = connection.prepareStatement(query);
for (int i = 0; i < parameters.size(); i++) {
    ps.setObject(i + 1, paremeters.get(i));
}

You could make it even better by supporting SQL types, by using the builder pattern, etc., but you should get the idea with this simple example.

edited Mar 3, 2011 at 20:27

answered Mar 3, 2011 at 20:03

JB Nizet

694k94 gold badges1.3k silver badges1.3k bronze badges

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

6 Comments

rajputhch Over a year ago

@JB Nizet:Thanks,Can you give me idea,how to use builder pattern for this problem to avoid checking if else conditions.

Matthew Gilliard Over a year ago

This is a nice solution, the cost of bringing in StringUtils is probably worth it. You need to space around "where " and "and ", to avoid select * from foo fwhere ...

JB Nizet Over a year ago

The builder pattern could be used to chain method calls, add support for and, or, joins, parentheses, and so on. But whatever the solution, if you want to add a clause if a parameter is not null, you will have to write an if block. You could encapsulate it into a method, though : void addClauseIfNotNull(Object paramValue, String clauseToAdd, List<String> clauses, List<Object> parameters) { if (param != null) {clauses.add(clause); paremeters.add(param);}

rajputhch Over a year ago

@JB Nizet:Thanks,I agree with your answer.Let me try this.

rajputhch Over a year ago

@JB Nizet:Still is it posiible to optimize by adding all parameters to the list object,from the list object by iterating all the parameters and exclude those having null values.

|

Matthew Gilliard · Accepted Answer · 2011-03-03 20:20:25Z

1

I assume you are using a JDBC connection to your database. You should use prepared statements, otherwise you are wide open for SQL injection attacks.

The second question is how to prevent a WHERE clause involving a field which the user did not supply. There are many (2^13 == 8192) combinations, so it is not practical to have a different statement for each possible user input. It would be possible to build the prepared statement dynamically in your case:

String statement = "SELECT * FROM " + dbName + "." + tableName;

String condition = " WHERE";
List<String> params = new ArrayList<String>();

if ( firstname  != null ){
    statement += condition + " firstname  = ?";
    condition = " AND";
    params.add(firstname);
}

if ( familyname != null ){
    statement += condition + " familyname  = ?";
    condition = " AND";
    params.add(familyname);
}

connection.prepareStatement(updateString);

Then you will need to add the contents of params when you execute the prepared statement.

edited Mar 3, 2011 at 20:20

answered Mar 3, 2011 at 19:50

Matthew Gilliard

9,5203 gold badges35 silver badges48 bronze badges

2 Comments

rajputhch Over a year ago

Thanks,i want approach to prepare only where clause if input value is not null.i will place this where clause in table.

rajputhch Over a year ago

Hi,I am not going to use this where clause,SP will fetch this where clause i prepared,it will take care rest of the things.So here My question is preparation of where caluse based on input parameters not null.

jzd · Accepted Answer · 2011-03-03 19:53:49Z

0

You will need to dynamically build the query in Java or use a stored procedure that will not filter on a field if it is null.

answered Mar 3, 2011 at 19:53

jzd

23.6k9 gold badges58 silver badges76 bronze badges

Comments

blong824 · Accepted Answer · 2011-03-03 21:16:34Z

I was curious about this as well so I created a new answer. This is what I came up with. It can be optimized but this does what you want using the Builder pattern. You can see from my test I pass in a null and it is omitted from the where string.

public class WhereBuilder {

private final String requestParm1;
private final String requestParm2;
private final String requestParm3;
private final String requestParm4;
private final String requestParm5;
private StringBuilder whereString = new StringBuilder();

public static class Builder {

    private String requestParm1 = null;
    private String requestParm2 = null;
    private String requestParm3 = null;
    private String requestParm4 = null;
    private String requestParm5 = null;
    private StringBuilder whereString = new StringBuilder("WHERE ");

    public Builder() {}

    public Builder requestParm1(String value) {
        if (value != null) {
            requestParm1 = value;
            whereString.append(" requestParm1 = '"  + requestParm1 + "' AND");
        }
        return this;
    }

    public Builder requestParm2(String value) {
        if (value != null) {
            requestParm2 = value;
            whereString.append(" requestParm2 = '"  + requestParm2 + "' AND");
        }
        return this;
    }

    public Builder requestParm3(String value) {
        if (value != null) {
            requestParm3 = value;
            whereString.append(" requestParm3 = '"  + requestParm3 + "' AND");
        }
        return this;
    }

    public Builder requestParm4(String value) {
        if (value != null) {
            requestParm4 = value;
            whereString.append(" requestParm4 = '"  + requestParm4 + "' AND");
        }
        return this;
    }

    public Builder requestParm5(String value) {
        if (value != null) {
            requestParm5 = value;
            whereString.append(" requestParm5 = '"  + requestParm5 + "' AND");
        }
        return this;
    }


    public WhereBuilder build() {
        return new WhereBuilder(this);
    }

}

private WhereBuilder(Builder builder) {
    requestParm1 = builder.requestParm1;
    requestParm2 = builder.requestParm2;
    requestParm3 = builder.requestParm3;
    requestParm4 = builder.requestParm4;
    requestParm5 = builder.requestParm5;
    whereString = builder.whereString;
}

public String getWhereString() {
    whereString.delete(whereString.length()-3, whereString.length());
    return whereString.toString();
}

public static void main(String[] args) {
    WhereBuilder wb = new WhereBuilder.Builder().requestParm1("hello").requestParm2("how")
                        .requestParm3("are").requestParm4(null).requestParm5("you").build();
    String whereString = wb.getWhereString();
    System.out.println(whereString);
}

}

The output of the main method is

WHERE  requestParm1 = 'hello' AND requestParm2 = 'how' AND requestParm3 = 'are' AND requestParm5 = 'you'

Still here also,conditions is checking null values,Is there any utility to exclude null values containing properties,before building the query.
Wouldn't that utility check for null behind the scenes? What do you want your call to look like?

Collectives™ on Stack Overflow

preparing query based on input values in java

4 Answers 4

6 Comments

2 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

6 Comments

2 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related