JasperReports没有正确读取参数?

我正在使用Taxe5.5.0.5框架使用Java编写的Web应用程序。这个框架没有现成的JasperReports支持,所以我编写了一个修改Chenillekit的JasperReport服务的服务。我不依赖Chenillekit版本,而是使用JasperReport 3.5.0依赖项。这可能不是必要的信息,但具体一点也不伤人。

总之,我的服务很好。我已经将它内置到webapp中,并且能够编译和输出PDF、XLS、HTML和CSV格式的基本报告。但是,我在获取JasperReport XML文件中的SQL以正确加载参数映射方面遇到了一个大问题。

尝试运行带有startdate和enddate参数的报表时,会出现以下错误。

SQLException: Missing IN or OUT parameter at index:: 1

SQL知识会说这意味着我有某种形式的参数没有传递到SQL中。调试语句向我表明,我正在正确地传递参数,并且至少有些参数正在进入XML报告。

例如,我将三个参数传递给报表、标题、开始日期和结束日期。标题出现在报表的呈现中,但开始日期和结束日期似乎在翻译中丢失了?

我不确定我遗漏了什么,因为几乎相同的代码在我公司的基于JSP TomcatServlet的JasperReports应用程序中起作用。

不管怎样,我将开始显示代码并解释发生了什么:

public StreamResponse getReport(String reportTitle, ExportFormat formMode, Date startDate, Date endDate) {
    Map<String,String> parameterMap = loadParameters(reportTitle);
    Connection conn = null;
    OutputStream os = new ByteArrayOutputStream();

    try{
        conn = Report.getConnection();

        Resource resc = new ContextResource(cimpl, "src/main/webapp/reports/"+reportTitle+".xml");

        log.debug("Calling fillAndExport to fetch the report " + reportTitle);
        log.debug("resource="+resc+"\n"+"formMode="+formMode+"\n"+"parameterMap="+parameterMap+"\n"+"conn="+conn+"\n"+
                "outputStream="+os);

        SimpleDateFormat repDate = new SimpleDateFormat("MM/dd/yyyy HH:mm");
        parameterMap.put("StartDate", repDate.format(startDate));
        parameterMap.put("EndDate", repDate.format(endDate));

        log.debug("StartDate into report: " + parameterMap.get("StartDate"));
        log.debug("EndDate into report: " + parameterMap.get("EndDate"));

        js.fillAndExport(resc, formMode, parameterMap, conn, os);
        SimpleDateFormat sdf = new SimpleDateFormat("MMMddyyyy");

        return new JasperStreamResponse((ByteArrayOutputStream) os, formMode, reportTitle+"-"+sdf.format(startDate)+"-"
                +sdf.format(endDate));
    }catch (Exception e){
        log.error("Caught exception while trying to execute the report fillAndExport service method.");
        throw new TapestryException("Issue executing report", e);
    } finally {
        if(conn != null){
            try {
                conn.close();
            } catch (SQLException e) {
                System.out.println("Could not close the JDBC connection!!");
            }
        }
    }
}

简而言之,我加载报表资源,并添加StartDate和EndDate参数(标题已经在参数map中)。然后,我调用使用fillandexport生成报告的jasperservice。如果没有异常,则返回到流中的浏览器。

以下是给定时间内附带的调试语句,我得到异常:

[DEBUG] AppModule.ReportService Loaded report class: com.moremagic.reports.TriggerReport
[DEBUG] AppModule.ReportService Calling fillAndExport to fetch the report Trigger
[DEBUG] AppModule.ReportService resource=context:src/main/webapp/reports/Trigger.xml
formMode=HTML
parameterMap={Title=Triggering Merchant Commission}
conn=oracle.jdbc.driver.T4CConnection@7c974e4b
outputStream=
[DEBUG] AppModule.ReportService StartDate into report: 08/22/2010 14:19
[DEBUG] AppModule.ReportService EndDate into report: 08/23/2010 14:19
[DEBUG] AppModule.JasperService Constructed configuration: {}
[DEBUG] AppModule.JasperService Invoking method com.moremagic.services.AppModule.buildJasperService(Logger, Map) (at AppModule.java:188).
[DEBUG] AppModule.JasperService Using template -> src/main/webapp/reports/Trigger.xml
[DEBUG] AppModule.JasperService In fillReport, parameterMap is : {EndDate=08/23/2010 14:31, StartDate=08/22/2010 14:31, Title=Triggering Merchant Commission}

[ERROR] AppModule.JasperService Caught exception in fillReport of ReportsServiceImpl {}
net.sf.jasperreports.engine.JRException: Error executing SQL statement for : WebappReport1
Caused by: java.sql.SQLException: Missing IN or OUT parameter at index:: 1

因此,您可以看到这些值一直在参数映射中,直到jasperservice调用japserreports api。现在我将显示JasperService代码和一些报表,这样您就可以看到SQL中断的情况。

JasperService:

/**
 * Fills the report design loaded from the supplied input resource and returns the generated report object.
 * <p/>
 * if parameter <em>jasperPrint<em> not null, the data filled into <em>jasperPrint</em>
 * instead of returns a new jasper print object.
 *
 * @param jasperPrint
 * @param inputResource the input resource (report template file ".jrxml")
 * @param parameterMap  the parameter map
 * @param dataSource    the datasource, either a JRDataSource or an SQL JDBC Connection.
 *
 * @return
 */
public JasperPrint fillReport(JasperPrint jasperPrint, Resource inputResource, Map parameterMap, Object dataSource) throws JRException
{
    JasperReport jasperReport = doCompileReportSource(inputResource);
    JasperPrint actualJasperPrint;

    logger.debug("In fillReport, parameterMap is : " + parameterMap + "\n startDate: " + parameterMap.get("StartDate") + "\n endDate: " + parameterMap.get("EndDate"));

    try
    {
        if (dataSource != null && dataSource instanceof JRDataSource)
            actualJasperPrint = JasperFillManager.fillReport(jasperReport, parameterMap, (JRDataSource) dataSource);
        else if(dataSource != null && dataSource instanceof Connection)
            actualJasperPrint = JasperFillManager.fillReport(jasperReport, parameterMap, (Connection) dataSource);
        else
            actualJasperPrint = JasperFillManager.fillReport(jasperReport, parameterMap);

        if (jasperPrint != null)
        {
            for (Object page : actualJasperPrint.getPages())
                jasperPrint.addPage((JRPrintPage) page);
        }
        else
            jasperPrint = actualJasperPrint;

        return jasperPrint;
    }
    catch (JRException e)
    {
        logger.error("Caught exception in fillReport of ReportsServiceImpl {}", e);
        throw new JRException(e);
    }
}

报告:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE jasperReport PUBLIC "-//JasperReports//DTD Report Design//EN" 
 "http://jasperreports.sourceforge.net/dtds/jasperreport.dtd">
<jasperReport 
    name="WebappReport1" 
    pageWidth="595" 
    pageHeight="842" 
    columnWidth="555" 
    columnSpacing="0" 
    leftMargin="20" 
    rightMargin="20" 
    topMargin="30" 
    whenNoDataType="AllSectionsNoDetail"
    bottomMargin="30"> 
<reportFont name="Arial_Normal" isDefault="true" fontName="Arial" size="9" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
<reportFont name="Arial_Bold" isDefault="false" fontName="Arial" size="9" isBold="true" pdfFontName="Helvetica-Bold" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
<reportFont name="Arial_Italic" isDefault="false" fontName="Arial" size="9" isItalic="true" pdfFontName="Helvetica-Oblique" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
<parameter name="Title" class="java.lang.String"/>
<parameter name="StartDate" class="java.lang.String" />
<parameter name="EndDate" class="java.lang.String" />


<!--This is report query string used to fill data-->
<queryString><![CDATA[
SELECT 
something
from table b
    where
b.ba_timestamp between to_date( $P!{StartDate}, 'MM/DD/YYYY HH24:MI' ) and to_date( $P!{EndDate}, 'MM/DD/YYYY HH24:MI' )+1 
]]></queryString>