CODEWITHSUNDEEP
javaprepared-statement
Angelina
Angelina

Reputation: 2265

How to dynamically set parameters for SQL prepared statement

I have 3 fields where user can choose to enter values. He has to enter at least one value for system to return anything.

I need to dynamically build my WHERE clause based on which one of the boxes user entered value.

I am having hard time with setting parameter values.

This is what I have so far:

StringBuilder queryStr = new StringBuilder();
Object param[];
int paramCount = 0;
Database db = (Database) ssb.getObject("db");

queryStr.append("SELECT * FROM tblData ");
queryStr.append("WHERE deleted != 1 ");

if(!field1.equals("")){
    queryStr.append("AND field1 = ? ");
    paramCount++;
}
if(!field2.equals("")){
    queryStr.append("AND field2 = ? ");
    paramCount++;
}
if(!field3.equals("")){
    queryStr.append("AND field3 = ? ");
    paramCount++;
}
param = new Object[paramCount];

for (int i=0; i <= paramCount; ++i)
     param[i] = field1; <-- ????

db.queryPS(queryString.toString(), param);

Upvotes: 0

Views: 913

Answers (2)

castletheperson
castletheperson

Reputation: 33516

Create a List of the fields that aren't empty.

StringBuilder queryStr = new StringBuilder();
List<String> param = new ArrayList<>();
Database db = (Database) ssb.getObject("db");

queryStr.append("SELECT * FROM tblData ");
queryStr.append("WHERE deleted != 1 ");

String[] fields = { field1, field2, field3 };
for (int i = 0; i < fields.length; i++) {
    if (!fields[i].isEmpty()) {
        queryStr.append("AND field" + (i + 1) + " = ? ");
        param.add(fields[i]);
    }
}

db.queryPS(queryString.toString(), param.toArray());

Upvotes: 1

Angelina
Angelina

Reputation: 2265

Awesome input @4castle. Thanks to you I got it resolved :)

StringBuilder queryStr = new StringBuilder();
ArrayList<String> parameterValues = new ArrayList<String>();
Database db = (Database) ssb.getObject("db");

queryStr.append("SELECT * FROM tblData ");
queryStr.append("WHERE deleted != 1 ");

if(!field1.equals("")){
    queryStr.append("AND field1 = ? ");
    parameterValues.add(field1);
}
if(!field2.equals("")){
    queryStr.append("AND field2 = ? ");
    parameterValues.add(field2);
}
if(!field3.equals("")){
    queryStr.append("AND field3 = ? ");
    parameterValues.add(field3);
}

db.queryPS(queryString.toString(), parameterValues.toArray());

Upvotes: 0

Related Questions