需要 JasperReports 空参数值才能显示全部

发布于 2024-10-12 10:14:41 字数 616 浏览 3 评论 0原文

我已经解决这个问题有一段时间了，我希望你能帮助我。

我在 iReport 上制作了一份报告，即使某些参数为空，我也希望填写该报告。

这是我所拥有的，

SELECT
    evento."titulo" AS evento_titulo,
    evento."data_inicio_realizacao" AS evento_data_inicio_realizacao,
    evento."data_fim_realizacao" AS evento_data_fim_realizacao,
    evento."id" AS evento_id
FROM
    "public"."evento" evento
WHERE
    evento."id" = $P{eventoid} OR
    evento."data_inicio_realizacao" BETWEEN $P{data1} AND $P{data2}

我希望 WHERE 子句是可选的，即，如果我将这些参数保留为空（或 id 的情况下为 0 值），我希望获取所有值而不是空白报告。

是否可以？我发现有些人创建了 $P{WHERE_CLAUSE} 并将整个 where 子句作为其值传递，但这对我不起作用。

原文

I've been through this problem for a while and I hope you can help me.

I have a report made on iReport, and I'd like the report to be filled even if some parameters are null.

Here's what I have

SELECT
    evento."titulo" AS evento_titulo,
    evento."data_inicio_realizacao" AS evento_data_inicio_realizacao,
    evento."data_fim_realizacao" AS evento_data_fim_realizacao,
    evento."id" AS evento_id
FROM
    "public"."evento" evento
WHERE
    evento."id" = $P{eventoid} OR
    evento."data_inicio_realizacao" BETWEEN $P{data1} AND $P{data2}

I'd like the WHERE clause to be optional, i.e., if I leave these parameters null (or 0-valued on id's case), I want to get all values instead of a blank report.

Is it possible? I found some people making a $P{WHERE_CLAUSE} and passing the whole where clause as its value, but it didn't work for me.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

甜嗑 2024-10-19 10:14:41

你可以这样做，而不是你现在正在做的动态sql...

select xxx
from <table>
where $P{eventoid} is null or evento."id" = $P{eventoid}

抱歉回复这么旧的帖子...

You can do this instead of the dynamic sql you're doing now...

select xxx
from <table>
where $P{eventoid} is null or evento."id" = $P{eventoid}

Sorry for replying to such an old post...

回复收藏 0 原文

忘羡 2024-10-19 10:14:41

好吧，我更努力地尝试了一下，并弄清楚如何通过在 $P{} 参数内传递整个 WHERE 子句来使其工作。

我只是删除了整个 WHERE 子句并添加了以下内容：

$P!{whereClause}

我在其中添加了一个参数，并从报告中删除了三个参数 $P{eventoid}、$P{data1} 和 $P{data2}。

我正在使用 Spring MVC 并在我的类 Controller 中，在该页面的 POST 请求映射下，我添加了一个名为 whereClause 的字符串变量，并将其值设置为“WHERE 1=1”（如果您愿意，也可以设置为 true）

String whereClause = "WHERE 1=1";

if (relIndice.getEventoId() != 0) {
    whereClause += " AND evento.id = " + relIndice.getEventoId().toString();
}

if (relIndice.getData1() != null) {
    whereClause += " AND evento.data_inicio_realizacao >= '" + relIndice.getData1().toString() + "'";
}

if (relIndice.getData2() != null) {
whereClause += " AND evento.data_inicio_realizacao <= '" + relIndice.getData2().toString() + "'";
}

whereClause += " ORDER BY evento.data_inicio_realizacao";

：设置 whereClause 变量的初始值后，我创建了 3 个 IF 语句，每个语句针对我想要包含在 SQL 中的 WHERE 语句中的字段之一。

由于“WHERE 1=1 无论如何都会运行，所以 AND 之前需要一个空格，以便形成一个干净且有效的语法。

最后我添加了一个 ORDER BY 子句，该子句也将运行。
就是这样，参数的值将是您从要使用的 getter 获取的值。

就我而言，我可以将任何值保留为 null 或零（在 eventoId 的情况下，因为它是 int），并且我将拥有该表中的所有记录，因为 SELECT 语句不会看到 WHERE 子句...因为它没有不存在！

谢谢你们！

Well I tried a bit harder and figured out how to make it work by passing the whole WHERE clause inside a $P{} param.

I simply deleted the whole WHERE clause and put this:

$P!{whereClause}

I put a single parameter in there and deleted the three parameters $P{eventoid}, $P{data1} and $P{data2} from the report.

I'm working on Spring MVC and inside my class Controller, under the POST request mapping for that page, I added a String variable called whereClause, and set its value to "WHERE 1=1" (or true if you prefer):

String whereClause = "WHERE 1=1";

if (relIndice.getEventoId() != 0) {
    whereClause += " AND evento.id = " + relIndice.getEventoId().toString();
}

if (relIndice.getData1() != null) {
    whereClause += " AND evento.data_inicio_realizacao >= '" + relIndice.getData1().toString() + "'";
}

if (relIndice.getData2() != null) {
whereClause += " AND evento.data_inicio_realizacao <= '" + relIndice.getData2().toString() + "'";
}

whereClause += " ORDER BY evento.data_inicio_realizacao";

After setting the initial value for the whereClause variable, I made 3 IF statements, each for one of the fields I want to include in the WHERE statement, in the SQL.

Since "WHERE 1=1 will run anyway, a blankspace is needed before the AND, in order to make a clean and working syntax.

And in the end I added an ORDER BY clause that will run anyway too.
And that's it, the values of your parameters will be the values you'll get from the getters you want to use.

In my case I can leave any values null or zero(on eventoId's case since it's an int), and I'll have all the records from that table since the SELECT statement won't see the WHERE clause... Coz it doesn't exist!

Thank you guys!

回复收藏 0 原文