因为jasper server中Multi-Select控件得到的值不是String,,而是Collection。这些值可以显示在report中或者打印出来。但是当Multi-Select中的值用到sql query中必须是String,所以需要把它转换成String list.
先建立一个parameter,类型为java.util.Collection。可以设定 default value 为
Arrays.asList(new Integer[] {Integer.valueOf(1), Integer.valueOf(2) } )
或者
Arrays.asList(new String[] {"entry1", "entry2" })
然后建立另一个parameter,类型为String。defaultValueExpression为下面其中的一个
$P{MultiTaskInput}.toString().replaceAll("[\\[\\]]", "")//for integer
或者
$P{MultiTaskInput}.toString().replaceAll("[\\[\\]]", "'").replaceAll(", ", "', '")//for String(char in database) 把 , 换成 ' , '
**因为collection的String格式为 [1],[2],[3]。所以到把方括号替换掉。
把第二个parameter带入query中,SQL为
SELECT * FROM TABLE WHERE cloumn in ($P!{param}) //注意 $P后要加!
这样就可以建立动态query, 数据库运行select基于parameter中list的值。 类似于
SELECT * FROM table WHERE column IN ('1','3','5')
关于$P!{}的原文解释:
The $P{} syntax is used for introducing parameter values just like you use the ? in a JDBC prepared statement. They are placeholders for prepared statement parameters.
The $P!{} simply expands the value of the parameter within the query text. It is used for dynamically building queries or for providing the whole query text as a parameter.
The $X{} syntax is a new addition in JR 1.3.2 and is something in between the previous two. It is a way to dynamically build a part of the query, but also introduce parameter placeholders.
There are two $X{} functions supported by the SQL query executer in JasperReports:
$X{IN, colName, paramName}
and
$X{NOTIN, colName, paramName}
paramName should be the name of a report parameter that is either of type Collection or array.
This syntax is to be used in order to produce these SQL sequences in the WHERE clause:
colName IN {val1, val2, ...}
or
colName NOT IN {val1, val2, ...}
where val1, val2, ... are values in the collection or array parameter mentioned.
Check the supplied /demo/sample/query for a working example of this.