How to Pass a Variable Parameter List to JasperReports' SQL 'IN' Predicate?

Passing a Variable Parameter List in JasperReports using SQL "IN" Predicate

When working with JasperReports and the SQL "IN" predicate, it is necessary to pass a parameter list to the query dynamically. This article demonstrates how to set the value of a parameter containing a list of integer values in a Java program.

The SQL query below requires a parameter list "roles" containing one or more integer values:

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

To dynamically set the value of the "roles" parameter in Java, use the "$X" variable provided by JasperReports:

// Assume the "roles" parameter is a list of integers
List<Integer> roles = ...;

// Convert the list to a comma-separated string
String roleString = roles.stream().map(String::valueOf).collect(Collectors.joining(","));

// Set the parameter value using the "$X" variable
parameters.put("roles", "$X{IN,customer-role," + roleString + "}");

Using the "$X{IN,column-name,parameter-name}" syntax allows you to specify the column to compare, the parameter name, and the list of values.

For example, the following query would use the "roles" parameter to filter a column called "customer_role":

SELECT * 
  FROM customer 
 WHERE $X{IN,customer_role,roles}

This solution allows you to pass a dynamic parameter list to the SQL "IN" predicate in JasperReports, ensuring that the query filters the data correctly based on the provided values.

The above is the detailed content of How to Pass a Variable Parameter List to JasperReports' SQL 'IN' Predicate?. For more information, please follow other related articles on the PHP Chinese website!