碧玉报表中的动态查询 [英] Dynamic query in jasper reports

问题描述

我们正在使用jaspersoft Studio创建报告，而我陷入了查询部分. 从逻辑上讲，我想要的查询类型是:

We are using jaspersoft studio to create a report and i am stuck at query part. Logically the kind of query i want is :

select * from mytable where
                    IF (condition1)
                              raw_sql_part_1
                    ELSE
                              raw_sql_part_2

所以现在在查询编辑器对话框"中实现这一点，我写道:

So now to achive this in "Query editor dialog", i wrote:

select * from mytable where $P!{param1}

并在表达式编辑器中将"param1"默认值添加为:

and added "param1" default value in Expression editor as:

"$P{param2}.equals("A") ?  "1 <> 1" :"1=1" , is 'For prompting' as false

还在参数列表中添加了"param2"，其中"is for提示"为true

Also added "param2" in parameter list with 'is for prompting' as true

我希望这样: 当我点击预览时，它将提示我输入param2的值，然后基于param2解析param1中的条件，然后最终将其替换为实际查询中的条件. param2的默认值为空字符串.

I expect this: When i hit preview it will prompt me for param2 value and then based on param2 resolve the condition in param1 and then finally substitute that in the actual query. The default value of the param2 is empty string.

实际发生的情况: 当我点击预览时，它询问我期望的param2的值，但是param2的值不用于解析param1中定义的条件，因为param1条件始终解析为其他部分，即"1 == 1"，并且主查询也变为

What actually happens: when i hit preview it ask me for the value of param2 which is expected,but value of param2 is not used to resolve condition defined in param1 as param1 condition always resolved to else part i.e "1==1" and the main query also became

select * from mytable where 1==1

Q1:我期望错了吗?

Q1: Am i expecting wrong ?

问题2:为什么不使用param2来解析param1中定义的条件?

我们正在使用Jaspersoft Studio 6.1.1版

We are using Jaspersoft studio version 6.1.1

推荐答案

实际上，我认为您不能基于另一个参数的值在一个参数上设置defaultValueExpression，但是它似乎可以如果参数在jrxml中的顺序正确，则可以正常工作.

Actually I did not think you could set defaultValueExpression on a parameter based on the value of another parameter, but it seems to work if the order of the parameters are correct in the jrxml.

示例

<parameter name="param1" class="java.lang.String">
    <defaultValueExpression><![CDATA["A"]]></defaultValueExpression>
</parameter>
<parameter name="param2" class="java.lang.String" isForPrompting="false">
    <defaultValueExpression><![CDATA[$P{param1}.equals("A") ?  "1 <> 1" :"1=1"]]></defaultValueExpression>
</parameter>

有效，所以我的猜测是您没有按正确的顺序定义参数.

Works, so my guess is that you have not defined the parameters in correct order.

注意:

select * from mytable where $P{param1}，将尝试使用准备好的语句

select * from mytable where $P{param1}, will try to use prepared statement

可以使用带有预准备语句或简单字符串替换的参数来执行jasper报告中的查询.

Queries in jasper report can be executed using parameters with prepared statement or with simple string substitution.

• $P{param}->准备好的语句

$P!{param}->字符串替换

在您的示例中，查询似乎应该是字符串替换

From your example it seems like String substition the query should be

select * from mytable where $P!{param1}

有关更多信息，请参见此: JasperReports:将参数传递给查询

See this for more information: JasperReports: Passing parameters to query

这篇关于碧玉报表中的动态查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！