个人中心
gpt4 book ai didi

java - 碧 Jade 报告 : IN Clause is empty in case using subDataset with parameter

转载 作者：行者123 更新时间：2023-11-29 08:41:24 25 4
gpt4 key购买 nike

为什么下面的查询在 JasperReports 中没有返回所需的结果。

SELECT bbd.Bill_Id, bbd.ItemName, bbd.ItemQuantity, bbd.ItemPrice, bbd.Date FROM `borrowerbillsdetails` bbd JOIN `borrowerbills` as bb ON bbd.`Bill_Id` = bb.`Bill_Id` WHERE $X{IN ,Bill_Id, billIds}

它只返回 3 个结果，而在我传递的参数列表中有 7 个 ID，所有 ID 在数据库表中都有数据。

我传递参数的 Controller 代码:

String cnic = txtCnic.getText();
String borrowerHistorypath = "/home/JRXML/BorrowerHistory.jrxml";
Connection con = null;
try {
    con = DBConnection.getConnection();

    List<BorrowerBill> borrowerBillLists = borrowerBillService.getAllBillsByCnic(cnic);
    List<Integer> billIds = new ArrayList<>();
    for(BorrowerBill borrowerBill:borrowerBillLists){
        billIds.add(borrowerBill.getBillId());
    }

    FileInputStream  fileInputStream = new FileInputStream(borrowerHistorypath);
    BufferedInputStream bufferedInputStream = new BufferedInputStream(fileInputStream);
    Map map = new HashMap();
    map.put("cnic", cnic);
    map.put("billIds",billIds);

    JasperReport jasperReport = (JasperReport) JasperCompileManager.compileReport(bufferedInputStream);
    JasperPrint jasperPrint = JasperFillManager.fillReport(jasperReport, map, con);

    JasperViewer jasperViewer = new JasperViewer(jasperPrint, false);
    jasperViewer.viewReport(jasperPrint,false);

相关的 jasper 报告 Jrxml 文件如下(用于 BorrowerBillItems)

数据集

<subDataset name="BillItemsDataSet" uuid="39900337-1c6a-403d-a889-82ee3ca1fd8a">
    <parameter name="billIds" class="java.util.List"/>
    <queryString language="SQL">
        <![CDATA[SELECT bbd.Bill_Id, bbd.ItemName, bbd.ItemQuantity, bbd.ItemPrice, bbd.Date FROM `borrowerbillsdetails` bbd JOIN `borrowerbills` as bb ON bbd.`Bill_Id` = bb.`Bill_Id` WHERE $X{IN ,Bill_Id, billIds}]]>
    </queryString>
    <field name="Bill_Id" class="java.lang.Integer">
        <fieldDescription><![CDATA[]]></fieldDescription>
    </field>
    <field name="ItemName" class="java.lang.String">
        <fieldDescription><![CDATA[]]></fieldDescription>
    </field>
    <field name="ItemQuantity" class="java.lang.Double">
        <fieldDescription><![CDATA[]]></fieldDescription>
    </field>
    <field name="ItemPrice" class="java.lang.Double">
        <fieldDescription><![CDATA[]]></fieldDescription>
    </field>
    <field name="Date" class="java.sql.Date">
        <fieldDescription><![CDATA[]]></fieldDescription>
    </field>
</subDataset>

表格

<jr:table xmlns:jr="http://jasperreports.sourceforge.net/jasperreports/components" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports/components http://jasperreports.sourceforge.net/xsd/components.xsd">
    <datasetRun subDataset="BillItemsDataSet" uuid="ccd44e17-2c5d-417c-b4a7-554d197ce483">
        <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
    </datasetRun>
    <jr:column width="113" uuid="44bd8219-2732-4853-bf2e-960be7c528be">
        <jr:tableHeader style="table 5_TH" height="30" rowSpan="1">
            <staticText>
                <reportElement x="0" y="0" width="113" height="30" uuid="bebb3df5-79aa-4905-b478-6995f7c53f05"/>
                <textElement textAlignment="Center" verticalAlignment="Middle">
                    <font size="14" isBold="true"/>
                </textElement>
                <text><![CDATA[S No]]></text>
            </staticText>
        </jr:tableHeader>
        <jr:detailCell style="table 8_TD" height="20" rowSpan="1">
            <textField>
                <reportElement x="0" y="0" width="113" height="20" uuid="5a44f3b8-f7e4-48ce-bf0a-fc2ca083a979"/>
                <textElement textAlignment="Center" verticalAlignment="Middle">
                    <font size="14" isBold="false"/>
                </textElement>
                <textFieldExpression><![CDATA[$F{Bill_Id}]]></textFieldExpression>
            </textField>
        </jr:detailCell>
    </jr:column>
    <jr:column width="135" uuid="c76aecbd-16aa-4ca2-90c9-613c7e5abee1">
        <jr:tableHeader style="table 5_TH" height="30" rowSpan="1">
            <staticText>
                <reportElement x="0" y="0" width="135" height="30" uuid="6441c557-38b8-47dc-bfdc-e2d476da830c"/>
                <textElement textAlignment="Center" verticalAlignment="Middle">
                    <font size="14" isBold="true"/>
                </textElement>
                <text><![CDATA[Item Name]]></text>
            </staticText>
        </jr:tableHeader>
        <jr:detailCell style="table 8_TD" height="20" rowSpan="1">
            <textField>
                <reportElement x="0" y="0" width="135" height="20" uuid="5a44f3b8-f7e4-48ce-bf0a-fc2ca083a979"/>
                <textElement textAlignment="Center" verticalAlignment="Middle">
                    <font size="14" isBold="false"/>
                </textElement>
                <textFieldExpression><![CDATA[$F{ItemName}]]></textFieldExpression>
            </textField>
        </jr:detailCell>
    </jr:column>
    <jr:column width="118" uuid="a98065a6-a07d-4bda-9e08-6d81552ce2ff">
        <jr:tableHeader style="table 5_TH" height="30" rowSpan="1">
            <staticText>
                <reportElement x="0" y="0" width="118" height="30" uuid="6928b4cb-519c-4020-930f-44ddaf1ddd6a"/>
                <textElement textAlignment="Center" verticalAlignment="Middle">
                    <font size="14" isBold="true"/>
                </textElement>
                <text><![CDATA[Quantity]]></text>
            </staticText>
        </jr:tableHeader>
        <jr:detailCell style="table 8_TD" height="20" rowSpan="1">
            <textField>
                <reportElement x="0" y="0" width="118" height="20" uuid="5a44f3b8-f7e4-48ce-bf0a-fc2ca083a979"/>
                <textElement textAlignment="Center" verticalAlignment="Middle">
                    <font size="14" isBold="false"/>
                </textElement>
                <textFieldExpression><![CDATA[$F{ItemQuantity}]]></textFieldExpression>
            </textField>
        </jr:detailCell>
    </jr:column>
    <jr:column width="119" uuid="fdeb9dac-b4ec-4cf1-936e-ac3c70846b61">
        <jr:tableHeader style="table 5_TH" height="30" rowSpan="1">
            <staticText>
                <reportElement x="0" y="0" width="119" height="30" uuid="f6afc863-f4f1-49d6-97ce-13bf049013f8"/>
                <textElement textAlignment="Center" verticalAlignment="Middle">
                    <font size="14" isBold="true"/>
                </textElement>
                <text><![CDATA[Price]]></text>
            </staticText>
        </jr:tableHeader>
        <jr:detailCell style="table 8_TD" height="20" rowSpan="1">
            <textField>
                <reportElement x="0" y="0" width="119" height="20" uuid="5a44f3b8-f7e4-48ce-bf0a-fc2ca083a979"/>
                <textElement textAlignment="Center" verticalAlignment="Middle">
                    <font size="14" isBold="false"/>
                </textElement>
                <textFieldExpression><![CDATA[$F{ItemPrice}]]></textFieldExpression>
            </textField>
        </jr:detailCell>
    </jr:column>
    <jr:column width="145" uuid="476a5c33-5b9a-4c33-8366-3027b490cab6">
        <jr:tableHeader style="table 5_TH" height="30" rowSpan="1">
            <staticText>
                <reportElement x="0" y="0" width="145" height="30" uuid="235f9670-1447-4079-a00a-5fe046d68514"/>
                <textElement textAlignment="Center" verticalAlignment="Middle">
                    <font size="14" isBold="true"/>
                </textElement>
                <text><![CDATA[Date]]></text>
            </staticText>
        </jr:tableHeader>
        <jr:detailCell style="table 8_TD" height="20" rowSpan="1">
            <textField>
                <reportElement x="0" y="0" width="145" height="20" uuid="5a44f3b8-f7e4-48ce-bf0a-fc2ca083a979"/>
                <textElement textAlignment="Center" verticalAlignment="Middle">
                    <font size="14" isBold="false"/>
                </textElement>
                <textFieldExpression><![CDATA[$F{Date}]]></textFieldExpression>
            </textField>
        </jr:detailCell>
    </jr:column>
</jr:table>

这是我得到的结果。

借款人账单项目是主要关注点。

enter image description here

在调试时，从上述查询生成的查询是当我指定它为 jasper 文件时，在 Debug模式下没有打印任何参数。

SQL query string: SELECT bbd.Bill_Id, bbd.ItemName, bbd.ItemQuantity, bbd.ItemPrice, bbd.Date FROM `borrowerbillsdetails` bbd JOIN `borrowerbills` as bb ON bbd.`Bill_Id` = bb.`Bill_Id` WHERE 0 = 0

而对于其他人，它会创建正确的查询，例如

SQL query string: Select * from borrowerbills where cnic = ?
   2016-10-09 22:20:10 DEBUG JRJdbcQueryExecuter:509 - Parameter #1 (cnic of type java.lang.String): 1610186922393

最佳答案

您的代码正在传递主数据集的值。您应该将参数的值传递给 subDataset。

在您的情况下，代码将是:

<jr:table xmlns:jr="http://jasperreports.sourceforge.net/jasperreports/components" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports/components http://jasperreports.sourceforge.net/xsd/components.xsd">
    <datasetRun subDataset="BillItemsDataSet" uuid="ccd44e17-2c5d-417c-b4a7-554d197ce483">
        <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
        <datasetParameter name="billIds">
            <datasetParameterExpression><![CDATA[$P{billIds}]]></datasetParameterExpression>
        </datasetParameter>
    </datasetRun>

或者您可以借助 $P{REPORT_PARAMETERS_MAP} 传递所有参数

<jr:table xmlns:jr="http://jasperreports.sourceforge.net/jasperreports/components" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports/components http://jasperreports.sourceforge.net/xsd/components.xsd">
    <datasetRun subDataset="BillItemsDataSet" uuid="ccd44e17-2c5d-417c-b4a7-554d197ce483">
        <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
        <parametersMapExpression><![CDATA[$P{REPORT_PARAMETERS_MAP}]]></parametersMapExpression>
    </datasetRun>

关于java - 碧 Jade 报告 : IN Clause is empty in case using subDataset with parameter，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/39946608/

25 4 0
文章推荐： MySQL SUM 和 CASE 可以在 DISTINCTROW 上使用吗？
文章推荐： macos - 苹果操作系统 :/usr/bin/env: bad interpreter: Operation not permitted
文章推荐： MYSQL存储过程: How to delete a just updated record
文章推荐： regex - 使用 match 在只有 bash 的字符串中查找子字符串
行者123
个人简介

我是一名优秀的程序员,十分优秀！

滴滴打车优惠券免费领取
滴滴打车优惠券
全站热门文章
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com