Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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{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?

like image 638
Daniel Moses Avatar asked Sep 07 '12 20:09

Daniel Moses


2 Answers

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}
        )
like image 109
edze Avatar answered Oct 20 '22 17:10

edze


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.

like image 1
helder.tavares.silva Avatar answered Oct 20 '22 16:10

helder.tavares.silva