CODEWITHSUNDEEP
javamysqlsqlprepared-statementdao
chicout
chicout

Reputation: 937

Can PreparedStatement do not take into account some conditions in WHERE clause?

For example, I have a statement

"SELECT * FROM Reports WHERE StartDate >= ? WHERE EndDate <= ? AND Performer = ?"

But sometimes some input fields on the web page are not filled, so I have to not take into account this conditions. i.e. I have no startdate filled, so statement must be

"SELECT * FROM Reports WHERE EndDate <= ? AND Performer = ?"

There are 3 different conditions. So, Do I have to write 8 different statements and DAO methods to accomplish the task? Really? Maybe there are other solutions?

Edit: I use MySQL/

Upvotes: 4

Views: 1720

Answers (3)

mohita207
mohita207

Reputation: 1

No Prepared statement cannot exclude contions on its own. The query as to be contructed to avoid unnecessary conditions.

You can generate SQL using the code :

StringBuilder whereClause = new StringBuilder();
String and = "";

if(EndDate == null || EndDate.length == 0)
{
    whereClause.append(your condition);
    and = " and";
}

if(StartDate == null || StartDate.length == 0)
{
    whereClause.append(and).append(your condition);
    and = " and";
}

if(Performer == null || Performer.length == 0)
{
    whereClause.append(and).append(your condition);
}

and based on you generated query you need to set the parameters to the prepared statement.

Upvotes: 0

krishnakumarp
krishnakumarp

Reputation: 9295

You dont need 8 different statements. You can build the query using if statements. For e.g.,

String query = "SELECT * FROM Reports where true";
if(startDate != null)
  query = query + " and startDate <= ?";
if(endDate != null)
  query = query + " and endDate <= ?";
if(performer != null)
  query = query + " and performer = ?";

Hope it works for you.

Upvotes: 0

Bohemian
Bohemian

Reputation: 425043

Change your SQL to cater for nulls. Because you have not told us which database you are using, I will use "vanilla" SQL:

SELECT * 
FROM Reports
WHERE (EndDate <= ? OR ? is null)
AND (Performer = ? OR ? is null)

Pass the parameters in twice each.

The other choice is to alter the SQL based on parameters being null (for example omitting Performer = ? from the where clause), but this can require a lot of code and testing. Iwould use the adaptable SQL and if it performs badly, then attempt something more advanced.

Upvotes: 9

Related Questions