Generate SQL query with library

0

Is it possible to generate the SQL query that will be executed when generating the report?
I found a way to get the query with the parameters, like this

final JasperReport report = JasperCompileManager.compileReport(layoutStream);
String query = report.getQuery().getText();

What I want to achieve is to get the query with the parameter values inserted. Is there a way to achieve this?
I'm using version 6.1.0 but I can probably upgrade if it's necessary.

darko.jankovic_1's picture
Joined: Oct 21 2019 - 11:35pm
Last seen: 1 month 2 weeks ago

1 Answer:

0

So this should be possible. 
Define a string parameter in your jrxml. Set the given parameters as your datasource query and set the query string on run time. 
https://community.jaspersoft.com/documentation/tibco-jaspersoft-studio-u...

Here is an example of a jrxml. I am running on a oracle db datasource, but hard seleting via dual. 
So in your java code via the jasper java api just figure out to set the given parameter.  

<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.4.0.final using JasperReports Library version 6.4.1  -->
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="report name" pageWidth="595" pageHeight="842" whenNoDataType="AllSectionsNoDetail" columnWidth="535" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="af88d3eb-c1fe-4d76-9a0c-e7394dc2ddb4">
    <property name="ireport.zoom" value="3.4522712143931042"/>
    <property name="ireport.x" value="0"/>
    <property name="ireport.y" value="0"/>
    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="wbms\resources\datasources\dataAdapters\Dev12pdb1.xml"/>
    <property name="com.jaspersoft.studio.data.sql.tables" value=""/>
    <style name="style1" isPdfEmbedded="true"/>
    <parameter name="sqlQueryToInject" class="java.lang.String" isForPrompting="false">
        <defaultValueExpression><![CDATA["Select * FROM (select 10 as age , 'Green' as Name from DUAL union select 15 as age , 'Red' as Name from DUAL union select 25 as age , 'Yellow' as Name from DUAL union select 2 as age , 'Blue' as Name from DUAL )"]]></defaultValueExpression>
    </parameter>
    <queryString>
        <![CDATA[ $P!{sqlQueryToInject} ]]>
    </queryString>
    <field name="AGE" class="java.math.BigDecimal"/>
    <field name="NAME" class="java.lang.String"/>
    <group name="Group1">
        <groupExpression><![CDATA[$F{AGE}]]></groupExpression>
        <groupHeader>
            <band height="30"/>
        </groupHeader>
    </group>
    <background>
        <band splitType="Stretch"/>
    </background>
    <detail>
        <band height="50">
            <textField>
                <reportElement x="100" y="0" width="100" height="30" uuid="571c1a0b-812d-43dd-b756-b62289273805"/>
                <textFieldExpression><![CDATA[$F{AGE}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="0" y="0" width="100" height="30" uuid="a6870cc0-efed-46c4-98be-44100faa40b8"/>
                <textFieldExpression><![CDATA[$F{NAME}]]></textFieldExpression>
            </textField>
        </band>
    </detail>
</jasperReport>


 

joseng62's picture
732
Joined: Dec 5 2014 - 2:43am
Last seen: 22 hours 31 min ago
Feedback
randomness