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{some.date}
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!{some.date.fragment}
And the some.date.fragment
parameter is defined with the following default:
($P{some.date} == null || $P{some.date}.equals("")) ? "" : "AND some_date >'" + new java.sql.Date($P{some.date}.getTime()).toString() + "'"
This is not working as the toString
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?
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{some.date} == null
?
""
:
"AND some_date >'" + (new SimpleDateFormat("dd.MM.yyyy HH:mm:ss.SSS")).format($P{some.date}) + "'"
)
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{some.date}
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{some.date} == null ? 1 : 0)
Now you can query:
SELECT
*
FROM foo
WHERE
bar = $P{bar}
AND
(
some_date > $P{some.date}
OR 1 = $P{is_null_pram}
)
I think you can use the function:
$X{EQUAL, <column_name>, <parameter_name>}
It optimizes the query as you can see in this help page.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With