如何在Jasper Reports中正确传递SQL查询作为参数? [英] How to correctly pass a SQL query as parameter in Jasper Reports?
问题描述
我已经研究了与该主题有关的所有其他问题,并且按照那里提到的所有步骤进行操作,但是当尝试将整个SQL语句作为参数传递时,仍然出现错误.
这是我的SQLQuery参数:
<parameter name="SQLTag" class="java.lang.String">
<defaultValueExpression><![CDATA["SELECT * FROM REPORT_VER WHERE TAG BETWEEN $P{Von} AND $P{Bis}"]]></defaultValueExpression>
</parameter>
我已经检查了它,并且当直接传递到Report SQL字段中时,SQL可以正常工作. 但是,由于我要使用条件SQL查询,因此需要通过参数传递它们.有人知道我想念什么吗?
我试图在 queryString /
上使用$P{SQLTag}
和$P!{SQLTag}
表达式
堆栈跟踪:
net.sf.jasperreports.engine.JRException: net.sf.jasperreports.engine.JRException: Error executing SQL statement for: Abnahme.
at com.jaspersoft.studio.editor.preview.view.control.ReportControler.fillReport(ReportControler.java:548)
at com.jaspersoft.studio.editor.preview.view.control.ReportControler.access$20(ReportControler.java:523)
at com.jaspersoft.studio.editor.preview.view.control.ReportControler$5.run(ReportControler.java:404)
at org.eclipse.core.internal.jobs.Worker.run(Worker.java:55)
Caused by: net.sf.jasperreports.engine.JRException: Error executing SQL statement for: Abnahme.
at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:344)
at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1245)
at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:723)
at net.sf.jasperreports.engine.fill.BaseReportFiller.setParameters(BaseReportFiller.java:438)
at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:550)
at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFill.run(BaseFillHandle.java:123)
如果在 defaultValueExpression 上使用参数,则应将它们作为通用的 Java 变量进行操作.>
例如:
<parameter name="fieldFilter" class="java.lang.String">
<defaultValueExpression><![CDATA["someValue"]]></defaultValueExpression>
</parameter>
<parameter name="query" class="java.lang.String">
<defaultValueExpression><![CDATA["SELECT * FROM some_table WHERE some_field = '" + $P{fieldFilter} + "'"]]></defaultValueExpression>
</parameter>
<queryString>
<![CDATA[$P!{query}]]>
</queryString>
或类似这样:
<parameter name="intFieldFilter" class="java.lang.Integer">
<defaultValueExpression><![CDATA[2017]]></defaultValueExpression>
</parameter>
<parameter name="strFieldFilter" class="java.lang.String">
<defaultValueExpression><![CDATA["someValue"]]></defaultValueExpression>
</parameter>
<parameter name="query" class="java.lang.String">
<defaultValueExpression><![CDATA[String.format("SELECT * FROM some_table WHERE some_field = '%1$s' AND trunc(date_field, 'yyyy') = trunc(to_date(%2$d, 'yyyy'), 'yyyy')", $P{strFieldFilter}, $P{intFieldFilter})]]></defaultValueExpression>
</parameter>
<queryString>
<![CDATA[$P!{query}]]>
</queryString>
I've looked into all other questions regarding this topic and I followed all steps mentioned there, however I'm still getting an error when trying to pass an entire SQL statement as Parameter.
This is my SQLQuery Parameter:
<parameter name="SQLTag" class="java.lang.String">
<defaultValueExpression><![CDATA["SELECT * FROM REPORT_VER WHERE TAG BETWEEN $P{Von} AND $P{Bis}"]]></defaultValueExpression>
</parameter>
I've checked it and the SQL works perfectly fine when passed directly into the Report SQL field. However because I want to have conditional SQL queries I need them passed through a parameter. Anyone knows what I'm missing?
I tried to use $P{SQLTag}
and $P!{SQLTag}
expressions at queryString/
Stack trace:
net.sf.jasperreports.engine.JRException: net.sf.jasperreports.engine.JRException: Error executing SQL statement for: Abnahme.
at com.jaspersoft.studio.editor.preview.view.control.ReportControler.fillReport(ReportControler.java:548)
at com.jaspersoft.studio.editor.preview.view.control.ReportControler.access$20(ReportControler.java:523)
at com.jaspersoft.studio.editor.preview.view.control.ReportControler$5.run(ReportControler.java:404)
at org.eclipse.core.internal.jobs.Worker.run(Worker.java:55)
Caused by: net.sf.jasperreports.engine.JRException: Error executing SQL statement for: Abnahme.
at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:344)
at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1245)
at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:723)
at net.sf.jasperreports.engine.fill.BaseReportFiller.setParameters(BaseReportFiller.java:438)
at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:550)
at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFill.run(BaseFillHandle.java:123)
In case using parameters at defaultValueExpression you should operate with them as a common Java variable.
For example:
<parameter name="fieldFilter" class="java.lang.String">
<defaultValueExpression><![CDATA["someValue"]]></defaultValueExpression>
</parameter>
<parameter name="query" class="java.lang.String">
<defaultValueExpression><![CDATA["SELECT * FROM some_table WHERE some_field = '" + $P{fieldFilter} + "'"]]></defaultValueExpression>
</parameter>
<queryString>
<![CDATA[$P!{query}]]>
</queryString>
or like this:
<parameter name="intFieldFilter" class="java.lang.Integer">
<defaultValueExpression><![CDATA[2017]]></defaultValueExpression>
</parameter>
<parameter name="strFieldFilter" class="java.lang.String">
<defaultValueExpression><![CDATA["someValue"]]></defaultValueExpression>
</parameter>
<parameter name="query" class="java.lang.String">
<defaultValueExpression><![CDATA[String.format("SELECT * FROM some_table WHERE some_field = '%1$s' AND trunc(date_field, 'yyyy') = trunc(to_date(%2$d, 'yyyy'), 'yyyy')", $P{strFieldFilter}, $P{intFieldFilter})]]></defaultValueExpression>
</parameter>
<queryString>
<![CDATA[$P!{query}]]>
</queryString>
这篇关于如何在Jasper Reports中正确传递SQL查询作为参数?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!