DMoses DMoses - 1 year ago 71
SQL Question

Conditional Where clauses in JasperReports

Let's say I want a JasperReport that lets the user filter on a date if they so wish. The SQL is as follows:

select * from foo where bar = $P{bar} and some_date > $P{}

Now, I don't want to filter by some date if they didn't pass the date in. I found the following kludge that people use:

select * from foo where bar = $P{bar} $P!{}

And the
parameter is defined with the following default:

($P{} == null || $P{}.equals("")) ? "" : "AND some_date >'" + new java.sql.Date($P{}.getTime()).toString() + "'"

This is not working as the
doesn't output the date in a format that my SQL server understands. I would like to have the conditional still use a prepared statement with the jdbc driver and toss the parameter in, I just want the prepared statement to be dependent on if the parameter is null or not. Can this be done?

Answer Source

Before you have used the $P!{} expression the JDBC-Driver does all formatting for you.

But if you use the $P!{} expression you have to format yourself.

Something like this should work:

$P{} == null 
"AND some_date >'" + (new SimpleDateFormat("dd.MM.yyyy HH:mm:ss.SSS")).format($P{}) + "'"

Depending on your data type you have to customize dd.MM.yyyy HH:mm:ss.SSS.

If you don't want to use the $P!{} expression you can avoid it with the solution below.

I personally don't like this way. It also may cause a bad execution plan.

If don't want to use $P!{} because you worry about sql injection. It's needless as long your parameter $P{} contains a safe data type like java.lang.Date.

Create a parameter. Let's call it ${is_null_pram} and add a default expression with param class Integer:

($P{} == null ? 1 : 0)

Now you can query:

FROM foo 
    bar = $P{bar}
            some_date > $P{}
            OR 1 = $P{is_null_pram}
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download