The Table's datasource is ignoring AND clause in query with value passed as an parameter

Question

I am trying to generate report with SQL Query:

SELECT brand.id,brand.brand,model.model FROM brand INNER JOIN model ON brand.id=model.id AND model.id=$P{ID}

It is taking the user input from JavaFX TextField. The Model.id will be replaced with single Integer, say 1 and it should output only one row! But when I am passing this parameters to the JasperReports's report, It is printing all rows available in the table.

The Java Code:

@FXML
public TextField one;

public void click(ActionEvent event) throws JRException, SQLException, ClassNotFoundException, IllegalAccessException, UnsupportedLookAndFeelException, InstantiationException {
    String reportSrcFile = "/home/sample/learn.jrxml";

    // First, compile jrxml file.
    JasperReport jasperReport1 = JasperCompileManager.compileReport(reportSrcFile);

    Connection conn = SqliConnect.getMySQLConnection();

    // Parameters for report
    int two = Integer.parseInt(one.getText());
    Map<String, Object> parameters = new HashMap<String, Object>();
    parameters.put("ID",one.getText());//The Exact Parameter name is "ID" in the jrxml file.

    JasperPrint print = JasperFillManager.fillReport(jasperReport1,
            parameters, conn);

    JasperViewer jv = new JasperViewer(print);
    jv.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
    jv.setTitle("Test Report");

    jv.setVisible(true);
}

Report's template:

<subDataset name="Dataset1" uuid="2691431f-5c6f-403f-94cc-829c17ef1636">
    <queryString>
        <![CDATA[SELECT brand.id,brand.brand,model.model FROM brand INNER JOIN model]]>
    </queryString>
    <field name="id" class="java.lang.Integer"/>
    <field name="brand" class="java.lang.String"/>
    <field name="model" class="java.lang.String"/>
</subDataset>
<parameter name="ID" class="java.lang.String">
    <defaultValueExpression><![CDATA[$P{REPORT_CONTEXT}.getId()]]></defaultValueExpression>
</parameter>
<queryString>
    <![CDATA[SELECT brand.id,brand.brand,model.model FROM brand INNER JOIN model ON brand.id=model.id AND model.id=$P{ID}]]>
</queryString>
<field name="id" class="java.lang.Integer"/>
<field name="brand" class="java.lang.String"/>
<field name="model" class="java.lang.String"/>
<title>
    <band height="79" splitType="Stretch">
        <textField>
            <reportElement x="11" y="16" width="100" height="30" uuid="d1485589-a4cf-4ab9-b896-0ef480beced4"/>
            <textFieldExpression><![CDATA[$P{ID}]]></textFieldExpression>
        </textField>
    </band>
</title>
<detail>
    <band height="250" splitType="Stretch">
        <componentElement>
            <reportElement x="160" y="50" width="200" height="200" uuid="e199bd58-8408-4711-85d5-ba76db9691b7">
                <property name="com.jaspersoft.studio.layout" value="com.jaspersoft.studio.editor.layout.VerticalRowLayout"/>
                <property name="com.jaspersoft.studio.table.style.table_header" value="Table_TH"/>
                <property name="com.jaspersoft.studio.table.style.column_header" value="Table_CH"/>
                <property name="com.jaspersoft.studio.table.style.detail" value="Table_TD"/>
            </reportElement>
            <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="Dataset1" uuid="7fecdfe6-bed4-433a-97f8-43cc6347c660">
                    <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
                </datasetRun>
                <jr:column width="66" uuid="9a6b765f-b6d4-4753-909e-dd091b296654">
                    <jr:columnHeader style="Table_CH" height="30">
                        <staticText>
                            <reportElement x="0" y="0" width="66" height="30" uuid="7e644d1c-a893-49df-a18a-bf788eb6b92a"/>
                            <text><![CDATA[id]]></text>
                        </staticText>
                    </jr:columnHeader>
                    <jr:columnFooter style="Table_CH" height="30"/>
                    <jr:detailCell style="Table_TD" height="30">
                        <textField>
                            <reportElement x="0" y="0" width="66" height="30" uuid="848a53bf-2b2e-46df-a7aa-26fc0b897c85"/>
                            <textFieldExpression><![CDATA[$F{id}]]></textFieldExpression>
                        </textField>
                    </jr:detailCell>
                </jr:column>
                <jr:column width="66" uuid="514580a5-f09c-43d1-952e-d4769c2e4686">
                    <jr:columnHeader style="Table_CH" height="30">
                        <staticText>
                            <reportElement x="0" y="0" width="66" height="30" uuid="c01152e9-1547-472b-946e-92011c02bc94"/>
                            <text><![CDATA[brand]]></text>
                        </staticText>
                    </jr:columnHeader>
                    <jr:columnFooter style="Table_CH" height="30"/>
                    <jr:detailCell style="Table_TD" height="30">
                        <textField>
                            <reportElement x="0" y="0" width="66" height="30" uuid="98c84ae5-b289-459a-b34b-a9becf43b9ce"/>
                            <textFieldExpression><![CDATA[$F{brand}]]></textFieldExpression>
                        </textField>
                    </jr:detailCell>
                </jr:column>
                <jr:column width="66" uuid="5c1d2026-45bc-4ec8-8be4-c444e47d093d">
                    <jr:columnHeader style="Table_CH" height="30">
                        <staticText>
                            <reportElement x="0" y="0" width="66" height="30" uuid="81f76de4-a68b-41c3-a2e8-bbaa625b71be"/>
                            <text><![CDATA[model]]></text>
                        </staticText>
                    </jr:columnHeader>
                    <jr:columnFooter style="Table_CH" height="30"/>
                    <jr:detailCell style="Table_TD" height="30">
                        <textField>
                            <reportElement x="0" y="0" width="66" height="30" uuid="d69c690e-05a4-453a-93ea-9061523975ad"/>
                            <textFieldExpression><![CDATA[$F{model}]]></textFieldExpression>
                        </textField>
                    </jr:detailCell>
                </jr:column>
            </jr:table>
        </componentElement>
    </band>
</detail>

What is the right way to do it?

Update: I have added the parameter for Datasources:

<subDataset name="Dataset1" uuid="2691431f-5c6f-403f-94cc-829c17ef1636">
    <property name="com.jaspersoft.studio.data.sql.tables" value=""/>
    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="Learn"/>
    <parameter name="ID" class="java.lang.String"/>
    <queryString>
        <![CDATA[SELECT brand.id,brand.brand,model.model FROM brand INNER JOIN model ON brand.id=model.id AND model.id=$P{ID}]]>
    </queryString>
    <field name="id" class="java.lang.Integer"/>
    <field name="brand" class="java.lang.String"/>
    <field name="model" class="java.lang.String"/>
</subDataset>

But now it is generating blank report.

1) You are using the query without WHERE clause (Dataset1) 2) You should pass the Map with parameters (*$P{REPORT_PARAMETER_MAP}*) to the Table's datasource. 3) It is better to move Table component to the Title band if you have the main query 4) You can set empty query, I think — Alex K, May 17 '16 at 09:53
Please check the update. I tried but now it generate blank report. — u4547878, May 17 '16 at 10:13
You should pass the parameter to the Table component (via subDataset). The simple way is to set the $P{REPORT_PARAMETER_MAP} — Alex K, May 17 '16 at 11:13
It gives error : net.sf.jasperreports.engine.design.JRValidationException: Report design not valid — u4547878, May 17 '16 at 12:33

shertage · Accepted Answer · 2016-05-17T12:09:07.237

0

The table component in your report uses the 'Dataset1' subdataset, which has no ID parameter defined and contains only a static query. You'll need to declare the ID parameter in the subdataset too, and use it in the query. I hope this helps, Sanda

PS: Please see the edited Raw Paste Data here: http://pastebin.com/ZMjn4jtP

edited May 17 '16 at 12:09

answered May 17 '16 at 09:28

shertage

236
2
4

Now it generate blank report. I have added the parameter as follows: – u4547878 May 17 '16 at 10:08
I updated the post. Am i doing anything wrong? It is generating blank page – u4547878 May 17 '16 at 10:26
Also check the following: the 'id' field in subdataset is of type java.lang.Integer, while the ID parameter is of type java.lang.String. The expression 'model.id=$P{ID}' in the query will return always false in this case. Try to make both field and parameter Integer. – shertage May 17 '16 at 10:30
1

Now, in the table datasetRun element, before the connection expression use the following dataset parameter expression: <![CDATA[$P{ID}]]> – shertage May 17 '16 at 11:14
Now it give me error: Caused by: net.sf.jasperreports.engine.JRException: org.xml.sax.SAXParseException; lineNumber: 83; columnNumber: 84; The element type "datasetParameterExpression" must be terminated by the matching end-tag "". Would you mind editing the code i have pasted in pastebin: http://pastebin.com/ZMjn4jtP – u4547878 May 17 '16 at 11:46
I have got another problem. Please check if you don't mind to answer :) http://stackoverflow.com/questions/37298730/converting-numbers-to-localutf8-bengali-numbers-in-jasper-report-mysql – u4547878 May 18 '16 at 11:53

The Table's datasource is ignoring AND clause in query with value passed as an parameter

1 Answers1