passing SQL "IN" parameter list in jasperreport

Question

I am working on Jasper Reports and my query uses SQL 'IN' predicate.

SELECT customer_name AS NAME, 
       id_customer   AS ID 
  FROM customer 
 WHERE customer_role IN ($P{roles})

Here the role parameter can have 1 or more integer values and will be dynamically decided when generating the jasper report.

Can anybody please help me on how to set the value of 'roles' parameter thru Java program dynamically.

score 27 · Accepted Answer · answered Jun 03 '11 at 11:28

27

Jasper Report has a special variable $X for that:

select * from customer where $X{IN,customer_role,roles}

should work. See here and here.

answered Jun 03 '11 at 11:28

Aaron Digulla

Woodchuck · Answer 2 · 2021-12-21T14:33:49.097

2

The examples linked to in the accepted answer don't come up for me.

An alternative that worked for me is instead of using:

...
WHERE customer_role IN ($P{roles})

I used this:

...
WHERE customer_role IN ($P!{roles})

And for the roles variable I pass in a String containing one or more values, each in single quotes, separated by commas (e.g., '1','2','3').

See here for reference.

edited Dec 21 '21 at 14:33

answered Jan 25 '21 at 17:23

Woodchuck

1

While this will work it will **not** use prepared statement, hence the application can be targeted by [SQL injection attacks](https://stackoverflow.com/questions/332365/how-does-the-sql-injection-from-the-bobby-tables-xkcd-comic-work) – Petter Friberg Jan 26 '21 at 08:09
Petter Friberg, do you have prepared statement usage example with jasper-reports? I believe the accepted answer also does not use prepared statements. – Woodchuck Dec 21 '21 at 14:32

score 1 · Answer 3 · edited Feb 24 '20 at 16:51

1

To complement @Aaron response, you can configure a list parameter ("roles" in your case) within JasperSoft Studio as follows:

List parameter

edited Feb 24 '20 at 16:51

mkl

answered Feb 23 '20 at 11:03

mikk3l

3 Answers3