CODEWITHSUNDEEP
javajdbc
rajputhch
rajputhch

Reputation: 627

preparing query based on input values in java

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

Upvotes: 0

Views: 4926

Answers (4)

blong824
blong824

Reputation: 4030

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'

Upvotes: 0

JB Nizet
JB Nizet

Reputation: 691715

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.

Upvotes: 2

Matthew Gilliard
Matthew Gilliard

Reputation: 9498

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.

Upvotes: 1

jzd
jzd

Reputation: 23629

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.

Upvotes: 0

Related Questions