How to pass the db connection information and query parameters from controller to JasperReportsMultiFormatView

Question

I am prototyping a web application using Spring MVC 3.0 with JasperReports. I have already done reporting applications using Spring + Jfreechart + iText + Apache POI and been able to use successfully the respective view classes provided by Spring to stream pdfs, xls and images.

This time I want to try to use JasperReports so that I can design my pdfs outside of the application and not have to worry about knowing the underlying api (be that jfreechart, itext, or poi).

Problem

I have a report1.jrxml file that contains a queryString tag with my query with two date parameters. When I test the report through iReport, it compiles and runs successfully. No problems here.

Now I am reading the JasperReports section from the following Spring documentation http://static.springsource.org/spring/docs/3.0.x/spring-framework-reference/htmlsingle/spring-framework-reference.html#view-jasper-reports and I am trying to get the JasperReportsMultiFormatView to work properly, but there is one piece that I am not understanding:

1. How does JasperReportMultiFormatView know the database to connect to (Recall that I have the query embedded in the report itself) ?

2. The documentation states to use the reportDataKey property in the view, but I do not see how this is the solution to my problem.

3. How do you pass parameters?

What can be done

JaperReports provides with a set of xxxManager objects that are responsible for compiling, fiiling, and exporting the report. You could create a custom class that implements the Spring View interface and do something like this:

Connection connection;
ServletOutputStream servletOutputStream = response .getOutputStream();
InputStream reportStream = getServlet().getServletConfig().getServletContext().getResourceAsStream("/reports/report1.jasper");
response.setContentType("application/pdf");
Class.forName("com.mysql.jdbc.Driver");
 connection = DriverManager.getConnection("jdbc:mysql://localhost: 
             3306/flightstats?user=user&password=secret");
JasperRunManager.runReportToPdfStream(reportStream,  
                 servletOutputStream, new HashMap(), connection);
connection.close();
servletOutputStream.flush();
servletOutputStream.close();

What I need

I need to accomplish what the code above those leveraging the Spring classes such as JasperReportsPdfView, JasperReportsXlsView, or even better JasperReportsMultiFormatView

So in summary I need to be able to pass the following from my controller to the jasper report:

1. Parameters
2. Db connection information so that the queryString inside the jasper knows who to run against

This is what I have and the output is a blank PDF document, I am assuming because it does not know how to run the query

@RequestMapping("/reports/**")
@Controller

public class ReportsController {

@RequestMapping(value ="/reports/usage/report", method = RequestMethod.GET)
public ModelAndView handleSimpleReportMulti(HttpServletRequest request, HttpServletResponse response) throws Exception {

    System.out.println("Made it here");

    Map model = new HashMap();
    //model.put("format", "pdf");
    model.put("START_DATE", new String("09-12-2011"));
    model.put("END_DATE", new String("09-17-2011"));

    return new ModelAndView("report1", model);
}
}

Answer 1

I found the answer to my question. I have changed my controller above to this:

@RequestMapping(value ="/reports/usage/report/{format}", method = RequestMethod.GET)
public ModelAndView handleSimpleReportMulti(ModelMap modelMap, @PathVariable("format") String format) throws Exception {

    //Map model = new HashMap();
    modelMap.put("format", format);
    modelMap.put("REPORT_CONNECTION", dataSource.getConnection());
    modelMap.put("START_DATE", new String("09-12-2011"));
    modelMap.put("END_DATE", new String("09-17-2011"));

    return new ModelAndView("report1", modelMap);       
}

I have changed my view.properties to this:

#report1(class)=org.springframework.web.servlet.view.jasperreports.JasperReportsPdfView
report1(class)=org.springframework.web.servlet.view.jasperreports.JasperReportsMultiFormatView
report1.url=/WEB-INF/reports/report1.jasper

I hope this helps.

Thanks